Nvarchar(Max) Still Being Truncated

nvarchar(max) still being truncated

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

Nvarchar(max) shrinks to 4000 chars in SQL Server

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.

String being truncated even if nvarchar(max)

Does PRINT len(@string) show the correct length? If so the PRINT @string is the problem. The print does only output up to 4000 chars.

Try

SELECT @string

in order to output the result as a row which is not truncated.

Why is a Variable declared as NVARCHAR(MAX) dropping chunks of the string?

This is working fine for me:

DECLARE @sql nvarchar(max) = 
    REPLICATE(CONVERT(nvarchar(max), N'a'), 4000)
  + REPLICATE(CONVERT(nvarchar(max), N'b'), 4000)
  + REPLICATE(CONVERT(nvarchar(max), N'c'), 4000)
  + REPLICATE(CONVERT(nvarchar(max), N'd'), 4000)
  + REPLICATE(CONVERT(nvarchar(max), N'e'), 4000)
  + REPLICATE(CONVERT(nvarchar(max), N'f'), 4000)
  + REPLICATE(CONVERT(nvarchar(max), N'g'), 4000)
  + REPLICATE(CONVERT(nvarchar(max), N'h'), 4000)
  + REPLICATE(CONVERT(nvarchar(max), N'i'), 4000);


PRINT LEN(@sql);  -- characters
PRINT DATALENGTH(@sql); -- bytes
PRINT '';

DECLARE @Printed int = 1, @ToPrint int = LEN(@sql);

WHILE @Printed < @ToPrint BEGIN 
    PRINT(SUBSTRING(
        @sql, @Printed, 4000))
    SET @Printed = @Printed + 4000
    PRINT('Printed: ' + CONVERT(varchar(11), @Printed)) -- *
END

* Always specify length.

Output is:

36000
72000

Nvarchar(Max) Still Being Truncatedaa... 4000 As ...aaa
Printed: 4001
bb... 4000 Bs ...bbb
Printed: 8001
cccccccccc... 4000 Cs ...ccc
Printed: 12001
       


       
Related Topics
          

             
 Fastest Way to Remove Non-Numeric Characters from a Varchar in SQL Server
 SQL Server - How to Lock a Table Until a Stored Procedure Finishes
 Conditional Count on a Field
 Optimize Query With Offset on Large Table
 How to Set Table Name in Dynamic SQL Query
 Group by and Aggregate Sequential Numeric Values
 Can a Table Field Contain a Hyphen
 SQL - Subtracting a Depleting Value from Rows
 How to Specify Condition in Count()
 Turn Off Constraints Temporarily (Ms Sql)
 Difference Between Cte and Subquery
 List of Special Characters For SQL Like Clause
 Getting Only Month and Year from SQL Date
 Select Info from Table Where Row Has Max Date
 SQL Join Differences
 Oracle'S Default Date Format
 Fast Way to Discover the Row Count of a Table in Postgresql
 How to Force Postgres to Use a Particular Index
          
dd... 4000 Ds ...ddd
Printed: 16001
nvarchar(max) still being truncated Nvarchar(max) shrinks to 4000 chars in SQL Server String being truncated even if nvarchar(max) Why is a Variable declared as NVARCHAee... 4000 Es ...eee
Printed: 20001
ffffffffff... 4000 Cs ...fff
Printed: 24001
gggggggggg... 4000 As ...ggg
Printed: 28001
hhhhhhhhhh... 4000 Bs ...hhh
Printed: 32001
iiiiiiiiii... 4000 Cs ...iii
Printed: 36001

So, I think the problem is elsewhere. In any case, this is a really sloppy way to validate the contents of dynamic SQL. Instead I would do:

SELECT CONVERT(xml, @sql);

