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.