DMo and SQL Server Agent

• Barkingdog

  SSCoach

  Points: 18670

  More actions

  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.

• Sean Burke

  SSCommitted

  Points: 1504

  More actions

  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