Scripts

Break the 8192 Character Limit

Occassionaly you’ll run into a point where you aren’t getting enough characters in the Text output of SSMS. Fortunately; there’s a quick thing you can do (within TSQL), and you don’t have to mess around with setting the results to Text Output either through the SSMS GUI, or CTRL+T.

You simply add a single line at the end of your statement.

FOR XML PATH(''), TYPE

You can do this after any statement and see what kind of results you get. They will be displayed in another Tab. You’ll get the XML syntax with that but there’s another way you can do it below.

Example:

use master;
set nocount on
select
name
from
sys.databases
for xml path(''), type

Or… If you want the results in straight text without all the XML syntax ( that you can simply copy and paste into something else ), you can put the results into a variable, and select the variable SELECT @MyVariable, and the xml path will list only text after you click on the link. Like so…

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s