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

Posted on 2008-11-06
Last Modified: 2012-06-21
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).
Question by:efd_support
    LVL 2

    Accepted Solution

    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
    LVL 4

    Expert Comment

    In VBS you can open processes loop through them looking for the process. - 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
    ' 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 & _
    WSCript.Echo strList
    ' End of List Process Example VBScript

    Open in new window


    Author Closing Comment

    Thank you!

    Featured Post

    Maximize Your Threat Intelligence Reporting

    Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

    Join & Write a Comment

    Introduction In my previous article ( I showed you how the XML Source component can be used to load XML files into a SQL Server database, us…
    JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
    Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
    Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

    734 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    24 Experts available now in Live!

    Get 1:1 Help Now