As a Network Administrator, you might have a situation where you need to execute a batch file, based on a SQL condition.
In short, here’s the logic I needed to work out:
– Check for SQL condition (if temp table exists for this situation)
– If condition = FALSE (table doesn’t exist), execute notrunning.bat
The notrunning.bat just restarts a Windows Service (services.msc in your command line) for me, which is dependent on a temporary table in SQL and needs to be restarted if that table gets dropped. Here’s how I monitor it and send an email notification to my phone if it fails:
- Download blat and dump the 3 files into C:\Windows
- Download stunnel and install. Tell it to ‘Install Service’, ‘Run Service’ and then ‘Run Stunnel’ (If you already have smtp available to a server and know the address, skip the stunnel part and use that. Gmail requires a ssl tunnel though).
- Edit Stunnel.conf, erasing everything in the file and changing the contents of the file to read this (Pop3 info isn’t really necessary but just kept it in case someone wants to use it):
- Install a blat command line profile by running:
blat -install smtp.gmail.com email@example.com -u gmailusername -pw gmailpassword – – gmailsmtp
- Create a batch file with this (change the options to suit your needs):
- Create a sql script named checksqltable.sql (I place it with the batch files: C:\BatchFiles\
- Create a new Windows Task and have it run the batch file every 15 minutes from 12:01am to 11:46pm.
# GLOBAL OPTIONS
client = yes
output = stunnel-log.txt
debug = 0
taskbar = no
# SERVICE-LEVEL OPTIONS
accept = 127.0.0.1:1099
connect = smtp.gmail.com:465
accept = 127.0.0.1:1109
connect = pop.gmail.com:995
— Now it will allow for a secure connection between blat and gmail. (Stunnel is a “SSL Tunnel” program).
— You now have a registry entry under software/blat called “gmailsmtp” which contains your encrypted username and pw for gmail so you don’t have to have it in plain text in the batch file.
osql -SServername -dmaster -E -iC:\BatchFiles\checksqltable.sql -n -b
IF ERRORLEVEL ==1 GOTO :NOTRUNNING
IF ERRORLEVEL ==0 GOTO :RUNNING
Echo %date% %time% Your Service was not running at this time. >> C:\BatchFiles\ProgramDownTimeLog.txt
c:\windows\blat.exe -p gmailsmtp -to “firstname.lastname@example.org, email@example.com” -s “SQL Table Alert” -ti 20 -try 2 -noh -body “YourService Down Alert” -attach “C:\BatchFiles\ProgramDownTimeLog.txt” -server 127.0.0.1:1099
— SQL Script:
select ComputerID from dbo.##YourTempTable
That’s it.. I just have the batch file check a sql condition and return true/false. False runs the routine to write to a log and send me an alert. True just ends the program. Simple solution and works great!
One other note worth mentioning.. I had EXIT in my code and couldn’t figure out for the longest time that EXIT makes it retain the last %errorlevel% value, so my query was always returning a 0 (true). Putting END in seemed to resolve this for me. Hopefully that saves someone a few extra minutes.