Prevent Truncation of Dynamically Generated Results in SQL Server Management Studio

By:   |   Updated: 2012-11-08   |   Comments (8)   |   Related: > SQL Server Management Studio Configuration


Problem

While working with the Results to Text option in SSMS, you may come across a situation where the output from dynamically generated data is truncated. In this article I will guide you on how to fix this issue and print all the text for the Results to Text option.

Solution

SQL Server Management Studio supports query execution results to be displayed in three different ways:

  • Results to Grid
  • Results to Text
  • Results to File

Results to Grid is the default format for SSMS. but in this tip we will use the Results To Text format.

Results to Text format is very useful for the below scenarios:

  • Generating and Printing Dynamic Data
  • Reading Long Text data without having to scroll
  • Dragging Text from the Results to a Query window
  • Selecting a particular string from the result

To get started, we will first change the default Results To Grid format to Results to Text using the below steps.

Step 1
In SQL Server Management Studio, under the Tools menu, click Options as shown in the image below:

SSMS

Step 2
In the Options dialog box, expand Query Results, expand SQL Server and then select General as shown in the image below. In the right side panel choose Results To Text option from the Default destination for results drop down list.

Click OK to save the changes as shown in the image below. The changes will go into effect once you open a New Query window.

SSMS Option Window

Another way to change the Results to text option is go to View > Toolbars > select SQL Editor (If not selected). Then select the below highlighted button to enable Results To Text option. You can also use the shortcut CTRL+T.

SSMS Toolbar

Example of Results to Text Option

In the below query I am generating the output query for the log-shipping monitor, but it is not showing the full output. You can see that the output command that I am dynamically generating is getting cut off.  The reason for this is that the default Results to Text option's maximum number of characters displayed is set to 256 characters. That's why the rest of the text output is truncated.

 
Dynamic Query

To prevent truncation of the output for the Results to Text option, you have to change the Maximum number of characters displayed in each column option to 8192 as shown below.

Changing the Setting From The Tools Menu

In the Options dialog box of Tools Menu, expand Query Results, expand SQL Server and then select Results to Text as shown in the image below. In the right side panel change the value of Maximum number of characters displayed in each column to 8192. Click OK to save the changes as shown in the image below. The changes will go into effect once you open a new query window.

SSMS Toolbar

Changing the Setting From The Query Menu

Changing the Maximum Number of characters displayed in each column from the Query menu will give you advantage of setting the maximum characters for the current query window only. The Query menu only appear in SSMS if you have a Query window open and the window is active. If you have selected the Object Explorer Pane or other options, the Query menu will not appear.

To change the max characters setting for the Results to Text options using the Query menu, first you have to click on the Query window, click on Query menu and go to Query Options... as shown below.

Query Menu

In the Query Options... window click on Result > Text and change the Maximum Number of characters displayed in each column to 8192.

Query Options Window

Getting the Full Output

Executing the query again will show the full output without truncation.

Query Output After Changing the Max character
Next Steps
  • Check the Maximum number of characters setting when running queries that generate dynamic output
  • Once you have the output displayed in the Results pane, you can simplify drag the text to a query window
  • Try the other SSMS results options as well


sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip



About the author
MSSQLTips author Jugal Shah Jugal Shah has 8+ years of extensive SQL Server experience and has worked on SQL Server 2000, 2005, 2008 and 2008 R2.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips


Article Last Updated: 2012-11-08

Comments For This Article




Friday, February 24, 2017 - 6:56:50 AM - Francis McFaul Back To Top (46639)

Thank you for the great tip!  However, since this SSMS option is set for each individual user, how could you set this for a SQL Agent job?  Is it based on the owner of the job, or what account the job is running under?  Would you need to log on as the SQL Agent service account and change the optiont, if using a domain account?  How would you set this option if a domain account isn't being used for the service?

Tuesday, February 7, 2017 - 12:23:48 PM - Julia Back To Top (46077)

 Could you please help how to do this in sqlcmd

 

Tuesday, August 23, 2016 - 11:33:50 AM - Scott Back To Top (43171)

If using XML and you want to avoid having <,>, and LF entitized, use this form.  TYPE says to return XML instead of NVARCHAR, and the .value function converts it to NVARCHAR but changes &lt;, &gt;, etc back to the original character.

SET @text = ( SELECT ....
                      FOR XML PATH(''), TYPE ).value('.', 'nvarchar(max)')

When generating a long script, spread PRINT statements throughout instead of concatenating into one huge string.

Alternatively, print 8000 chars at a time with something to mark the extra breaks.

PRINT @text;
IF LEN(@text) > 8000 BEGIN
    PRINT '-- REMOVE THIS LINE BREAK'
    PRINT SUBSTRING(@text, 8001, 8000);
END
IF LEN(@text) > 16000 BEGIN
    PRINT '-- REMOVE THIS LINE BREAK'
    PRINT SUBSTRING(@text, 16001, 8000);
END
IF LEN(@text) > 24000 BEGIN
    PRINT '-- REMOVE THIS LINE BREAK'
    PRINT SUBSTRING(@text, 24001, 8000);
END
-- Repeat up to maximum possible length

Saturday, November 17, 2012 - 2:36:22 PM - Deron S Dilger Back To Top (20395)

When I need to output column values for text outputs larger than the 8192 character limit I use "output to text file."  But I do believe this still introduces a carriage-return+linefeed (aka CrLf) into the data at the same point.  I then manually clean those with a regex in my text editor.

Friday, November 16, 2012 - 8:55:18 AM - Eilert Hjelmeseth Back To Top (20378)

Siva, results to text maintains line breaks and tabs (e.g., CHAR(10) or CHAR(9))

 

Sam, to print results to text with more than 8192 characters, I typically do results to grid but cast it as XML (beware of any potential <> characters in the results...).The below example should return 10,000 lines when casting as xml, but if you don't cast to xml and just do results to text you won't get all the data.

 

I believe this method has a cap on it as well but I've never reached it.

 

DECLARE @text NVARCHAR(MAX);

SET @text = (
    SELECT
        CHAR(10) + CAST(A.[number] AS NVARCHAR(MAX))
    FROM (SELECT TOP 100 [number] FROM master..spt_values) A
    CROSS JOIN (SELECT TOP 100 [number] FROM master..spt_values) B
    ORDER BY A.[number]
    FOR XML PATH('')
);

SELECT CAST(@text AS XML);

Wednesday, November 14, 2012 - 5:48:45 PM - sam Back To Top (20344)

Great post. Can you please let me know what do I do if I want to print the results to text with more than 8192 characters.

Wednesday, November 14, 2012 - 10:44:21 AM - Jugal Back To Top (20337)
  • Generating and Printing Dynamic Data
  • Reading Long Text data without having to scroll
  • Dragging Text from the Results to a Query window
  • Selecting a particular string from the result

Wednesday, November 14, 2012 - 7:38:53 AM - Siva Prasad Back To Top (20334)

Hi Jugal,

nice article bro. can you explain me the reasons why do we actually have to change the execution results to 'Results to Text' from the default 'Results to Grid'. i know only one reason for it. it is only because of memory issue since Results to Grid consumes more memory. can you give me any other reasons why we have to change the Execution results from default value?

 

Regards,

Siva Prasad

 














get free sql tips
agree to terms