BCP queryout not working

Question

Hi, i am trying to run following script but i am getting error : Incorrect syntax near queryout 

Here's the script,bcp "SELECT * FROM QA_PLANETWHOLESALE.dbo.T_BE_2_DUNS" queryout "J:\sql_backup_DO_NOT_DELETE\T_BE_2_DUNS.dat" -T -n -S "WDWZP4065\SIT"

I have tried online for answer but didnt help.

Answer 1

You have to execute the bcp at Command Prompt.

Alternate: use xp_cmdshell execution.

xp_cmdshell execution examples:

http://sqlusa.com/bestpractices2005/notepad/

---

Kalman Toth SQL SERVER 2012 & BI TRAINING

Answer 2

Thanks SQLUSA for your reply,

I tried it in command promt but i am getting error : unable to open BCP host data file.

between i will try to use xp_cmdshell.

Answer 3

If does not work at Command Prompt, it will not work with xp_cmdshell. They are the same thing. Make sure the entire command is on ONE line.

Maybe the path is not good.  Related threads:

http://social.msdn.microsoft.com/Forums/en-US/sqlexpress/thread/419e024e-cc8b-4be4-b4f3-07ce3cfdd1f1

http://social.msdn.microsoft.com/Forums/en-US/sqldatabaseengine/thread/bdb7df37-14af-48d5-857c-67ce6d0460a2

---

Kalman Toth SQL SERVER 2012 & BI TRAINING

Answer 4

Just curious if your J drive is a network drive. try queryout to a local drive and see if it works.

---

 | Blog: MSBICOE.com | MCITP - BI, SQL Developer & DBA

Hate to mislead others, if I'm wrong slap me. Thanks!

Answer 5

Hi, if i do something like,

exec master..xp_cmdshell "BCP SELECT *  FROM QA_PLANETWHOLESALE.dbo.T_BE_2_DUNS QUERYOUT C:\BE_2_DUNS.txt -T -c"

I get error :

Copy direction must be either 'in', 'out' or 'format'.
usage: BCP {dbtable | query} {in | out | queryout | format} datafile
  [-m maxerrors]            [-f formatfile]          [-e errfile]
  [-F firstrow]             [-L lastrow]             [-b batchsize]
  [-n native type]          [-c character type]      [-w wide character type]
  [-N keep non-text native] [-V file format version] [-q quoted identifier]
  [-C code page specifier]  [-t field terminator]    [-r row terminator]
  [-i inputfile]            [-o outfile]             [-a packetsize]
  [-S server name]          [-U username]            [-P password]
  [-T trusted connection]   [-v version]             [-R regional enable]
  [-k keep null values]     [-E keep identity values]
  [-h "load hints"]         [-x generate xml format file]
NULL

--Wondering if i dont have permission to run BCP on my machine.. The BCP in my question works on other system.

Thanks

Answer 6

First make it work at Command Prompt.

It may be a path issue.

Right now you are missing QUERYOUT in the command.

See bcp example here:

http://www.sqlusa.com/bestpractices/imageimportexport/

---

Kalman Toth SQL SERVER 2012 & BI TRAINING

Answer 7

The syntax should be:

exec master..xp_cmdshell 'BCP "SELECT *  FROM QA_PLANETWHOLESALE.dbo.T_BE_2_DUNS" QUERYOUT C:\BE_2_DUNS.txt -T -c'

You must wrap the query in double quotes, to make it a single argument to BCP.

But I agree with Kalman; have your command working from a normal command-line window first. When you have that part working, you can try it from xp_cmdshell - if you really need to run it from within SQL Server.

---

Erland Sommarskog, SQL Server MVP, [e-mail]

Answer 8

Thanks SQLUSA,

Your link helped. It works now.

EXEC xp_cmdshell 'bcp "SELECT *  FROM QA_PLANETWHOLESALE.dbo.DNB_Prospect" QUERYOUT J:\sql_backup_DO_NOT_DELETE\DNB_Prospect.dat -T -n -SWDWZP4065\SIT'

Answer 9

Hello SQLUSA,

One last question, the bcp worked fine on DEV server but if i run it on TEST server if get permission issues saying i dont have permission to exectue xp_cmdshell. So i tried to activate it using,

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

still i am getting error : Login failed for user 'xx\servername$'

Answer 10

Such is life, you can't expect everything to work out of the box. Not the least when it comes to installation-specific configuration like logins.

xxx\servername$ is a machine account, and I think this means that SQL Server runs under Local Service or similar. You would need to add that machine account as a user in SQL Server, or change the service account to a domain account.

Or maybe you should think a second time whether you are on the right track. You ran into to that xp_cmdshell is disabled - there is a good reason for this. If xp_cmdshell is enabeled, an intruder that finds his way into SQL Server can advance in the network with help of xp_cmdshell.

Maybe you should run this export from a client-side program, or do an SSIS package instead. That would relieve you from many of the configuration hassles.

---

Erland Sommarskog, SQL Server MVP, [e-mail]