How to Execute a Batch File Based on a SQL Condition

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:

  1. Download blat and dump the 3 files into C:\Windows
  2. 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).
  3. 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):
  4. # GLOBAL OPTIONS

    client = yes
    output = stunnel-log.txt
    debug = 0
    taskbar = no

    # SERVICE-LEVEL OPTIONS

    [SMTP Gmail]
    accept = 127.0.0.1:1099
    connect = smtp.gmail.com:465

    [POP3 Gmail]
    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).

  5. Install a blat command line profile by running:
    blat -install smtp.gmail.com youremail@yourchoice.com -u gmailusername -pw gmailpassword – – gmailsmtp

  6. — 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.

  7. Create a batch file with this (change the options to suit your needs):
  8. @echo off
    osql -SServername -dmaster -E -iC:\BatchFiles\checksqltable.sql -n -b

    echo %errorlevel%
    IF ERRORLEVEL ==1 GOTO :NOTRUNNING
    IF ERRORLEVEL ==0 GOTO :RUNNING

    :NOTRUNNING
    Echo %date% %time% Your Service was not running at this time. >> C:\BatchFiles\ProgramDownTimeLog.txt
    c:\windows\blat.exe -p gmailsmtp -to “email1@gmail.com, email2@gmail.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
    END

    :RUNNING
    END

  9. Create a sql script named checksqltable.sql (I place it with the batch files: C:\BatchFiles\
  10. — SQL Script:

    select ComputerID from dbo.##YourTempTable

  11. Create a new Windows Task and have it run the batch file every 15 minutes from 12:01am to 11:46pm.

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.

Submit a comment

CommentLuv badge

reverse phone lookupTattoo DesignsSEO
My New Amazon Script Is Close!
A new php script for sale is coming!
Sign up to get notified

..