SSMS – Selecting rows as json truncates long json strings

Nov 18, 2018 One-minute read

T-SQL • SQL Server • SSMS • JSON • Azure Data Studio

In the newer versions of Microsoft SQL Server, it is now super easy to return selected rows as json data.

Unfortunately, SSMS seems to truncate json strings that are longer than 2033 chars no matter what you do. This seems to happen no matter if you show the results in the grid, text, or a file.

Here is a workaround using a variable and PRINT if you want to just get the json string on your clipboard:

DECLARE @RESULT NVARCHAR(MAX);

SET @RESULT = (SELECT * FROM TABLE FOR JSON AUTO, ROOT('Data'))

PRINT @RESULT;

Otherwise, you could always download Azure Data Studio which seems to show results without truncation issues. If you are unfamiliar with ADS, it is basically a multi-platform re-write of SSMS.