SQL Server truncation and 8192 limitation

Question

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

You can export the data to a flat file which will not be truncated. To do this:

1. Right click the Database
2. Click Tasks -> Export Data
3. Select your Data Source (defaults should be fine)
4. Choose "Flat File Destination" for the Destination type.
5. Pick a file name for the output.
6. On the "Specify Table Copy or Query", choose "Write a query to specify the data to transfer"
7. Paste in your query

Remaining steps should be self explanatory. This will output the file to text and you can open it in your favorite text editor.

Answer 2

I also use XML but a slightly different method that gets around most of the issues with XML entitisation.

declare @VeryLongText nvarchar(max) = '';  SELECT top 100 @VeryLongText = @VeryLongText + '  ' + OBJECT_DEFINITION(object_id)  FROM sys.all_objects  WHERE type='P' and is_ms_shipped=1  SELECT LEN(@VeryLongText)  SELECT @VeryLongText AS [processing-instruction(x)] FOR XML PATH('')  PRINT @VeryLongText /*WILL be truncated*/ 

Make sure that the "XML data" limit in SSMS is set sufficiently high!