Json OutPut Truncation

_{Wednesday, November 27, 2019 9:06 AM}

Hello Everyone,

When using Json_Query function OR Assign Json Output to a variable , the results are truncating. It is showing only upto 43680 characters only.

Using Json Query

========

select Json_Query(select * from mytable for json path) as JsonTestResult set truncating

Assign Json Output to variable

===================

declare @json varchar(max)=(select * from mytable for json path)Result set truncating

Kindly provide any solution for this.

Anil

_{Wednesday, November 27, 2019 11:03 AM ✅Answered}

It seems SSMS UI issue as I exported data to test file using SSIS and no truncation happened.

Anil

_{Wednesday, November 27, 2019 9:58 AM}

Can you provide sample data for testing?

Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

_{Wednesday, November 27, 2019 10:00 AM}

See this thread

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;

Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

_{Wednesday, November 27, 2019 10:17 PM}

Download and install SSMS 18 which resolves this issue. SSMS 18 also permits you to set a higher max, up two million characters.

Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Additional resources