source: http://www.MSSQLTips.com/tip.asp?id=2795 -- printed: 9/4/2015 1:48:47 AM
Prevent Truncation of Dynamically Generated Results in SQL Server Management StudioWritten By: Jugal Shah -- 11/8/2012
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.
SQL Server Management Studio supports query execution results to be displayed in three different ways:
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:
To get started, we will first change the default Results To Grid format to Results to Text using the below steps.
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.
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.
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.
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.
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.
In the Query Options... window click on Result > Text and change the Maximum Number of characters displayed in each column to 8192.
Getting the Full Output
Executing the query again will show the full output without truncation.