question

Nick Kavadias avatar image
Nick Kavadias asked

Any way around the PRINT limit of nvarchar(max) in SSMS

it seems that the PRINT function will not print past 4000 characters for nvarchar and 8000 character for char in management studio. Is there any way around this?

here is some demo code to prove it

declare @x varchar(max)
set @x = replicate('x' ,7999)
PRINT @x
set @x = replicate('x' ,8000)
PRINT @x
set @x = replicate('x' ,8001)
PRINT @x
declare @y nvarchar(max)
set @y = replicate('y' ,3999)
PRINT @y
set @y = replicate('y' ,4000)
PRINT @y
set @y = replicate('y' ,4001)
PRINT @y
t-sqlssms
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

TimothyAWiseman avatar image
TimothyAWiseman answered

This is a documented limitation of the print command in BOL. As a workaround, I use this stored procedure that I wrote:

CREATE PROCEDURE [dbo].[LongPrint]
      @String NVARCHAR(MAX)
AS
/*
Example:
exec LongPrint @string =
'This String
Exists to test
the system.'
*/
/* This procedure is designed to overcome the limitation
in the SQL print command that causes it to truncate strings
longer than 8000 characters (4000 for nvarchar).
It will print the text passed to it in substrings smaller than 4000
characters.  If there are carriage returns (CRs) or new lines (NLs in the text),
it will break up the substrings at the carriage returns and the
printed version will exactly reflect the string passed.
If there are insufficient line breaks in the text, it will
print it out in blocks of 4000 characters with an extra carriage
return at that point.
If it is passed a null value, it will do virtually nothing.
NOTE: This is substantially slower than a simple print, so should only be used
when actually needed.
 */
DECLARE
               @CurrentEnd BIGINT, /* track the length of the next substring */
               @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, 1073741822)   
END /*End While loop*/

This was originally posted on SQLServerCentral.com at http://www.sqlservercentral.com/scripts/Print/63240/

10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Kev Riley avatar image
Kev Riley answered

These limitations are documented in BOL. I guess they must be legacy hangovers still present internally in PRINT from when 8000 was the 'max' for varchar and 4000 for nvarchar.

Some potential workarounds, but they might not fit your situation:

  • add some logic to test the length of the variable and split into manageable chunks of 8000 or less varchars, and PRINT each one

  • set 'Results to Text' and use select @x


Edit : As noted by some of the other answers, REPLICATE has it's own issues too, so be careful as to how you build up your string. It seems from you original question that your issue is still with PRINT and you just used REPLICATE in your question to demonstrate the point.

1 comment
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

The functions that appear to truncate down to either 4000 or 8000 characters just need to be given explicit MAX datatypes for the input parameters. Try this: `SELECT LEN(REPLICATE(N'y', 16000)) AS [NoExplicitDatatype], LEN(REPLICATE(CONVERT(NVARCHAR(MAX), N'y'), 16000)) AS [ExplicitDatatypeViaConvert];`
0 Likes 0 ·
Gustavo avatar image
Gustavo answered

I think REPLICATE AND/OR LEN have a limitation too:

declare @x varchar(max)
set @x = replicate('x' ,7999)
print len(@x) -- 7999
set @x = replicate('x' ,8000)
print len(@x) -- 8000
set @x = replicate('x' ,8001)
print len(@x) -- 8000
declare @y nvarchar(max)
set @y = replicate('y' ,3999)
print len(@y) -- 3999
set @y = replicate('y' ,4000)
print len(@y) -- 4000
set @y = replicate('y' ,4001)
print len(@y) -- 4001
set @y = replicate('y' ,8001)
print len(@y) -- 8000
go

Couldn't set anything bigger than 8000 on both varchar and nvarchar.

The same way you cant declare text variables, maybe you can't manipulate any string variable higher then 8000.

Even setting results to text, the select statement only returned the initial 8000 chars ( and the SSMS output to text limit is set to the max value of 8192 ).

1 comment
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

The functions that appear to truncate down to either 4000 or 8000 characters just need to be given explicit MAX datatypes for the input parameters. Try this: `SELECT LEN(REPLICATE(N'y', 16000)) AS [NoExplicitDatatype], LEN(REPLICATE(CONVERT(NVARCHAR(MAX), N'y'), 16000)) AS [ExplicitDatatypeViaConvert];`
0 Likes 0 ·
Fatherjack avatar image
Fatherjack answered

I must admit to being a bit confused with this, but sadly I dont have time to envestigate it any further at the moment.

I started playing with the code and altered it to

declare @y nvarchar(max)
declare @z nvarchar(max)
set @y = replicate('y' ,3999)
print len(@y) -- 3999
set @y = replicate('y' ,4000)
print len(@y) -- 4000
set @y = replicate('y' ,4001)
print len(@y) -- 4001
set @y = replicate('y' ,8001)
SELECT @z = 'z' + @y + @y
print len(@y) -- 8000
print len(@z) -- ?!?!?!
Select @z
go

And get the results
7999
8000
8000
3999
4000
4001
8000
16001

so... a variable can hold more than 8000 (16001 in this case). Using Copy/Paste to get the @z result into excel shows there are indeed 16001 characters in the variable

Maybe you can used the logic this is exhibiting to get around your problem?

Jonathan [Edit:]

Changing the line
set @y = replicate('y' ,4001)
to be
set @y = @y + @y
and that returns 8000 characters - I wonder if its a feature of REPLICATE?

[EDIT2] Gaaah, I;ve got caught up in it ..!

REPLICATE help in BoL ms-help://MS.SQLCC.v10/MS.SQLSVR.v10.en/s10de_6tsql/html/0cd467fb-3f22-471a-892c-0039d9f7fa1a.htm states that

If string_expression is not of type varchar(max) or nvarchar(max), REPLICATE truncates the return value at 8,000 bytes. To return values greater than 8,000 bytes, string_expression must be explicitly cast to the appropriate large-value data type.

1 comment
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

As you saw in BOL: The functions that appear to truncate down to either 4000 or 8000 characters just need to be given explicit MAX datatypes for the input parameters. Try this: `SELECT LEN(REPLICATE(N'y', 16000)) AS [NoExplicitDatatype], LEN(REPLICATE(CONVERT(NVARCHAR(MAX), N'y'), 16000)) AS [ExplicitDatatypeViaConvert];`
0 Likes 0 ·
rahman avatar image
rahman answered
In my string, i want to print just ''[ACTIONSTATUS]'' from @str what can i do after these two statements DECLARE @str VARCHAR(250) SET @str = 'This is a table [EPM_AUDIT_ISB35].[DBO].[ACTIONSTATUS] in the Medflow Database'
2 comments
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Given that this is a 6 year old thread, it might be better to start a new question.
2 Likes 2 ·
This is also an entirely different question that has nothing to do with printing more than 4000 NVARCHAR or 8000 VARCHAR characters via PRINT. Please delete this question and either create a new one, or simply do a search here and on DBA.StackExchange.com looking for something like "extract part of a sting" or similar.
0 Likes 0 ·
srutzky avatar image
srutzky answered
Depending on your needs, there is a quick and easy way to get the full contents of the MAX variable (`VARCHAR(MAX)`, `NVARCHAR(MAX)`, and even `VARBINARY(MAX)`): convert it to XML and `SELECT` it. Of course, some characters and string sequences can cause an error, so just make it an XML comment and all should be fine. To ensure that there is no truncation in SSMS, go to the **Query** menu -> **Query Options...** -> **Results** and on the right side, under the "Maximum Characters Retrieved" section, set "XML data" to the appropriate size (options are: 1 MB, 2 MB, 5 MB, and Unlimited). DECLARE @Test NVARCHAR(MAX); SET @Test = REPLICATE(CONVERT(NVARCHAR(MAX), N'y'), 20000) + N' 4 < 5 & "' + NCHAR(13) + NCHAR(10) + N'Next line.'; SELECT CONVERT(XML, N' ') AS [All NVARCHAR Characters]; DECLARE @TestBinary VARBINARY(MAX); SET @TestBinary = CONVERT(VARBINARY(MAX), @Test); SELECT @TestBinary AS [Contents of VARBINARY Variable]; SELECT CONVERT(XML, N' ') AS [All VARBINARY Bytes]; And for the sake of completeness, this question was also covered over at DBA.StackExchange: [How to output more than 4000 characters in sqlcmd][1] [1]: http://dba.stackexchange.com/questions/97188/how-to-output-more-than-4000-characters-in-sqlcmd/97350#97350
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.