SQL 2019 : Json Data in Resultset gets truncated

Question

Hi,

I am using SQL 2019. I have a table where I have a large number of rows (around 100K+). I want to leverage on SQL to return the data in JSON format. I have used both FOR JSON AUTO &

for json path, Root('Data'), but my results are getting truncated in the output to gird.

I have tried setting the max allowed in the Options -> Query Results -> Results To Grid to the following, but I am still not noticing any change.

• Non Xml Data: 2097152
  • XML Data: Unlimuted

Appreciate any directions on the above.

Thanks

Answer 1

Hi @Soni Samuel Panackacheril ,

Agree with Erland. You can use print instead of select. Another option would be to download and use Azure Data Studio which is a visual platform like SSMS. It seems to spit out the entire, non-truncated json string as expected out of the box!

Best regards,
Seeya

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 2

Are you trying this in SQL Management studio? Try the following

DECLARE @RESULT NVARCHAR(MAX);  
  
SET @RESULT = (<<Your select query here that returns the json data>>)  
  
PRINT @RESULT;  

Hope this helps

Answer 3

This is an issue in SSMS (or rather the .NET Framework) that does not handle the result from FOR JSON correctly. This result comes back with a certain GUID as the column name, and then this is not handled correctly.

The way to work around is as sreejukg shows. Almost. That is, assign the output from FOR JSON to a variable. But you should return the value with SELECT, and not with PRINT, since PRINT is limited to 8000 bytes.

 DECLARE @RESULT NVARCHAR(MAX);  
 SET @RESULT = (<<Your select query here that returns the json data>>)  
 SELECT @RESULT;

Answer 4

are getting truncated in the output to gird.

Yes, the output to grid is very limited. Keep inb mind, SSMS = Management Studio is a tool to manage SQL Server, it's not really intended to be a data frontend.