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).
efd_supportAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

pjcooleCommented:
If you are using at Bat file to stop and start the services why not check the ERROR level after running the command.  The following are the return codes for the NET Start and NET stop commands.

 - 0 = Success
 - 1 = Not Supported
 - 2 = Access Denied
 - 3 = Dependent Services Running
 - 4 = Invalid Service Control
 - 5 = Service Cannot Accept Control
 - 6 = Service Not Active
 - 7 = Service Request Timeout
 - 8 = Unknown Failure
 - 9 = Path Not Found
 - 10 = Service Already Running
 - 11 = Service Database Locked
 - 12 = Service Dependency Deleted
 - 13 = Service Dependency Failure
 - 14 = Service Disabled
 - 15 = Service Logon Failure
 - 16 = Service Marked For Deletion
 - 17 = Service No Thread
 - 18 = Status Circular Dependency
 - 19 = Status Duplicate Name
 - 20 = Status Invalid Name
 - 21 = Status Invalid Parameter
 - 22 = Status Invalid Service Account
 - 23 = Status Service Exists
 - 24 = Service Already Paused
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
ChizlCommented:
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

0
efd_supportAuthor Commented:
Thank you!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.