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 –