Nvarchar (max) is still truncating

Question

So, I am writing a stored procedure in MS SQL Server 2008. This is a very long query, and I have to write it dynamically, so I create a variable called @Query and create it of type NVARCHAR(MAX) . Now I have been told that in modern versions of SQL Server NVARCHAR(MAX) can contain an absurd amount of data, which exceeds the maximum maximum of 4000 characters. However, @Query still truncates to 4000 characters when I try to print it.

 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 wrong, or am I completely wrong about how NVARCHAR(MAX) works?

Answer 1

To see the dynamic SQL generated, switch 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 three aaaaa... , the middle of which is the longest with the addition of "SELECT ..". Follow the Ln... Col.. indicator in the status bar at the bottom right, showing 4510 at the end of the second 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 

Answer 2

The problem seems to be related to the SET statement. I think this expression cannot exceed 4000 bytes. There is no need to make any changes to any settings if all you are trying to do is assign a dynamically generated statement that is more than 4000 characters. What you need to do is split your task. If your statement is 6,000 characters long, find a logical breakpoint and then join the other half with 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 usual, i.e. EXEC ( @Query )

Answer 3

The problem is implicit conversion.

If you have the Unicode / nChar / nVarChar values ​​that you concatenate, SQL Server will implicitly convert your string to nVarChar (4000), and unfortunately it's too stupid to realize that it truncates your string or even gives you a warning that the data has been truncated in this regard!

When concatenating long strings (or strings that you think might be long), always pre-concatenate string construction with CAST ("like" nVarChar (MAX)) :

 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 that this is how SQL Server works. : (

I know that other workarounds on the Internet say breaking your code into multiple SET / SELECT assignments using multiple variables, but this is not necessary given the above solution.

For those with a maximum of 8000 characters, probably because you didn't have Unicode, so it was implicitly converted to VarChar (8000).

Explanation:
What happens behind the scenes is that even if the variable that you assign to use (MAX), SQL Server will evaluate the right side of the value that you assign first, and by default, nVarChar (4000) or VarChar (8000 ) (depending on what you concatenate). After you finish calculating the value (and after truncating it for you), it then converts it to (MAX) when assigning it to a variable, but by then it is too late.

Answer 4

Text results allow 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*/ 

Answer 5

Your first problem is limiting the PRINT statement. I am not sure why sp_executesql fails. It should support almost any input length.

Perhaps the reason the request is garbled is something other than truncation.

Answer 6

Today I ran into the same problem and found that beyond 4000 characters, I had to split the dynamic query into two lines and combine 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) 

Answer 7

The problem with creating dynamic SQL using a string expression is that SQL limits the evaluation of string expressions to 4000 characters. You can assign a longer string to nvarchar (max), but as soon as you include + in the expression (for example, + CASE ... END +), the result of the expression is limited to 4000 characters.

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

Answer 8

Printing trims varchar (MAX) to 8000, nvarchar (MAX) to 4000 characters.

But;

 PRINT CAST(@query AS NTEXT) 

will print the entire request.

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