March 12, 2013 at 2:40 pm
I'm trying to create some code to quickly run on each server to get a listing of failed jobs currently on that particular server.
The code so far is as follows:
SELECT DISTINCT(sj.name), sjs.last_run_outcome, sjh.run_status
FROM msdb.dbo.sysjobs AS SJ INNER JOIN msdb.dbo.sysjobhistory AS SJH
ON SJ.job_id = SJH.job_id
INNER JOIN msdb.dbo.sysjobsteps AS SJS
ON sj.job_id = SJS.job_id
WHERE
SJS.last_run_outcome = 0
AND
SJH.run_status = 0
The code runs but in my case on my test server I get 3 results back showing 0's in the "last_run_outcome" column & the "run_status" column.
The thing is...when I look at the Job Activity Monitor, it shows "Succeeded" under the "Last Run Outcome" column for those 3 particular jobs.
Am I doing something wrong? Querying the wrong table?
Thanks!
March 12, 2013 at 3:03 pm
DISTINCT(sj.name)
That doesn't return unique names, distinct returns unique rows:
DISTINCT
Specifies that only unique rows can appear in the result set. Null values are considered equal for the purposes of the DISTINCT keyword.
You will need something like this hack to get the latest run date and time:
and convert(varchar,SJH.run_date) +
right('000000' + convert(varchar,SJH.run_time),6) =
(select MAX(convert(varchar,x.run_date) +
right('000000' + convert(varchar,x.run_time),6))
from msdb.dbo.sysjobhistory x
where x.job_id = SJH.job_id)
March 12, 2013 at 3:27 pm
robertd 77391 (3/12/2013)
DISTINCT(sj.name)That doesn't return unique names, distinct returns unique rows:
DISTINCT
Specifies that only unique rows can appear in the result set. Null values are considered equal for the purposes of the DISTINCT keyword.
You will need something like this hack to get the latest run date and time:
and convert(varchar,SJH.run_date) +
right('000000' + convert(varchar,SJH.run_time),6) =
(select MAX(convert(varchar,x.run_date) +
right('000000' + convert(varchar,x.run_time),6))
from msdb.dbo.sysjobhistory x
where x.job_id = SJH.job_id)
WHOA!! :w00t: Robert!! That was seriously spot on!! Thank you very much!! :w00t: I will have to dissect your code and see exactly what you did.
I very much appreciate your help on this matter!!
I can now go home, play some Black Ops II and know I'll come in to work tomorrow to working code. You made my evening!! 😀
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply