x

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
more ▼

asked Nov 18, 2009 at 03:52 AM in Default

Nick Kavadias gravatar image

Nick Kavadias
476 3 3 7

(comments are locked)
10|1200 characters needed characters left

4 answers: sort voted first

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/

more ▼

answered Nov 18, 2009 at 01:45 PM

TimothyAWiseman gravatar image

TimothyAWiseman
15.5k 20 23 32

wise man indeed!
Dec 02, 2009 at 01:42 AM Nick Kavadias
(comments are locked)
10|1200 characters needed characters left

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.

more ▼

answered Nov 18, 2009 at 05:06 AM

Kev Riley gravatar image

Kev Riley ♦♦
52.7k 47 49 76

(comments are locked)
10|1200 characters needed characters left

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 ).

more ▼

answered Nov 18, 2009 at 09:17 AM

Gustavo gravatar image

Gustavo
592 4 4 7

(comments are locked)
10|1200 characters needed characters left

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.

more ▼

answered Nov 18, 2009 at 10:47 AM

Fatherjack gravatar image

Fatherjack ♦♦
42.3k 74 78 108

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x984
x124

asked: Nov 18, 2009 at 03:52 AM

Seen: 12301 times

Last Updated: Nov 18, 2009 at 03:59 AM