Problems executing a bcp command 1

[collierd]

Hello

I am using SQL 2000

Does anybody know what is wrong with the following command
I am simply typing this in exactly as you see it and executing it within Query analyser

bcp "select * from mdw..product_duplications order by productid" queryout "c:\proddup.csv" -c -T -t',' -r\n\r

I have tried numerous variances on the select statment, i.e. reduced statement, single quotes, default database is mdw therefore remove this part, and the problem doesn't seem to lie there
Also the same with the file to copy to (proddup.csv)

It returns the following every time:

Line 10: Incorrect syntax near query out

Thanks

Damian

 

[MeanGreen]

I added dbo for owner and added a space after the -t. You might also verify that you have Trusted Connection rights.
Try this:

bcp "select * from mdw.dbo.product_duplications order by productid" queryout "c:\proddup.csv" -c -T -t ',' -r\n\r

Hope this thlps.

 

[collierd]

Unfortunately not
I have trusted connection rights
Is there anything else required for executing 'bcp'

 

[MeanGreen]

I ran your BCP statement against the northwind.dbo.orders table and it worked fine with my changes. Have you tried to run the above query that I submitted? Have you tried running that query within Query Analyzer under your trusted connection? The statement should work. Let me know if it is not working. I used double quotes for sql statement and queryout, but single quotes for seperator.

Hope this helps.

 

[collierd]

Basically, I cut and paste your statment so it is exactly the same
We have been using trusted connections, i.e. Windows authentication driven rather than sql
I can run any query, update, delete etc I want but bcp still won't execute correctly

Damian.

 

[tlbroadbent]

Are you running the BCP from the command prompt or in Query analyzer? BCP is a command line utility. Terry L. Broadbent - DBA
Computing Links:

http://tlbroadbent.home.attbi.com/prog.htm

faq183-874 contains "Suggestions for Getting Quick and Appropriate Answers" to your questions.

 

[collierd]

I have tried running it from query analyzer
and running it as:

exec master..xp_cmdshell "bcp 'select * from mdw.dbo.product_duplications order by productid' queryout 'c:\proddup.csv' -c -T -t ',' -r\n\r"

The resultant is:

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"]
NULL

I don't know why?

Any thoughts - its seems to be getting closer

Damian.

 

[collierd]

The following works but doesn't put headers on, skips a line between each and I can't use a more specific select

exec master..xp_cmdshell "bcp mdw.dbo.product_duplications out c:\proddup.csv -c -T -t, -r\n\r"

Any thoughts?

Thanks

Damian

 

[tlbroadbent]

Try the following.

exec xp_cmsshell 'bcp "select * from mdw..product_duplications order by productid" queryout "c:\proddup.csv" -c -T -t'','' -r\n\r'
Terry L. Broadbent - DBA
Computing Links:

http://tlbroadbent.home.attbi.com/prog.htm

faq183-874 contains "Suggestions for Getting Quick and Appropriate Answers" to your questions.

 

[collierd]

Terry

The following actually works after removing the quotes around the comma
Thanks for your help
The only problem now is that the headers are missing

Any thoughts about that

exec xp_cmdshell 'bcp "select * from mdw..product_duplications order by productid" queryout "c:\proddup.csv" -c -T -t, -r\n'

Thanks

Damian.

 

[collierd]

Sticking with that, it solves the problem