bcp password prompt

Question

Post reply

bcp password prompt

pveilleux

Ten Centuries

Points: 1363
More actions
September 17, 2007 at 9:07 am

#197844

good morning,
I have written a sql script to build some bcp out statements in a file to be run as a batch. In this script, I have coded in the password (I know - not good practice, but its a test system). Everything looks good with the batch file the script creates. However, when I run the batch file, I am prompted for a password, even though it is in the script. When I run the command exactly as it appears in the batch file, it runs without prompting for a password.
Environment is SQL 2000 SP3 and bcp 8.00.832
anyone have any idea on how to prevent the prompts? I have over 800 tables to bcp out and can't really type in the password 800+ times.
thanks....
Peter

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1004403 More actions · Answer 1

It would be good if you posted one of the offending lines of code (sans password)... there's lot's of options in BCP...

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

TK-487581 Mr or Mrs. 500 Points: 586 More actions · Answer 2

have you tried what happens if you don't type in the password. This is a hit and try method. The other thing could be that it is not recognizing that you have enterd a password - so check there is no space between them.

I remember writing bcp commands without problem. we even used the environment variables to save passwords.

btw, just wondering, is using the environment variables to save passwords a good practice?

Jeff Moden SSC Guru Points: 1004403 More actions · Answer 3

Or, the OP could post the offending code, sans password, so we can actually try to troubleshoot the command for him

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

pveilleux Ten Centuries Points: 1363 More actions · Answer 4

I figured it out. For a field terminator I was using a pipe, but I was using the notation we use when doing it in Unix. It looked like this:

-t\|

when it should have looked like this:

-t"|"

once I made the change, I was no longer prompted and bcp used the password I provided. I don't know exactly why \| didn't work, so if anyone can explain that, great.

Jeff Moden SSC Guru Points: 1004403 More actions · Answer 5

Because the "pipe" character is not one of those "special" characters... \t doesn't mean "use [t] as the delimiter", for example... it means "use a tab as the delimiter". the [|] character is not on the reserved list of characters so it must be a literal in the command...

... and thanks for the feedback. Most folks just go on their merry way when they figure something out for themselves...

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

george sibbald SSC Guru Points: 104210 More actions · Answer 6

Peter, haveyou tried the -T option to connect with your windows id, then you will not need to enter your password.

george

---------------------------------------------------------------------

Jeff Moden SSC Guru Points: 1004403 More actions · Answer 7

Great idea, George... just keep in mind that only works if you're calling BCP via xp_CmdShell in T-SQL or you have Windows Authentication enabled on the server...

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Sergiy SSC Guru Points: 110208 More actions · Answer 8

Jeff Moden (21/09/2007)
Great idea, George... just keep in mind that only works if you're calling BCP via xp_CmdShell in T-SQL or you have Windows Authentication enabled on the server...

... and you don't use "Local System account" to start SQL Server. :hehe:

_____________
Code for TallyGenerator

pveilleux Ten Centuries Points: 1363 More actions · Answer 9

george sibbald (9/21/2007)
Peter, haveyou tried the -T option to connect with your windows id, then you will not need to enter your password.
george

I did try that but it was still failing because of the \|. Once I resolved that, both -T and -U, -P worked fine.