Prevent Truncation for Dynamically Generated T-SQL Results in SSMS

I have recently run into an issue where I run one script that generated another (T-SQL), and for some reason, some of the output is cut off or truncated in SSMS.  I want to prevent this.  This truncation is caused by the settings below being too low for your case.  Altering these setting will significantly decrease the chance of truncation when creating T-SQL scripts in a loop.  Initially I was changing this in one place, and still had the truncation effect me.  Silly me, there are TWO place where you have to change the limit in order to make it function in all scenarios.

This one I do catch, Tools > Options. Expand the path of Query Results > SQL Server > Results to Text and change the Maximum number of characters displayed in each column to the fields maximum of 8192.

The second setting is the one I tend to fail to remember.  This is not just because the Query menu does not show in SSMS if a query window is not open or if you have clicked off the query window to the Object Explorer pane, for example. Open a new query window and ensure it has the focus, then go to Query > Query Options. Here you can see the setting is located in Results > Text.  Change the Maximum number of characters displayed in each column the fields maximum of 8192.

“NOTE: Keep in mind that when you make a setting change for Query Options, it only applies to that immediate window and any new windows you open. It will not affect the options for any other query windows which were open at the time but did not have the focus.”






Leave a Reply

Your email address will not be published. Required fields are marked *