August 3, 2021 at 4:28 pm
I'm pretty new a creating JSON output form SQL server. I'm having trouble with carriage returns being placed at inappropriate positions in the output when saving the results as a JSON file. Is there a setting or adjustment needed in my SSMS environment?
The data was created using the methods form blog post: https://www.sqlservercentral.com/blogs/how-to-create-multi-object-json-arrays-in-sql-server-2.
Output SQL:
SELECT
npi
,type
,name = JSON_QUERY(name, '$')
,facility_name
,facility_type = JSON_QUERY(facility_type, '$')
,addresses = JSON_QUERY(addresses, '$')
,specialty = JSON_QUERY(specialty, '$')
,accepting
,last_updated_on
,plans = JSON_QUERY(plans, '$')
FROM #Provjson
FOR JSON PATH
The sample output data test JSON file attached.
Thank you in advance to any help.
August 4, 2021 at 5:10 pm
Thanks for posting your issue and hopefully someone will answer soon.
This is an automated bump to increase visibility of your question.
August 4, 2021 at 8:38 pm
Found this solution on github: https://github.com/MicrosoftDocs/sql-docs/issues/1501
The problem seems to be that SSMS can't return it all at once properly so instead it simply appends results with a max result size of 2034, adds a line break (for some reason?) and repeats that until it all returns.
wrapping the select and cast as nvarchar(max) removes the carriage returns. That's odd so it's still a caveat I think that should be noted. Someone would try to copy the results and there could be line breaks in the results because of SSMS returning them unexpectedly throughout the data.
August 12, 2021 at 4:14 am
I've tested it on multiple strings with different outcomes and cannot figure out how exactly it works and what it does to a string, any help would be appreciated!
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply