CODEWITHSUNDEEP
sql-serverbatch-file
JonnyBravoJr
JonnyBravoJr

Reputation: 338

sqlcmd doesn't run from .bat file, but it does work from a command line

I am trying to run a simple batch file that calls a backup script I have in SQL Server. When I enter this into a command line, it works but when run from a .bat file, nothing happens. No errors, no output at all.

sqlcmd.exe -S WIN-FPQSHCEB0EM -E -Q "EXEC sp_BackupDatabases @backupLocation='E:\Backups\', @databaseName=’xxxxxx’, @backupType='F'"
PAUSE

Any idea why this wouldn't work?

Upvotes: 3

Views: 5608

Answers (2)

Darrin
Darrin

Reputation: 416

I struggled for a few hours with this same problem. I was using "sqlcmd.exe -U sa -P $fancy!Paswd ..." from within a .bat file. Now, on the cmd.exe prompt, if you cut-n-paste that command and run it, it works. From a .bat file (with 'SETLOCAL EnableDelayedExpansion EnableExtensions' at the top of the script) the command fails with: "Sqlcmd: Error: Microsoft SQL Server Native Client 11.0 : Login failed for user 'sa'..".

Why was it failing? Because the '!' character in the password is a cmd.exe metacharacter used for !VARIABLE! just like %VARIABLE% syntax. Therefore, from within a .bat script, the script was replacing "!Paswd" with empty space, and that is not the correct password! So... hope this saves someone else from chasing their tail. stackoverflow searches kept bringing me to this answer and a few other not-so-accurately-related questions, so I figure the Googling community with this exact problem will now find this tip. My advice is to not use a password with special character '!' in it.

Upvotes: 0

James Joyce
James Joyce

Reputation: 1774

I know this is an old question, but it happened to me recently!

Its because of the quote marks in your SQL... I couldn't find a method to escape them, so I resorted to using a script file.. Put your SQL into the script file, then use the "-i " to run that, instead of inline SQL...

Upvotes: 5

Related Questions