Obtaining Job Status Programmatically...

[dougcoulter]

Is it possible to determine a job's status programmatically?

For example, upon starting the SQL Server Agent, I would like to determine when/if several of my jobs that run overnight actually did. These jobs reset certain tables that are used throughout the day, and if they are not properly executed, data intended for these tables throughout the day will not be reliable.

Thanks...

 

[tlbroadbent]

Job history is stored in the msdb.dbo.sysjobhistory table. You can use the stored procedure, sp_help_jobhistory, to view history. You can also create your own script to return a less verbose result set.

Example: Select essential job history info

Select
RunDate=h.run_date,
RunTime=h.run_time,
JobName=convert(varchar(40),j.name),
StepName=convert(varchar(60), h.step_name),
JobMessages=convert(varchar(2048),h.message)

From msdb.dbo.sysjobhistory h
Inner Join msdb.dbo.sysjobs j
On h.job_id = j.job_id

-- Select jobs run yesterday or today
Where h.run_date>convert(varchar(10), dateadd(d,-1,getdate()),112)

-- Eliminate the DBA jobs based on job name
And left(j.name,3)<>'DBA'
Terry L. Broadbent
FAQ183-874 contains tips for posting questions in these forums.
NOTE: Reference to the FAQ is not directed at any individual.

 

[dougcoulter]

Thanks again Terry - that is exactly what I was looking for. I was pretty sure this information resided in a system table(s), but was unsure of where.

Thanks!