Concatenating large number of rows into a single string

Question

I have a table containing a large number of rows. Each row has 2 columns which I need to use - the first is some HTML formatting and the second is the text. I need to concatenate all these rows into a single string value so that I can e-mail this massive string. Due to the large number of rows I can't use a VARCHAR variable - I have to use text. The current solution is to use a cursor to read all the rows and then insert into a temporary table with a single column of type text and then simply append to the single row in that table.

The problem is that the string seems to get cutoff after about 33000 characters. I have absolutely no idea why this is happening.

EDIT: I have taken some of the advice from the answers given here and changed the solution to use VARCHAR(MAX). However I'm still getting cutoff, but in a very odd way. It doesn't seem like the variable is getting an overflow, it's simply not appending all the values.

DECLARE @Result VARCHAR(MAX)
SET @Result = ''

SELECT @Result = @Result + ltrim(isnull(format,'')) + '&nbsp;' + text + '<BR></TD></TR><TR><TD CLASS="GEN" align="left" BGCOLOR="#E9ECFD">' 
FROM SomeTable

The really odd bit is that if I take out the bit where I'm appending all that html at the end of the line, it all works fine. I've even inserted everything into a tmp table first and then did this concatenation select - still no luck. The tmp table contains the correct values (including the html), but the final variable doesn't.

Answer 1

You'd better write the concatenated strings into a file. To write a string to a file, you could use a SP like this: http://www.simple-talk.com/code/WorkingWithFiles/spWriteStringTofile.txt

Answer 2

Due to the large number of rows I can't use a VARCHAR variable - I have to use text

From SQL Server 2005, there is support for VARCHAR(MAX) and NVARCHAR(MAX) to handle large strings over 8000 (4000 for NVARCHAR) characters, up to ~2GB. So as of 2005, you should be looking to use those instead of TEXT/NTEXT which will be being deprecated in a future version of SQL Server.

This support for MAX makes things easier than having to deal with TEXT, so give that a whirl.

Answer 3

Edit: I think the main problem is that you are using varbinary(16) as the pointer to update your text value. A signed 16-bit integer will run out at around 32,768 - so thats probably your problem.

Try using a bigint pointer instead of varbinary

Also note that UPDATETEXT has been deprecated and Microsoft recommends you use UPDATE with a WRITE clause instead - see here.

Example from this msdn page:

UPDATE Production.Document
SET DocumentSummary .WRITE (N'features',28,10)

Similarly, text is on the way out and you should probably use varchar(max) instead.