Generate Job Schedule Descriptions

  • Richard Sutherland

    Mr or Mrs. 500

    Points: 520

    Comments posted to this topic are about the item Generate Job Schedule Descriptions

  • Denny Rgeeh

    SSC Rookie

    Points: 30

    Did not work for me on sql2005. Many errors about columns that do not exist...

  • Richard Sutherland

    Mr or Mrs. 500

    Points: 520

    True. This is a SQL Server 2000 only script. Sorry it wasn't clear.

  • Brian Gilles

    Grasshopper

    Points: 19

    Very useful code! I get several regular requests for job schedules and this saves me having to create manual static printouts of the job schedules.

    Below is a repost of the code, with a few fixes:

    USE msdb

    GO

    /*

    Microsoft doesn't provide a way to SELECT the job schedule descriptions. They do

    provide a stored procedure [msdb.dbo.sp_get_schedule_description] which will return

    a job schedule description for one job.

    The below select statement was derived from information in that procedure, and lists

    all jobs on a server with their name, description and schedule description.

    */

    SELECT

    j.name

    , j.description

    , CASE

    WHEN j.enabled = 0 THEN 'Disabled'

    WHEN s.job_id IS NULL THEN 'Unscheduled'

    WHEN s.freq_type = 0x1 -- OneTime

    THEN

    'Once on '

    + CONVERT(

    CHAR(10)

    , CAST( CAST( s.active_start_date AS VARCHAR ) AS DATETIME )

    , 102 -- yyyy.mm.dd )

    WHEN s.freq_type = 0x4 -- Daily

    THEN 'Daily'

    WHEN s.freq_type = 0x8 -- weekly

    THEN

    CASE

    WHEN s.freq_recurrence_factor = 1

    THEN 'Weekly on '

    WHEN s.freq_recurrence_factor > 1

    THEN 'Every '

    + CAST( s.freq_recurrence_factor AS VARCHAR )

    + ' weeks on '

    END

    + LEFT(

    CASE WHEN s.freq_interval & 1 = 1 THEN 'Sunday, ' ELSE '' END

    + CASE WHEN s.freq_interval & 2 = 2 THEN 'Monday, ' ELSE '' END

    + CASE WHEN s.freq_interval & 4 = 4 THEN 'Tuesday, ' ELSE '' END

    + CASE WHEN s.freq_interval & 8 = 8 THEN 'Wednesday, ' ELSE '' END

    + CASE WHEN s.freq_interval & 16 = 16 THEN 'Thursday, ' ELSE '' END

    + CASE WHEN s.freq_interval & 32 = 32 THEN 'Friday, ' ELSE '' END

    + CASE WHEN s.freq_interval & 64 = 64 THEN 'Saturday, ' ELSE '' END

    , LEN(

    CASE WHEN s.freq_interval & 1 = 1 THEN 'Sunday, ' ELSE '' END

    + CASE WHEN s.freq_interval & 2 = 2 THEN 'Monday, ' ELSE '' END

    + CASE WHEN s.freq_interval & 4 = 4 THEN 'Tuesday, ' ELSE '' END

    + CASE WHEN s.freq_interval & 8 = 8 THEN 'Wednesday, ' ELSE '' END

    + CASE WHEN s.freq_interval & 16 = 16 THEN 'Thursday, ' ELSE '' END

    + CASE WHEN s.freq_interval & 32 = 32 THEN 'Friday, ' ELSE '' END

    + CASE WHEN s.freq_interval & 64 = 64 THEN 'Saturday, ' ELSE '' END ) - 1 -- LEN() ignores trailing spaces

    )

    WHEN s.freq_type = 0x10 -- monthly

    THEN

    CASE

    WHEN s.freq_recurrence_factor = 1

    THEN 'Monthly on the '

    WHEN s.freq_recurrence_factor > 1

    THEN 'Every '

    + CAST( s.freq_recurrence_factor AS VARCHAR )

    + ' months on the '

    END

    + CAST( s.freq_interval AS VARCHAR )

    + CASE

    WHEN s.freq_interval IN ( 1, 21, 31 ) THEN 'st'

    WHEN s.freq_interval IN ( 2, 22 ) THEN 'nd'

    WHEN s.freq_interval IN ( 3, 23 ) THEN 'rd'

    ELSE 'th'

    END

    WHEN s.freq_type = 0x20 -- monthly relative

    THEN

    CASE

    WHEN s.freq_recurrence_factor = 1

    THEN 'Monthly on the '

    WHEN s.freq_recurrence_factor > 1

    THEN 'Every '

    + CAST( s.freq_recurrence_factor AS VARCHAR )

    + ' months on the '

    END

    + CASE s.freq_relative_interval

    WHEN 0x01 THEN 'first '

    WHEN 0x02 THEN 'second '

    WHEN 0x04 THEN 'third '

    WHEN 0x08 THEN 'fourth '

    WHEN 0x10 THEN 'last '

    END

    + CASE s.freq_interval

    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 'week day'

    WHEN 10 THEN 'weekend day'

    END

    WHEN s.freq_type = 0x40

    THEN 'Automatically starts when SQLServerAgent starts.'

    WHEN s.freq_type = 0x80

    THEN 'Starts whenever the CPUs become idle'

    ELSE ''

    END

    + CASE

    WHEN j.enabled = 0 THEN ''

    WHEN s.job_id IS NULL THEN ''

    WHEN s.freq_subday_type = 0x1 OR s.freq_type = 0x1

    THEN ' at '

    + CASE

    WHEN s.active_start_time = 0 THEN '12:00'

    WHEN s.active_start_time = 120000 THEN '12:00'

    [highlight=#ffff11]-- Fix for active start time < 1am

    WHEN s.active_start_time < 10000

    THEN STUFF(

    LEFT( CAST ( s.active_start_time + 120000 AS VARCHAR ), 4 )

    , 3

    , 0

    , ':'

    )

    --[/highlight] WHEN s.active_start_time < 100000

    THEN STUFF(

    LEFT( CAST ( s.active_start_time AS VARCHAR ), 3 )

    , 2

    , 0

    , ':'

    )

    WHEN s.active_start_time < 120000

    THEN STUFF(

    LEFT( CAST ( s.active_start_time AS VARCHAR ), 4 )

    , 3

    , 0

    , ':'

    )

    WHEN s.active_start_time < 220000

    THEN STUFF(

    LEFT( CAST ( s.active_start_time - 120000 AS VARCHAR ), 3 )

    , 2

    , 0

    , ':'

    )

    ELSE STUFF(

    LEFT( CAST ( s.active_start_time - 120000 AS VARCHAR ), 4 )

    , 3

    , 0

    , ':'

    )

    END

    + CASE

    WHEN s.active_start_time < 120000 THEN ' AM'

    ELSE ' PM'

    END

    WHEN s.freq_subday_type IN ( 0x2, 0x4, 0x8 )

    THEN ' every '

    + CAST( s.freq_subday_interval AS VARCHAR )

    + CASE freq_subday_type

    WHEN 0x2 THEN ' second'

    WHEN 0x4 THEN ' minute'

    WHEN 0x8 THEN ' hour'

    END

    + CASE

    WHEN s.freq_subday_interval > 1 THEN 's'

    [highlight=#ffff11]-- Fix for single h/m/s

    ELSE ''

    --[/highlight] END

    ELSE ''

    END

    + CASE

    WHEN j.enabled = 0 THEN ''

    WHEN s.job_id IS NULL THEN ''

    WHEN s.freq_subday_type IN ( 0x2, 0x4, 0x8 )

    THEN ' between '

    + CASE

    WHEN s.active_start_time = 0 THEN '12:00'

    WHEN s.active_start_time = 120000 THEN '12:00'

    [highlight=#ffff11]-- Fix for active start time < 1am

    WHEN s.active_start_time < 10000

    THEN STUFF(

    LEFT( CAST ( s.active_start_time + 120000 AS VARCHAR ), 4 )

    , 3

    , 0

    , ':'

    )

    --[/highlight] WHEN s.active_start_time < 100000

    THEN STUFF(

    LEFT( CAST ( s.active_start_time AS VARCHAR ), 3 )

    , 2

    , 0

    , ':'

    )

    WHEN s.active_start_time < 120000

    THEN STUFF(

    LEFT( CAST ( s.active_start_time AS VARCHAR ), 4 )

    , 3

    , 0

    , ':'

    )

    WHEN s.active_start_time < 220000

    THEN STUFF(

    LEFT( CAST ( s.active_start_time - 120000 AS VARCHAR ), 3 )

    , 2

    , 0

    , ':'

    )

    ELSE STUFF(

    LEFT( CAST ( s.active_start_time - 120000 AS VARCHAR ), 4 )

    , 3

    , 0

    , ':'

    )

    END

    + CASE

    WHEN s.active_start_time < 120000 THEN ' AM'

    ELSE ' PM'

    END

    + ' and '

    + CASE

    WHEN s.active_end_time = 0 THEN '12:00'

    WHEN s.active_end_time = 120000 THEN '12:00'

    [highlight=#ffff11]-- Fix for active end time < 1am

    WHEN s.active_end_time < 10000

    THEN STUFF(

    LEFT( CAST ( s.active_end_time + 120000 AS VARCHAR ), 4 )

    , 3

    , 0

    , ':'

    )

    --[/highlight] WHEN s.active_end_time < 100000

    THEN STUFF(

    LEFT( CAST ( s.active_end_time AS VARCHAR ), 3 )

    , 2

    , 0

    , ':'

    )

    WHEN s.active_end_time < 120000

    THEN STUFF(

    LEFT( CAST ( s.active_end_time AS VARCHAR ), 4 )

    , 3

    , 0

    , ':'

    )

    WHEN s.active_end_time < 220000

    THEN STUFF(

    LEFT( CAST ( s.active_end_time - 120000 AS VARCHAR ), 3 )

    , 2

    , 0

    , ':'

    )

    ELSE STUFF(

    LEFT( CAST ( s.active_end_time - 120000 AS VARCHAR ), 4 )

    , 3

    , 0

    , ':'

    )

    END

    + CASE

    WHEN s.active_end_time < 120000 THEN ' AM'

    ELSE ' PM'

    END

    ELSE ''

    END AS schedule

    FROM

    dbo.sysjobs j WITH (nolock)

    LEFT JOIN dbo.sysjobschedules s WITH (nolock) ON s.job_id = j.job_id

    AND s.enabled = 1

    ORDER BY

    j.name

  • Richard Sutherland

    Mr or Mrs. 500

    Points: 520

    Brian,

    Thanks very much for the fixes!

    Richard

  • borismoyse

    SSC Enthusiast

    Points: 109

    Works great in 2000. Very handy. Has anyone come up with a version that works in 2005?

    Thanks

    Boris

  • Richard Sutherland

    Mr or Mrs. 500

    Points: 520

    Not that I know of. Want to take a crack at it?

  • borismoyse

    SSC Enthusiast

    Points: 109

    My Boss just came up with a sp for 2005 which has a slightly different output but provides essentially the same results. She's MUCH better at coding then I am.

    I'll forward her a link to this forum and ask her to post the procedure. It works great by BTW.

  • Richard Sutherland

    Mr or Mrs. 500

    Points: 520

    I just finished with a 2005 version, and have submitted it to SQL Server Central.

  • rg-722805

    Say Hey Kid

    Points: 703

    thank you for the helpful script; the original version ran fine when I tried on SQL2000, but above fixed script results in error, anyone else have this issue? Thanks...

    Msg 156, Level 15, State 1, Line 24

    Incorrect syntax near the keyword 'WHEN'.

  • rg-722805

    Say Hey Kid

    Points: 703

    was able to reformat above code a bit and run successfully, thanks...

  • andegre

    SSCertifiable

    Points: 5875

    can someone explain the "freq_interval & 64 = 64"...specifically the & character. What does that do?

  • DBA-640728

    SSChampion

    Points: 12896

    can you send the link to the sql 2005 version?

  • Richard Sutherland

    Mr or Mrs. 500

    Points: 520

  • matt32

    SSC Veteran

    Points: 264

    Hi .. check out the

    exec msdb..sp_help_schedule default,default,default,1

    on SqlServer 2005

    but there is a bug in sp_get_schedule_description

    it delivers the wrong description for dayly jobs with a interval of n days

    eg: create a schedule to run every 3 days at 12:00:00

    the sp will give you every day at 12:00:00 :w00t:

    i wrote this one instead by using the core of the statement above 😎 :

    SELECT s.schedule_id,

    s.schedule_uid,

    s.[name],

    s.enabled,

    s.freq_type,

    s.freq_interval,

    s.freq_subday_type,

    s.freq_subday_interval,

    s.freq_relative_interval,

    s.freq_recurrence_factor,

    s.active_start_date,

    s.active_end_date,

    s.active_start_time,

    s.active_end_time,

    s.date_created,

    CASE

    WHEN s.freq_type = 0x1

    THEN

    'Once on '

    + CONVERT (

    CHAR (10),

    CAST (CAST (s.active_start_date AS VARCHAR) AS DATETIME),

    102)

    WHEN s.freq_type = 0x4 AND s.freq_interval > 0

    THEN

    CASE

    WHEN s.freq_interval > 1

    THEN

    'Every ' + CAST (s.freq_interval AS VARCHAR) + ' days'

    ELSE

    'Every day'

    END

    WHEN s.freq_type = 0x8

    THEN

    CASE

    WHEN s.freq_recurrence_factor = 1

    THEN

    'Weekly on '

    WHEN s.freq_recurrence_factor > 1

    THEN

    'Every '

    + CAST (s.freq_recurrence_factor AS VARCHAR)

    + ' weeks on '

    END

    + LEFT (

    CASE

    WHEN s.freq_interval & 1 = 1 THEN 'Sunday, '

    ELSE ''

    END

    + CASE

    WHEN s.freq_interval & 2 = 2 THEN 'Monday, '

    ELSE ''

    END

    + CASE

    WHEN s.freq_interval & 4 = 4 THEN 'Tuesday, '

    ELSE ''

    END

    + CASE

    WHEN s.freq_interval & 8 = 8 THEN 'Wednesday, '

    ELSE ''

    END

    + CASE

    WHEN s.freq_interval & 16 = 16 THEN 'Thursday, '

    ELSE ''

    END

    + CASE

    WHEN s.freq_interval & 32 = 32 THEN 'Friday, '

    ELSE ''

    END

    + CASE

    WHEN s.freq_interval & 64 = 64 THEN 'Saturday, '

    ELSE ''

    END,

    LEN (

    CASE

    WHEN s.freq_interval & 1 = 1 THEN 'Sunday, '

    ELSE ''

    END

    + CASE

    WHEN s.freq_interval & 2 = 2 THEN 'Monday, '

    ELSE ''

    END

    + CASE

    WHEN s.freq_interval & 4 = 4 THEN 'Tuesday, '

    ELSE ''

    END

    + CASE

    WHEN s.freq_interval & 8 = 8 THEN 'Wednesday, '

    ELSE ''

    END

    + CASE

    WHEN s.freq_interval & 16 = 16 THEN 'Thursday, '

    ELSE ''

    END

    + CASE

    WHEN s.freq_interval & 32 = 32 THEN 'Friday, '

    ELSE ''

    END

    + CASE

    WHEN s.freq_interval & 64 = 64 THEN 'Saturday, '

    ELSE ''

    END)

    - 1)

    WHEN s.freq_type = 0x10

    THEN

    CASE

    WHEN s.freq_recurrence_factor = 1

    THEN

    'Monthly on the '

    WHEN s.freq_recurrence_factor > 1

    THEN

    'Every '

    + CAST (s.freq_recurrence_factor AS VARCHAR)

    + ' months on the '

    END

    + CAST (s.freq_interval AS VARCHAR)

    + CASE

    WHEN s.freq_interval IN (1, 21, 31) THEN 'st'

    WHEN s.freq_interval IN (2, 22) THEN 'nd'

    WHEN s.freq_interval IN (3, 23) THEN 'rd'

    ELSE 'th'

    END

    WHEN s.freq_type = 0x20

    THEN

    CASE

    WHEN s.freq_recurrence_factor = 1

    THEN

    'Monthly on the '

    WHEN s.freq_recurrence_factor > 1

    THEN

    'Every '

    + CAST (s.freq_recurrence_factor AS VARCHAR)

    + ' months on the '

    END

    + CASE s.freq_relative_interval

    WHEN 0x01 THEN 'first '

    WHEN 0x02 THEN 'second '

    WHEN 0x04 THEN 'third '

    WHEN 0x08 THEN 'fourth '

    WHEN 0x10 THEN 'last '

    END

    + CASE s.freq_interval

    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 'week day'

    WHEN 10 THEN 'weekend day'

    END

    WHEN s.freq_type = 0x40

    THEN

    'Automatically starts when SQLServerAgent starts.'

    WHEN s.freq_type = 0x80

    THEN

    'Starts whenever the CPUs become idle'

    ELSE

    ''

    END

    + CASE

    WHEN s.freq_subday_type = 0x1 OR s.freq_type = 0x1

    THEN

    ' at '

    + LEFT (s.active_start_time, 2)

    + ':'

    + substring (s.active_start_time, 3, 2)

    + ':'

    + right (s.active_start_time, 2)

    WHEN s.freq_subday_type IN (0x2, 0x4, 0x8)

    THEN

    ' every '

    + CAST (s.freq_subday_interval AS VARCHAR)

    + CASE freq_subday_type

    WHEN 0x2 THEN ' second'

    WHEN 0x4 THEN ' minute'

    WHEN 0x8 THEN ' hour'

    END

    + CASE WHEN s.freq_subday_interval > 1 THEN 's' END

    ELSE

    ''

    END

    + CASE

    WHEN s.freq_subday_type IN (0x2, 0x4, 0x8)

    THEN

    ' between '

    + LEFT (s.active_start_time, 2)

    + ':'

    + substring (s.active_start_time, 3, 2)

    + ':'

    + right (s.active_start_time, 2)

    + ' and '

    + LEFT (s.active_end_time, 2)

    + ':'

    + substring (s.active_end_time, 3, 2)

    + ':'

    + right (s.active_end_time, 2)

    ELSE

    ''

    END

    AS schedule

    FROM (SELECT schedule_id,

    schedule_uid,

    originating_server_id,

    [name],

    owner_sid,

    enabled,

    freq_type,

    freq_interval,

    freq_subday_type,

    freq_subday_interval,

    freq_relative_interval,

    freq_recurrence_factor,

    active_start_date,

    active_end_date,

    REPLICATE ('0', 6 - len (cast (active_start_time AS VARCHAR)))

    + cast (active_start_time AS VARCHAR)

    AS active_start_time,

    REPLICATE ('0', 6 - len (cast (active_end_time AS VARCHAR)))

    + cast (active_end_time AS VARCHAR)

    AS active_end_time,

    date_created,

    date_modified,

    version_number

    FROM msdb.dbo.sysschedules) AS s

    regards Matthias

Viewing 15 posts - 1 through 15 (of 15 total)

You must be logged in to reply to this topic. Login to reply