How to use the bcp utility correctly? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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

General SQL Server Forums

New to SQL Server Programming

How to use the bcp utility correctly?

Author

Topic

Butterfly82
Starting Member

30 Posts

Posted - 2007-12-09 : 09:35:09

Hi guys,

I'm trying to use the bcp utility to transfer data from an SQL Server table (CUSTOMERS) to a text file (test.txt).

Here is what I have:

DECLARE @FileName varchar(50)
SET @FileName = 'C:\test.txt'

EXEC bcp BAirwaysDB..CUSTOMERS out @FileName -S<servername> -T

only I keep getting the error:
Msg 102, Level 15, State 1, Line 4
Incorrect syntax near '.'.

and when running:

bcp BAirwaysDB..CUSTOMERS out C:\test.txt -S<servername> -T

on the command line I get:
SQLState = 28000, NativeError = 18456
Error = [Microsoft][SQL Native Client][SQL Server]Login failed for user 'RIVERA\Melanie'.
SQLState = 42000, NativeError = 4060
Error = [Microsoft][SQL Native Client][SQL Server]Cannot open database "BAirways" requested by the login. The login failed.

Can you see where I'm going wrong with this? Any feedback would be really appreciated.

Thank you
Butterfly82

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2007-12-09 : 14:34:04

(1) You need to use dynamic SQL. (2) -T = Trusted/windows authentication. Make sure you have access.


DECLARE @FileName varchar(50), @Sql varchar(100)
SET @FileName = 'C:\test.txt'

Set @Sql = 'bcp "SELECT * FROM BAirwaysDB..CUSTOMERS" queryout ' + @FileName  + ' -S"<servername>" -T'

master..xp_cmdshell @Sql

Dinakar Nethi
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

jezemine
Master Smack Fu Yak Hacker

2886 Posts

Posted - 2007-12-09 : 15:46:37

or just run it from the cmd line and skip the xp_cmdshell business. is there any reason you need to do the export from inside a proc?

rmiao
Master Smack Fu Yak Hacker

7266 Posts

Posted - 2007-12-09 : 17:35:22

If not, dts or ssis is another option.

Butterfly82
Starting Member

30 Posts

Posted - 2007-12-09 : 22:17:23

No there is no reason I need to do it within a procedure. I tried this first at work as I've been asked to look into it and figure out how to transfer data using it I got a bit of a different error so came home and tried it here and got the errors I detailed above I feel it is a start if I figure out how to carry it out at home first.

I used -T at home as I thought I would have access here, I also tried putting in the username and leaving the password blank as I didn;t set up a password.

Dinakar thank you for the feedback. I tried what you sugested only I got the following error:
Incorrect syntax near 'master'.

I looked into DTS only I've been asked to look into bcp for the future. Thanks for that though rmiao your feedback is appreciated.

rmiao
Master Smack Fu Yak Hacker

7266 Posts

Posted - 2007-12-09 : 22:54:03

You can get bcp syntax by typing 'bcp /?' in dod prompt or look at books online.

monty
Posting Yak Master

130 Posts

Posted - 2007-12-09 : 22:55:44

*****Dinakar thank you for the feedback. I tried what you sugested only I got the following error:
Incorrect syntax near 'master'.******

type as exec master..xp_cmdshell @Sql

its me monty

Butterfly82
Starting Member

30 Posts

Posted - 2007-12-10 : 06:05:50

rmiao, I read up on the syntax, tag options before I gave it a go. Thanks for the info though.

Thanks for the input monty, I added 'exec' and found access was blocked to sys.xp_cmdshell I have to enable this using sp_configure. I'll keep you up to date on my progress.

Butterfly82

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources