Hi guys,

I desperately need help with my BCP script everything works good so far I do not add in the script my password as -P actualpassword

However I would like to know how to do this in the script since I need to import plenty of files into mysql server.
The script I have looks like this

@echo off
bcp TestDB3.dbo.xxx_data in "Z:\path\data\XXX.txt" -F 2 -S servername -U MY_USER_NAME -f "Z:\path\data\format-data.fmt"
pause

the above script works fine but, asks me all the time to prompt my password. However if if I try to add the password as in the following scrip it will complain that the login has failed:


@echo off
bcp TestDB3.dbo.xxx_data in "Z:\path\data\XXX.txt" -F 2 -S servername -U MY_USER_NAME -P xxxxxxx -f "Z:\path\data\format-data.fmt"
pause


Any ideas how to fix this? Thanks in advance.

SQL login or Windows authentication?

sql server authentication

I wouldn't have thought having a command file with both the username and password to an SQL account that has permissions to modify data is a good idea. Have you considered using windows auth (-T)? Remember that if you have this script set up to run in a job then it runs using the credentials of the account that SQL Server Agent uses.

If you insist on using SQL auth then have you confirmed your ability to connect directly to the SQL instance (using SSMS for instance) using the details in your script? If you can then check the accounts ability to modify data.

It will also be useful if you post the actual error message in full.

Thanks for the answer, I have considered to use windows auth, but I would like to know why there is a -P option if it does not work?

This is the error I get:

SQLSTATE = 28000, NativeError = 18456
Error = [Microsoft] [SQL Server Native Client 10.0] [SQL Server]Login failed for user 'myname'

The -P option does work

So the login is failing. Check that you can connect to the instance using SSMS with these SQL login details (change the drop down from Windows Auth to SQL Auth when connecting). Then check that this account can access the database in question.

Do you have any special characters in the password that may need to be escaped?

My first guess is that there is a Windows "special character" inside the password.

Purely for testing purposes to see if I'm correct, create a second SQL Authenticated user with only letters and numbers in both the user name and the password (no special characters at all). Modify your script to use this username and password.

If the modified script will run using the sanitized username and password, then we need to explore which characters you are using and how you need to work around the problem. You can find details at remarks if this is the case.

-PatP

Is the password password?

Sorry for the late answer, and thanks for the help guys. The password is exactly the same password, since it is a script I am able to see with letters the password, so I am 100% sure I am not missing something, also there are no slashes on it that might make believe the password has some special characters.

I think the easier thing will be to get a windows authentication, rather than sql.

Thanks a lot!