Appending Text to a SQL Text Data Type

I am trying to use a cursor to create a mass Text field with the results fro
m
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 hel
p?
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)> 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
"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)|||I've put Print statements in there to check this out. It shows Datalength a
s
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:
>How are you determining this? What does 'SELECT DATALENGTH(TextField) FRO
M
>#tempTrigger' return?
>
>[quoted text clipped - 36 lines]