Tuesday, April 5, 2016

Batch script -- using FOR loop for multiple servers list to run SQL script

1.Save all servers in "Serverlist.txt" file in "E:\batch" folder.
example: Serverlist.txt
server1
server2
server3

2.Save "sqlquery.sql" file in "E:\batch" folder
3.Save below script as Test.bat in "E:\batch" folder

set log=E:\batch\Test_log.txt
echo start for loop >>%log%
for /F "tokens=*" %%S in (E:\batch\Serverlist.txt) do (
e:\ give here sqlcmd location\sqlcmd -E -S %%S -i "E:\batch\sqlquery.sql"  -s "," -h-1 -W >>"E:\batch\output.csv"
IF NOT ERRORLEVEL 0 GOTO :ERR
)
echo End for loop>>%log%
echo !Success. sqlcmd ok. >>%log%
GOTO :EOP
:ERR
echo !Error. sqlcmd failed >>%log%
:EOP

4.Output will store in "E:\batch\output.csv" file.

Note:Include SET NOCOUNT ON and SET NOCOUNT OFF in your "sqlquery.sql" to avoid info message in your output.