SQL Server truncation and 8192 limitation

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.

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=1SELECT 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!

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

UPDATE: To demonstrate that this works, here's some SQL that selects a single 100,000 character column. If I save the grid output to a csv file, all 100,000 characters are there with no truncation.

DECLARE @test nvarchar(MAX), @i int, @line nvarchar(100)SET @test = ''; SET @i = 100WHILE @i < 100000BEGIN    SET @test = @test + STUFF(REPLICATE('_', 98) + CHAR(13) + CHAR(10), 1, LEN(CAST(@i AS nvarchar)), CAST(@i AS nvarchar))    SET @i = @i + 100ENDSELECT @test

Notes:

1. It doesn't seem to make any difference what the character length setting is, as I orignally thought.
2. I'm using SQL 2008 R2 (both the server and Management Studio)
3. It doesn't seem to make a difference if the long column is stored in a local variable (as in this example), or selected from an actual table