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

avatar image

Nick Kavadias
476 3 5 11

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

6 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

avatar image

TimothyAWiseman
15.6k 22 51 38

(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

avatar image

Kev Riley ♦♦
64.2k 48 62 81

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];

May 21, 2015 at 01:16 PM srutzky
(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

avatar image

Gustavo
592 4 6 11

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];

May 21, 2015 at 01:15 PM srutzky
(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

avatar image

Fatherjack ♦♦
43.7k 79 98 117

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];

May 21, 2015 at 01:16 PM srutzky
(comments are locked)
10|1200 characters needed characters left

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'

more ▼

answered May 21, 2015 at 12:50 PM

avatar image

rahman
1

Given that this is a 6 year old thread, it might be better to start a new question.

May 21, 2015 at 12:54 PM JohnM

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.

May 21, 2015 at 01:01 PM srutzky
(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.

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:

x1069
x154

asked: Nov 18, 2009 at 03:52 AM

Seen: 20643 times

Last Updated: May 21, 2015 at 01:57 PM

Copyright 2016 Redgate Software. Privacy Policy