SQL Server json被截断(即使使用NVARCHAR(max)时) [英] SQL Server json truncated (even when using NVARCHAR(max) )

问题描述

DECLARE @result NVARCHAR(max);

SET @result = (SELECT * FROM table
               FOR JSON AUTO, ROOT('Data'))

SELECT @result;

这将返回约43000个字符的json字符串，并且某些结果将被截断.

This returns a json string of ~43000 characters, with some results truncated.

SET @result = (SELECT * FROM table
               FOR JSON AUTO, ROOT('Data'))

这将返回〜2000个字符的json字符串.有什么方法可以防止截断吗?甚至在处理一些大数据时，字符串还有成千上万个字符吗?

This returns a json string of ~2000 characters. Is there any way to prevent any truncation? Even when dealing with some bigdata and the string is millions and millions of characters?

推荐答案

我没有找到官方"答案，但似乎这是新的"FOR JSON"语句的错误，该语句将结果拆分为行长2033个字符. 根据建议此处到目前为止，最好的选择是遍历结果连接返回的行:

I didn't find and 'official' answer, but it seems that this is an error with the new 'FOR JSON' statement which is splitting the result in lines 2033 characters long. As recommended here the best option so far is to iterate through the results concatenating the returned rows:

string result = "";
while (reader.Read())
{
    result += Convert.ToString(reader[0]);                        
}

顺便说一句，似乎最新版本的SSMS已经在应用这种解决方法，以便将结果显示在一行中.

BTW, it seems that the latest versions of SSMS are already applying some kind of workaround like this to present the result in a single row.

这篇关于SQL Server json被截断(即使使用NVARCHAR(max)时)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！