SQL 'For json' query for over 100,000 records - truncating results

[bmacbmac]

Hello!

I have over 100,000 records in SQL that I need exported to json. My query is:

SELECT documentinfo.irecordid,
(select tempparties.* from tempparties WHERE tempparties.irecordid = documentinfo.irecordid and tipartytype = 1 FOR JSON AUTO) AS first,
(SELECT tempparties.* from tempparties WHERE tempparties.irecordid = documentinfo.irecordid and tipartytype = 2  FOR JSON AUTO) AS last,
(SELECT tempimages.* from tempimages WHERE tempimages.irecordid = documentinfo.irecordid FOR JSON AUTO) AS images,
(select templand.* from templand WHERE templand.irecordid = documentinfo.irecordid FOR JSON AUTO) AS land,
(SELECT temprelated.* from temprelated WHERE temprelated.irecordid = documentinfo.irecordid FOR JSON AUTO) AS relateds
FROM documentinfo as documentinfo
FOR JSON AUTO

I need to save results to a file for a client, but if I save results to file, the data becomes truncated pretty early on in the file. I have gone into options and set the grid and text results to the max size, but still can't get it to work.

Any advice on exporting a large number json records to a data file?

 

[fredericofonseca]

yes. Don't use SSMS for this - it is not what it was made for.

get a powershell script (some examples at

https://www.sqlshack.com/working-with-powershells-invoke-sqlcmd/)

or use plain sqlcmd

Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

FAQ219-2884
FAQ181-2886

 

[bmacbmac]

Ah yes.. sqlcmd was the way to go!