SQL Server truncation and 8192 limitation

Question

Hello, In SQL Server 2005 I am trying to query a varchar(MAX) column which has some rows with text data that exceed the 8192. Yet, In Management Studio I have under Tools --> Options --> Query Results --> Results to Text --> Max numbers of characters displayed in each column = 8192, which is a maximum. Accordingly, it seems the truncation on these rows occurs only due to the limitation imposed by text output.

The only thing I see to get around this is to use a SUBSTRING function to grab say the first 8000 chars, then the next 8000 chars etc. etc. But this is ugly and error prone.

I should mention that SSIS and BCP are not options for me.

Does anyone have a better suggestion? Thanks!

Answer 1

The truncation you are talking about only happens in Management Studio. If you pull the column into another app, it will not be truncated.

There's no way you're using Query Analzyer to talk to SQL Server 2005. Do you mean Management Studio?

Answer 2

Does anyone have a better suggestion?

Option 1. Don't get the query results through that tool. Get the query results from your own App that doesn't have this limitation on resulting output.

Option 2. Don't return the data through general result sets, instead issue PRINT commands which are not limitted in this fashion.

Answer 3

If given a choice I would have the query return the data as "For XML Auto" or "For XML Raw" or "For XML explicit" that way the limitations are much higher and you can do much more with the outputed results.

Answer 4

My solution was a bit round-about but got me there (as long as the output is less than 65535 characters):

1. In SQL Management Studio, set the limit for grid results to 65535 (Tools > Options > Query Results > SQL Server > Results to Grid > Non XML data)
2. Run the query, output to grid
3. Right-click the results, choose "Save Results As..." and save the results to a file
4. Open the file in notepad or similar to get the output