SQL 2000 Agent Jobs status

[ericarivera411]

Hey guys,
I am supposed to write a query that will return the results of my Agent Jobs status, run duration, next run date, if the job failed or not for a certain job category, TPA Conversion. My problem is that I have the following SQL statement, it runs, but it returns to many results. They want only one result per job. Anyone have any great ideas? Any help is appreciated. Thanks!


select Jobs.name as 'Job Name',

syscat.name as Category,



CASE sysjobschedules.freq_type

WHEN 1 THEN 'Once'
WHEN 4 THEN 'Daily'
WHEN 8 THEN 'Weekly'
WHEN 16 THEN 'Monthly'
end as 'Job Frequency',


MAX(run_date) AS 'Last Execution Date',

/* run_time, is really the time the job completed */
SUBSTRING(REPLICATE('0',6 - DATALENGTH(ltrim(CAST(run_time AS varchar(6))))) + ltrim(CAST(run_Time AS varchar(6))),1,2) + ':' +

SUBSTRING(REPLICATE('0',6 - DATALENGTH(ltrim(CAST(run_time AS varchar(6))))) + ltrim(CAST(run_Time AS varchar(6))),3,2) + ':' +

SUBSTRING(REPLICATE('0',6 - DATALENGTH(ltrim(CAST(run_time AS varchar(6))))) + ltrim(CAST(run_Time AS varchar(6))),5,2) as Completion_Time,

run_duration as "Duration(HHMMSS)",

(SUBSTRING(REPLICATE('0',7 - DATALENGTH(ltrim(CAST(run_duration AS varchar(7))))) + ltrim(CAST(run_duration AS varchar(7))),1,3) * 3600) + /*convert hrs to seconds */

(SUBSTRING(REPLICATE('0',7 - DATALENGTH(ltrim(CAST(run_duration AS varchar(7))))) + ltrim(CAST(run_duration AS varchar(7))),4,2) * 60) + /*convert mins to seconds */

(SUBSTRING(REPLICATE('0',7 - DATALENGTH(ltrim(CAST(run_duration AS varchar(7))))) + ltrim(CAST(run_duration AS varchar(7))),6,2)) AS Run_Seconds, /*convert seconds to number */


Case run_status

WHEN 0 THEN 'Failed'

WHEN 1 THEN 'Succeeded'

WHEN 2 THEN 'Retry'

WHEN 3 THEN 'Canceled'

WHEN 4 THEN 'In progress'
end as Outcome

FROM SQL1.msdb.dbo.sysjobschedules, SQL1.msdb.dbo.sysjobhistory HIST


INNER JOIN SQL1.msdb.dbo.sysjobs jobs on hist.job_id=jobs.job_id

INNER JOIN SQL1.msdb.dbo.syscategories syscat on jobs.category_id = syscat.category_id


WHERE syscat.name = 'TPA Conversion'


GROUP BY jobs.name, syscat.name, sysjobschedules.freq_type, hist.run_date, Hist.run_time, Hist.run_duration, Hist.run_status


Results:

BMA INFORMATION TPA Conversion Daily 20061214 22:00:00 4121 2481 Succeeded
BMA INFORMATION TPA Conversion Daily 20061214 22:00:00 14708 6428 Succeeded
BMA INFORMATION TPA Conversion Daily 20061214 22:41:22 400 240 Succeeded
BMA INFORMATION TPA Conversion Daily 20061214 22:45:23 5911 3551 Succeeded
BMA INFORMATION TPA Conversion Daily 20061214 23:44:35 19 19 Succeeded
BMA INFORMATION TPA Conversion Daily 20061214 23:44:54 206 126 Succeeded
BMA INFORMATION TPA Conversion Daily 20061214 23:47:01 2 2 Succeeded
BMA INFORMATION TPA Conversion Daily 20061214 23:47:03 5 5 Succeeded
BMA INFORMATION TPA Conversion Daily 20061215 22:00:00 4223 2543 Succeeded
BMA INFORMATION TPA Conversion Daily 20061215 22:00:00 14130 6090 Failed
BMA INFORMATION TPA Conversion Daily 20061215 22:42:24 446 286 Succeeded
BMA INFORMATION TPA Conversion Daily 20061215 22:47:10 5419 3259 Failed
BMA INFORMATION TPA Conversion Daily 20061217 12:19:27 111 71 Canceled
BMA INFORMATION TPA Conversion Daily 20061217 12:19:27 113 73 Canceled
BMA INFORMATION TPA Conversion Daily 20061217 22:00:00 14709 6429 Succeeded
BMA INFORMATION TPA Conversion Daily 20061217 22:00:01 4217 2537 Succeeded
BMA INFORMATION TPA Conversion Daily 20061217 22:42:19 452 292 Succeeded
BMA INFORMATION TPA Conversion Daily 20061217 22:47:11 5727 3447 Succeeded
BMA INFORMATION TPA Conversion Daily 20061217 23:44:40 17 17 Succeeded
BMA INFORMATION TPA Conversion Daily 20061217 23:44:57 207 127 Succeeded
BMA INFORMATION TPA Conversion Daily 20061217 23:47:04 5 5 Succeeded
BMA INFORMATION TPA Conversion Daily 20061217 23:47:09 0 0 Succeeded
BMA INFORMATION TPA Conversion Daily 20061218 22:00:00 14556 6356 Succeeded
BMA INFORMATION TPA Conversion Daily 20061218 22:00:01 4146 2506 Succeeded
BMA INFORMATION TPA Conversion Daily 20061218 22:41:48 445 285 Succeeded
BMA INFORMATION TPA Conversion Daily 20061218 22:46:34 5650 3410 Succeeded
BMA INFORMATION TPA Conversion Daily 20061218 23:43:25 18 18 Succeeded
BMA INFORMATION TPA Conversion Daily 20061218 23:43:43 205 125 Succeeded
BMA INFORMATION TPA Conversion Daily 20061218 23:45:48 2 2 Succeeded
BMA INFORMATION TPA Conversion Daily 20061218 23:45:50 5 5 Succeeded
BMA INFORMATION TPA Conversion Daily 20061219 22:00:00 4557 2757 Succeeded
BMA INFORMATION TPA Conversion Daily 20061219 22:00:00 15002 6602 Succeeded
BMA INFORMATION TPA Conversion Daily 20061219 22:45:58 428 268 Succeeded
BMA INFORMATION TPA Conversion Daily 20061219 22:50:28 5700 3420 Succeeded
BMA INFORMATION TPA Conversion Daily 20061219 23:47:29 20 20 Succeeded
BMA INFORMATION TPA Conversion Daily 20061219 23:47:49 205 125 Succeeded
BMA INFORMATION TPA Conversion Daily 20061219 23:49:54 4 4 Succeeded
BMA INFORMATION TPA Conversion Daily 20061219 23:49:58 4 4 Succeeded
BMA INFORMATION TPA Conversion Weekly 20061214 22:00:00 4121 2481 Succeeded
BMA INFORMATION TPA Conversion Weekly 20061214 22:00:00 14708 6428 Succeeded



etc...

 

[mutley1]

Hi Erica,

Try this. You can amend to add the last runtime etc. and success or failure. (only coveres enabled jobs but you can tailor that too.

set nocount on
go
use msdb
go
set rowcount 0
declare 
@job_id varchar(200),
@sched_id varchar(200),
@freq_type int,
@freq_interval int,
@freq_subday_type int,
@freq_subday_interval int,
@freq_relative_interval int,
@freq_recurrence_factor int,
@active_start_date int,
@schedule_word varchar(1000),
@schedule_day varchar(200),
@conv_start_time char(6),
@conv_end_time char(6),
@category varchar (50)

create table #joblistings
(job_id varchar(200),
sched_id varchar(200),
job_name sysname,
-- sched_name sysname null,
status int,
scheduled int null,
schedule_word varchar(1000) null,
freq_type int null,
freq_interval int null,
freq_subday_type int null,
freq_subday_interval int null,
freq_relative_interval int null,
freq_recurrence_factor int null,
active_start_date int null,
active_end_date int null,
active_start_time int null,
active_end_time int null,
date_created datetime null,
category varchar (50) null)

insert into #joblistings 
(job_id,
sched_id ,
job_name ,
-- sched_name ,
status ,
scheduled ,
schedule_word ,
freq_type,
freq_interval,
freq_subday_type,
freq_subday_interval,
freq_relative_interval,
freq_recurrence_factor,
active_start_date,
active_end_date,
active_start_time,
active_end_time,
date_created,
category) 

select 
j.job_id,
c.schedule_id,
j.name ,
-- c.name ,
j.enabled,
c.enabled,
null,
c.freq_type,
c.freq_interval,
c.freq_subday_type,
c.freq_subday_interval,
c.freq_relative_interval,
c.freq_recurrence_factor,
c.active_start_date,
c.active_end_date,
c.active_start_time,
c.active_end_time,
j.date_created,
x.name

from sysjobs j, 
	sysjobschedules c,
	syscategories x

where j.job_id*=c.job_id
and j.category_id = x.category_id

 

while 1=1

begin

set rowcount 0
set @schedule_word = ''
if (select count(*) from #joblistings where scheduled=1 and schedule_word is null) = 0
break
else
begin
set rowcount 1
select @job_id=job_id,
@sched_id=sched_id,
@freq_type=freq_type,
@freq_interval=freq_interval,
@freq_subday_type=freq_subday_type,
@freq_subday_interval=freq_subday_interval,
@freq_relative_interval=freq_relative_interval,
@freq_recurrence_factor=freq_recurrence_factor,
@active_start_date = active_start_date,
@conv_start_time = case 
when len(active_start_time) < 6 then replicate('0',6-(len(active_start_time))) + cast(active_start_time as varchar(5))
else cast(active_start_time as varchar(6))
end,

@conv_end_time= case 
when len(active_end_time) < 6 then replicate('0',6-(len(active_end_time))) + cast(active_end_time as varchar(5))
else cast(active_end_time as varchar(6))
end,

@category = category

from #joblistings 
where schedule_word is null
and scheduled=1

if exists(select @freq_type where @freq_type in (1,64))

begin

select @schedule_word = case @freq_type 
when 1 then 'Occurs Once, On '+cast(@active_start_date as varchar(8))+', At '+@conv_start_time
when 64 then 'Occurs When sQL server Agent starts'
end

end

else

begin

if @freq_type=4

begin

select @schedule_word = 'Occurs Every '+cast(@freq_interval as varchar(10))+' Day(s)'
end

 

if @freq_type=8

begin

select @schedule_word = 'Occurs Every '+cast(@freq_recurrence_factor as varchar(3))+' Week(s)'

select @schedule_day=''

if (sELECT (convert(int,(@freq_interval/1)) % 2)) = 1
select @schedule_day = @schedule_day+'sun'

if (sELECT (convert(int,(@freq_interval/2)) % 2)) = 1
select @schedule_day = @schedule_day+'Mon'

if (sELECT (convert(int,(@freq_interval/4)) % 2)) = 1
select @schedule_day = @schedule_day+'Tue'

if (sELECT (convert(int,(@freq_interval/8)) % 2)) = 1
select @schedule_day = @schedule_day+'Wed'

if (sELECT (convert(int,(@freq_interval/16)) % 2)) = 1
select @schedule_day = @schedule_day+'Thu'

if (sELECT (convert(int,(@freq_interval/32)) % 2)) = 1
select @schedule_day = @schedule_day+'fri'

if (sELECT (convert(int,(@freq_interval/64)) % 2)) = 1
select @schedule_day = @schedule_day+'sat'

select @schedule_word = @schedule_word+', On '+@schedule_day

end

 
if @freq_type=16

begin

select @schedule_word = 'Occurs Every '+cast(@freq_recurrence_factor as varchar(3))+' Month(s) on Day '+cast(@freq_interval as varchar(3))+' of that Month'

end

 
if @freq_type=32

begin

select @schedule_word = case @freq_relative_interval
when 1 then 'first'
when 2 then 'second'
when 4 then 'Third'
when 8 then 'fourth'
when 16 then 'Last'

end

select @schedule_word = 

case @freq_interval

when 1 then 'Occurs Every '+@schedule_word+' sunday of the Month'
when 2 then 'Occurs Every '+@schedule_word+' Monday of the Month'
when 3 then 'Occurs Every '+@schedule_word+' Tueday of the Month'
when 4 then 'Occurs Every '+@schedule_word+' Wednesday of the Month'
when 5 then 'Occurs Every '+@schedule_word+' Thursday of the Month'
when 6 then 'Occurs Every '+@schedule_word+' friday of the Month'
when 7 then 'Occurs Every '+@schedule_word+' saturday of the Month'
when 8 then 'Occurs Every '+@schedule_word+' Day of the Month'
when 9 then 'Occurs Every '+@schedule_word+' Weekday of the Month'
when 10 then 'Occurs Every '+@schedule_word+' Weekend Day of the Month'

end

end

select @schedule_word = 

case @freq_subday_type

when 1 then @schedule_word+', At '+@conv_start_time
when 2 then @schedule_word+', every '+cast(@freq_subday_interval as varchar(3))+' second(s) Between '+@conv_start_time+' and '+@conv_end_time
when 4 then @schedule_word+', every '+cast(@freq_subday_interval as varchar(3))+' Minute(s) Between '+@conv_start_time+' and '+@conv_end_time
when 8 then @schedule_word+', every '+cast(@freq_subday_interval as varchar(3))+' Hour(s) Between '+@conv_start_time+' and '+@conv_end_time

end

end

end

update #joblistings
set schedule_word=@schedule_word
where job_id=@job_id
and sched_id=@sched_id
set rowcount 0

end

select job_name ,

--sched_name ,

status = case status
when 1 then 'Enabled'
when 0 then 'Disabled'
else ' '

 end,

scheduled= case scheduled 
when 1 then 'Yes'
when 0 then 'No'
else ' '

 end,

schedule_word as 'frequency' ,
--active_start_date,
--active_end_date,
--date_created,
category

from #joblistings 
where scheduled=1
order by category, job_name

drop table #joblistings

Cheers,

M.

 

[ericarivera411]

Thanks! You gave me some great ideas!

 

[mutley1]

You're welcome! Happy Hols!