Then you can click on the output cell and it opens in an XML text editor for review (you can then copy and paste that output into a query window if you want IntelliSense or any chance in executing, but you'll have to replace encoded characters like > --> >. I talk about this approach (and another one) here:

• Validate the contents of large dynamic SQL strings

If you insist on doing it this bricklaying way, perhaps there is some kind of non-printing or string-termination character that's at that point. If you say it is around character 5,600 then you could do:

DECLARE @i int = 5550, @c nchar(1);
WHILE @i <= 5650
BEGIN
  PRINT '';
  SET @c = SUBSTRING(@sql, @i, 1);
  PRINT '------   ' + RTRIM(@i) + '------:';
  PRINT 'Raw:     ' + @c;
  PRINT 'ASCII:   ' + ASCII(@c);
  PRINT 'UNICODE: ' + UNICODE(@c);
  SET @i += 1;
END

You should be able to scan down and match the last sequence of characters you see in the broken print output. Then look for anything where the Raw: line is empty and the ASCII: line is anything other than typical (9, 10, 13, 32).

But I don't think this is the problem. I'll go back to an earlier comment where I suggested that the string itself is the problem. In the question, you mention @sql, but don't show how it's populated. I would bet that some string you're adding to that is getting truncated. Some things to look out for:

• Intermediate variables/parameters declared as varchar/nvarchar but with no length (which sometimes leads to silent truncation at 1 character, and sometimes 30):

    DECLARE @sql nvarchar(max) = N'SELECT * FROM dbo.table ';
    DECLARE @where nvarchar = N'WHERE some condition...';
    SET @sql += @where;
    PRINT @sql;
  

  Output:

    SELECT * FROM dbo.table W
  

• Intermediate variables/parameters declared as varchar/nvarchar but too short (which leads to silent truncation at whatever the declaration is):

    DECLARE @sql nvarchar(max) = N'SELECT * FROM dbo.table ';
    DECLARE @where nvarchar(10) = N'WHERE some condition...';
    SET @sql += @where;
    PRINT @sql;
  

  Output:

    SELECT * FROM dbo.table WHERE some
  

• Explicit CONCAT with NULL, which leads to silently dropping any NULL input):

    DECLARE @sql nvarchar(max) = N'SELECT * FROM dbo.table ';
    DECLARE @where nvarchar(32);
    DECLARE @orderby nvarchar(32) = N' ORDER BY col1';
    SET @sql = CONCAT(@sql, @where, @orderby);
    PRINT @sql;
  

  Output:

    SELECT * FROM dbo.table  ORDER BY col1
  

• Not using the N prefix when concatenating Unicode string literals > 4000 characters (example here):

    DECLARE @sql nvarchar(max) = '';
  
    SET @sql = @sql + '... literally 4001 characters ...';
  

  The output here (as shown in the example) will be truncated at 4,000 characters. However if you define your strings properly, this won't happen:

    DECLARE @sql nvarchar(max) = N'';
  
    SET @sql = @sql + N'... literally 4001 characters ...';
  

These things can be hard to spot in overly complex dynamic SQL generation, so it's never a bad idea to simplify and try any way you can to divide & conquer the major components in the eventual string. Based on the repro you attempted I would almost certainly guess it is the "variable declared too short" symptom. Safest is to ensure every input to a dynamic SQL string should be declared as nvarchar(max); no real good reason to use anything else except for entity names which are constrained by metadata anyway.

SQL NVARCHAR and VARCHAR Limits

I understand that there is a 4000 max set for NVARCHAR(MAX)

Your understanding is wrong. nvarchar(max) can store up to (and beyond sometimes) 2GB of data (1 billion double byte characters).

From nchar and nvarchar in Books online the grammar is

nvarchar [ ( n | max ) ]

The | character means these are alternatives. i.e. you specify either n or the literal max.

If you choose to specify a specific n then this must be between 1 and 4,000 but using max defines it as a large object datatype (replacement for ntext which is deprecated).

In fact in SQL Server 2008 it seems that for a variable the 2GB limit can be exceeded indefinitely subject to sufficient space in tempdb (Shown here)

Regarding the other parts of your question

Truncation when concatenating depends on datatype.

1. varchar(n) + varchar(n) will truncate at 8,000 characters.
2. nvarchar(n) + nvarchar(n) will truncate at 4,000 characters.
3. varchar(n) + nvarchar(n) will truncate at 4,000 characters. nvarchar has higher precedence so the result is nvarchar(4,000)
4. [n]varchar(max) + [n]varchar(max) won't truncate (for < 2GB).
5. varchar(max) + varchar(n) won't truncate (for < 2GB) and the result will be typed as varchar(max).
6. varchar(max) + nvarchar(n) won't truncate (for < 2GB) and the result will be typed as nvarchar(max).
7. nvarchar(max) + varchar(n) will first convert the varchar(n) input to nvarchar(n) and then do the concatenation. If the length of the varchar(n) string is greater than 4,000 characters the cast will be to nvarchar(4000) and truncation will occur.

Datatypes of string literals

If you use the N prefix and the string is <= 4,000 characters long it will be typed as nvarchar(n) where n is the length of the string. So N'Foo' will be treated as nvarchar(3) for example. If the string is longer than 4,000 characters it will be treated as nvarchar(max)

If you don't use the N prefix and the string is <= 8,000 characters long it will be typed as varchar(n) where n is the length of the string. If longer as varchar(max)

For both of the above if the length of the string is zero then n is set to 1.

Newer syntax elements.

1. The CONCAT function doesn't help here

DECLARE @A5000 VARCHAR(5000) = REPLICATE('A',5000);

SELECT DATALENGTH(@A5000 + @A5000), 
       DATALENGTH(CONCAT(@A5000,@A5000));

The above returns 8000 for both methods of concatenation.

2. Be careful with +=

DECLARE @A VARCHAR(MAX) = '';

SET @A+= REPLICATE('A',5000) + REPLICATE('A',5000)

DECLARE @B VARCHAR(MAX) = '';

SET @B = @B + REPLICATE('A',5000) + REPLICATE('A',5000)


SELECT DATALENGTH(@A), 
       DATALENGTH(@B);`

Returns

-------------------- --------------------
8000                 10000

Note that @A encountered truncation.

How to resolve the problem you are experiencing.

You are getting truncation either because you are concatenating two non max datatypes together or because you are concatenating a varchar(4001 - 8000) string to an nvarchar typed string (even nvarchar(max)).

To avoid the second issue simply make sure that all string literals (or at least those with lengths in the 4001 - 8000 range) are prefaced with N.

To avoid the first issue change the assignment from

DECLARE @SQL NVARCHAR(MAX);
SET @SQL = 'Foo' + 'Bar' + ...;

To

DECLARE @SQL NVARCHAR(MAX) = ''; 
SET @SQL = @SQL + N'Foo' + N'Bar'

so that an NVARCHAR(MAX) is involved in the concatenation from the beginning (as the result of each concatenation will also be NVARCHAR(MAX) this will propagate)

Avoiding truncation when viewing

Make sure you have "results to grid" mode selected then you can use

select @SQL as [processing-instruction(x)] FOR XML PATH 

The SSMS options allow you to set unlimited length for XML results. The processing-instruction bit avoids issues with characters such as < showing up as <.

Stored procedure variable defined nvarchar(max) gets truncated to 4000 chars

In my case i wanted to be able to copy paste the results of the sp so the solution is:

1) Replace print with select
2) Add char(13) after char(10)

BEGIN
    SET NOCOUNT ON;
    DECLARE @v_sql nvarchar(max)
    SELECT @v_sql = N'';
    IF UPPER(@action)='DISABLE'
    BEGIN
        SELECT @v_sql = @v_sql + 'ALTER INDEX '+ QUOTENAME(name,'[')+' ON ' + quotename( OBJECT_SCHEMA_NAME(object_id),'[') +'.'+ quotename( OBJECT_NAME(object_id),'[') + ' DISABLE;'+char(10) + char(13)
        FROM sys.indexes
        WHERE OBJECTPROPERTY([object_id], 'IsMsShipped') = 0 AND type =2;   
    END
    IF UPPER(@action)='REBUILD'
    BEGIN
        SELECT @v_sql = @v_sql + 'ALTER INDEX '+QUOTENAME(name,'[')+' ON ' +  quotename( OBJECT_SCHEMA_NAME(object_id),'[') +'.'+ quotename( OBJECT_NAME(object_id),'[') + ' REBUILD WITH(SORT_IN_TEMPDB=ON);'+char(10) + char(13)
        FROM sys.indexes
        WHERE OBJECTPROPERTY([object_id], 'IsMsShipped') = 0 AND type =2;
    END
    IF @v_sql <> ''
    BEGIN
        select @v_sql
        --EXEC sp_executesql @v_sql;
    END
END

---

Related Topics