SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 How to use the bcp utility correctly?
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Butterfly82
Starting Member

30 Posts

Posted - 12/09/2007 :  09:35:09  Show Profile  Reply with Quote
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



Edited by - Butterfly82 on 12/09/2007 09:36:02

dinakar
Flowing Fount of Yak Knowledge

USA
2507 Posts

Posted - 12/09/2007 :  14:34:04  Show Profile  Visit dinakar's Homepage  Reply with Quote
(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/
Go to Top of Page

jezemine
Flowing Fount of Yak Knowledge

USA
2885 Posts

Posted - 12/09/2007 :  15:46:37  Show Profile  Visit jezemine's Homepage  Reply with Quote
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?


elsasoft.org
Go to Top of Page

rmiao
Flowing Fount of Yak Knowledge

USA
7266 Posts

Posted - 12/09/2007 :  17:35:22  Show Profile  Reply with Quote
If not, dts or ssis is another option.
Go to Top of Page

Butterfly82
Starting Member

30 Posts

Posted - 12/09/2007 :  22:17:23  Show Profile  Reply with Quote
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.

Edited by - Butterfly82 on 12/09/2007 22:19:12
Go to Top of Page

rmiao
Flowing Fount of Yak Knowledge

USA
7266 Posts

Posted - 12/09/2007 :  22:54:03  Show Profile  Reply with Quote
You can get bcp syntax by typing 'bcp /?' in dod prompt or look at books online.
Go to Top of Page

monty
Posting Yak Master

130 Posts

Posted - 12/09/2007 :  22:55:44  Show Profile  Reply with Quote
*****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
Go to Top of Page

Butterfly82
Starting Member

30 Posts

Posted - 12/10/2007 :  06:05:50  Show Profile  Reply with Quote
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
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.11 seconds. Powered By: Snitz Forums 2000