Force a scheduled job to FAIL

• rivendell

  SSC Veteran

  Points: 288

  More actions

  February 9, 2004 at 8:42 pm

  #152036

  Hi!

  Is there a way to force a scheduled job to fail if it has been running for about an hour so that I can be notified through mail?

  Or is there a way to force a scheduled job to fail if it has been running for 1hr and automatically rerun it?

   

  Thanks!

• noeld

  SSC Guru

  Points: 96590

  More actions

  February 11, 2004 at 6:41 am

  #493625

  Use Another job to MONITOR the running time and act accordingly!

  

  
  * Noel

• amackay_1

  Ten Centuries

  Points: 1240

  More actions

  February 11, 2004 at 7:01 am

  #493629

  I'm interested in doing the same thing.  Can you provide more details on how to set this up noeld?  Thanks

• amackay_1

  Ten Centuries

  Points: 1240

  More actions

  February 11, 2004 at 7:17 am

  #493631

  Just found another thread that might help us out:

  http://www.sqlservercentral.com/forums/shwmessage.aspx?messageid=99401

• noeld

  SSC Guru

  Points: 96590

  More actions

  February 11, 2004 at 10:39 am

  #493683

  how about:

  CREATE  PROCEDURE IsJobRunning @JobName as varchar(100) = Null as

  --check!!

  if (@JobName IS NULL)

   RETURN -1

  Declare @theID as uniqueidentifier

  --get the ID

  Select @theID = A.job_id

   FROM OPENROWSET('SQLOLEDB','dbServerName'; 'Uname'; 'pwd' , 'SET FMTONLY OFF  exec msdb.dbo.sp_help_job')  as A

  WHERE

  A.[name]= @JobName

  if EXISTS(

  Select *

   FROM OPENROWSET('SQLOLEDB','dbServerName'; 'Uname'; 'pwd' , 'SET FMTONLY OFF  exec master.dbo.xp_sqlagent_enum_jobs 1,dbo')  as A

   WHERE A.[Job ID] = @theID AND

         A.Running = 1 

  )

   RETURN 1

  ELSE

   RETURN 0

   

  
  * Noel