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