Apr
30
2013

Write a Batch (.bat) file error handler for SQL instance down when fetching details from several SQL Servers

I have written several batch files to automate activities in SQL Server environment and send HTML reports over email on completion; but whenever SQL Server is down or server is unavailable then the HTML output get disturbed as no error handling is being done. When tried to write error handling to fix the issue, faced trouble as was using following code as part of an error handling,

 

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%

))

 

Basically above code is just a part of a big batch file, basically above code is fetching list of SQL server and related application name from a text file and passing it to sqlcmd command in order to execute query, fetch data and put it in html file. Whenever I executed above query by giving wrong SQL name (to test) it never went to ELSE condition.

 

Found difficult to fix it so raised question on forum and got my answer as (thanks to foxidrive),

 

 

use IF NOT ERRORLEVEL 1 ( instead of IF %ERRORLEVEL%==0 (

…as soon as I implemented it in my code, it worked

 

Now next issue was to ECHO HTML tags basically arrow signs <>, as I have to ECHO without quotes “” hence found another solution is by using CARROT (^) characters just before any HTML < or > tag as REM
echo ^<center^>^<a href=”someur.com”^>

 

So final code looked like,

 

FOR /F “tokens=1,2 delims=,” %%G in (%CD%\SQLServerList_SQLReport.txt) DO (

sqlcmd -E -S %%G -q “exit(select @@servername)”

IF NOT ERRORLEVEL 1 (

sqlcmd -E -S %%G -i %MainSCRIPT% -v appname = “%%H” >> %HTMLFILE%

) ELSE (

ECHO ^<tr^>^<tr rowspan=3^>^<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=3^>^<font face=verdana size=1^>%%G^</font^>^</td^>>> %HTMLFILE%

ECHO ^<td align=center colspan =6 rowspan=3^>^<font face=verdana size=1 color=RED^>^<b^>Unable to connect to SQL Server ^<I^>%%G^</I^>, check connectivity*^</b^>^</font^>^<tr^>^</tr^>^<tr^>^</tr^>^<tr^>^</tr^>^<tr^>^</tr^>^<tr^>^</tr^>^<tr^>^</tr^>>> %HTMLFILE%))

 

Now solution is in place across all my batch scripts.

 

While struggling to get answer, found few useful references on different forums which dealt with handling error levels resulted by SQL code, hope can be handy if someone looking to write that kind of error handling…

 

Reference for IF ELSE usage in batch file:

http://stackoverflow.com/questions/5795430/error-handling-with-batch-file-sqlcmd

http://social.technet.microsoft.com/Forums/en-US/ITCG/thread/4729574b-9cd6-4da4-86ac-05f8eeeee6e0/

http://stackoverflow.com/questions/6498460/batch-programming-error-handling-and-start-command

 

References for handling ERRORLEVEL:

http://www.sqlservercentral.com/Forums/Topic1188295-392-1.aspx

http://stackoverflow.com/questions/5789568/sqlcmd-utility-from-bat-file-how-to-return-errorlevel-in-case-of-syntax-error

Question posted by me – http://stackoverflow.com/questions/16286167/writing-error-handler-for-sql-server-instance-down-in-a-small-batch-file-program

Download PDF

About the Author: Nitin G

Indian born, trekker, biker, photographer, lover of monsoons. I've been working full time with SQL Server since year 2005 and blogs to post the content aquired during my research on new topics or fixing issues faced by me as a DBA while working in different kind of projects, hope some of my posts may helps others in SQLDBA community. Everything you read on my blog is my own personal opinion and any code is provided "AS-IS" with no warranties!

1 Comment + Add Comment

  • HI

    I have around 20 sql server instances.I need to generate a html file that shows sql service status for each instance as –

    Application DB_Instance Status
    XXXX Sqlserver\instance UP

    How can i go about generating such a file.

Leave a comment

Subscribe to this blog via Email

Enter your email address to subscribe to this blog and receive notifications of new posts by email.

Join 195 other subscribers

Translate this blog!

EnglishFrenchGermanItalianPortugueseRussianSpanish

Calender

August 2019
M T W T F S S
« Nov    
 1234
567891011
12131415161718
19202122232425
262728293031  

View Post by Categories

%d bloggers like this: