SQL Server json truncated (even when using NVARCHAR(max) )

Question

DECLARE @result NVARCHAR(max);

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

SELECT @result;

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

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

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?

Answer 1

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]);                        
}

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.

Answer 2

I was able to get the full, non-truncated string by using print instead of select in SQL Server 2017 (version 14.0.2027):

DECLARE @result NVARCHAR(max);

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

PRINT @result;

Another option would be to download and use Azure Data Studio which I think is a multi-platform re-write of SSMS (similar to how Visual Studio was re-written as VS Code). It seems to spit out the entire, non-truncated json string as expected out of the box!

Answer 3

This will also work if you insert into a temp table - not presenting does not apply the truncate of SSMS. Might be usefull if you need to calculate several values.

declare @json table (j nvarchar(max));
insert into @json select * from(select* from Table where Criteria1 for json auto)a(j)
insert into @json select * from(select* from Table where Criteria2 for json auto)a(j)
select * from @json