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
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.