CODEWITHSUNDEEP
sqlbcpxp-cmdshell
user1260251
user1260251

Reputation: 83

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.

Upvotes: 0

Views: 1609

Answers (2)

user1260251
user1260251

Reputation: 83

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.

Upvotes: 0

Kashif Qureshi
Kashif Qureshi

Reputation: 1490

This has allways work for me:

 Exec Master..xp_cmdshell 'bcp "select top 5 columnname from dbname.dbo.tablename" queryout "c:\temp\test9.csv" -c -U username -P password -S servername\instancename'

Upvotes: 1

Related Questions