SP_Help_Job and SP_StopJob

• prn9974

  SSCrazy

  Points: 2060

  More actions

  September 23, 2003 at 6:09 am

  #106233

  Hello,

  I am looking to check if a particular job is running and if it is running(over a period of time) I want to stop it dynamically.

  SP_Help_Job gives me the details, but I need to get the @execution_status parameter as output and use it to stop the job. Is there any other way?

  Thanks

• philcart

  SSC-Forever

  Points: 47795

  More actions

  September 23, 2003 at 6:31 pm

  #475512

  Try this,

  
  
  SELECT a.name, a.current_execution_status
  
  FROM OPENROWSET('SQLOLEDB','<your server>';'< sql login >';',sql password>',
  
     'SET FMTONLY OFF EXEC msdb..sp_help_job') AS a
  
  WHERE a.current_execution_status = 1
  
  

  This will not work if your server is setup for windows authentication. If that is the case, write a stored procedure that performs much the same function as sp_help_job.

  Hope this helps

  Phill Carter

  --------------------

  Colt 45 - the original point and click interface

  --------------------
  Colt 45 - the original point and click interface

• prn9974

  SSCrazy

  Points: 2060

  More actions

  September 24, 2003 at 4:40 am

  #475559

  Thanks very much. This is much quicker way. I actually ended up having a temp table (same structure as 'XP_sqlagent_enum_jobs' return values) and inserted the data from 'XP_sqlagent_enum_jobs' into it. I used the 'Running' column value for it. Its in a stored procedure now, so as you suggested it might work for 'windows authentication mode'. Thanks again.

  Regards

  Narayan