How to print VARCHAR(MAX) using Print Statement?

Question

I have a code which is:

DECLARE @Script VARCHAR(MAX)

SELECT @Script = definition FROM manged.sys.all_sql_modules sq
where sq.object_id = (SELECT object_id from managed.sys.objects 
Where type = 'P' and Name = 'usp_gen_data')

Declare @Pos int

SELECT  @pos=CHARINDEX(CHAR(13)+CHAR(10),@script,7500)

PRINT SUBSTRING(@Script,1,@Pos)

PRINT SUBSTRING(@script,@pos,8000)

The length of the Script is around 10,000 Characters and Since I am using print Statement which can hold only max of 8000. So I am using two print statements.

The problem is when I have a script which is of say 18000 characters then I used to use 3 print statements.

So Is there a way that I could set the number of print statements depending on the length of the script?

Solution 1

You could do a WHILE loop based on the count on your script length divided by 8000.

EG:

DECLARE @Counter INT
SET @Counter = 0
DECLARE @TotalPrints INT
SET @TotalPrints = (LEN(@script) / 8000) + 1
WHILE @Counter < @TotalPrints 
BEGIN
    -- Do your printing...
    SET @Counter = @Counter + 1
END

Solution 2

I know it's an old question, but what I did is not mentioned here.

For me the following worked.

DECLARE @info NVARCHAR(MAX)

--SET @info to something big

PRINT CAST(@info AS NTEXT)

Solution 3

The following workaround does not use the PRINT statement. It works well in combination with the SQL Server Management Studio.

SELECT CAST('<root><![CDATA[' + @MyLongString + ']]></root>' AS XML)

You can click on the returned XML to expand it in the built-in XML viewer.

There is a pretty generous client side limit on the displayed size. Go to Tools/Options/Query Results/SQL Server/Results to Grid/XML data to adjust it if needed.

Solution 4

Here is how this should be done:

DECLARE @String NVARCHAR(MAX);
DECLARE @CurrentEnd BIGINT; /* track the length of the next substring */
DECLARE @offset tinyint; /*tracks the amount of offset needed */
set @string = replace(  replace(@string, char(13) + char(10), char(10))   , char(13), char(10))

WHILE LEN(@String) > 1
BEGIN
    IF CHARINDEX(CHAR(10), @String) between 1 AND 4000
    BEGIN
           SET @CurrentEnd =  CHARINDEX(char(10), @String) -1
           set @offset = 2
    END
    ELSE
    BEGIN
           SET @CurrentEnd = 4000
            set @offset = 1
    END   
    PRINT SUBSTRING(@String, 1, @CurrentEnd) 
    set @string = SUBSTRING(@String, @CurrentEnd+@offset, LEN(@String))   
END /*End While loop*/

Taken from http://ask.sqlservercentral.com/questions/3102/any-way-around-the-print-limit-of-nvarcharmax-in-s.html

Solution 5

Came across this question and wanted something more simple... Try the following:

SELECT [processing-instruction(x)]=@Script FOR XML PATH(''),TYPE

View more solutions

Comments

[peter]

If you look at my code I am also using the @Pos variable to find the line break and print accordingly. So How could I use that in your code.

[Rohit Sharma]

@peter You can just take the current SUBSTR and look at only the part you are dealing with at the time and iterate on that or if you know that there will be a line break before the 8k limit each time then just do the WHILE based on finding line breaks.

[Rohit Sharma]

@peter can you loop based on the line breaks? Eg look for linebreak, if found print up to line break, substr from line break to next 8k chars, search, print, new substr etc?

[peter]

Yes I can look for line break but the problem is: I am looking for line breaks after 7500 characters so in this case I found line break at 7567 character so First I am printing from 1 to 7567 characters and then I am printing from 7567 to reamining characters but I should find the second line break from 7567+7500 characters and keep on doing that. How do i do that

[Rohit Sharma]

@peter In your while loop, save the position you last found the line break and then do a SUBSTR with the last found line break as the starting position + 7500. That will give you the next string to search within and then keep doing the same logic until you are beyond the length of the script string.

[Stefan Steiger]

Caution, if script is SQL code, this will BREAK up a statement right in the middle... Example: SELECT ISN (newline here) ULL(SUM(Whatever), 0.0)

[Jirka Hanika]

Did anyone apply this method to real data with success? I'm using SQL Server 2008 R2 SP2. SQL Server Management Studio as a client. PRINT documentation seems to promise truncation to 4K/8K characters (ntext/text), but strangely, I only see truncation to 16K characters which I'm not sure where it is coming from and how to disable it.

[gordy]

@JirkaHanika I see the same truncation at 16k using convert(text, @vmax)

[Jirka Hanika]

@gordy - So it seems to me that this method does not really work in SSMS.

[Iain Samuel McLean Elder]

+1. But this method encodes characters that have a special meaning in XML. For example, < is replaced with <.

[Alex KeySmith]

Worked fine for me (on my rather backwards setup) of SSMS 2012 and SQL 2005 mode 80.

[Admin]

This works for me on SQL 2008 R2 SP2 (10.50.1600) using either CAST() or CONVERT(), and on SQL 2008 SP2 (10.0.5500).

[Martin Smith]

I see truncation after 16,002 characters, still longer than max though. DECLARE @info NVARCHAR(MAX) = 'A';SET @info = REPLICATE(@info, 16000) + 'BC This is not printed';PRINT @info;PRINT CAST(@info AS NTEXT);

[Rob.Kachmar]

Great technique! BTW, the actual article which originated this technique was from SQLServerCentral.com >>> sqlservercentral.com/scripts/Print/63240

[Rob.Kachmar]

I would strong advise against using this solution. The correct technique is below from @ben-b BTW, LENGTH() isn't even a function in SQL Server

[GaTechThomas]

For those of you who are able to run this successfully, please specify what client you're using and what settings you're using for query results.

[Ali U]

you can write script without <root>.... like: SELECT CAST(@MyLongString AS XML)

[Felix Bayer]

More simple would be SELECT CAST(@STMT AS XML) as already stated in another comment. Produces exactly the same output and is indeed less complicated than creating a stored procedure for output.

[Edyn]

@Felix While that would be much simpler, it doesn't quite work for SQL. Casting to XML tries to convert the SQL text to XML. It will replace <, >, and & with &lt;, &gt; and &amp; and it won't handle chars not allowed in XML. Additionally, if you have a situation where you do a comparison of < and then >, it thinks that's an element and throws an invalid node error.

[Jirka Hanika]

@aliyouhannaei - Yes and no. You are right that the root element isn't strictly necessary. But, without the CDATA section, your method starts having trouble with some strings. Especially those that contain <. If they aren't XML, the query will usually error out. If they are XML, the string may end up reformatted into another "equivalent" XML form.

[Jirka Hanika]

@IainElder - That's a good point and there's a workaround for it from Adam Machanic. It's this: SELECT @MyLongString AS [processing-instruction(x)] FOR XML PATH(''). The string will be wrapped in a PI called "x", but the PI won't be wrapped in another element (because of PATH('')).

[Pedram]

Thanks for the answer! It helped me!

[izzy]

Worked for me in SQL Server 2014 using SSMS 2014

[Johnny Bones]

This worked for me, but it also chopped one of my field names in half. So, if I used this method to PRINT (@string) and then EXECUTE (@string), the EXECUTE fails.

[jumxozizi]

ntext , text, and image data types will be removed in a future version of Microsoft SQL Server. Avoid using these data types in new development work, and plan to modify applications that currently use them.

[RAmPE]

Worked in SQL 1016 SSMS build 13.0.16100.1 running against SQL 2016. I went with a cursor to avoid future issues and the potential of the string values going over 16k characters.

[ErikE]

Ah yes, the default limit is 2 MB. My string cut off at 2,097,145 characters (7 characters shy of 2 MB). Thanks for the note about changing the configuration value for max XML size.

[Zarepheth]

I just added Ben B's solution as a temporary stored procedure. Keeps my scripts a bit cleaner, but I agree that it's a lot of lines for debugging.

[shiggity]

The function is LEN() not LENGTH()

[Randy Burden]

This doesn't work for me as the PRINT function adds line breaks in bad places and would require more cleanup than it's worth but this is the closest solution to the problem.

[Jolley71717]

Worked flawlessly

[Jana Weschenfelder]

Didn't work for me in SQL Server Management Studio for SQL Server 2014. It cuts after 16.000 characters. As written by Martin Smith.

[Michael Møldrup]

This won't work for very long texts, even with "Maximum Characters Retrieved - XML data" set to unlimited

[Jirka Hanika]

@MichaelMøldrup - Make sure that your Tools/Options/Query Results/SQL Server/General has Default destination for results set to Results to Grids. If still no luck, elaborate on how long was your data and how much of it was displayed.

[Lukas Thum]

I used print(substring(@script, @Counter * 8000, (@Counter + 1) * 8000)) to print my script.

[mostafa8026]

this procedure has a conflict with Unicode characters. how to handle utf8 for example?

[mostafa8026]

in the reply to above comment, it can be done by changing the @script type to nvarchar.

[kooch]

Wonderful, just what i was looking for!

[MgSam]

This answer doesn't work. The data still gets truncated. Happened around 3900 characters for me.

[Jirka Hanika]

@MgSam - Try printing the length of your string first and then the client side limit described in the answer.

[José Margaça Lopes]

I inserted the print statement before the counter increment, print substring(@script, (@Counter * 8000) + 1, 8000)

[andrew pate]

You could extend Yavav's suggestion here and simply use many 8000 char chunks... covering your worst case scenario... So if @SQL_InsertQuery was actually short just the first few lines print, the remaining ones just print an empty string

[Dean]

Should add TLDR to your answer...