SQL Bulk Copy Utility adding Carriage Returns to JSON files-CodePudding

We're using a SQL bcp call to write data from a stored procedure out to a JSON file. When I run it and open up that file there are CRLF's added and it's breaking it into lines. This causes problems with validation and if you try to format with Notepad or JSON Buddy it throws a parsing error.

set @cmd = 'bcp "exec [OurServer].[dbo].[sp_OurStoredProcedure]" queryout "' @outputFile '" -UTF8 -T'
exec master..xp_cmdshell @cmd

The stored procedure is using FOR JSON PATH at the end of the my SELECT statement to parse my results into JSON.

The output looks like this in Notepad when viewing all characters:

CodePudding user response：

We were finally able to get the desired output. We had been trying to CAST the result as an nvarchar(MAX) like this:

SELECT CAST(
            (SELECT ProductID 'ID', ProductCost 'Cost' 
             FROM products FOR JSON PATH, ROOT('feedProducts') 
            ) 
            AS NVARCHAR(MAX)) AS feedData

After a lot of trial and error we loaded into a variable directly and then returned that variable from our stored procedure like this:

DECLARE @txtResult NVARCHAR(MAX)
SET @txtResult = (
                  SELECT ProductID 'ID', ProductCost 'Cost' 
                  FROM products FOR JSON PATH, ROOT('feedProducts') 
                 )
SELECT @txtResult as feedData

When we call our bcp utility below the second procedure works, while the first one does not.

bcp "exec [ReplinkDotNetTest].[dbo].[sprocName]" queryout "' @outputFile '" -T -w

The first procedure cuts off our data and does not give us a complete result while the second procedure gives us the entire return and we get the file we have been looking for.