user1260251 user1260251 - 2 months ago 12
SQL Question

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.

Answer

OK. Qashif Kureshi 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.