June 22, 2008 at 10:26 am
Hi ,
I am trying to pull out records for sql agent job for detail of its last run, successfull/failed , schedule enabled or not etc and getting stuck for group by conditions as below query pulling all records and i want single record for each job for its last run.
SELECT distinct j.[name],
MAX(CAST(
STUFF(STUFF(CAST(jh.run_date as varchar),7,0,'-'),5,0,'-') + ' ' +
STUFF(STUFF(REPLACE(STR(jh.run_time,6,0),' ','0'),5,0,':'),3,0,':') as datetime)) AS [LastRun],
CASE jh.run_status WHEN 0 THEN 'Failed'
WHEN 1 THEN 'Success'
WHEN 2 THEN 'Retry'
WHEN 3 THEN 'Canceled'
WHEN 4 THEN 'In progress'
END AS Status
,jh.run_date as [Job last run date]
,jh.run_time as [job last run time]
,j.enabled as [Job Enabled]
,js.enabled as [Job schedule Enabled]
,js.next_run_date as [Job next run date]
,js.next_run_time as [job next run time]
FROM msdb.dbo.sysjobs j left outer join
sysjobhistory jh on j.job_id = jh.job_id
left outer join
sysjobschedules js on j.job_id =js.job_id
group by j.name,j.enabled,js.enabled,js.next_run_date
,js.next_run_time,jh.run_date,jh.run_time,jh.run_status
Any suggestion is appreciated....
June 23, 2008 at 2:42 pm
I used a temp table to select the latest one. (Not a good one, but works.):hehe:
November 12, 2010 at 2:14 am
use the below query u ll get 1 record for each job.
select max(lastrun),name from(
SELECT distinct j.[name],
MAX(CAST(
STUFF(STUFF(CAST(jh.run_date as varchar),7,0,'-'),5,0,'-') + ' ' +
STUFF(STUFF(REPLACE(STR(jh.run_time,6,0),' ','0'),5,0,':'),3,0,':') as datetime)) AS [LastRun],
CASE jh.run_status WHEN 0 THEN 'Failed'
WHEN 1 THEN 'Success'
WHEN 2 THEN 'Retry'
WHEN 3 THEN 'Canceled'
WHEN 4 THEN 'In progress'
END AS Status
,jh.run_date as [Job last run date]
,jh.run_time as [job last run time]
,j.enabled as [Job Enabled]
,js.enabled as [Job schedule Enabled]
,js.next_run_date as [Job next run date]
,js.next_run_time as [job next run time]
FROM msdb.dbo.sysjobs j left outer join
msdb.dbo.sysjobhistory jh on j.job_id = jh.job_id
left outer join
msdb.dbo.sysjobschedules js on j.job_id =js.job_id
group by j.name,j.enabled,js.enabled,js.next_run_date
,js.next_run_time,jh.run_date,jh.run_time,jh.run_status) a
group by a.name
November 24, 2015 at 2:29 pm
The table msdb..sysjobschedules does not have any field called "enabled"
Change the query by this
SELECT distinct j.[name],
MAX(CAST(
STUFF(STUFF(CAST(jh.run_date as varchar),7,0,'-'),5,0,'-') + ' ' +
STUFF(STUFF(REPLACE(STR(jh.run_time,6,0),' ','0'),5,0,':'),3,0,':') as datetime)) AS [LastRun],
CASE jh.run_status WHEN 0 THEN 'Failed'
WHEN 1 THEN 'Success'
WHEN 2 THEN 'Retry'
WHEN 3 THEN 'Canceled'
WHEN 4 THEN 'In progress'
END AS Status
,jh.run_date as [Job last run date]
,jh.run_time as [job last run time]
, j.enabled as [Job Enabled]
,js.next_run_date as [Job next run date]
,js.next_run_time as [job next run time]
FROM msdb.dbo.sysjobs j left outer join
msdb..sysjobhistory jh on j.job_id = jh.job_id
left outer join
msdb..sysjobschedules js on j.job_id =js.job_id
group by j.name,j.enabled,js.next_run_date
,js.next_run_time,jh.run_date,jh.run_time,jh.run_status
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply