I have written a small batch file to connect 15 SQL Servers via sqlcmd and store the output of the t-sql query in an HTML format output file. The script runs fine when all SQL Servers are up and running, but whenever any one of SQL Server is down then following error get saved in output file - "HResult 0xFFFFFFFF, Level 16, State 1 SQL Server Network Interfaces: Error Locating Server/Instance Specified [xFFFFFFFF]. "
Instead of writing such vague error, I tried to write error handler for connection failure using IF ELSE also but it didn't worked out, need help.
Part of code I tried to write for error handling (if SQL instance is down) is as below, the input text file contains following two values for all sql servers --> SQLinstancename,Application name
FOR /F "tokens=1,2 delims=," %%G in (%CD%\SQLServerList_SQLReport.txt) DO (
sqlcmd -E -S %%G -q "exit(select @@servername)"
IF ERRORLEVEL==0 (
sqlcmd -E -S %%G -i %MainSCRIPT% -v appname = "%%H" >> %HTMLFILE%
) ELSE (
ECHO "<tr><tr rowspan=1><td align=center bgcolor = gray rowspan=1><b><font face=verdana color=white size=1>%%H</font></b></td>" >> %HTMLFILE%
ECHO "<td align=center rowspan=1><font face=verdana size=1>%%G</font></td>" >> %HTMLFILE%
ECHO "<td align=center colspan =6 rowspan=1><font face=verdana size=1 color=RED><b>Unable to connect to SQL Server, check the connectivity*</b></font><tr></tr><tr></tr>" >> %HTMLFILE%
))
You omitted the percent signs from the variable.
IF %ERRORLEVEL%==0 (
but in a loop you would be better off using this, which doesn't need percent signs or delayed expansion. You have to test this way because errorlevel 0 is always true.
if not errorlevel 1 (