checking whether a job is running

A small stored procedure that tells whether a job is running or not.<br /><br /><font face="Courier New">CREATE&nbsp<img src='/community/emoticons/emotion-4.gif' alt=';P' />ROCEDURE&nbsp;dbo.IsJobRunning<br />(<br />&nbsp;&nbsp;@job_name&nbsp;varchar(255),<br />&nbsp;&nbsp;@is_job_running&nbsp;int&nbsp;OUTPUT<br />)<br />AS<br />&nbsp;&nbsp;BEGIN<br />&nbsp;&nbsp;&nbsp;&nbsp;DECLARE&nbsp;@job_id&nbsp;uniqueidentifier&nbsp;<br />&nbsp;&nbsp;&nbsp;&nbsp;<br />&nbsp;&nbsp;&nbsp;&nbsp;CREATE&nbsp;TABLE&nbsp;#xp_results&nbsp;<br />&nbsp;&nbsp;&nbsp;&nbsp;(<br />&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;job_id&nbsp;uniqueidentifier&nbsp;NOT&nbsp;NULL,<br />&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;last_run_date&nbsp;int&nbsp;NOT&nbsp;NULL,<br />&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;last_run_time&nbsp;int&nbsp;NOT&nbsp;NULL,<br />&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;next_run_date&nbsp;int&nbsp;NOT&nbsp;NULL,<br />&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;next_run_time&nbsp;int&nbsp;NOT&nbsp;NULL,<br />&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;next_run_schedule_id&nbsp;int&nbsp;NOT&nbsp;NULL,<br />&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;requested_to_run&nbsp;int&nbsp;NOT&nbsp;NULL,&nbsp;--&nbsp;BOOL<br />&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;request_source&nbsp;int&nbsp;NOT&nbsp;NULL,<br />&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;request_source_id&nbsp;sysname&nbsp;COLLATE&nbsp;database_default&nbsp;NULL,<br />&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;running&nbsp;int&nbsp;NOT&nbsp;NULL,&nbsp;--&nbsp;BOOL<br />&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;current_step&nbsp;int&nbsp;NOT&nbsp;NULL,<br />&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;current_retry_attempt&nbsp;int&nbsp;NOT&nbsp;NULL,<br />&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;job_state&nbsp;int&nbsp;NOT&nbsp;NULL<br />&nbsp;&nbsp;&nbsp;&nbsp<img src='/community/emoticons/emotion-5.gif' alt='' /><br />&nbsp;&nbsp;&nbsp;&nbsp;<br />&nbsp;&nbsp;&nbsp;&nbsp;SELECT&nbsp;@job_id&nbsp;=&nbsp;sj.job_id&nbsp;<br />&nbsp;&nbsp;&nbsp;&nbsp;FROM&nbsp;msdb.dbo.sysjobs&nbsp;sj<br />&nbsp;&nbsp;&nbsp;&nbsp;WHERE&nbsp;sj.name&nbsp;=&nbsp;@job_name<br />&nbsp;&nbsp;&nbsp;&nbsp;<br />&nbsp;&nbsp;&nbsp;&nbsp;INSERT&nbsp;INTO&nbsp;#xp_results<br />&nbsp;&nbsp;&nbsp;&nbsp;EXEC&nbsp;master.dbo.xp_sqlagent_enum_jobs&nbsp;1,&nbsp;''<br /><br />&nbsp;&nbsp;&nbsp;&nbsp;SELECT&nbsp;@is_job_running&nbsp;=&nbsp;running<br />&nbsp;&nbsp;&nbsp;&nbsp;FROM&nbsp;#xp_results<br />&nbsp;&nbsp;&nbsp;&nbsp;WHERE&nbsp;job_id&nbsp;=&nbsp;@job_id<br />&nbsp;&nbsp;END</font id="Courier New"><br /><br />After a few days of struggling I think this is the best way to do it. But I am waiting for your comments.<br /><br />--<br /><br /><i>"Recommended By Dr. Audioscrobbler." <br /<a target="_blank" href=http://www.last.fm/user/chopeen/>http://www.last.fm/user/chopeen/</a></i>

You are right, but it won't return this information as an OUTPUT parameter. Which means that you cannot assign it to a variable.

--

"Recommended By Dr. Audioscrobbler."
http://www.last.fm/user/chopeen/