CODEWITHSUNDEEP
sqlsql-server-2008
Jaco Pretorius
Jaco Pretorius

Reputation: 24840

Concatenating large number of rows into a single string

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.

Upvotes: 2

Views: 1661

Answers (3)

codeulike
codeulike

Reputation: 23064

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.

Upvotes: 1

AdaTheDev
AdaTheDev

Reputation: 147224

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.

Upvotes: 1

vulkanino
vulkanino

Reputation: 9124

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

Upvotes: -1

Related Questions