-- $CARDS: mssql/tools/task_StopJob.sql,v 1.1 2004/08/06 18:05:56 glad Exp $
/*************************************************************************/
/* */
/* Procedure which is started by the separate task */
/* for the control of duration of performance of other task */
/* which name is underlined in a variable @vJobName. */
/* */
/* December, 25 2002?. Moscow. The author of a script Gennady Kobzarev */
/*************************************************************************/
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE PROCEDURE task_StopJob @vJobName SYSNAME,
@vMaxInterval INT
AS
BEGIN
DECLARE @vJobId uniqueidentifier
,@vRunDuration int
,@vValue decimal(5,2)
,@vBeginTime datetime
,@vCurrentTime datetime
,@vEndTime datetime
,@vJobStatus int
SET @vBeginTime = GetDate()
SET @vEndTime = dateadd(ss, @vMaxInterval, GetDate())
SELECT @vJobId = job_id FROM msdb.dbo.sysjobs WHERE name = @vJobName
SELECT @vRunDuration = msdb.dbo.Duration_In_Seconds(last_run_duration)
FROM msdb.dbo.sysjobservers where job_id = @vJobId
CREATE TABLE #tTmp
(
job_id uniqueidentifier
,last_run_date int
,last_run_time int
,next_run_date int
,next_run_time int
,next_run_schedule_id int
,requested_to_run int
,request_source int
,request_source_id nvarchar(200)
,running int
,current_execution_step int
,current_retry_attempt int
,current_execution_status int
)
SET @vCurrentTime = dateadd(ss, @vRunDuration, @vBeginTime)
WHILE 1=1
BEGIN
/*Parameters of the task in earlier created temporary table are read out*/
INSERT INTO #tTMP
EXECUTE master.dbo.xp_sqlagent_enum_jobs 1, 'sa', @vJobId
/*The status of performance of the task is determined*/
SELECT @vJobStatus = current_execution_status FROM #tTmp
/*If the task is carried out (the status - 1) and has passed maximal time,*/
IF @vJobStatus in (0, 1, 2, 3, 7) and GetDate() >= @vEndTime
BEGIN /* execution the task stops and error is generated.*/
exec msdb.dbo.sp_stop_job @job_id = @vJobId
raisError('The maximal interval is exceeded!', 16, 1)
BREAK
return
END
IF @vJobStatus in (4, 5)
BREAK
IF @vJobStatus in (6, 8)
BEGIN
exec msdb.dbo.sp_stop_job @job_id = @vJobId
raisError('6, 8!', 16, 1)
BREAK
return
END
DELETE FROM #tTmp
/* Wait one minute */
WAITFOR DELAY '000:01:00'
END
END
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
|