nvarchar(max) still being truncated

Question

I'm writing a stored procedure in SQL Server 2008. It's a really long query and I have to write it dynamically, so I create a variable called @Query and make it of type NVARCHAR(MAX). Now, I have been told that in modern versions of SQL Server, NVARCHAR(MAX) can hold a ridiculous amount of data, way more than the original 4000 character maximum. However, @Query is still getting truncated to 4000 characters when I try to print it out.

DECLARE @Query NVARCHAR(max);
SET @Query = 'SELECT...' -- some of the query gets set here
SET @Query = @Query + '...' -- more query gets added on, etc.

-- later on...
PRINT LEN(@Query) -- Prints out 4273, which is correct as far as I can tell
PRINT @Query      -- Truncates value to 4000 characters
EXEC sp_executesql @Query -- totally crashes due to malformed (truncated) query

Am I doing something incorrectly, or am I completely wrong about how NVARCHAR(MAX) works?

Alan

NVARCHAR(MAX) never had a limit on 4000 characters..... – Manor

Also have come upon this issue of a string parameter being truncated at 4270 characters using NVARCHAR(MAX) in SQL Server 2019 – Liard

Answer 1

To see the dynamic SQL generated, change to text mode (_shortcut: Ctrl-T), then use SELECT

PRINT LEN(@Query) -- Prints out 4273, which is correct as far as I can tell
--SET NOCOUNT ON
SELECT @Query

As for sp_executesql, try this (in text mode), it should show the three aaaaa...'s the middle one being the longest with 'SELECT ..' added. Watch the Ln... Col.. indicator in the status bar at bottom right showing 4510 at the end of the 2nd output.

declare @n nvarchar(max)
set @n = REPLICATE(convert(nvarchar(max), 'a'), 4500)
SET @N = 'SELECT ''' + @n + ''''
print @n   -- up to 4000
select @n  -- up to max
exec sp_Executesql @n

Outcurve

There's a default in MS SQL Server that limits max characters per column in text results. To change it, go to the Query menu > Query Options > Results > Text and change the Maximum to something like 8192. – Pontone

I stopped using SELECT to print out my dynamic queries once I found out that PRINT formatted them... but it's good to know that SELECT won't truncate them. – Alan

@Cyrena - Despite mine apparently being set to 256, it doesn't actually limit the display. Oh well. – Alan

@Cyr - The default in SSMS (at least in 2008) is 2 billion or so, iirc. And (n)(var)char is immune, that refers to the old 'text' type, also iirc – Outcurve

@cyberkiwi Can you print this in SSMS? CAST(REPLICATE(N'A',4000) AS NVARCHAR(max)) + REPLICATE(N'B',4000) + REPLICATE(N'C',4000) + REPLICATE(N'D',4000) – Thermodynamics

@cyberkiwi: Huh, I guess I didn't know that had changed. I remember having to do that a few years ago, and when I got SSMS 2008 I immediately went and changed it. Thanks for letting me know! – Pontone

Answer 2

The problem is with implicit conversion.

If you have Unicode/nChar/nVarChar values you are concatenating, then SQL Server will implicitly convert your string to nVarChar(4000), and it is unfortunately too dumb to realize it will truncate your string or even give you a Warning that data has been truncated for that matter!

When concatenating long strings (or strings that you feel could be long) always pre-concatenate your string building with CAST('' as nVarChar(MAX)) like so:

SET @Query = CAST('' as nVarChar(MAX))--Force implicit conversion to nVarChar(MAX)
           + 'SELECT...'-- some of the query gets set here
           + '...'-- more query gets added on, etc.

What a pain and scary to think this is just how SQL Server works. :(

I know other workarounds on the web say to break up your code into multiple SET/SELECT assignments using multiple variables, but this is unnecessary given the solution above.

For those who hit an 8000 character max, it was probably because you had no Unicode so it was implicitly converted to VarChar(8000).

Warning:
You still Cannot have a Single Unbroken Literal String Larger than 4000 (or 8000 for VarChar).
Literal Strings are those you hard-code and wrap in apostrophe's.
You must Break those Strings up or SQL Server will Truncate each one BEFORE concatenating.
I add ' + ' every 20 lines (or so) to make sure I do not go over.
That's an average of at most 200 characters per line - but remember, spaces still count!

Explanation:
What's happening behind the scenes is that even though the variable you are assigning to uses (MAX), SQL Server will evaluate the right-hand side of the value you are assigning first and default to nVarChar(4000) or VarChar(8000) (depending on what you're concatenating). After it is done Concatenating and figuring out the value (and after truncating it for you) it then converts it into (MAX) when assigning it to your variable, but by then it is too late.

Marquess

This answer is on the exact issue. – Philharmonic

Killer feature :S ... I changed to using NVARCHAR(4000) instead. Couldn't get NVARCHAR(MAX) to work without truncation even with this solution. The truncation was to 256 characters in my case (simple SELECT CONCAT() using a function with NVARCHAR(MAX), and only in SQLCMD - NB this was not the display width issue. – Caning

For a more detailed explanation of how implicit conversion is the issue here, see this answer from another SO question regarding the same topic. – Scalenus

The pre-concatenation with CAST('' as nVarChar(MAX)) solved my issue, thank you so much! I was trying to do an UPDATE dbo.MyTable SET MyColumn (of type nvarchar(max)) = 'string longer than 4000' and it kept truncating the string (len(MyColumn) = 4000 after running the update) before I applied this workaround. Doing an an UPDATE dbo.MyTable SET MyColumn = CAST('' as nVarChar(MAX)) + 'string longer than 4000' yielded the expected result. – Gertrudgertruda

@Caning I ran into this same issue recently and figured out a workaround for it. Because I was adding a bunch of Literal Strings, I didn't notice it before, but today one of them was larger than 4000, so it was Silently Truncated. I updated the Answer with a "Warning" about this and included the workaround (Break up your Large Literal Strings). – Marquess

Answer 3

Problem seems to be associated with the SET statement. I think the expression can't be more than 4,000 bytes in size. There is no need to make any changes to any settings if all you are trying to do is to assign a dynamically generated statement that is more than 4,000 characters. What you need to do is to split your assignment. If your statement is 6,000 characters long, find a logical break point and then concatenate second half to the same variable. For example:

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 )

Ledet

This really solved a problem that I been having with this for a long time. Thank you so much. – Colombes

This should be the answer, it seems the poster was concatenating more than 4000 chars in either of his 'SET' statements, NVARCHAR(MAX) does store more than 4000 chars – Thesda

Agreed that this should be the answer. – Rhapsody

Well that was three years ago now so I can't say for sure which should be the answer. The one I selected probably helped me the most, but it's good this one has been helpful to others as well. – Alan

@Marquess below says it has to do with implicit conversion from varchar(max) to nvarchar(max) and suggests using cast(... as nvarchar(max)). I've ran into this problem and it was solved by simply explicitly stating that my string is of the nvarchar variety by doing set @Query = N'SELECT ...' and this worked for me. – Drum

Btw, this is also true for if you are creating a query using DECLARE, e.g. DECLARE @my_query NVARCHAR(MAX) = '... some really long query ...' will also truncate at 4000 characters. Ran into this on SQL Server 2008. – Bula

Answer 4

Print truncates the varchar(MAX) to 8000, nvarchar(MAX) to 4000 chars.

But;

PRINT CAST(@query AS NTEXT)

will print the whole query.

Peridium

Answer 5

Results to text only allows a maximum of 8192 characters.

I use this approach

DECLARE @Query NVARCHAR(max);

set @Query = REPLICATE('A',4000)
set @Query = @Query + REPLICATE('B',4000)
set @Query = @Query + REPLICATE('C',4000)
set @Query = @Query + REPLICATE('D',4000)

select LEN(@Query)

SELECT @Query /*Won't contain any "D"s*/
SELECT @Query as [processing-instruction(x)] FOR XML PATH /*Not truncated*/

Thermodynamics

@cyberkiwi - Using the PI stops SQL Server replacing XML entities (i.e. < with < etc.) – Thermodynamics

Answer 6

Your first problem is a limitation of the PRINT statement. I'm not sure why sp_executesql is failing. It should support pretty much any length of input.

Perhaps the reason the query is malformed is something other than truncation.

Macrogamete

Then is it not a limitation of the EXEC statement? – Alan

No, EXEC can go up to 2GB at least – Outcurve

not sure, but Exec (sp_executesql) does have a limitation of 8000 characteres in sql2005 – Ferry

Answer 7

The problem with creating dynamic SQL using string expression is that SQL does limit the evaluation of string expressions to 4,000 chars. You can assign a longer string to an nvarchar(max) variable, but as soon as you include + in the expression (such as + CASE ... END + ), then the expression result is limited to 4,000 chars.

One way to fix this is to use CONCAT instead of +. For example:

SET @sql = CONCAT(@sql, N'
     ... dynamic SQL statements ...
    ', CASE ... END, N'
     ... dynamic SQL statements ...
    ')

Where @sql is declared as nvarchar(max).

Yeti

Answer 8

I have encountered the same problem today and found that beyond that 4000 character limit, I had to split the dynamic query into two strings and concatenate them when executing the query.

DECLARE @Query NVARCHAR(max);
DECLARE @Query2 NVARCHAR(max);
SET @Query = 'SELECT...' -- some of the query gets set here
SET @Query2 = '...' -- more query gets added on, etc.

EXEC (@Query + @Query2)

Ebullient

EXEC sp_executesql (@Query + @Query2) fails. It expects a single statement of type nvarchar. – Aright

Thanks. EXEC can concatenate the strings into a single nvarchar itself, I don't know why I put in sp_executesql in the first place! – Ebullient

I found that SET @Query = @Query + '...' works just as well – Colombes

Answer 9

Use this PRINT BIG function to output everything:

IF OBJECT_ID('tempdb..#printBig') IS NOT NULL
  DROP PROCEDURE #printBig

GO

CREATE PROCEDURE #printBig (
   @text NVARCHAR(MAX)
 )
AS

--DECLARE @text NVARCHAR(MAX) = 'YourTextHere'
DECLARE @lineSep NVARCHAR(2) = CHAR(13) + CHAR(10)  -- Windows \r\n

DECLARE @off INT = 1
DECLARE @maxLen INT = 4000
DECLARE @len INT

WHILE @off < LEN(@text)
BEGIN

  SELECT @len =
    CASE
      WHEN LEN(@text) - @off - 1 <= @maxLen THEN LEN(@text)
      ELSE @maxLen
             - CHARINDEX(REVERSE(@lineSep),  REVERSE(SUBSTRING(@text, @off, @maxLen)))
             - LEN(@lineSep)
             + 1
    END
  PRINT SUBSTRING(@text, @off, @len)
  --PRINT '@off=' + CAST(@off AS VARCHAR) + ' @len=' + CAST(@len AS VARCHAR)
  SET @off += @len + LEN(@lineSep)

END

Source:

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

Jac

Answer 10

I was creating a JSON-LD to create a site review script. **DECLARE @json VARCHAR(MAX);** The actual JSON is about 94K.

I got this to work by using the CAST('' AS VARCHAR(MAX)) + @json, as explained by other contributors:- so **SET @json = CAST('' AS VARCHAR(MAX)) + (SELECT .....**

2/ I also had to change the Query Options:- Query Options -> 'results' -> 'grid' -> 'Maximum Characters received' -> 'non-XML Data' SET to 2000000. (I left the 'results' -> 'text' -> 'Maximum number of characters displayed in each column' as the default)

Doty

this saved my life thank you! – Cabstand

Answer 11

Solution for truncating JSON column in Azure Data Studio

Reference: https://mcmap.net/q/75616/-sql-nvarchar-and-varchar-limits

Reference: https://github.com/microsoft/azuredatastudio/issues/392#issuecomment-1239773578

Teillo