Checking job status from TSQL procedure

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Checking job status from TSQL procedure

Author

Topic

cornelius
Starting Member

11 Posts

Posted - 2007-05-09 : 12:26:18

Hello
I have a job that runs every 30 seconds, and when it runs- I disable another job that might cause an error when running at the same time with the first job. I disable the job using sp_update_job function.
The problem is that once in a while the second job is already running when sp_update_job is issued, and the first job (which was unsuccessful with disabling the second job) ends with an error.
I want to add a check to see if the first job is currently running, and if so- stop it using sp_stop_job.
I know that the function sp_help_job can supply information regarding a job, but I don't know how to use it from a TSQL procedure. The function returns a very large ResultSet and I only need to get the current_execution_status.
Can anybody help me with the syntax to do so?
I also tried using sp_stop_job function with try/catch blocks. but apparently the catch block does not catch the error generated when I try to stop a job that is not running at the moment.

Thank you
Nili

jezemine
Master Smack Fu Yak Hacker

2886 Posts

Posted - 2007-05-09 : 12:53:37

msdb.dbo.sp_help_job will give you the current execution status.

www.elsasoft.org

cornelius
Starting Member

11 Posts

Posted - 2007-05-10 : 02:55:52

Thanx,
I was aware of the function but am not sure of the way to use it.
Can you please give me an example of how to use sp_help_job from within a procedure- how do I read the ResultSet data (and specifically the execution status)?

so it would behave like:

if (is_job_running)
begin
sp_stop_job N'MyJob'
end

Thank you.

jezemine
Master Smack Fu Yak Hacker

2886 Posts

Posted - 2007-05-10 : 03:19:21

you could do something like this:


-- first create a temp table with the same columns
-- the result set of sp_help_job

insert into #mytemptable 
exec msdb.dbo.sp_help_job @job_id=@myjobid

-- now select from #mytemptable to see what the 
-- current_execution_status column has in it, and act accordingly.

see BOL here http://msdn2.microsoft.com/en-us/library/ms186722.aspx
for what the possible values of current_execution_status are.

Alternatively, you could pass @execution_status to sp_help_job to get a list of all jobs by execution status (such as all that are currently idle or suspended)

See the BOL page above for detail on the proc. It takes many different params.

www.elsasoft.org

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources