2

I'm scheduling a task in Windows to connect to an off-site SQL Server every night to run a query and save the results as a .CSV file. I'm able to connect to the SQL Server using SSMS with no problems at all. I'm able to connect to the SQL Server manually using SQLCMD from a command prompt with no problems at all. My issue is I cannot use SQLCMD within a batch file. The syntax I'm using is......

sqlcmd -S ServerName\InstanceName -d DatabaseName -U UserName -P Password

If I manually enter that into my command window, it works like a champ.

If I try to connect using a batch file, I get the error, "Msg 18456, State 1, Server ServerName\InstanceName, Line 1 Login failed for user 'UserName'.

I've also tried right clicking the batch file and running as administrator with no success. Thank you in advance for your help.

Travis

UPDATE

I just now discovered that if you take the -P Password parameter out of the batch file, you will be prompted for the password. After entering my password, I'm logged in successfully. Are there limitations on passing passwords to the SQL server from a batch file? Being that this is an off-site server that's not on our network, I'm unable to use Windows Authentication.

Improve this question
3
  • For me my issue was encoding. When I made bat file to be utf-8 things worked. Previously it was on ucs-2 Commented Jul 5, 2017 at 11:33
  • This didn't work for me. If I left out the -P parameter, when the script is ran it fails with a "The last operation was terminate because the user pressed CTRL + C" Commented Jan 23, 2023 at 21:16
  • Saving batch as UTF8 didn't work me neither. Commented Jan 23, 2023 at 21:16

3 Answers 3

Reset to default
8

I had a similar issue where sqlcmd worked in the command window when typed manually, but not in a batch file.

What I worked out was that my password contained a %. In a batch file this is a special character and it was not being echoed to the command window.

The solution was to use %% to escape it.

Improve this answer
Sign up to request clarification or add additional context in comments.

1 Comment

This can definitely be an issue I spent 30 minutes trying to figure this out! Thanks!
1

Try this on notepad

sqlcmd -S (IP OR SERVER)\SQL2008 -d database -U user -P pass -q "SELECT * FROM TABLE"

and save it like .bat

And Windows Authentication

sqlcmd -S (IP OR SERVER)\SQL2008 -d database -E -q "select * from foliosiac"
Improve this answer

1 Comment

Thank you for your reply. It gives me the same results as before. If I manually type in the command provided into a DOS window, it works fine. If I save it in Notepad as a .bat file and try to run it, it gives me the same error I mentioned in my post.
0

If you try with a .bat file in a Notepad as suggested below:

Try applying /S , '/' character instead of -S:

sqlcmd /S (IP OR SERVER)\SQL2008 /d database /U user /P pass /q "SELECT * FROM TABLE"

Improve this answer

1 Comment

That won't work. It would fail with "invalid option /S"

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.