December 1, 2006 at 9:23 am
i'm making a little query to display the Job information
similar to the lay-out of Enterprise Manager
the question is... how do i get just one result instead of
the multiple rows for each job?
here's what i have so far:
use msdb
go
--***********************************************************
--***********************************************************
--Job Name
select distinct [name] as 'Job Name',
--***********************************************************
--***********************************************************
--Enabled or Disabled
case [enabled] when 1 then 'Enabled' else 'Disabled'
end as 'Enabled',
--***********************************************************
--***********************************************************
--Last Run Date & Time combined into the same column on output.
cast
(ltrim(str(run_date))+' '+stuff(stuff(right('000000'+ltrim(str(run_time)), 6) , 3, 0, ':'), 6, 0, ':')
as datetime) as 'Last Run',
--***********************************************************
--***********************************************************
--Last Run Status (Failed or Success)
case [sysjobhistory].[run_status]
when 0 then 'Failed' else 'Success'
end as 'Status' ,
--***********************************************************
--***********************************************************
--Last Run Duration HHMMSS Converted to 00:00:00
STUFF(STUFF(REPLACE(STR(run_duration,6),' ','0'),5,0,':'),3,0,':')
as 'Duration',
--***********************************************************
--***********************************************************
--Next Run Date & Time combined into the same column on output.
cast
(ltrim(str(next_run_date))+' '+stuff(stuff(right('000000'+ltrim(str(next_run_time)), 6) , 3, 0, ':'), 6, 0, ':')
as datetime) as 'Next Run'
--***********************************************************
--***********************************************************
-- From Joined System Tables: Sysjobs, Sysjobschedules & Sysjobhistory.
from sysjobs
join sysjobschedules on sysjobs.job_id = sysjobschedules.job_id
join sysjobhistory on sysjobs.job_id = sysjobhistory.job_id
where next_run_date like '20%'
any recommendations would be helpful.
thanks in advance.
_________________________
December 1, 2006 at 10:17 am
it is because the history lists a status for each step in the job.
Add a where clause " Where step_id = 0" and you will get the results your looking for.
Eric
December 1, 2006 at 11:12 am
cool... thanks for the reply, but
now i'm getting this:
Msg 241, Level 16, State 1, Line 5
Conversion failed when converting datetime from character string.
how can the "where sysjobhistory.step_id" be causing this?
_________________________
December 1, 2006 at 11:46 am
Also, just in case the job is not scheduled, but run on a regular basis, I would recommend the following change to the script.
left join sysschedules
I made a few other mods....see the following for the script
--alter procedure cspJobMonitor
--as
--***********************************************************
--***********************************************************
--Job Name
select
distinct [name] as 'Job Name',
--***********************************************************
--***********************************************************
--Enabled or Disabled
case
[enabled] when 1 then 'Enabled' else 'Disabled'
end
as 'Enabled',
--***********************************************************
--***********************************************************
--Last Run Date & Time combined into the same column on output.
cast
(
ltrim(str(run_date))+' '+stuff(stuff(right('000000'+ltrim(str(run_time)), 6) , 3, 0, ':'), 6, 0, ':')
as
datetime) as 'Last Run',
--***********************************************************
--***********************************************************
step_id
as Step,
--***********************************************************
--***********************************************************
--Last Run Status (Failed or Success)
case
[h].[run_status]
when
0 then 'Failed' else 'Success'
end
as 'Status' ,
--***********************************************************
--***********************************************************
--Last Run Duration HHMMSS Converted to 00:00:00
STUFF
(STUFF(REPLACE(STR(run_duration,6),' ','0'),5,0,':'),3,0,':')
as
'Duration',
--***********************************************************
--***********************************************************
--Next Run Date & Time combined into the same column on output.
cast
(
ltrim(str(next_run_date))+' '+stuff(stuff(right('000000'+ltrim(str(next_run_time)), 6) , 3, 0, ':'), 6, 0, ':')
as
datetime) as 'Next Run'
--***********************************************************
--***********************************************************
-- From Joined System Tables: Sysjobs, Sysjobschedules & Sysjobhistory.
from
msdb.dbo.sysjobs j
left
join msdb.dbo.sysjobschedules s on j.job_id = s.job_id
join
msdb.dbo.sysjobhistory h on j.job_id = h.job_id
where
cast
(ltrim(str(run_date))+' '+stuff(stuff(right('000000'+ltrim(str(run_time)), 6) , 3, 0, ':'), 6, 0, ':')
as
datetime) > dateadd(day, -1, getdate())
and
step_id = 0
December 1, 2006 at 2:32 pm
yes but it doesn't show ALL jobs.
i'm trying to create a list of all jobs success, fail, enabled, disabled,
the works! only the most current job given. even if the job is currently
running still need to see it listed.
by the way this returns all the types of status:
case [h].[run_status]
when 0 then 'Failed'
when 1 then 'Success'
when 2 then 'Retry'
when 3 then 'Cancelled'
else 'Running'
end as 'Status'
any ideas though about the most recent/current job displayed?
thanks again by the way.
_________________________
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply