I was actually calling the system stored procedure "msdb.dbo.sp_help_jobschedule" in my previous post about modifying column list from a called stored procedure,
While working on that I noticed that [more]the description (schedule_description) being returned by stored procedure (if you set @include_description = 1) returns date and times as integer values (rather than date/time format).
While looking around I found this feedback from microsoft connect : sp_get_schedule_description produces basic output, be nice if it had formatted dates & times (you might be required to login to view it)
Turns out the behavior was changed from SQL2000 to SQL2005 and won't be changed(or corrected should I say) since other applications that depended on the format might break.
So I figured I'd have a function/method for returning a more user friendly description instead.
Found this article as a baseline: Generate Job Schedule Description and created the functions below (GetScheduleDescription and GetTimeString)
— START
IF EXISTS (
SELECT *
FROM sys.objects
WHERE
object_id = OBJECT_ID(N'[dbo].[GetTimeString]')
AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
DROP FUNCTION [dbo].[GetTimeString]
GO
— =============================================
— Author: Ryan Garaygay
— Create date: 03/25/2008
— Description: Return Time String Representation from INT (scheduled job format, HHmmss)
— =============================================
CREATE FUNCTION [dbo].[GetTimeString](@timeInteger INT)
RETURNS NVARCHAR(255)
AS
BEGIN
— convert to 12hr time
DECLARE @originalTimeInteger INT
SET @originalTimeInteger = @timeInteger
IF(@timeInteger >= 130000)
SET @timeInteger = @timeInteger – 120000
— convert to 6 chars (pad if necessary)
DECLARE @TM VARCHAR(255)
SET @TM = REPLACE(STR(@timeInteger, 6), SPACE(1), '0')
DECLARE @RETVAL VARCHAR(255)
SET @RETVAL =
SUBSTRING(@TM,1,2) + ':' +
SUBSTRING(@TM,3,2) + ':' +
SUBSTRING(@TM,5,2)
+ CASE
WHEN @originalTimeInteger < 120000 THEN ' AM'
ELSE ' PM'
END
RETURN @RETVAL
END
GO
IF EXISTS (
SELECT *
FROM sys.objects
WHERE
object_id = OBJECT_ID(N'[dbo].[GetScheduleDescription]')
AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
DROP FUNCTION [dbo].[GetScheduleDescription]
GO
— =============================================
— Author: Ryan Garaygay
— Create date: 03/25/2008
— Description: Return Schedule Description
— a modification of sp_get_schedule_description
— but there is an issue with SQL2005 where the date/times returned are in INT
— instead of recognizable date/time formats
— =============================================
CREATE FUNCTION [dbo].[GetScheduleDescription](
@freq_type INT = NULL,
@freq_interval INT = NULL,
@freq_subday_type INT = NULL,
@freq_subday_interval INT = NULL,
@freq_relative_interval INT = NULL,
@freq_recurrence_factor INT = NULL,
@active_start_date INT = NULL,
@active_end_date INT = NULL,
@active_start_time INT = NULL,
@active_end_time INT = NULL,
@enabled BIT = NULL)
RETURNS NVARCHAR(255)
AS
BEGIN
DECLARE @schedule_description NVARCHAR(255)
SET @schedule_description =
'Occurs ' +
CASE
WHEN @freq_type = 0x1 — OneTime
THEN
'once on '
+ CONVERT(
CHAR(10)
, CAST( CAST( @active_start_date AS VARCHAR ) AS DATETIME )
, 101 — mm/dd/yyyy
)
WHEN @freq_type = 0x4 — Daily
THEN
CASE
WHEN @freq_interval = 1
THEN 'daily '
WHEN @freq_interval > 1
THEN 'every '
+ CAST( @freq_interval AS VARCHAR )
+ ' days '
END
WHEN @freq_type = 0x8 — weekly
THEN
CASE
WHEN @freq_recurrence_factor = 1
THEN 'weekly on '
WHEN @freq_recurrence_factor > 1
THEN 'every '
+ CAST( @freq_recurrence_factor AS VARCHAR )
+ ' weeks on '
END
+ LEFT(
CASE WHEN @freq_interval & 1 = 1 THEN 'Sunday, ' ELSE '' END
+ CASE WHEN @freq_interval & 2 = 2 THEN 'Monday, ' ELSE '' END
+ CASE WHEN @freq_interval & 4 = 4 THEN 'Tuesday, ' ELSE '' END
+ CASE WHEN @freq_interval & 8 = 8 THEN 'Wednesday, ' ELSE '' END
+ CASE WHEN @freq_interval & 16 = 16 THEN 'Thursday, ' ELSE '' END
+ CASE WHEN @freq_interval & 32 = 32 THEN 'Friday, ' ELSE '' END
+ CASE WHEN @freq_interval & 64 = 64 THEN 'Saturday, ' ELSE '' END
, LEN(
CASE WHEN @freq_interval & 1 = 1 THEN 'Sunday, ' ELSE '' END
+ CASE WHEN @freq_interval & 2 = 2 THEN 'Monday, ' ELSE '' END
+ CASE WHEN @freq_interval & 4 = 4 THEN 'Tuesday, ' ELSE '' END
+ CASE WHEN @freq_interval & 8 = 8 THEN 'Wednesday, ' ELSE '' END
+ CASE WHEN @freq_interval & 16 = 16 THEN 'Thursday, ' ELSE '' END
+ CASE WHEN @freq_interval & 32 = 32 THEN 'Friday, ' ELSE '' END
+ CASE WHEN @freq_interval & 64 = 64 THEN 'Saturday, ' ELSE '' END
) – 1 — LEN() ignores trailing spaces
)
WHEN @freq_type = 0x10 — monthly
THEN
CASE
WHEN @freq_recurrence_factor = 1
THEN 'monthly on the '
WHEN @freq_recurrence_factor > 1
THEN 'every '
+ CAST( @freq_recurrence_factor AS VARCHAR )
+ ' months on the '
END
+ CAST( @freq_interval AS VARCHAR )
+ CASE
WHEN @freq_interval IN ( 1, 21, 31 ) THEN 'st'
WHEN @freq_interval IN ( 2, 22 ) THEN 'nd'
WHEN @freq_interval IN ( 3, 23 ) THEN 'rd'
ELSE 'th'
END
WHEN @freq_type = 0x20 — monthly relative
THEN
CASE
WHEN @freq_recurrence_factor = 1
THEN 'monthly on the '
WHEN @freq_recurrence_factor > 1
THEN 'every '
+ CAST( @freq_recurrence_factor AS VARCHAR )
+ ' months on the '
END
+ CASE @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 @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 @freq_type = 0x40
THEN 'Automatically starts when SQLServerAgent start@'
WHEN @freq_type = 0x80
THEN 'Starts whenever the CPUs become idle'
ELSE ''
END
+ CASE
WHEN @freq_subday_type = 0x1 OR @freq_type = 0x1
THEN ' at '
+ dbo.[GetTimeString](@active_start_time)
WHEN @freq_subday_type IN ( 0x2, 0x4, 0x8 )
THEN ' running every '
+ CAST( @freq_subday_interval AS VARCHAR )
+ CASE @freq_subday_type
WHEN 0x2 THEN ' second'
WHEN 0x4 THEN ' minute'
WHEN 0x8 THEN ' hour'
END
+ CASE
WHEN @freq_subday_interval > 1 THEN 's'
ELSE ''
END
+ ' between '
+ dbo.[GetTimeString](@active_start_time)
+ ' and '
+ dbo.[GetTimeString](@active_end_time)
ELSE ''
END
+ CASE
WHEN @freq_type IN ( 4, 8, 16, 32 ) — daily, weekly, monthly and monthly relative
THEN '. Schedule will be used '
+
CASE
WHEN (@active_end_date IS NULL OR @active_end_date = 99991231)
THEN ' starting '
+ CONVERT(
CHAR(10)
, CAST( CAST( @active_start_date AS VARCHAR ) AS DATETIME )
, 101 — mm/dd/yyyy
)
+ '.'
ELSE
' between '
+ CONVERT(
CHAR(10)
, CAST( CAST( @active_start_date AS VARCHAR ) AS DATETIME )
, 101 — mm/dd/yyyy
)
+ ' and '
+ CONVERT(
CHAR(10)
, CAST( CAST( @active_end_date AS VARCHAR ) AS DATETIME )
, 101 — mm/dd/yyyy
)
+ '.'
END
ELSE ''
END
RETURN @schedule_description
END
GO
IF EXISTS (
SELECT *
FROM sys.objects
WHERE
object_id = OBJECT_ID(N'[dbo].[CM1_SEL_ScheduleByJobID]')
AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[CM1_SEL_ScheduleByJobID]
— END
Again, as previously mentioned, used INSERT-EXEC to simply store the values of my "msdb.dbo.sp_help_jobschedule" query to a table variable. Then for the final SELECT statement, I called my new GetScheduleDescription
[ScheduleDescription] = dbo.GetScheduleDescription(
freq_type,
freq_interval,
freq_subday_type,
freq_subday_interval,
freq_relative_interval,
freq_recurrence_factor,
active_start_date,
active_end_date,
active_start_time,
active_end_time,
enabled)
Hope this helps anyone.
REVISED as of 3/27/08 – handling of 12:mm PM and changes to further enhance the description returned.
2 replies on “Generate SQL Job Schedule Description”
Could you help with a script to create a list of jobs and its schedule descriptions without creating a function?
hi Siji, it looks like it is possible – just move the logic out of the function but the downside would be readability. you might be able to do it using case statements within the select code