Sql Server Agent - How to Test Job Execution Status
Feb 28th, 2007 by Onyrix 607 Views | 
Email This Post
| 
Print This Post
(1 votes, average: 5 out of 5)
Loading ...
Have you some Sql Server Agent job you want to test the execution status?
The result set includes a column called current_execution_status, but you might not know how to interpret the column values because SQL Server Books Online (BOL) doesn’t document them.
EXEC msdb..sp_help_job
@job_name = ‘InfiniteLoop’,
@job_aspect = N’job’
However, you can learn a lot by directly reading stored-procedure code. The parameter-declaration section of sp_help_job, as shown below, tells you how to interpret the value of current_execution_status:
- @execution_status INT = NULL,
- — 1 = Executing,
- — 2 = Waiting For Thread,
- — 3 = Between Retries,
- — 4 = Idle,
- — 5 = Suspended,
- — 6 = [obsolete],
- — 7 = PerformingCompletion
- — Actions
Now, you can pass the result set to your program by using the syntax:
which stores sp_help_job’s output in a table. . . .
INSERT INTO MyTable EXEC MyProc
del.icio.us
Digg
Furl
Reddit
Technorati
Stumbleupon