SQLServerCentral / Discuss Content Posted by Richard Sutherland / Article Discussions / Article Discussions by Author / Generate Job Schedule Descriptions / Latest Posts

RE: Generate Job Schedule Descriptions

matt32 — Tue, 30 Mar 2010 05:04:23 GMT

Hi .. check out the exec msdb..sp_help_schedule default,default,default,1on SqlServer 2005but there is a bug in sp_get_schedule_descriptionit delivers the wrong description for dayly jobs with a interval of n dayseg: create a schedule to run every 3 days at 12:00:00the sp will give you every day at 12:00:00 :w00t:i wrote this one instead by using the core of the statement above :cool: :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 sregards Matthias

RE: Generate Job Schedule Descriptions

Richard Sutherland — Mon, 29 Mar 2010 05:42:19 GMT

http://www.sqlservercentral.com/scripts/Maintenance+and+Management/62222/

RE: Generate Job Schedule Descriptions

DBA-640728 — Thu, 25 Mar 2010 17:47:40 GMT

can you send the link to the sql 2005 version?

RE: Generate Job Schedule Descriptions

gregory.anderson — Thu, 07 Jan 2010 16:42:14 GMT

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

RE: Generate Job Schedule Descriptions

rg-722805 — Wed, 15 Apr 2009 14:19:49 GMT

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

RE: Generate Job Schedule Descriptions

rg-722805 — Wed, 15 Apr 2009 10:55:08 GMT

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 24Incorrect syntax near the keyword 'WHEN'.

RE: Generate Job Schedule Descriptions

Richard Sutherland — Wed, 08 Oct 2008 10:21:40 GMT

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

RE: Generate Job Schedule Descriptions

borismoyse — Wed, 08 Oct 2008 10:14:29 GMT

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.

RE: Generate Job Schedule Descriptions

Richard Sutherland — Tue, 07 Oct 2008 16:50:01 GMT

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

RE: Generate Job Schedule Descriptions

borismoyse — Tue, 07 Oct 2008 12:11:08 GMT

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

RE: Generate Job Schedule Descriptions

Richard Sutherland — Wed, 19 Mar 2008 05:51:06 GMT

Brian,Thanks very much for the fixes!Richard

RE: Generate Job Schedule Descriptions

Brian Gilles — Mon, 17 Mar 2008 13:13:07 GMT

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 msdbGO/* 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 scheduleFROM dbo.sysjobs j WITH (nolock) LEFT JOIN dbo.sysjobschedules s WITH (nolock) ON s.job_id = j.job_id AND s.enabled = 1ORDER BY j.name

RE: Generate Job Schedule Descriptions

Richard Sutherland — Tue, 08 Jan 2008 11:57:01 GMT

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

RE: Generate Job Schedule Descriptions

Denny Rgeeh — Mon, 07 Jan 2008 10:20:40 GMT

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

Generate Job Schedule Descriptions

Richard Sutherland — Mon, 05 Nov 2007 02:28:38 GMT

Comments posted to this topic are about the item [B]Generate Job Schedule Descriptions[/B]