Search code examples
sqlexcelt-sqlxp-cmdshell

xp_cmdshell queryout is generating a Excel file correctly but with ONE blank line in the middle of the file

I have a stored procedure that runs once at the end of every month.

It generates a few files. One of them is a .csv file.

SELECT @Command = 'bcp "EXEC DB1.dbo.spCreateFile" queryout "' + @LocalFilePath + @MainFileName + '" -c -T -x'

EXEC master..xp_cmdshell @Command

This creates the file OK. The problem is that in the middle of the file, there is a BLANK line. I'm trying to understand why that would happen. I'm thinking maybe it has to do with the size of the queryout results? The query returns over 15k records to put in the Excel file. Is it trying to break it down?

Does anyone have any clue. The problem here is that it's extremely hard to troubleshoot this because syntax wise/logic wise, this is OK so I have no clue what the issue could be.

Any help is greatly appreciated.

Here's a screenshot of the excel file:

Solution

  • I found out what the issue was guys.

    It's the query that's bringing back a null row.

    I will need to take a look at the query. Unfortunately I'm going to hate looking at it because it's massive and 1 value in it is nulling everything out for that row.