How to pass a variable value through a batch file to an sql script from the command line

Question

I've been trying to pass a variable value that i can enter in the command line to open a batch file and then pass that value to an sql script. In this instance I'm looking to change a database user's password to a new password by setting it in the command line.

command line - update_passwords.bat server_name 1234

batch file -

@echo off
set sql_server_name=%1
set newPass=%2

osql -S %1 -U dbuser -P user_pass -v newPass=%2 -i c:\sql_script -o c:\sql_log

sql file - 

ALTER LOGIN user1 WITH PASSWORD = %2;

I get an incorrect syntax near '%' error when I run the the batch file

Any help is greatly appreciated

It looks like you're setting sql_server_name and newPass at the top and never using them. Beyond that, are you getting an error with the batch file you currently have? If so, what's the error message? — gmm
– gmm, Commented Jan 22, 2016 at 17:44
im stating the server name in the command line and passing it to the batch file as %1, I then enter the password i want the db account's password to be changed to as %2. I get an incorrect syntax near '%' error. Thanks for looking at this for me — MollyMoo
– MollyMoo, Commented Jan 22, 2016 at 17:48
You should put the information about the error message into the question body. — gmm
– gmm, Commented Jan 22, 2016 at 17:51
I think sqlcmd let's you use -v for script variables but osql does not. To refer to it I believe you use this syntax: $(newPass) — shawnt00
– shawnt00, Commented Jan 22, 2016 at 18:30

gmm · Accepted Answer · 2016-01-22 20:45:52Z

1

There are two main issues. One is that %2 won't be available in c:\sql_script, so the SQL statement is trying to set the password to "%2", literally. You could resolve that by passing the SQL statement on the command line instead of in an input file. The second issue is that the SQL statement needs quotes around the %2. Try the following.

osql -S %1 -U dbuser -P user_pass -q "ALTER LOGIN user1 WITH PASSWORD = '%2';" -o c:\sql_log

Now, if the new password has apostrophes in it, you would need to escape them by converting them to double apostropes.

Given that you're not using the sql_server_name and newPass variables after setting them, you can remove them completely. This will remove the security issue mentioned by shawnt00.

edited Jan 22, 2016 at 20:45

answered Jan 22, 2016 at 17:54

gmm

4783 silver badges13 bronze badges

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

2 Comments

shawnt00 Over a year ago

This seemed to work in my quick test: ...WITH PASSWORD = '%newpass:'=''%' You'll also want to clear that password variable so nobody can see it later and probably use setlocal.

MollyMoo Over a year ago

Thanks for all the help folks...the above suggestion of passing the sql statement in the osql command won't work for me as I need to change the passwords for a large number of database users. Sorry I should have made that clear at the start. So the -i file has a number of ALTER statements in it. I can set the new passwords in that input file but was hoping to be able to pass the new password value from the command line.. still dont know how to do this/if it's possible but thanks for the help

Collectives™ on Stack Overflow

How to pass a variable value through a batch file to an sql script from the command line

1 Answer 1

2 Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Related