Results 1 to 5 of 5
  1. #1
    Join Date
    Dec 2007
    Posts
    288

    Unanswered: Listing Maintenance Plan Jobs

    This SQL will list Maintenance Plan job but it only works on a SQL Serve 2000 Instance. How do I modify this so that it will work against a SQL Server 2005 Instance????

    Code:
    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

  2. #2
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10

  3. #3
    Join Date
    Dec 2007
    Posts
    288
    Thank you.. cool bit of code but we use Lotus Notes.. not Exchange and Outlook. Really just looking to get a list of scheduled job out to a results set that I can copy into Excel. What I have works like a charm.. just not in SQL Server 2005

  4. #4
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Have you tried looking up the system table mappings?
    If the actual tablenames and columns have not changed you should be able to change sysmytable to sys.mytable

    Try a find & replace. If it fails, I would google for 2005 code and scrap the above. It is likely easier to find someone else's code than rewrite the above.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  5. #5
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    From the article
    Code:
    SELECT        srv.srvname,
                  sj.name,
                  COALESCE(sj.description, ''),
                  ss.name,
                  ss.schedule_id,
                  sc.name,
                  ss.freq_type,
                  ss.freq_interval,
                  ss.freq_subday_type,
                  ss.freq_subday_interval,
                  ss.freq_relative_interval,
                  ss.freq_recurrence_factor,
                  COALESCE(STR(ss.active_start_date, 8), CONVERT(CHAR(8), GETDATE(), 112)),
                  STUFF(STUFF(REPLACE(STR(ss.active_start_time, 6), ' ', '0'), 3, 0, ':'), 6, 0, ':'),
                  STR(ss.active_end_date, 8),
                  STUFF(STUFF(REPLACE(STR(ss.active_end_time, 6), ' ', '0'), 3, 0, ':'), 6, 0, ':'),
                  sj.enabled,
                  ss.enabled
    FROM          msdb..sysschedules AS ss
    INNER JOIN    msdb..sysjobschedules AS sjs ON sjs.schedule_id = ss.schedule_id
    INNER JOIN    msdb..sysjobs AS sj ON sj.job_id = sjs.job_id
    INNER JOIN    sys.sysservers AS srv ON srv.srvid = sj.originating_server_id
    INNER JOIN    msdb..syscategories AS sc ON sc.category_id = sj.category_id
    WHERE         ss.freq_type IN(1, 4, 8, 16, 32)
    ORDER BY      srv.srvname,
                  sj.name,
                  ss.name
    George
    Home | Blog

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •