bcp command not working

Question

I'm using the following bcp command

BCP "select * from PACIFIC.dbo.CEOExtractCorpRulesView with(nolock) " 
   queryout "d:\temp\CEOExtractCorpRulesView.txt" 
   -S"tcp:BCBWEC-VIRTD2\SQL2005" -c -t"|!" -U"userid" -P"password" 

I get an error every time

Msg 102, Level 15, State 1, Line 1 Incorrect syntax near 'queryout'.

Please let me know what the problem might be. This is not my script.

Thanks

Solution 1

I get the same error if I try and execute this script by pasting it into SSMS and clicking execute.

AFAIK you need to open a command window and run this.

Solution 2

If you have access to xp_cmdshell you can call bcp from that

exec xp_cmdshell 'BCP "select * from PACIFIC.dbo.CEOExtractCorpRulesView with(nolock) " 
   queryout "d:\temp\CEOExtractCorpRulesView.txt" 
   -S"tcp:BCBWEC-VIRTD2\SQL2005" -c -t"|!" -U"userid" -P"password" '

NOTE: I have not checked the above for proper character escaping... That's a pain.

Otherwise, you will have to run bcp from command line. It's a command-line tool. The T-SQL interpreter knows nothing of it.

Solution 3

Agreed with Martin. BCP is for the C prompt not for SQL Server Management Studio. To run it inside SSMS you need to use xp_cmdshell (and be logged as a user with rights to get to xp_cmdshell).