Post reply

DMo and SQL Server Agent

  • August 24, 2001 at 10:43 am

    #64393

    Every once in a while the SQL ServerAgent (SQL 7) stops unexpectedly. How can I use DMO to check if the service is running, and better yet, to notify me, if it fails?

    TIA,

    Bill

    P.S. I have selected the "Autorestart Agent" option but it doesn't work reliably.

  • August 24, 2001 at 12:00 pm

    #417777

    You can use the JobServer object to check the status of the agent.

     

    Dim oServer As SQLDMO.SQLServer

    Dim oJobserver As SQLDMO.JobServer

    Set oServer = New SQLDMO.SQLServer

    oServer.LoginSecure = True

    oServer.Connect vServerName

    Set oJobserver = oServer.JobServer

    

    Debug.Print oJobserver.Status

    

    '  Do your stuff based on the status, and clean up here.

    The status property will return a long. Here is a listing of contstants, the status number, and associated descriptions.

    ConstantValueDescription

    SQLDMOSvc_Continuing6Service execution state in transition from paused to running.

    SQLDMOSvc_Paused2Service execution is paused.

    SQLDMOSvc_Pausing7Service execution state in transition from running to paused.

    SQLDMOSvc_Running1Service is running.

    SQLDMOSvc_Starting4Service execution state in transition from stopped to running.

    SQLDMOSvc_Stopped3Service is stopped.

    SQLDMOSvc_Stopping5Service execution state in transition from running to stopped.

    SQLDMOSvc_Unknown0Unable to determine service execution state.

    Hope this helps,

    Sean

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply