Problem with Updatetext in SQL 2000 trying to concatenate multiple text fields

Hi all, I am creating a search table where the keywords field is made
up of several text fields and this is causing me some problems. I can
concatentate the text ok but i can't seem to concatenate matching
records here is the cursor loop. I'm not a fan of cursors but also
didn't see another way of achieving this.

declare @.ptr1 varbinary(16)
declare @.Ptr2 varbinary(16)
declare @.profileid int
declare @.x int

set @.profileid = 0

while @.profileid is not null
begin

select
@.profileid = min([id]),
@.ptr1 = MIN(textptr(text1))
from #holding
where [id] @.profileid

declare c2 cursor fast_forward for
select textptr(searchterms), datalength(searchterms)
from search
where search.[id] = @.profileid

open c2

fetch c2 into @.ptr2, @.x

while @.@.fetch_status = 0
begin
updatetext search.searchterms @.ptr2 null 0 #holding.text1 @.ptr1
fetch c2 into @.ptr2, @.x
end

close c2
deallocate c2

end

The #holding table contains the fields that i want to concatenate and
the search table is the resulting table. This example would loop
through search and find id 1 in search and then append another field
matching id 1 in holding then move onto the next field in turn going
through the whole table.

i.e.

search holding result after each loop
id text id text
1 abc 1 def abcdef
2 ghi 2 jkl ghijkl

When I run this, some of the records concatenate properly but most dont
with the same text being appended to the end of searchterms. i.e loads
of results will end up with jkl tagged onto the end. I can't figure out
when my loop is falliing over!!! Can anyone help?

DanDan (dan_barber2003@.hotmail.com) writes:

Quote:

Originally Posted by

select
@.profileid = min([id]),
@.ptr1 = MIN(textptr(text1))
from #holding
where [id] @.profileid

This does not look good. Is there are any reason to assume that text
pointers are aligned with id?

Either do

SELECT @.profileid = MIN(id)
FROM #holding
WHERE id @.profileid

SELECT @.ptr1 = textptr(text1) FROM #holding WHERE id = @.profileid

Or run a second cursor over #holding.

And, yeah, this is ugly, painful and no fun at all. But it's the only
alternative.

In SQL 2005 there are new data types that replaces text & co, and
which does not have all the restrictions of text. Or the need to
use UPDATETEXT...

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Dan wrote:

Quote:

Originally Posted by

When I run this, some of the records concatenate properly but most dont
with the same text being appended to the end of searchterms. i.e loads
of results will end up with jkl tagged onto the end. I can't figure out
when my loop is falliing over!!! Can anyone help?

I don't know /why/ the loop is doing the wrong thing, but adding some
debugging output after the UPDATETEXT line (e.g. SELECT * FROM SEARCH)
should at least reveal /when/ it's doing the wrong thing.

Also, I would seriously avoid columns named "id".|||Thanks Erland, you hit the nail on the head, i misunderstood what the
textptr did and thought both could be set at the same point. Hopefully
this will be the one and only time i have to use either text
concatenation or cursors. Is is the varchar(max) that replaces text in
SQL Server 2005?

Thanks again

Dan
Erland Sommarskog wrote:

Quote:

Originally Posted by

Dan (dan_barber2003@.hotmail.com) writes:

Quote:

Originally Posted by

select
@.profileid = min([id]),
@.ptr1 = MIN(textptr(text1))
from #holding
where [id] @.profileid

>
This does not look good. Is there are any reason to assume that text
pointers are aligned with id?
>
Either do
>
SELECT @.profileid = MIN(id)
FROM #holding
WHERE id @.profileid
>
SELECT @.ptr1 = textptr(text1) FROM #holding WHERE id = @.profileid
>
Or run a second cursor over #holding.
>
And, yeah, this is ugly, painful and no fun at all. But it's the only
alternative.
>
In SQL 2005 there are new data types that replaces text & co, and
which does not have all the restrictions of text. Or the need to
use UPDATETEXT...
>
>
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
>
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

|||Dan (dan_barber2003@.hotmail.com) writes:

Quote:

Originally Posted by

Is is the varchar(max) that replaces text in SQL Server 2005?

Yes.

And to make it clear, text is still there but deprecated. You will
have to make an explicit change to the tables to use the new goodies.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx