Nvarchar(max) shrinks to 4000 chars in SQL Server

Question

I am creating a dynamic query in stored procedure, and when I try to print that dynamic query, it only prints a part of the whole query.

My variable that holds the whole dynamic query is declared like below

DECLARE @SQL NVARCHAR(MAX)

When I print the variable's length like below, It gives the length of the whole query, which is good.

PRINT LEN(@SQL)

But, when I print the script itself, It prints only a part of the whole query.

PRINT @SQL

I also tried to print it like below

PRINT CONVERT(NVARCHAR(MAX),@SQL)

Why it only prints first 4000chars? What am I doing wrong?

Why not read the docs? https://msdn.microsoft.com/en-us/library/ms176047.aspx A message string can be up to 8,000 characters long if it is a non-Unicode string, and 4,000 characters long if it is a Unicode string. Longer strings are truncated. The varchar(max) and nvarchar(max) data types are truncated to data types that are no larger than varchar(8000) and nvarchar(4000). — Vladimir Baranov, May 26 '16 at 12:45
Your data isn't being shrunk/truncated. SSMS has a limit to how many characters it will display for certain fields. Using another application to interpret the `SELECT` query results will yield the full value of the field. — Siyual, May 26 '16 at 12:48
`Why it only prints first 4000chars?` Because this is how `PRINT` works, which is written in the docs very clearly. — Vladimir Baranov, May 26 '16 at 12:52
Possible duplicate of [nvarchar(max) still being truncated](http://stackoverflow.com/questions/4833549/nvarcharmax-still-being-truncated) — Morpheus, May 26 '16 at 14:53

score 8 · Accepted Answer · answered May 26 '16 at 12:47

8

You are not doing anything wrong. The Print command is limited to outputting 4000 characters (see BOL - Books Online, for more details). It does not mean nvarchar(max) has shrunk to 4000 characters.

answered May 26 '16 at 12:47

Randy Minder

47,200
49
204
358

score 2 · Answer 2 · edited May 23 '17 at 11:58

2

try as below to print entire string,

SET @Query = 'SELECT ....' [Up To 4,000 characters, then rest of statement as below]

SET @Query = @Query + [rest of statement]

Now run your query as normal i.e. EXEC ( @Query )

got the answer from below link

nvarchar(max) still being truncated

edited May 23 '17 at 11:58

Community

1
1

answered May 26 '16 at 12:50

bmsqldev

2,627
10
31
65

score 2 · Answer 3 · answered May 26 '16 at 12:51

It's PRINT that is limited to 4000 characters, not the NVARCHAR(MAX) variable.

A workaround would be to just something like this PRINT BIG function with your string if you want to output everything.

https://www.richardswinbank.net/doku.php?id=tsql:print_big

You could also just SELECT the variable which isn't limited and copy the contents from the results.

Nvarchar(max) shrinks to 4000 chars in SQL Server

3 Answers3