I'm using SQL server 2005, and I'm trying to write a query that goes
against the sysjobs, sysjobactivity, and sysjobhistory table that will
return all of the job history for each job executed in the last 24
hours, so that I can get a nice pretty daily email that tells me
everything I need to know without going to SQL Server Management Studio.
Unfortunately, after 4 hours of studying the relationships between these
three tables, I'm stumped.
they all contain job_id...
The sysjobs table contains 1 row for eac hjob.
the sysjobhistory table contains 1 row for each step executed, along
with a "pseudo-row" (step_id=0) containing the job outcome.
the sysjobactivity table contains the scheduling information -
particularly when each job was executed.
Sometimes, a job executes more than once in a 24 hour period. But
joining the sysjobactivity table to the sysjobhistory table doesn't seem
to be possible.
There is a "job_history_id" in the sysjobactivity table that *IS*
related to the instance_id in sysjobhistory - but instance_id is a
unique key and if you add that to the join syntax, you only get the
pseudo step with step_id 0.
Example of this:
select
a.name, b.start_execution_date, c.*
from
sysjobs A
inner join sysjobactivity B
on a.job_id=b.job_id
inner join sysjobhistory C
on b.job_id=C.job_id
and b.job_history_id=c.instance_id
This doesn't work for me, because some of my jobs have 20+ unrelated
steps, and if individual steps fail, the job still completes and reports
success.. so I have to report on the individual steps within the job.
rick
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
Introducing the Fusion Authority Quarterly Update. 80 pages of hard-hitting,
up-to-date ColdFusion information by your peers, delivered to your door four
times a year.
http://www.fusionauthority.com/quarterly
Archive:
http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:256015
Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
