bcp - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

Old Forums

CLOSED - General SQL Server

bcp

Author

Topic

notsosuper
Posting Yak Master

190 Posts

Posted - 2005-07-27 : 11:17:11

I didn't understand snytax that much, would you help me understand

bcp "SELECT au_fname, au_lname FROM pubs..authors ORDER BY au_lname" queryout Authors.txt -c -Sservername -Usa -Ppassword

when I run this in query analyzer, of course it doesn't understand queryout Authors.txt -c -Sservername -Usa -Ppassword this part. Do I need to create author.txt somewhere, what I need to do?

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2005-07-27 : 11:34:10

bcp is a command line (DOS) utility.
You need to run that from the dos propmpt.

If You want to do it from QA, use:
EXEC xp_cmdshell 'bcp "SELECT au_fname, au_lname FROM pubs..authors ORDER BY au_lname" queryout Authors.txt -c -S servername -U sa -P password'

I didn't understand snytax
You made my day

rockmoose

notsosuper
Posting Yak Master

190 Posts

Posted - 2005-07-27 : 11:41:12

Gooooooood! Since I made your day, I need more help :)
It is giving me this problem Could not find stored procedure 'xp_cmdshell'.
which is very normal,how will I create sp?

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2005-07-27 : 11:43:47

it's in the master database.
So You either have to be in the master db, or use:

EXEC master..xp_cmdshell '....'

rockmoose

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2005-07-27 : 11:48:06

>> which is very normal,how will I create sp?
That's the spirit !
If it doesn't work, fix it !

This one, You don't have to create Yourself though.
It's also an "extended" stored procedure, notice the xp_ prefix.
That means it is not SQL, but compiled in some dll that SQL server uses.

You could write Your own, but I don't recommend it

rockmoose

notsosuper
Posting Yak Master

190 Posts

Posted - 2005-07-27 : 11:50:20

Thank you so much rockmoose, but still gives me an error because again What it means queryout Authors.txt -c -S servername -U sa -P password'

do I need to create Authors.txt somewhere? and why -c - and S servername -U sa -P password' do I need to replace them with anything?

Thanks for the help

notsosuper
Posting Yak Master

190 Posts

Posted - 2005-07-27 : 11:55:01

just ignore that question I managed to exec and 23 rows copied but where?

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2005-07-27 : 11:57:26

quote:
Originally posted by notsosuper

Thank you so much rockmoose, but still gives me an error because again What it means queryout Authors.txt -c -S servername -U sa -P password'

do I need to create Authors.txt somewhere? and why -c - and S servername -U sa -P password' do I need to replace them with anything?

Thanks for the help

notsosuper,
replace servername with the name of YOUR server
password with YOUR sa - password, if You are going to login as sa
You get the point.

Authors.txt will be created for You, so You might want to use;
"C:\Authors.txt" so that You know where it will end up.
If You use xp_cmdshell, it will be on the Server machine.

-c is some switch that tells bcp to output in some format, You need to refer to BOL, for an explanation of all the switches.

You can also type bcp /? from the command prompt, to see a list of all the switches and parameters.

rockmoose

notsosuper
Posting Yak Master

190 Posts

Posted - 2005-07-27 : 11:59:50

THAT WAS A GREAT EXPLANATION!!! THANKS A LOT!!!

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2005-07-27 : 12:05:55

You are welcome,
Happy bcp:ing!!

rockmoose

notsosuper
Posting Yak Master

190 Posts

Posted - 2005-07-27 : 13:13:30

rockmoose I need one last favor, when I run this in master thing I don't get result, you think because aliases causing problem?
EXEC xp_cmdshell 'bcp "select a.contactid, a.clientid, a.contactname, b.contactid, b.contmethodtype, b.contmethodvalue, c.clientid, c.groupid, c.clientname,c.billingaddress1, c.billingcity, c.billingstate, c.billingzip
From contacts as a , contactmethods as b, clients as c
where a.contactid = b.contactid
and a.clientid = c.clientid
ORDER BY a.contactname, b.contactmethodtype " queryout C:\ddd.txt -c -S xxx -U -P '

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2005-07-27 : 13:35:06

Use:

From YOURDB..contacts as a , YOURDB..contactmethods as b, YOURDB..clients as c

When bcp logs in to the server, it uses the default database of the login used, usually master.

rockmoose

notsosuper
Posting Yak Master

190 Posts

Posted - 2005-07-27 : 13:46:08

so, if YOURDB = localtest then should I say From localtest..contacts as a , localtest..contactmethods as b, localtest..clients as c

or localtest.dbo

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2005-07-27 : 14:23:01

localtest.dbo
it's always best to fully qualify the objects.

localtest..
first sql server looks for objects owned by the user, then for objects owned by dbo, a little slower.

rockmoose

notsosuper
Posting Yak Master

190 Posts

Posted - 2005-07-27 : 14:32:33

I did this and not working..

EXEC xp_cmdshell 'bcp "select a.contactid, a.clientid, a.contactname, b.contactid, b.contmethodtype, b.contmethodvalue, c.clientid, c.groupid, c.clientname,c.billingaddress1, c.billingcity, c.billingstate, c.billingzip
From localtest.dbo..contacts as a , localtest.dbo..contactmethods as b, localtest.dbo..clients as c
where a.contactid = b.contactid
and a.clientid = c.clientid ORDER BY a.contactname, b.contmethodtype " queryout C:\ddd.txt -c -S xxx -U -P '

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2005-07-27 : 14:39:55

notsosuper, ddd.txt was created on the database server and not your client machine since you ran it inside Query Analyzer using xp_cmdshell. If you were to run it from your client machine via a cmd window, then it would create it on your machine. So go check out the database server and find that file.

Tara

notsosuper
Posting Yak Master

190 Posts

Posted - 2005-07-27 : 14:52:39

but it created in my c drive before with authors table when I added three different table and alias started cause problem, where do I need to look for this file? so I can be able to do in my machine only with dos right?

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2005-07-27 : 14:54:49

localtest.dbo..contactmethods

How many qualifiers do You need, darnit.
You got the snytax wrong. LOL.

localtest.dbo.contactmethods <--------------------

>> So go check out the database server and find that file
two blocks away

Already pointed that out to him.

rockmoose

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2005-07-27 : 14:55:12

You must have the database server local then, so the db server and client machine are one and the same. Are you getting an error in Query Analyzer?

Go to Start..Run. Type in cmd and hit enter. Use bcp.exe command from there. bcp /? will show you its options.

Tara

notsosuper
Posting Yak Master

190 Posts

Posted - 2005-07-27 : 15:05:17

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

that's what i get

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2005-07-27 : 15:08:50

I think the hard returns in the query are giving you problems as I was able to replicate your problem and fix it by doing this this:



EXEC xp_cmdshell 'bcp "select a.contactid, a.clientid, a.contactname, b.contactid, b.contmethodtype, b.contmethodvalue, c.clientid, c.groupid, c.clientname,c.billingaddress1, c.billingcity, c.billingstate, c.billingzip From localtest.dbo.contacts as a , localtest.dbo.contactmethods as b, localtest.dbo.clients as c where a.contactid = b.contactid and a.clientid = c.clientid ORDER BY a.contactname, b.contmethodtype" queryout C:\ddd.txt -c -Sservername -Usa -Psapwd'

Tara

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources