0

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.

user1260251
  • 83
  • 1
  • 1
  • 8
  • is `MSSQLSERVER` your database name? – Vamsi Prabhala Sep 21 '16 at 15:29
  • Do you really need to call BCP from SQL? There is nothing wrong with that, but it is not really intended for that invocation method. If you are already in SQL, you can use `openrowset` with Ace provider to write to a text file. And in your particular case you got command line arguments wrong, read Books Online and test from `cmd.exe` window to get them right first. – ajeh Sep 21 '16 at 15:30
  • try using `exec xp_cmdshell 'bcp "select top 5 patientid from tpsqldb1.cdw.dbo.pmview" queryout "c:\users\pmckann\documents\test.csv" -T -S TPSQLDB1 -d cdw'` – Vamsi Prabhala Sep 21 '16 at 15:31
  • No, TPSQLDB1\MSSQLSERVER is what I understood to be my server name and server instance. I tried vkp's code taking out \MSSQLSERVER and adding the database name cdw (-d cdw) and it still did not write. – user1260251 Sep 21 '16 at 15:45
  • Ajeh, what command line arguments did I get wrong? – user1260251 Sep 22 '16 at 20:34

2 Answers2

1

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'
Kashif Qureshi
  • 1,460
  • 2
  • 13
  • 20
0

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.

Kashif Qureshi
  • 1,460
  • 2
  • 13
  • 20
user1260251
  • 83
  • 1
  • 1
  • 8