if exists (select * from dbo.sysobjects where id = object_id(N'dbo.usp_get_job_status') and OBJECTPROPERTY(id, N'IsProcedure') = 1) drop procedure dbo.usp_get_job_status GO SET QUOTED_IDENTIFIER ON GO SET ANSI_NULLS ON GO create procedure dbo.usp_get_job_status as begin select j.job_id , j.name as job_name , case when j.enabled = 1 then 'Enabled' else 'Disabled' end as job_enabled , j.description as job_description , j.start_step_id , j.date_created as job_created , j.date_modified as job_modified , case j.notify_level_eventlog when 0 then 'never' when 1 then 'on success' when 2 then 'on fail' when 3 then 'on completion' else 'unknown' end as eventlog_notification , case j.notify_level_email when 0 then 'never' when 1 then 'on success' when 2 then 'on fail' when 3 then 'on completion' else 'unknown' end as email_notification , case j.notify_level_netsend when 0 then 'never' when 1 then 'on success' when 2 then 'on fail' when 3 then 'on completion' else 'unknown' end as net_send_notification , case j.notify_level_page when 0 then 'never' when 1 then 'on success' when 2 then 'on fail' when 3 then 'on completion' else 'unknown' end as pager_notification , s.step_id , s.step_name , s.command as job_step_command , s.on_success_step_id as step_ok_next_step_id , case s.on_success_action when 1 then 'quit w/success' when 2 then 'quit w/fail' when 3 then 'goto next step' when 4 then 'goto step' else 'unknown' end as step_success_action , s.on_fail_step_id as step_fail_next_step_id , case s.on_fail_action when 1 then 'quit w/success' when 2 then 'quit w/fail' when 3 then 'goto next step' when 4 then 'goto step' else 'unknown' end as step_fail_action , s.database_name as step_database_name , coalesce(s.output_file_name, N'no output file in use') as step_out_filename , case s.last_run_outcome when 0 then 'failed' when 1 then 'succeeded' when 3 then 'canceled' else 'unknown' end as last_step_run_outcome , s.last_run_duration as last_step_run_duration_seconds , s.last_run_date as last_step_run_date , s.last_run_time as last_step_run_time , h.message from msdb..sysjobs j with (nolock) inner join msdb..sysjobsteps s with (nolock) on j.job_id = s.job_id inner join msdb..sysjobhistory h with (nolock) on j.job_id = h.job_id and s.step_id = h.step_id inner join ( select job_id , step_id , year(most_recent_run_date) * 10000 + month(most_recent_run_date) * 100 + day(most_recent_run_date) as latest_run_date , datepart(hh, most_recent_run_date) * 10000 + datepart(mi, most_recent_run_date) * 100 + datepart(ss, most_recent_run_date) as latest_run_time from ( select job_id , step_id , cast(max( left(cast(run_date as varchar(8)),4) + '-' + substring(cast(run_date as varchar(8)),5,2) + '-' + right(cast(run_date as varchar(8)),2) + ' ' + left(right('000000' + rtrim(cast(run_time as varchar(6))), 6),2) + ':' + substring(right('000000' + rtrim(cast(run_time as varchar(6))), 6),3,2) + ':' + right('000000' + rtrim(cast(run_time as varchar(6))), 2) ) as datetime) as most_recent_run_date from msdb..sysjobhistory with (nolock) group by job_id, step_id ) a ) mh on j.job_id = mh.job_id and s.step_id = mh.step_id and h.run_date = mh.latest_run_date and h.run_time = mh.latest_run_time order by j.job_name, s.step_id end GO SET QUOTED_IDENTIFIER OFF GO SET ANSI_NULLS ON GO /* Don McMunn is a professional, business-focused consultant specializing in data warehousing and business intelligence design, development and delivery for over a decade with the Fortune 500 and SMB segments. He may be reached at dmcmunn at ipcdesigns dot com */