October 31, 2008 at 1:40 pm
Can anyone help me convert this to work on my 2005 Instances??
It is a bit of SQL to list MSDB job Schedules
USE MSDB
GO
SET NOCOUNT ON
create table #temp
([Job Name] varchar(128),
[Job Enabled] varchar(3),
[Plan Name] varchar(128),
[Job Type] varchar(50),
[Databases] varchar(1000),
[Schedule Name] varchar(128),
[Schedule Enabled] varchar(3),
[Schedule] varchar(20),
[Interval] varchar(200),
[Start Date] char(10),
[End Date] char(10)
)
declare @job_name varchar(128), @JobEnabled varchar(3),
@plan_name varchar(128), @ScheduleName varchar(128), @ScheduleEnabled varchar(3),
@job_type varchar(50), @plan_id uniqueidentifier,
@dbString varchar(1000), @freq_interval int, @frequency varchar(20),
@Interval varchar(200), @freq_type int, @freq_recurrence_factor int,
@freq_relative_interval int, @freq_subday_type int, @freq_subday_interval int,
@active_start_time int, @active_end_time int,
@xActive_start_time varchar(8), @xActive_end_time varchar(8),
@startdate char(10), @enddate char(10)
declare maintCursor cursor for
select sj.name as job_Name,
case when sj.enabled = 1 then 'Yes'
when sj.enabled = 0 then 'No'
end
as JobEnabled,
sd.plan_id, sm.plan_name, sch.name,
case when sch.enabled = 1 then 'Yes'
when sch.enabled = 0 then 'No'
end
as ScheduleEnabled,
sch.freq_interval,
case when js.command like '%-BkUpDB%' then 'Database backup job'
when js.command like '%-BkUpLog%' then 'Transaction log backup job'
when js.command like '%-CkDB%' then 'Integrity checks job'
when js.command like '%-RebldIdx%' then 'Optimizations job'
end
as [Job_Type],
case when sch.freq_type = 16 or sch.freq_type = 32 then 'Monthly job'
when sch.freq_type = 8 then 'Weekly Job'
when sch.freq_type = 4 then 'Daily Job'
end
as [Schedule],
sch.freq_type, sch.freq_recurrence_factor, sch.freq_relative_interval,
sch.freq_subday_type, sch.freq_subday_interval,
sch.active_start_time, sch.active_end_time,
sch.active_start_date, sch.active_end_date
from sysjobs sj inner join sysdbmaintplan_jobs sd
on sj.job_id = sd.job_id inner join sysdbmaintplans sm
on sd.plan_id = sm.plan_id inner join sysjobsteps js
on sj.job_id = js.job_id inner join sysjobschedules sch
on sch.job_id = sj.job_id
order by sj.name
open maintCursor
fetch next from maintCursor into @job_Name, @JobEnabled, @plan_id, @plan_name,
@ScheduleName, @ScheduleEnabled, @freq_interval, @job_type, @frequency,
@freq_type, @freq_recurrence_factor, @freq_relative_interval, @freq_subday_type,
@freq_subday_interval, @active_start_time, @active_end_time, @StartDate, @EndDate
while @@fetch_status = 0
begin
select @xActive_start_time = replicate('0', 6 - len(@active_start_time)) + cast(@active_start_time as varchar)
select @xActive_end_time = replicate('0', 6 - len(@active_end_time)) + cast(@active_end_time as varchar)
select @xActive_start_time = substring(@xActive_start_time, 1, 2) + ':' + substring(@xActive_start_time, len(@xActive_start_time) - 3, 2) + ':' + right (@xActive_start_time, 2)
select @xActive_end_time = substring(@xActive_end_time, 1, 2) + ':' + substring(@xActive_end_time, len(@xActive_end_time) - 3, 2) + ':' + right (@xActive_end_time, 2)
select @dbString = ''
select @Interval = ''
select @dbString = @dbString + database_name + ', ' from sysdbmaintplan_databases
where plan_id = @plan_id
if @freq_type = 4
begin
select @Interval = @Interval + 'Every ' + cast(@freq_interval as varchar) + ' day(s).'
if @freq_subday_type = 1
begin
select @Interval = @Interval + ' Once at ' + @xActive_start_time + '.'
end
else
begin
select @Interval = @Interval + ' Every ' + cast(@freq_subday_interval as varchar) +
case cast(@freq_subday_type as varchar)
when '4' then ' minute(s)'
when '8' then ' hour(s)'
end
select @Interval = @Interval + ', starting at ' + @xActive_start_time + ' ending at ' + @xActive_end_time + '.'
end
end
if @freq_type = 8
begin
select @Interval = @Interval + 'Every ' + cast(@freq_recurrence_factor as varchar) + ' week(s) on'
if @freq_interval & 1 = 1 select @Interval = @Interval + ' SUN'
if @freq_interval & 2 = 2 select @Interval = @Interval + ' MON'
if @freq_interval & 4 = 4 select @Interval = @Interval + ' TUE'
if @freq_interval & 8 = 8 select @Interval = @Interval + ' WED'
if @freq_interval & 16 = 16 select @Interval = @Interval + ' THU'
if @freq_interval & 32 = 32 select @Interval = @Interval + ' FRI'
if @freq_interval & 64 = 64 select @Interval = @Interval + ' SAT'
select @Interval = @Interval + '.'
if @freq_subday_type = 1
begin
select @Interval = @Interval + ' Once at ' + @xActive_start_time + '.'
end
else
begin
select @Interval = @Interval + ' Every ' + cast(@freq_subday_interval as varchar) +
case cast(@freq_subday_type as varchar)
when '4' then ' minute(s)'
when '8' then ' hour(s)'
end
select @Interval = @Interval + ', starting at ' + @xActive_start_time + ' ending at ' + @xActive_end_time + '.'
end
end
if @freq_type = 16
begin
select @Interval = 'Day ' + cast(@freq_interval as varchar) + ', every ' + cast(@freq_recurrence_factor as varchar) + ' month(s).'
if @freq_subday_type = 1
begin
select @Interval = @Interval + ' Once at ' + @xActive_start_time + '.'
end
else
begin
select @Interval = @Interval + ' Every ' + cast(@freq_subday_interval as varchar) +
case cast(@freq_subday_type as varchar)
when '4' then ' minute(s)'
when '8' then ' hour(s)'
end
select @Interval = @Interval + ', starting at ' + @xActive_start_time + ' ending at ' + @xActive_end_time + '.'
end
end
if @freq_type = 32
begin
select @Interval = 'The ' +
case cast(@freq_relative_interval as varchar)
when '1' then 'first '
when '2' then 'second '
when '4' then 'third '
when '8' then 'fourth '
when '16' then 'last '
end
+
case cast(@freq_interval as varchar)
when '1' then 'Sunday'
when '2' then 'Monday'
when '3' then 'Tuesday'
when '4' then 'Wednesday'
when '5' then 'Thursday'
when '6' then 'Friday'
when '7' then 'Saturday'
when '8' then 'day'
when '9' then 'weekday'
when '10' then 'weekend day'
end
+
', every ' + cast(@freq_recurrence_factor as varchar) + ' month(s).'
if @freq_subday_type = 1
begin
select @Interval = @Interval + ' Once at ' + @xActive_start_time + '.'
end
else
begin
select @Interval = @Interval + ' Every ' + cast(@freq_subday_interval as varchar) +
case cast(@freq_subday_type as varchar)
when '4' then ' minute(s)'
when '8' then ' hour(s)'
end
select @Interval = @Interval + ', starting at ' + @xActive_start_time + ' ending at ' + @xActive_end_time + '.'
end
end
Select @StartDate = substring(@StartDate, 5, 2) + '/' + substring(@StartDate, 7,2) + '/' + substring(@StartDate, 1, 4)
Select @EndDate = substring(@EndDate, 5, 2) + '/' + substring(@EndDate, 7,2) + '/' + substring(@EndDate, 1, 4)
if right(@EndDate, 4) = '9999'
Select @EndDate = 'None'
insert into #temp
([Job Name], [Job Enabled], [Plan Name], [Job Type], [Databases], [Interval], [Schedule Name], [Schedule Enabled], [schedule], [Start Date], [End Date])
values
(@job_name, @JobEnabled, @plan_name, @job_type, @dbString, @Interval, @ScheduleName, @ScheduleEnabled, @frequency, @StartDate, @EndDate)
fetch next from maintCursor into @job_Name, @JobEnabled, @plan_id, @plan_name,
@ScheduleName, @ScheduleEnabled, @freq_interval, @job_type, @frequency,
@freq_type, @freq_recurrence_factor, @freq_relative_interval, @freq_subday_type,
@freq_subday_interval, @active_start_time, @active_end_time, @StartDate, @EndDate
end
close maintCursor
deallocate maintCursor
select [Plan Name], [Job Name], [Job Enabled], [Job Type], left(Databases, len(databases)-1) as Databases,
[Schedule Name], [Schedule Enabled], Schedule as [Schedule Type], Interval, [Start Date], [End Date]
from #temp
order by [plan name], [Job Type], [Schedule Name]
drop table #temp
October 31, 2008 at 2:13 pm
What errors is that giving on SQL 2005? Or is it returning incorrect results?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
October 31, 2008 at 2:34 pm
Here's the table list you need:
SYSJOBS SJ JOin
SYSDBMAINTPLAN_JOBS MPJ On
SJ.job_Id = MPJ.job_Id JOin
sysdbmaintplans MP On
MPJ.plan_Id = MP.plan_id Join
sysjobsteps JST On
SJ.job_Id = JST.job_Id Join
sysjobschedules JS On
SJ.job_id = JS.job_id join
sysschedules S ON
JS.schedule_id = S.schedule_Id
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply