JChutey JChutey - 1 month ago 21
SQL Question

Within SQL Server Mgmt Studio, is it possible to query against 'SQL Server Agent > Jobs'

Within SQL Server Mgmt Studio, is it possible to query against 'SQL Server Agent > Jobs' and successfully transfer the results produced for 'failed Jobs within a 24hr period' to something like Excel?

Answer

I usually create a report that uses a similar query to the one below to find all the jobs that failed and what steps failed. You can filter it for the job you want as well. All the information is in the msdb database.

SELECT j.name JobName,h.step_name StepName, 
CONVERT(CHAR(10), CAST(STR(h.run_date,8, 0) AS dateTIME), 111) RunDate, 
STUFF(STUFF(RIGHT('000000' + CAST ( h.run_time AS VARCHAR(6 ) ) ,6),5,0,':'),3,0,':') RunTime, 
h.run_duration StepDuration,
case h.run_status when 0 then 'failed'
when 1 then 'Succeded' 
when 2 then 'Retry' 
when 3 then 'Cancelled' 
when 4 then 'In Progress' 
end as ExecutionStatus, 
h.message MessageGenerated
FROM sysjobhistory h inner join sysjobs j
ON j.job_id = h.job_id
where h.run_status = 0 and CONVERT(varchar(8),h.run_date,112) = CONVERT(varchar(8),GetDate(),112)