PRINT and SELECT Character Limits

trying something a little different today… writing a post that is the examples. A simple one to start off this method of blogging. Enjoy!


		SET NOCOUNT ON
		 
		DECLARE @SQL NVARCHAR (max)
		SET @SQL = 
		'There is a character limit set when outputting the query results to text to 256 character. This message will not print out fully. So the question is:
		"How do i change this?" Well it is quite simplereally: what you need to do is go to go to the menus at the top of the screen, and click on tools ---> 
		options. Then, on the left-hand side, Expand Query Results, and then expand the SQL Server, and highlight "Results to Text". On the right hand side 
		there is an option called "maximum number of characters displayed in each column:. Increase this number (I usually increase to 4000, which is the character 
		limit for PRINT statements... although worth noting that results to grid are capped at 65535 for non-xml data and 2mb for XML) and click ''OK''. You will need 
		to re-open your query windows for this change to take affect.'

		select 'Hello. ' + @SQL

		SET @SQL =  
		'note that this character limit does not apply to a PRINT statement. As stated before, a PRINT statement can be longer. For example, this sentence is pretty long*
		and yet it prints out no issues. 
		
		
		
		( * In fact, the total length of this sentence is actually: '

		DECLARE @SQLLEN nvarchar (3) = LEN (@sql)

		PRINT 'Hello again. ' + @SQL + @SQLLEN +')' +CHAR (10)

		SET NOCOUNT OFF

Author: Richie Lee

Full time computer guy, part time runner. Full time Dad, part time blogger. Knows a thing or two about Pokémon. Knows too much about SQL Agent. Writer of fractured sentences. Maker of the best damn macaroni cheese you've ever tasted.

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