bcp password prompt
-
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
-
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) -
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?
-
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) -
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.
-
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) -
Peter, haveyou tried the -T option to connect with your windows id, then you will not need to enter your password.
george
---------------------------------------------------------------------
-
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) -
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 -
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.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply