Search code examples
jsonsql-servert-sqlbcp

SQL Bulk Copy Utility adding Carriage Returns to JSON files

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: enter image description here

Solution

  • 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 [DatabaseName].[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.