SQL Server truncation and 8192 limitation
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
You can export the data to a flat file which will not be truncated. To do this:
- Right click the Database
- Click Tasks -> Export Data
- Select your Data Source (defaults should be fine)
- Choose "Flat File Destination" for the Destination type.
- Pick a file name for the output.
- On the "Specify Table Copy or Query", choose "Write a query to specify the data to transfer"
- 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.