0

So there is such a code:

DECLARE @DocJSONGoods nvarchar(MAX)
SELECT @DocJSONGoods = (
       SELECT ProductsItems, 
          DocCDProductsChapterNumber,           
          DocCDProductsNomenclatureData,    
              DocCDProductsPurchase,            
          DocCDProductsAmount,          
          DocCDProductsValue,               
          DocCDProductsCountry
              FROM @TabChaptersGoods
          FOR JSON PATH, ROOT('DocCDProductsList')
              )

which pulls 92 records from the table variable (some of which - DocCDProductsNomenclatureData,
DocCDProductsPurchase - are also nvarchar(MAX) and contain JSON) into the nvarchar(MAX) variable. Nothing unusual.

However, in the end we get only 29 full records in JSON and a trimmed 30 right across the nvarchar(max) field containing JSON.

SELECT LEN(@DocJSONGoods) issues 213565. Copy&Paste from SSMS Result Grid variable content of @DocJSONGoods into Notepad++ issues length 72133. Content is Latin and Cyrillic Unicod.

I am writing a completely stupid code where I concatenate records into @DocJSONGoods in a loop and get almost the same result in the end - the 30 entry is cut off (slightly in a different place) but still cut off.

What problem did I encounter? Why is this happening?

Charlieface
  • 52,284
  • 6
  • 19
  • 43
geenSf
  • 1
  • 1
    Does this answer your question? [SQL Server json truncated (even when using NVARCHAR(max) )](https://stackoverflow.com/questions/51087037/sql-server-json-truncated-even-when-using-nvarcharmax) – Thom A Apr 22 '23 at 07:41
  • Maybe it's SSMS. If you go to Tools > Options and then look into Query Results > SQL Server > Results to Grid what is your "Non XML data" maximum length setting? Check also in Results to Text. – AlwaysLearning Apr 22 '23 at 09:37
  • OMG! this is a real SMS!!! set the maximum value in Tools > Options > Query Results > SQL Server > Results to Grid and everything is OK! Thank you very much AlwaysLearning!! – geenSf Apr 22 '23 at 11:36
  • Side note: you don't need a variable, you can just use a straight subquery `SELECT (SELECT ... FOR JSON PATH ...);` – Charlieface Apr 23 '23 at 01:24

0 Answers0