Search code examples
sqlbcpxp-cmdshell

SQL Server BCP function will not write a file

After downloading the BCP utility to sql server, I ran this code to allow me to run the BCP from inside SSMS:

EXEC sp_configure 'show advanced options', 1
GO
RECONFIGURE
GO
EXEC sp_configure 'xp_cmdshell', 1
GO
RECONFIGURE
GO

Now I am trying to write a test file to my computer with this code:

exec xp_cmdshell 
'bcp 
"select top 5 patientid from tpsqldb1.cdw.dbo.pmview" 
queryout
"c:\users\pmckann\documents\test.csv" -T t, -S TPSQLDB1\MSSQLSERVER
'

But no file is written. It says query executed successfully at the bottom, but no file is created. The rest of the output is this:

enter image description here

Any pointers, no matter how basic, would be greatly appreciated.

Solution

  • OK. Kashif Qureshi had a good answer but I want to be more specific about why my original code didn't work. You can't separate out the lines of the bcp command. It must be in a single line. Also, I removed the -S line.:

    EXECUTE master..xp_cmdshell 'bcp "select top 5 * from cdw.dbo.pmview" queryout C:\temp\test.csv -t, -c -T '

    Thanks everybody for your help.