Thursday, April 23, 2015

Notification when a group of jobs complete

You can store all jobs name in a table variable or temp variable which you want to monitor and then use below code (this code is just for one job name)


DECLARE @JobStatus INT
SET @JobStatus = 0
EXEC MSDB.dbo.sp_start_job @Job_Name = 'JobName'
SELECT @JobStatus = current_execution_status  FROM OPENROWSET('SQLNCLI', 'Server=localhost;Trusted_Connection=yes;', 
                          'EXEC MSDB.dbo.sp_help_job @job_name = ''JobName'', @job_aspect = ''JOB'' ')     
WHILE @JobStatus <> 4
BEGIN   
        SELECT @JobStatus = current_execution_status  FROM OPENROWSET('SQLNCLI', 'Server=localhost;Trusted_Connection=yes;', 
                                'EXEC MSDB.dbo.sp_help_job @job_name = ''JobName'', @job_aspect = ''JOB'' ')    
END

SP_Help_Job provides the status of the job

Value

Description

0

Returns only those jobs that are not idle or suspended.

1

Executing.

2

Waiting for thread.

3

Between retries.

4

Idle.

5

Suspended.

7

Performing completion actions.

No comments:

Post a Comment