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!

Answer 3

Did you try this simple solution? Only 2 clicks away!

At the query window,

1. set query options to "Results to Grid", run your query
2. Right click on the results tab at the grid corner, save results as any files

You will get all the text you want to see in the file!!! I can see 130,556 characters for my result of a varchar(MAX) field

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

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 = 100
WHILE @i < 100000
BEGIN
    SET @test = @test + STUFF(REPLICATE('_', 98) + CHAR(13) + CHAR(10), 1, LEN(CAST(@i AS nvarchar)), CAST(@i AS nvarchar))
    SET @i = @i + 100
END
SELECT @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

Answer 5

I ran in to this trying to export XML. This is the solution I used:

Select the Result to Grid option, right click the link that shows up in the Results pane, then select Save Results As, choose the All Files file type, give the file a name and click Save. All the xml data is saved correctly to a file.

I'm using SSMS 10, and I could't get Torre's solution to work. The export wizard kept thinking the input column was an image:

The data type for "input column "XML_F52E2B61-18A1-11d1-B105-00805F49916B" (26)" is DT_IMAGE

Answer 6

In SSMS if you select data from a row it is limited to a small number of characters, but if you Edit data from a row, the full value will be there. It might not always be there but if you ctrl-a, ctrl-c then past it in an editor it will all be there.

Answer 7

Another workaround , use HeidiSql for this tricky queries. It does not have the limits in the field lenght.

Answer 8

I usually use XML to get huge debug string as output (using test harness from Luke):

    declare @test nvarchar(max), @i int, @line nvarchar(100)
    set @test = ''; set @i = 100
    while @i < 100000
    begin
        set @test = @test + stuff(replicate('_', 98) + char(13) + char(10), 1, len(cast(@i as nvarchar)), cast(@i as nvarchar))
        set @i = @i + 100
    end
    -- ctrl+d for "results to grid" then click the xml output
    --select cast('<root>' + @test + '</root>' as xml)

-- revised
select @test for xml path(''), type;

Answer 9

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 Analyzer to talk to SQL Server 2005. Do you mean Management Studio?

Answer 10

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.