CODEWITHSUNDEEP
sql-serverbatch-filesqlcmd
TravisK76
TravisK76

Reputation: 21

Unable to connect to SQL Server using SQLCMD via a batch file

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.

Upvotes: 2

Views: 10552

Answers (3)

David Glickman
David Glickman

Reputation: 805

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.

Upvotes: 8

Alberto Montellano
Alberto Montellano

Reputation: 6266

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"

Upvotes: 0

user3357576
user3357576

Reputation: 41

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"

Upvotes: 1

Related Questions