I have a large number of jobs that I run using a cursor (All jobs start with PS_ and each job loads a single table from ORACLE) so they can run in parallel. I want to be able to be notified when the last job completes. Should this done using a trigger? Or should I just add a loop statement to the stored procedure that fires this job? I know which individual takes the longest and I could simply just add a notification to send email on success but I want it to be more robust. Below is what I am using to trigger the jobs.
Thanks in advance!
BEGIN
SET NOCOUNT ON;
DECLARE @year CHAR(4)
DECLARE @month CHAR(2)
DECLARE @day CHAR(2)
SET @year = CAST(DATEPART(YEAR, GETDATE()) AS VARCHAR)
SET @month = CASE WHEN LEN(CAST(DATEPART(MONTH, GETDATE()) AS VARCHAR)) = 1
THEN '0'+ CAST(DATEPART(MONTH, GETDATE()) AS VARCHAR)
ELSE CAST(DATEPART(MONTH, GETDATE()) AS VARCHAR)
END
SET @day = CASE WHEN LEN(CAST(DATEPART(DAY, GETDATE()) AS VARCHAR)) = 1
THEN '0' + CAST(DATEPART(DAY, GETDATE()) AS VARCHAR)
ELSE CAST(DATEPART(DAY, GETDATE()) AS VARCHAR)
END
DECLARE @job_name NVARCHAR(255)
DECLARE cur CURSOR
FOR
SELECT name
FROM msdb.dbo.sysjobs X
WHERE SUBSTRING(X.name, 1, 3) = 'PS_'
OPEN cur
FETCH NEXT FROM cur INTO @job_name
WHILE @@fetch_status = 0
BEGIN
CURSOR_START:
--if the job has not completed
IF ( SELECT COUNT(*)
FROM sysjobhistory
WHERE job_id = ( SELECT job_id
FROM sysjobs
WHERE name = @job_name
)
AND step_id = 0 --job outcome
AND run_status = 1 --0 = failed 1 = success
AND run_date = @year + @month + @day
) = 0
AND
--and the job is not currently running
( SELECT COUNT(*)
FROM sysjobactivity
WHERE job_id = ( SELECT job_id
FROM sysjobs
WHERE name = @job_name
)
AND CONVERT(VARCHAR(10), start_execution_date, 111) = CONVERT(VARCHAR(10), GETDATE(), 111)
AND stop_execution_date IS NULL
) = 0
BEGIN
IF ( SELECT COUNT(*)
FROM sysjobactivity a1
INNER JOIN sysjobs a2 ON a2.job_id = a1.job_id
WHERE CONVERT(VARCHAR(10), start_execution_date, 111) = CONVERT(VARCHAR(10), GETDATE(), 111)
AND stop_execution_date IS NULL
AND SUBSTRING(name, 1, 3) = 'PS_'
) < 10
BEGIN
PRINT 'EXEC msdb.dbo.sp_start_job @job_Name='
+ CAST(@job_name AS VARCHAR)
EXEC msdb.dbo.sp_start_job @job_Name=@job_Name
WAITFOR DELAY '00:00:10'
END
ELSE
BEGIN
PRINT 'WAIT 10 jobs already running: EXEC msdb.dbo.sp_start_job @job_Name='
+ CAST(@job_name AS VARCHAR)
WAITFOR DELAY '00:00:10'
GOTO CURSOR_START
END
END
ELSE
BEGIN
PRINT CAST(@job_name AS VARCHAR)
+ ' The job is already currently running or completed sucessfully today'
END
FETCH NEXT FROM cur INTO @job_name
END
CLOSE cur
DEALLOCATE cur
END
No comments:
Post a Comment