Thank this author by sharing:
By Sam Bendayan,
I've had to do lots of work recently generating very large dynamic SQL strings, and one of the biggest annoyances is that when you PRINT a VARCHAR(MAX)/NVARCHAR(MAX) string that is longer than 8,000/4,000 characters, SSMS only displays the first 8,000/4,000 characters. This makes it nearly impossible to work in SSMS when generating large SQL strings. I always like to print them out and then copy/paste them to a new window to see if they run, but you can't because of this limitation. I spoke to someone in Microsoft a while back about this and was told that this behavior is 'by design'.
I can't see how that holds water. The whole point of the (MAX) datatypes is that they support very large sizes. So not being able to print them out equals a bug as far as I'm concerned. However, I was able to find an entry in Connect about this (https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=196643) and it looks like it was admitted as a bug in that entry. If you've had this problem and find it annoying, you can follow the link above and vote for the issue.
Now, here is the workaround. I created the attached SP that is able to print out all the characters in these long strings. Instead of doing a PRINT command on the string, call this SP and pass it the string as a variable. You will receive the full length of the string printed out in the output window in SSMS.
As for the details of how it works, here is the scenario:
So, if you have a string that looks like this (the 4,000th position is in red bold font):
....SELECT * FROM sys.objects
SELECT * FROM sys.objects WHERE obj
ect_id LIKE '%' -- (this is the first line of the next 4,000-character chunk)
It prints out the following:
....SELECT * FROM sys.objects -- (this is the last line of the first chunk)
SELECT * FROM sys.objects WHERE object_id LIKE '%' --(this is the first line of the next chunk)
The last line of the first string is not printed out because we know we're going to run into problems if we do. So instead, we end that string prematurely and start the next string right after the premature end of the first one. This solution assumes that you are regularly inserting CR/LF characters in your Dynamic SQL, which I think is a must in order to keep them neatly formatted. Especially if they are very long strings.
Now, the bug...the only problem I haven't fixed yet is that an extra CR/LF is added to the printed out code. Notice the extra CR/LF in the last code sample above. However, this is a minor issue. The main thing is that the code can be copied/pasted into a new window and it will run without any manual massaging. When I have time to work on this further I'll fix the bug and repost the SP on my blog.
In summary, I think you will find this a handy SP for working with very long Dynamic SQL strings. It has served me well in printing out very large Dynamic SQL strings, which I find indispensable in debugging complex Dynamic SQL operations.
We need to find the first occurrence of character ie non numeric data in the string ...
Trim Non-Alpha characters from string
Random 64 Characters alphanumeric String
A bit of a break from the SQL Server side with this great new .NET class developed by new author Jer...
Conversion failed when converting datetime from character string
As a member of SQLServerCentral, you get free access to loads of fresh content: thousands
of articles and SQL scripts, a library of free eBooks, a weekly database news roundup,
a great Q & A platform… And it’s our huge, buzzing community of SQL Server Professionals
that makes it such a success.