More ntext trouble

I'm trying to build a REPLACE() function for NTEXT values.
I'm facing a lot of trouble doing it, and I think it could be a very common problem.
I think the biggest problem is when one READTEXT a chunk of a NTEXT value, I couldn't find any way to put that chunk into an NVARCHAR in order to postprocess it.
Let me show you some source code:

BEGIN TRAN
DECLARE @.ptrval varbinary(16)
DECLARE @.datalen integer
DECLARE @.chunk nvarchar(4000)
DECLARE @.i integer
DECLARE @.q integer
DECLARE @.maxlenvarchar integer

-- LET THE POINTER @.ptrval POINTS TO THE NTEXT VALUE, GATHER THE REAL LENGTH INTO @.datalen (300000 bytes approx.)
SELECT @.ptrval = TEXTPTR(ntext_value), @.datalen = DATALENGTH(ntext_value)/2
FROM my_table
WHERE id = 8293

SELECT @.i = 0, @.maxlenvarchar = 4000

-- I WANT TO REPLACE ALL OCCURRENCIES OF CHARACTER 'A' WITH CHARACTER 'B'
-- INTO THE NTEXT FIELD. SO, I THOUGHT THAT IT COULD BE POSSIBLE DOING IT THIS WAY:
-- 1) READ THE NTEXT VALUE IN CHUNKS OF 4000 BYTES
-- 2) PUT EACH CHUNK INTO AN NVARCHAR VARIABLE (@.chunk)
-- 3) APPLY THE REPLACE FUNCTION TO EACH @.chunk
-- 4) REBUILD THE NTEXT VALUE AND STORE IT AGAIN INTO THE TABLE

-- HERE, IN THE WHILE LOOP IS PART OF THE JOB, I COULDN'T GO BEYOND THIS
WHILE @.datalen > 0
BEGIN
IF @.datalen > @.maxlenvarchar
SELECT @.q = @.maxlenvarchar
ELSE
SELECT @.q = @.datalen

-- THIS COMMAND READS 4000 BYTES OF THE NTEXT VALUE,..
-- BUT HOW CAN I STORE IT INTO A NVARCHAR IN ORDER TO USE REPLACE() ??
READTEXT my_table.ntext_value @.ptrval @.i @.q

SELECT @.i = @.i + @.maxlenvarchar, @.datalen = @.datalen - @.q

-- I DON'T KNOW HOW TO "PRINT" THE CHUNK, THIS COMMAND PRINTS THE POINTER VALUE (HEX)
PRINT @.ptrval

-- I CAN SEE THE TEXT POINTER IS ALWAYS VALID
PRINT TEXTVALID ('my_table.ntext_value', @.ptrval)
END

COMMIT

-- WHAT I HAVEN'T SEE YET IS THE PART OF REBUILDING THE NTEXT VAL AND WRITE IT AGAIN INTO THE DATABASE.Have you tried using the PATINDEX and UPDATETEXT functions ?|||I've read about them ... but I cannot imagine in which way I should use them in order to do what I want.|||Here you go:

