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.

About these ads

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s