Force a scheduled job to FAIL
-
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!
-
Use Another job to MONITOR the running time and act accordingly!
* Noel -
I'm interested in doing the same thing. Can you provide more details on how to set this up noeld? Thanks
-
Just found another thread that might help us out:
http://www.sqlservercentral.com/forums/shwmessage.aspx?messageid=99401
-
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
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply