Check current_execution_status on SQL Agent Job (WaitFor)

This T-SQL code uses sp_help_job to check to current status of a specific job (JobID) and waits until its Idle.

About sp_help_job:

Returns information about jobs that are used by SQL Server Agent to perform automated activities in SQL Server. More info?

First step is to create a simple SQL Agent Job called ‘WaitJob‘ that executes the statement:
WAITFOR DELAY '00:00:05'

Note: Be sure that ‘Ad Hoc Distributed Queries’ are enabled (More info?).

The T-SQL code checks the current status of a specific job (in this case ‘WaitJob’ and waits for it (by using the WAITFOR-statement):

The results:

Download the source code here.

Advertisements