Appending Text to a SQL Text Data Type

31 Dec 2005 8:19 PM

bobnunny

I am trying to use a cursor to create a mass Text field with the results from
the selections from a series of VarChar(8000) values. I know I need to use
UpdateText, but it only seems to store the 1st one it reads. Can anyone help?
Here's my text:

Declare @TriggerText nVarChar(4000)
Declare @ptrval Binary(16)
Declare @Offset Int

-- Create temporary table to hold Text field
Create Table #tempTrigger
    (TextField Text NULL)
Insert Into #tempTrigger Select ''

-- Get Trigger "basis"
Declare curTriggerBasis Insensitive Cursor For
    Select c.Text
        From sysObjects o (nolock)
        Inner Join sysComments c (nolock)
            On o.ID = c.ID
        Where o.Name = 'cttx_Customer'
        Order By ColID
    For Read Only
Open curTriggerBasis
Fetch Next From curTriggerBasis Into @TriggerText
While @@Fetch_Status = 0
    Begin
        Select @ptrval = TEXTPTR(TextField),
                @Offset = DataLength(TextField)
            From #tempTrigger (nolock)
        UpdateText #tempTrigger.TextField @ptrval @Offset 0 @TriggerText
        Fetch Next From curTriggerBasis Into @TriggerText
    End
Close curTriggerBasis
Deallocate curTriggerBasis

Select * From #tempTrigger (nolock)

31 Dec 2005 8:38 PM

Dan Guzman

> but it only seems to store the 1st one it reads

How are you determining this? What does 'SELECT DATALENGTH(TextField) FROM
#tempTrigger' return?

--
Happy Holidays

Dan Guzman
SQL Server MVP

Show quote

"bobnunny" <u17151@uwe> wrote in message news:59ac1c8e96b9c@uwe...
>I am trying to use a cursor to create a mass Text field with the results
>from
> the selections from a series of VarChar(8000) values. I know I need to
> use
> UpdateText, but it only seems to store the 1st one it reads. Can anyone
> help?
> Here's my text:
>
> Declare @TriggerText nVarChar(4000)
> Declare @ptrval Binary(16)
> Declare @Offset Int
>
> -- Create temporary table to hold Text field
> Create Table #tempTrigger
> (TextField Text NULL)
> Insert Into #tempTrigger Select ''
>
> -- Get Trigger "basis"
> Declare curTriggerBasis Insensitive Cursor For
> Select c.Text
> From sysObjects o (nolock)
> Inner Join sysComments c (nolock)
> On o.ID = c.ID
> Where o.Name = 'cttx_Customer'
> Order By ColID
> For Read Only
> Open curTriggerBasis
> Fetch Next From curTriggerBasis Into @TriggerText
> While @@Fetch_Status = 0
> Begin
> Select @ptrval = TEXTPTR(TextField),
> @Offset = DataLength(TextField)
> From #tempTrigger (nolock)
> UpdateText #tempTrigger.TextField @ptrval @Offset 0 @TriggerText
> Fetch Next From curTriggerBasis Into @TriggerText
> End
> Close curTriggerBasis
> Deallocate curTriggerBasis
>
> Select * From #tempTrigger (nolock)

31 Dec 2005 10:20 PM

bobnunny

I've put Print statements in there to check this out. It shows Datalength as
4000 everytime except the last one. BUT, like an idiot I was checking the
loop so hard, but the Select statement at the end will only return the first
4000. Once I changed that to DataLength, it showed it had it all.

Thanx!

Dan Guzman wrote:
Show quote

>> but it only seems to store the 1st one it reads
>
>How are you determining this? What does 'SELECT DATALENGTH(TextField) FROM
>#tempTrigger' return?
>
>>I am trying to use a cursor to create a mass Text field with the results
>>from
>[quoted text clipped - 36 lines]
>>
>> Select * From #tempTrigger (nolock)