So I'm writing a stored procedure in MS 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?
This question is related to
sql
sql-server-2008
dynamic-sql
The answer is
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).
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)
Print truncates the varchar(MAX) to 8000, nvarchar(MAX) to 4000 chars.
But;
PRINT CAST(@query AS NTEXT)
will print the whole query.
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:
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).
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 figuring out the value (and after truncating it for you) it then converts it to (MAX) when assigning it to your variable, but by then it is too late.
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*/
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 )
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.
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)
Source: Stackoverflow.com