SQL nvarchar(max) 仍然被截断
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/4833549/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
nvarchar(max) still being truncated
提问by Andrew Arnold
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 @Queryand make it of type NVARCHAR(MAX). Now, I have been toldthat in modern versions of SQL Server, NVARCHAR(MAX)can hold a ridiculous amount of data, way more than the original 4000 character maximum. However, @Queryis still getting truncated to 4000 characters when I try to print it out.
所以我在 MS SQL Server 2008 中编写了一个存储过程。这是一个非常长的查询，我必须动态编写它，所以我创建了一个名为 的变量@Query并使其类型为NVARCHAR(MAX)。现在，有人告诉我，在现代版本的 SQL Server 中，NVARCHAR(MAX)可以容纳数量惊人的数据，远远超过原始 4000 个字符的最大值。但是，@Query当我尝试打印出来时，它仍然被截断为 4000 个字符。
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?
我做错了什么，还是我完全错误的NVARCHAR(MAX)工作方式？
采纳答案by RichardTheKiwi
To see the dynamic SQL generated, change to text mode (shortcut:Ctrl-T), then use SELECT
要查看生成的动态 SQL，请更改为文本模式（快捷键：Ctrl-T），然后使用 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.
至于sp_executesql，试试这个（在文本模式下），它应该显示三个aaaaa...中间的最长，并添加了“SELECT ..”。观察Ln... Col..右下角状态栏中的 指示器，在第二个输出结束时显示 4510。
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
回答by kannas
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 语句有关。我认为表达式的大小不能超过 4,000 字节。如果您尝试做的只是分配一个超过 4,000 个字符的动态生成的语句，则无需对任何设置进行任何更改。您需要做的是拆分您的作业。如果您的语句长度为 6,000 个字符，请找到一个逻辑断点，然后将后半部分连接到同一变量。例如：
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 )
现在正常运行您的查询，即 EXEC ( @Query )
回答by MikeTeeVee
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) alwayspre-concatenate your string building with CAST('' as nVarChar(MAX))like so:
如果您有要连接的 Unicode/nChar/nVarChar 值，那么 SQL Server 会将您的字符串隐式转换为 nVarChar(4000)，不幸的是，它太笨了，无法意识到它会截断您的字符串，甚至会警告您数据已被被截断了！
连接长字符串（或您认为可能很长的字符串）时，始终使用CAST('' as 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 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.
想到这就是 SQL Server 的工作方式，这是多么痛苦和可怕。 :(
我知道网络上的其他解决方法说使用多个变量将您的代码分解为多个 SET/SELECT 分配，但鉴于上述解决方案，这是不必要的。
对于那些最大达到 8000 个字符的人，这可能是因为您没有Unicode 因此它被隐式转换为 VarChar(8000)。
说明：
幕后发生的事情是，即使您分配给的变量使用 (MAX)，SQL Server 也会评估您首先分配的值的右侧，并默认为 nVarChar(4000) 或 VarChar(8000)（取决于关于您要连接的内容）。完成计算出值后（并为您截断它之后），然后在将其分配给您的变量时将其转换为 (MAX)，但到那时为时已晚。
回答by Martin Smith
Results to text only allows a maximum of 8192 characters.
结果到文本只允许最多 8192 个字符。
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*/
回答by Marcelo Cantos
Your first problem is a limitation of the PRINTstatement. I'm not sure why sp_executesqlis failing. It should support pretty much any length of input.
你的第一个问题是PRINT语句的局限性。我不确定为什么sp_executesql失败。它应该支持几乎任何长度的输入。
Perhaps the reason the query is malformed is something other than truncation.
也许查询格式错误的原因不是截断。
回答by boyukbas
Print truncates the varchar(MAX) to 8000, nvarchar(MAX) to 4000 chars.
打印将 varchar(MAX) 截断为 8000，将 nvarchar(MAX) 截断为 4000 个字符。
But;
但;
PRINT CAST(@query AS NTEXT)
will print the whole query.
将打印整个查询。
回答by Tony M
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.
使用字符串表达式创建动态 SQL 的问题在于 SQL 确实将字符串表达式的计算限制为 4,000 个字符。您可以为 nvarchar(max) 变量分配更长的字符串，但只要在表达式中包含 +（例如 + CASE ... END + ），表达式结果就会限制为 4,000 个字符。
One way to fix this is to use CONCAT instead of +. For example:
解决此问题的一种方法是使用 CONCAT 而不是 +。例如：
SET @sql = CONCAT(@sql, N'
... dynamic SQL statements ...
', CASE ... END, N'
... dynamic SQL statements ...
')
Where @sql is declared as nvarchar(max).
其中@sql 被声明为nvarchar(max)。
回答by Paul Andrew
Use this PRINT BIGfunction to output everything:
使用此PRINT BIG函数输出所有内容：
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:
来源：
回答by Patrick
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.
我今天遇到了同样的问题，发现超过 4000 个字符的限制，我不得不将动态查询拆分为两个字符串，并在执行查询时将它们连接起来。
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)