code example (http://www.1aspstreet.com/xq/ASP/txtCodeId.395/lngWid.5/qx/vb/scripts/ShowCode.htm)|||/*
SP on WWW page linked in rnealejr's message would process only a table with one record.
It also "rereplace".
*/

/* DESCRIPTION
Replacing is thought to be very simple, but this is not right in blobs.

I use overlaying SUBSTRING frame of 4000 chars
sliding by (4000+1-len(@.OldStr))
-> 4000 chars sure
Cursor *MUST* be used, because of statement UPDATETEXT,
SQL pseudoprocedure, which does not have FROM part
and needs poiter to blob, unique for each blob column and table row.

Temp tables:
#BlobTable - table with Blobs (to be updated)
#BlobPos - current pos to be sure not to "rereplace" 'A'->'AA' ('XXAXX'->'XXAAAAAAAAAAAA...XX')
#BlobRes - results of single find
#BlobUpd - list for blob updates

Beware of replacing data with table option 'text in row' set !!!
4000 limit is for nvarchar(international,UNICODE), use 8000 varchar for single language.

*/

--INITIALIZATION (creating temp tables, generating some 16kB blob data)
set nocount on
set textsize 2147483647
if object_id('tempdb..#BlobTable') is not null drop table #BlobTable
if object_id('tempdb..#BlobPos') is not null drop table #BlobPos
if object_id('tempdb..#BlobRes') is not null drop table #BlobRes
if object_id('tempdb..#BlobUpd') is not null drop table #BlobUpd
GO
create table #BlobTable (
id int identity(1,1) primary key
,Blob ntext not null
,BlobCopy ntext not null
)
create table #BlobPos (
id int primary key
,ptr varbinary(16) not null
,BlobLen int not null
,BlobPos int not null
,BlobDelta int not null
)
create table #BlobRes (
id int primary key
,Pos int not null
)
create table #BlobUpd (
id int not null
,Pos int not null
)
GO
--generating test data (10 different blobs, about 80000 chars each)
declare @.ptr varbinary(16)
declare @.ptrCopy varbinary(16)
declare @.index int
declare @.addtext nvarchar(4000)
declare @.Counter int
set @.addtext=replicate('X',4000)
while 10>(select count(*) from #BlobTable) begin
insert #BlobTable(Blob,BlobCopy)
select
replicate('X',count(*))+'abcdefghijklmabcdefghijkl m'
,replicate('X',count(*))+'abcdefghijklmabcdefghijk lm'
from #BlobTable
select @.ptr=textptr(Blob),@.index=datalength(Blob)/2,@.ptrCopy=textptr(BlobCopy)
from #BlobTable
where id=SCOPE_IDENTITY()
set @.Counter=0
while @.Counter<20 begin
set @.index=@.index
updatetext #BlobTable.Blob @.ptr @.index 0 @.addtext
set @.Counter=@.Counter+1
end
end
update #BlobTable set
BlobCopy=Blob
GO
--INITIALIZATION-END

--REPLACE ('efgh' WITH 'efgh?')
declare @.OldStr nvarchar(4000) --up to 4000 UNICODE chars
declare @.OldStrLike nvarchar(4000)
declare @.length int
declare @.lengthDelta int
declare @.ptr varbinary(16)
declare @.NewStr nvarchar(4000) --up to 4000 UNICODE chars
declare @.index int
declare @.c cursor
--init of replace
set @.OldStr='efgh'
set @.NewStr='efgh?'
set @.lengthDelta=len(@.NewStr)-len(@.OldStr)
set @.length=len(@.OldStr)
insert #BlobPos(id,ptr,BlobLen,BlobPos,BlobDelta)
select id,ptr=textptr(Blob),BlobLen=datalength(Blob)/2, BlobPos=0,BlobDelta=0
from #BlobTable
--loop of finding
insert #BlobRes(id,Pos)
select bt.id,charindex(@.OldStr,substring(Blob,bp.BlobPos+ 1,4000))
from #BlobTable bt
join #BlobPos bp
on bt.id=bp.id and (bp.BlobPos+@.length)<=bp.BlobLen
while @.@.rowcount>0 begin
insert #BlobUpd(id,Pos)
select br.id,bp.BlobPos+br.Pos-1+bp.BlobDelta
from #BlobRes br
join #BlobPos bp
on br.id=bp.id and br.Pos>0
update bp set
bp.BlobPos=bp.BlobPos+case when br.Pos>0 then br.Pos else 4000+1-len(@.OldStr) end
,bp.BlobDelta=bp.BlobDelta+case when br.Pos>0 then @.lengthDelta else 0 end
from #BlobPos bp
join #BlobRes br
on bp.id=br.id
delete #BlobRes
insert #BlobRes(id,Pos)
select bt.id,charindex(@.OldStr,substring(Blob,bp.BlobPos+ 1,4000))
from #BlobTable bt
join #BlobPos bp
on bt.id=bp.id and (bp.BlobPos+@.length)<=bp.BlobLen
end
--update of blob by list
set @.c=cursor local forward_only static for
select bu.Pos,bp.ptr
from #BlobUpd bu
join #BlobPos bp
on bu.id=bp.id
open @.c
fetch next from @.c into @.index,@.ptr
while @.@.fetch_status=0 begin
updatetext #BlobTable.Blob @.ptr @.index @.length @.NewStr
fetch next from @.c into @.index,@.ptr
end
close @.c
deallocate @.c
GO
--END OF REPLACE

--FINALIZE
set nocount off
select * from #BlobTable
drop table #BlobTable
GO

/*
This algorithm is really slow, especially on large blobs.
I found many fast optimalizations for this almost simple algorithm,
but I realized that I am not so much interested in blobs.
I should spend a week of freetime or more to merge ideas and optimize, too much for fun.
*/

More Control over Data Driven Subscription email content

I really like the data driven subscription functionality but I'm having
trouble with the nitty gritty of customizing subsctiption email content.
Here's my example - we send out a report each week to those who have not
booked time against our project management system. I want to include a
hyperlink in the comments section of the email that takes the recipient to
their timesheet. I thought this would be simple because RS has the ability
to include a hyperlink to the report itself.
In addition, I can't seem to access any reportserver global values other
than the report execution time and report name (these are included in the
MSDN walk-throughs as well). Does anyone know what parameters, etc. are
available to be parsed into a subscription email?
Has anyone had luck adding formatting like carriage returns, etc. to the
email comments section? I've tried ascii values but no luck.Hi John,
An option for this problem would be to create a report that is parameterised
per person. This report could contain some text to explain that time needs to
be captured and you could add an action on a text box using the current
person name (or id) that would link to the timesheet system.
Your subscription could then mail out rendered versions of these reports in
HTML format which would be embedded in the body of the email. From a users
point of view, it would do the trick.
To the best of my knowledge, the only two variables available via the
subscription engine are the two listed below. Maybe an option for SP3 is to
include column values from the subscription table as variables?
Lastly, to do fancy formatting in the comments section you need to embed
HTML tags. So, your new line would need the <BR> or <P> tag. (of course, this
is also an option to include a link in the mail that would link to the login
page of your timesheet system...)
"john" wrote:
> I really like the data driven subscription functionality but I'm having
> trouble with the nitty gritty of customizing subsctiption email content.
> Here's my example - we send out a report each week to those who have not
> booked time against our project management system. I want to include a
> hyperlink in the comments section of the email that takes the recipient to
> their timesheet. I thought this would be simple because RS has the ability
> to include a hyperlink to the report itself.
> In addition, I can't seem to access any reportserver global values other
> than the report execution time and report name (these are included in the
> MSDN walk-throughs as well). Does anyone know what parameters, etc. are
> available to be parsed into a subscription email?
> Has anyone had luck adding formatting like carriage returns, etc. to the
> email comments section? I've tried ascii values but no luck.

More Blank Page Trouble

I've got a report that is printing a blank page between every page. I've
always set all my margins to zero so I don't experience this problem.
However, on this report, it is a landscape report (11 x 8.5 in), and the
report width is less than 9.5 inches. The margins are set to zero. For some
reason, I'm getting the blank page problem. I think this began to occur when
I added a group.
People have mentioned that I need to see what the printer's printing area
is. I have looked through all the printer properties and they only specify
page size (11 x 8.5). There is nothing specified about the size of the
printing area.
Any ideas?On May 25, 12:16 pm, Liz <L...@.discussions.microsoft.com> wrote:
> I've got a report that is printing a blank page between every page. I've
> always set all my margins to zero so I don't experience this problem.
> However, on this report, it is a landscape report (11 x 8.5 in), and the
> report width is less than 9.5 inches. The margins are set to zero. For some
> reason, I'm getting the blank page problem. I think this began to occur when
> I added a group.
> People have mentioned that I need to see what the printer's printing area
> is. I have looked through all the printer properties and they only specify
> page size (11 x 8.5). There is nothing specified about the size of the
> printing area.
> Any ideas?
If you are having this problem when exporting to Excel, make sure that
if you have multiple table/matrix controls on the report, they are all
the exact same width. If you are exporting to PDF, put two or more
table/matrix controls (that are vertically adjacent) inside rectangle
controls. Hope this helps.
Regards,
Enrique Martinez
Sr. Software Consultant

Money in SQL and ASP.NET

Hi,
I'm having some trouble with my asp.net page and my sql database. What I'm trying to do is allow the user to upload an number to the database, the number is a money amount like 2.00 (￡2.00) or 20.00(￡20.00). I've tried using money and smallmoney datatypes but the numbers usually end up looking like this in the database...
I enter 2.00 and in the database it looks like 2.00000, and even if I enter the information directly into the database I get the same results. I'm not going to be using big numbers with lots of decimal places like this 1000,000,0000. Can anyone help me? All I want is to know what to set the value to on my aspx page and what setting to set the field to in my database, I'd just like two pound to appear as 2.00. any help would be great.

I'm using Microsoft Visual Web Developer 2005 Express Edition and Microsoft SQL Server 2005 if that helps.

Thanks.

Hi,

The money or smallmoney datatypes are the right ones to use in your case. By design, they have four decimal at the end and you can find relevent infromation related to these two from Books Online. As to your question, you use either of these types in your database to hold your data and when it is time to show your data on your asp.net page, you can format them into what you need. For example, {0,c2} will give you two decimals as you want. In GridView:

<asp:BoundField HeaderText="Price/Unit" DataField="UnitPrice" DataFormatString="{0:c2}"HtmlEncode="false"> </asp:BoundField>

Or

<asp:TemplateFieldHeaderText="UnitPrice"><ItemTemplate><asp:LabelID="Label2"runat="server"Text='<%# Eval("UnitPrice","{0:c2}") %>'></asp:Label></ItemTemplate></asp:TemplateField>|||

Hi,

You don't say what type of controls you're trying to display this in. However, one simple way (that I think you can apply to any text box or label control) is this:

TextBox1.Text = (512.23).ToString("c")

If you need to set the page to a different currency than your own, one way is to set the UICulture of the page directive:

<%@. Page Language="VB" AutoEventWireup="false" UICulture="en-GB" CodeFile="Default4.aspx.vb" Inherits="Default4" %>

You can also set this in the web.config file (under globalization)

How the numbers are stored in SQL aren't relevant to how they'll be displayed - as you've discovered!

Hope this helps.

Paul

Mon- Fri Bit fields Query

I am having trouble figuring out how to complete this any help is appreiciated.

I have Mon , Tues , Weds , Thurs , Fri as bit fields I need to write a query for a report to see if the value is true, and display M, T, W, Th, F in a column Days if they are scheduled that day

example:

Mon and Wed I got on the bus the checkboxes are set to True, I want one column in the report Days: to display M, W

Ray:

Is something like this sufficient?

set nocount on

declare @.monStr varchar (11) set @.monStr = 'M,'
declare @.tueStr varchar (11) set @.tueStr = 'T,'
declare @.wedStr varchar (11) set @.wedStr = 'W,'
declare @.thuStr varchar (11) set @.thuStr = 'Th,'
declare @.friStr varchar (11) set @.friStr = 'F,'

declare @.recurringEvent table
( eventName varchar (20) not null,
Mon bit,
Tues bit,
Wed bit,
Thur bit,
Fri bit
)

insert into @.recurringEvent values ('Ride Bus', 1,0,1,0,0)
insert into @.recurringEvent values ('Go to Classes', 1,0,1,1,0)
insert into @.recurringEvent values ('Sleep @. night', 1,1,1,1,1)
insert into @.recurringEvent values ('Feed Dog', 1,0,1,0,1)
insert into @.recurringEvent values ('Feed Gator', 0,0,0,0,0)

select eventName,
case when convert (tinyint, mon) + convert (tinyint, tues)
+ convert (tinyint, wed) + convert (tinyint, thur)
+ convert (tinyint, fri) = 0
then ''
else left (left (@.monStr, 2 * mon)
+ left (@.tueStr, 2 * tues)
+ left (@.wedStr, 2 * wed)
+ left (@.thuStr, 3 * thur)
+ left (@.friStr, 2 * fri),
datalength (left (@.monStr, 2 * mon)
+ left (@.tueStr, 2 * tues)
+ left (@.wedStr, 2 * wed)
+ left (@.thuStr, 3 * thur)
+ left (@.friStr, 2 * fri)) - 1
)
end as Days
from @.recurringEvent

-- -
-- S A M P L E O U T P U T
-- -

-- eventName Days
-- -- -
-- Ride Bus M,W
-- Go to Classes M,W,Th
-- Sleep @. night M,T,W,Th,F
-- Feed Dog M,W,F
-- Feed Gator

|||

I prefer the case construct

case when mon=0 and tues=0 and wed=0 and thur=0 and fri=0

over

case when convert (tinyint, mon) + convert (tinyint, tues)
+ convert (tinyint, wed) + convert (tinyint, thur)
+ convert (tinyint, fri) = 0

Sorry. I just don't really like the look of this particular query.

Dave

|||Yea I'm not able to get this to work I'm going to look into it more this has to be a common scenerio.|||

Mugambo's code will return NULL if one of the data fields is NULL for any day of the week. This code should always work because NULL's and 0's will be caught by the ELSE for each test and an empty string will be returned.

set nocount on

declare @.recurringEvent table
( eventName varchar (20) not null,
Mon bit,
Tues bit,
Wed bit,
Thur bit,
Fri bit
)

insert into @.recurringEvent values ('Ride Bus', 1,NULL,1,0,0)
insert into @.recurringEvent values ('Go to Classes', 1,0,1,1,0)
insert into @.recurringEvent values ('Sleep @. night', 1,1,1,1,1)
insert into @.recurringEvent values ('Feed Dog', 1,0,1,0,1)
insert into @.recurringEvent values ('Feed Gator', 0,0,0,0,0)

Select EventName,
Replace(RTrim(
Case When Mon = 1 Then 'M ' Else '' End
+ Case When Tues = 1 Then 'T ' Else '' End
+ Case When Wed = 1 Then 'W ' Else '' End
+ Case When Thur = 1 Then 'TH ' Else '' End
+ Case When Fri = 1 Then 'F ' Else '' End)
, ' ', ',')
From @.RecurringEvent

|||Ok I just kind of dont understand how to use the code?|||

Thanks for picking me up Matros; I knew I didn't like what I had but I was too busy to continue with it.

Dave

|||

Ray,

The code posted here uses a table variable (which you won't need) so that the code can be tested. Table variable exist in memory only for the duration of the query, so no table structure changes occur. This means you can copy/paste the code, as is, in to a query window so you can run it and, more importantly, learn from it.

In your query, you will need to add this part...

Replace(RTrim(
Case When Mon = 1 Then 'M ' Else '' End
+ Case When Tues = 1 Then 'T ' Else '' End
+ Case When Wed = 1 Then 'W ' Else '' End
+ Case When Thur = 1 Then 'TH ' Else '' End
+ Case When Fri = 1 Then 'F ' Else '' End)
, ' ', ',') As WeekdayUsage

as though it were another field being returned from the query. Of course, this assumes that the bit field you use to store Monday usage is named Mon, and Tuesday's field is named Tue, etc..

If you continue to have problems implementing this solution, perhaps it would be best to post the existing query so that we may assist further.

|||

I was playing around with the code, and I don't believe this is what I am trying to do let me explain a lil more.

I have a table that does a transportation schedule. It has Mon, Tues, Wed, Thurs, and Fri as bit data types so that I may have checkboxes on the front-end. I need to build a report in reporting services that will list the days attend like so:

Data Types:

Rec# int Not Null PK Indentity Seed

Name Varchar(25) Null

Pickup Varchar(25) Null

Time Smalldatetime Null

Mon Bit Null

Tues Bit Null

Wed Bit Null

Thurs Bit Null

Fri Bit Null

Report Ex:

Name: Pickup: Time: (Days Attend: "Virtual Column doesn't exist in database")

Bill Center City 9:00am (M,W "True Value in database is True or 1")

Robert West Ave. 9:30am (W,Th,F "True Value in database is True or 1")

Can I do a case statement for each, and cancatenate them to display together, also I need them all to display in the same column?

|||That is exactly what mastros' code does:

Replace(RTrim(
Case When Mon = 1 Then 'M ' Else '' End
+ Case When Tues = 1 Then 'T ' Else '' End
+ Case When Wed = 1 Then 'W ' Else '' End
+ Case When Thur = 1 Then 'TH ' Else '' End
+ Case When Fri = 1 Then 'F ' Else '' End)
, ' ', ',') As DaysAttend

The output of this will be a column called DaysAttend which has a value of "M,W".

|||

If I build a view for the report how would I use the code?

Select PickupTime, Vehicle#, Destination, Mon, Tues, Wed, Thurs, Fri

Replace(RTrim(

Case When Mon = 1 Then 'M ' Else '' End

+ Case When Tues = 1 Then 'T ' Else '' End

+ Case When Wed = 1 Then 'W ' Else '' End

+ Case When Thurs = 1 Then 'TH ' Else '' End

+ Case When Fri = 1 Then 'F ' Else '' End)

, ' ', ',') As DaysAttend

From Schedule

|||Sweet I got it works great thanks..... Alot everyone. I