Quite recently, as part of some project activity I had to export some data to a flat file using the good old bcp command using the xp_cmdshell. I had put all the right arguments but still I was getting an error. It was only then I discovered this strangeness of the error.

Let me explain with the example. Let’s take this simple query from the good old AdventureWorks, output as the set that we would like to export as shown below –

select top 3
FirstName
,LastName
,MaritalStatus
from AdventureWorksDW2008R2.dbo.DimCustomer

Below is the full query that would be used to get the file output via the bcp.

declare @sql varchar(200)
,@bcpCommand varchar(4000)
,@outputFilePath varchar(200)
--set the sql query
set @sql = 'select top 3
FirstName
,LastName
,MaritalStatus
from AdventureWorksDW2012.dbo.DimCustomer'
--set the output file path
set @outputFilePath = 'C:\Users\vkarthik\Documents\Learning\PractiseData\Output.txt'

--set the bcp command
set @bcpCommand = 'bcp "'+ @sql + '" queryout "'+@outputFilePath+'" -S"." -T -t","'

--execute the bcp command
exec master..xp_cmdshell @bcpCommand

Before running the command ensure that xp_cmdshell configuration is enabled. Just follow this link

Here is the output you would get.

Just ‘NULL’. It just doesn’t make any sense right? It is only after series of searches I have found out. The query that we use should not contain any carriage returns. So here is what I did. I added a replace command to strip out the carriage returns as shown below right after setting the query-

--set the sql query
set @sql = 'select top 3
FirstName
,LastName
,MaritalStatus
from AdventureWorksDW2012.dbo.DimCustomer'
--replacing the carriage returns
set @sql = REPLACE(REPLACE(@sql,CHAR(13),''),CHAR(10),'')

Now that you are set, on re-run the command should work.

Here is the result below –