How to write a batch file calling sqlcmd to query SQL Server and if instance unreachable, it would echo the server name to a log file?

user_14074327 0
Viewed 221

The idea is to write a batch file to execute a query against a list of SQL servers and get some information from them, if any SQL Server is not reachable, I want this batch file to write servername\instancename to a text file for my reference.

I have around 100 SQL Server instances (in text file), when I get the result, it is hard to know which instance was unreachable during the batch file execution.

This is the script I use to get the info from the SQL Servers in (listed in text file) and it works:

for /F "tokens=*" %%S in (SQLLIST.txt) do sqlcmd -E -h -1 -W -M -S %%S -i C:\Foldername\Query.sql >> Destination\QueryResult.csv -s ","

I figure I may need to add an IF statement to do that? Please help.

sqlcmd batch-file sql-server
1 Answers

NOTE I do not have Sqlcmd on this device, so not able to test this.

I would say using -b switch to get errorlevel This should simply echo to screen the instance and errorlevel 0 is success and 1 is error:

@echo off
setlocal enabledelayedexpansion
for /F "tokens=*" %%S in (SQLLIST.txt) do (
    sqlcmd -b -E -h -1 -W -M -S %%S -i C:\Foldername\Query.sql >> Destination\QueryResult.csv -s ","
    echo %%S !errorlevel!
)

So you could log it to file:

@echo off
setlocal enabledelayedexpansion
(for /F "tokens=*" %%S in (SQLLIST.txt) do (
    sqlcmd -b -E -h -1 -W -M -S %%S -i C:\Foldername\Query.sql >> Destination\QueryResult.csv -s ","
    echo %%S !errorlevel!
 )
)2>&1>mysqlcmd.log

or log it to seperate

@echo off
setlocal enabledelayedexpansion
(for /F "tokens=*" %%S in (SQLLIST.txt) do (
    sqlcmd -b -E -h -1 -W -M -S %%S -i C:\Foldername\Query.sql >> Destination\QueryResult.csv -s ","
    echo %%S !errorlevel!
 )
)2>&1>mysqlcmd.log

Or you can specify success or failure:

@echo off
setlocal enabledelayedexpansion
(for /F "tokens=*" %%S in (SQLLIST.txt) do (
    sqlcmd -b -E -h -1 -W -M -S %%S -i C:\Foldername\Query.sql >> Destination\QueryResult.csv -s ","
    if !errorlevel! equ 0 set outp=Success
    if !errorlevel! equ 1 set outp=Failed
    echo %%S !outp!
 )
)2>&1>mysqlcmd.log
user_78187490
Related