asked on

Testing whether a Windows Service is shut down as part of a scheduled SQL job

We have a nightly Maintenance job that runs on one of our servers that:
A) Notify's our Analysts to get out of the DB
B) Shuts down windows services via a called batch file.
C) Drops and re-creates a couple tables with ~ 9 million rows
D) Indexes the final table
E) Starts the services that were stopped in B
F) Notify's Analysts all done

If any of the steps fails, it sends out a failure notification. The problem we've encountered several times is step B always succeeds in CALLING the script and running it, but on rare occasions the services don't actually shut down. This is known as a Bad Thing, in it introduces duplicate records into the DB, which blows up the indexes and takes a lot of time to fix.
What we need is a suggestion on how to , between B & C above, test whether the services are on or off and shut down the SQL job if they are not. We have a script for testing and notifying us when it is not, but it always comes back as a success as far as SQL is concerned. Either it finds they are shut off and reports "success" (this is good), or it finds they are turned on, sends out the warnings to our pagers and then reports to SQL "success" (this is bad).

ASKER CERTIFIED SOLUTION

pjcoole

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Chizl

In VBS you can open processes loop through them looking for the process.

http://www.computerperformance.co.uk/vbscript/wmi_process.htm - Code Attached Below

If the process still exists, then alert you and quit script.

' Process.vbs
' Free Sample VBScript to discover which processes are running
' Author Guy Thomas http://computerperformance.co.uk/
' Version 1.4 - December 2005
' -------------------------------------------------------' 
Option Explicit
Dim objWMIService, objProcess, colProcess
Dim strComputer, strList
 
strComputer = "."
 
Set objWMIService = GetObject("winmgmts:" _
& "{impersonationLevel=impersonate}!\\" _ 
& strComputer & "\root\cimv2") 
 
Set colProcess = objWMIService.ExecQuery _
("Select * from Win32_Process")
 
For Each objProcess in colProcess
strList = strList & vbCr & _
objProcess.Name
Next
 
WSCript.Echo strList
WScript.Quit
 
' End of List Process Example VBScript

Open in new window

efd_support

ASKER

Thank you!