query to list failed jobs only

Can i get a query to list only failed SQL Agent jobs ?msdb.dbo.sp_help_job will return info about the SQL Agent jobs. Failed
jobs are represented by (last_run_outcome = 0). Unfortunately,
sp_help_job calls another proc that does an INSERT...EXEC... so you
can't wrap the results of the "exec msdb.dbo.sp_help_job" into a little
batch that captures the results in a temp table and selects only the
rows from that temp table where last_run_outcome = 0. But you could do
it manually by running sp_help_job in QA, saving the results to a text
file (or CSV), opening the file in Excel and sorting by that
last_run_outcome column to get the failed jobs at the top of the list.
I know it's a little ugly but it would work.
Alternately, if you don't care about multi-server jobs (just local jobs)
then you can get the info you're after (more or less) just by joining
the msdb.dbo.sysjobs table and the msdb.dbo.sysjobservers table. Like this:
select j.job_id, j.[name] from dbo.sysjobs as j
inner join dbo.sysjobservers as s on s.job_id = j.job_id
where s.last_run_outcome = 0
order by j.[name]
*mike hodgson* |/ database administrator/ | mallesons stephen jaques
*T* +61 (2) 9296 3668 |* F* +61 (2) 9296 3885 |* M* +61 (408) 675 907
*E* mailto:mike.hodgson@.mallesons.nospam.com |* W* http://www.mallesons.com
Hassan wrote:

>Can i get a query to list only failed SQL Agent jobs ?
>
>|||Here's a query to find jobs with any failed steps:
SELECT DISTINCT j.name
FROM msdb.dbo.sysjobhistory
inner join msdb.dbo.sysjobs
on h.job_id = j.job_id
where h.run_status <> 1
and h.step_id > 0
If you want to limit the scope to the past w's history, add:
and convert(smalldatetime, convert(char(8), h.run_date), 112) > getdate() -
7
http://www.aspfaq.com/
(Reverse address to reply.)
"Hassan" <fatima_ja@.hotmail.com> wrote in message
news:uhSZDAhGFHA.904@.tk2msftngp13.phx.gbl...
> Can i get a query to list only failed SQL Agent jobs ?
>