How to tell (programatically) if job is currently executing

[john76]

Hi all,

I am trying to set up a schedule for a data warehouse refresh and I need to be able to code some logic that can determine if the job is already active.

pseudocode:

--Begin job
If (data is ready){
If (job is not running){
Run Job "Refresh Warehouse"
}
}
Else{
Exit
}

I am going to set this code to execute every 15 minutes, but I haven't been able to figure out the "If (job is not running)" part yet.

Any ideas?

John

 

[john76]

I ran a search on this forum on the keywords "agent job" and it turned up a helpful thread: thread183-869335

Using that I came up with this code:

If Exists(	Select		*
			From		tempdb..sysobjects
			Where		Name Like '#xp_results%'
)Drop Table	#xp_results

  CREATE TABLE #xp_results (job_id                UNIQUEIDENTIFIER NOT NULL,
                            last_run_date         INT              NOT NULL,
                            last_run_time         INT              NOT NULL,
                            next_run_date         INT              NOT NULL,
                            next_run_time         INT              NOT NULL,
                            next_run_schedule_id  INT              NOT NULL,
                            requested_to_run      INT              NOT NULL, -- BOOL
                            request_source        INT              NOT NULL,
                            request_source_id     sysname          COLLATE database_default NULL,
                            running               INT              NOT NULL, -- BOOL
                            current_step          INT              NOT NULL,
                            current_retry_attempt INT              NOT NULL,
                            job_state             INT              NOT NULL)

Declare	@is_sysadmin	int,
	@job_owner	sysname,
	@job_id		UniqueIdentifier

Set	@is_sysadmin	=	1
Set	@job_owner	=	'TNT8/johns'
Set	@job_id		=	(Select	job_id From msdb..sysjobs Where name = 'Test')

Insert Into	#xp_results
	Exec	master.dbo.xp_sqlagent_enum_jobs @is_sysadmin, @job_owner, @job_id

Select		running,	-- Boolean value:  0 = not running, 1 = running
		job_state	-- See @execution_status description of sp_job_help in BOL for explanation of values
	From		#xp_results

Obviously it needs some serious cleanup, but it definately has me on the right track and (here's the important part) IT WORKS! ;-)

Thanks to all,
John