Creating Batch file to automate script execution process

[apat]

Hi All,

I am new to this. I have to create automation script to execute various sql scripts in different sql servers. I have total 15 sql scripts (5 for Dev, 5 for Staging and 5 for Production server). I need to create a batch file which will use SQLCMD to execute these scripts on desired server. Basically, once the batch file is executed user should be prompted for an argument(parameter) and if they supply D (-D or /D) then the control should go to all the development server scripts and execute them in order given in the DEV server. and the same for UAT and Prod server.

Can anyone please provide some help?

Thanks in advance...

[aGerman]

No idea how to use sqlcmd, but you can find help on msdn.

Regards
aGerman

[apat]

I know how to use SQLCMD.

All I want is, how to accomplish this task for automating in batch file.

e.x. when I execute batch file and supply argument D then it should execute commands on my Dev server. Same way once I put P then it should use Production server to execute commands.

[aGerman]

I see.
You can find arguments in %1 ... %9.
If you call the batchfile like call your.bat -D you could write something like that:

Code: Select all

@echo off &setlocal
if "%~1"=="-D" call :Dev
if "%~1"=="-P" call :Prod
pause
goto :eof

:Dev
echo Your Dev stuff here.
goto :eof

:Prod
echo Your Prod stuff here.
goto :eof

Regards
aGerman

[apat]

Thank you aGerman... I am using below code as per your suggestion...


@echo off
if "%1" == "D" ( sqlcmd -S DevServer -U UsrName -P Pwd -d Dbname -i d:\sqlcmd\dev.sql -i d:\sqlcmd\dev2.sql
goto eod)
if "%1" == "T" ( sqlcmd -S TestServer -U UsrName -P Pwd -d Dbname -i d:\sqlcmd\test.sql
goto eod)
if "%1" == "P" ( sqlcmd -S PrdServer -U UsrName -P Pwd -d Dbname -i d:\sqlcmd\prd.sql
)
:eod

do you suggest me any modifications to make it better?

Thanks...

[orange_batch]

Looks fine, but if you ever need to combine arguments/switches you would best make a solution involving shift.