How to write bcp command to create header columns in sql..?

Question

1.00/5 (1 vote)

See more:

SQL

exec master..xp_cmdshell 'BCP "select ''''empcode'''',''''fsname'''',''''ntype'''',''''desg'''',''''userid'''',''''dcr_count''''" queryout "d:\header.csv"  -c  -T -t'

when i execute the above query i would get error..i have mentioed below..please help me out..

SQLState = 37000, NativeError = 102
Error = [Microsoft][SQL Server Native Client 10.0][SQL Server]Incorrect syntax near ''.
SQLState = 37000, NativeError = 8180
Error = [Microsoft][SQL Server Native Client 10.0][SQL Server]Statement(s) could not be prepared.
NULL

Posted 26-Jun-15 0:02am

Member 11337367

Updated 26-Jun-15 0:09am

Andy Lanng

v2

Add a Solution

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Andy Lanng · Answer 1 · 2015-06-26T00:13:00

Solution 1

you can't use double quotes
use double single quotes instead. They should work fine:

SQL

exec master..xp_cmdshell 'BCP ''select ''''empcode'''',''''fsname'''',''''ntype'''',''''desg'''',''''userid'''',''''dcr_count'''''' queryout ''d:\header.csv''  -c  -T -t'

Posted 26-Jun-15 0:13am

Andy Lanng

Comments

Member 11337367 26-Jun-15 6:16am

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]
[-d database name]
NULL

this error is showing

Andy Lanng 26-Jun-15 6:31am

ok - that's good. It means that your syntax is now correct ^_^

This is, however, a different question. I have not used BCP before. I was able to help you with the question regarding syntax only :S

Please accept this solution and post a new question regarding the new error.

PS: I have done a quick search and it appears to be a well known confusion with BCP. I'm sure you'll get a good solution if you ask "Why do I get the error from BCP query 'Copy direction must be either 'in', 'out' or 'format'.'"

good luck ^_^

Member 11337367 26-Jun-15 7:33am

i have posted new question but still not yet answered..pls help me out

Andy Lanng 26-Jun-15 7:36am

have you searched google? There seem to be lots of people asking the same question.

Plus: it's Friday and this site is voluntary. It might just be bad timing :S