Generate Job Schedule Descriptions

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » Article Discussions » Article Discussions by Author » Discuss Content Posted by Richard Sutherland » Generate Job Schedule Descriptions

15 posts, Page 2 of 2

««1 2

Generate Job Schedule Descriptions

Rate Topic

Display Mode

Topic Options

Author

Message

rg-722805

Posted Wednesday, April 15, 2009 2:19 PM

SSC Veteran

Group: General Forum Members
Last Login: Thursday, February 21, 2013 2:32 PM
Points: 237, Visits: 249

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

Post #697910

gregory.anderson

Posted Thursday, January 07, 2010 4:42 PM

SSC Veteran

Group: General Forum Members
Last Login: Wednesday, June 12, 2013 7:44 AM
Points: 263, Visits: 434

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

Post #843990

DBA-640728

Posted Thursday, March 25, 2010 5:47 PM

SSC-Addicted

Group: General Forum Members
Last Login: Friday, June 14, 2013 9:59 AM
Points: 498, Visits: 1,734

can you send the link to the sql 2005 version?

Post #890318

Richard Sutherland

Posted Monday, March 29, 2010 5:42 AM

Old Hand

Group: General Forum Members
Last Login: Monday, November 28, 2011 9:59 AM
Points: 306, Visits: 37

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

Post #891755

matt32

Posted Tuesday, March 30, 2010 5:04 AM

Valued Member

Group: General Forum Members
Last Login: Friday, January 18, 2013 5:32 AM
Points: 61, Visits: 171

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 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 s

regards Matthias

Post #892600

« Prev Topic | Next Topic »

15 posts, Page 2 of 2

««1 2

Permissions