A DBA I was talking to was trying to create a simple process that finds each SQL Agent Job on a given instance, extracts the basic information regarding it, and compiles a list that can be presented to non-DBAs for information about what sort of “undiscovered” processes are running in a given environment. He had gotten a lot of the information compiled, but was having a problem dissecting the Job Schedule “Frequency Interval” field. Now, to anyone who is familiar with binary, this is a fairly straight-forward bit-flag usage of a higher-order data-type. But, not all DBAs have this in their background, and I think many could benefit from seeing it spelled out.
In the SQL Agent, you have the ability to have week-based job schedules, in which, you can specify particular days of the week. For example, you may have a weekly job that performs your FULL backups, that only runs on Saturday, and then have your DIFFs run on Monday, Tuesday, Wednesday, Thursday, Friday, and Sunday. Well, SQL Agent stores this sort of information in a single field as an integer. This may seem awkward, but the logic behind it is very straight-forward. Each of the lowest-order 7 bits represents a yes/no flag for a particular day of the week:
So, now that we have examined what this integer really means, the question naturally follows: “How do we represent this integer as a series of days?” I have written two different pieces of code to accomplish this. The first simply takes an integer at the beginning of the script and returns a simple string containing the days represented by that integer (this could easily be turned into a UDF or stored procedure). The second piece of code goes about things a little differently, but works well for the particular use case that we were originally discussing.
The first T-SQL:
SET NOCOUNT ON; DECLARE @numberToTest TINYINT = 119, @weekString CHAR(21) = 'SunMonTueWedThuFriSat', @resultString VARCHAR(35) = '' IF OBJECT_ID('TempDB..#daysOfWeek') IS NOT NULL DROP TABLE #daysOfWeek CREATE TABLE #daysOfWeek ( [dayNumber] TINYINT, [dayCode] TINYINT ) INSERT INTO [#daysOfWeek] ( [dayNumber], [dayCode] ) VALUES (1,1), (2,2), (3,4), (4,8), (5,16), (6,32), (7,64) SELECT @resultString = @resultString + CASE WHEN (@numberToTest & [dayCode]) = [dayCode] THEN SUBSTRING(@weekString,(([dayNumber] - 1) * 3 + 1),3) + ', ' ELSE '' END FROM [#daysOfWeek] -- Strip off the last comma IF (LEN(@resultString) > 1) SET @resultString = LEFT(@resultString,LEN(@resultString) - 1) SELECT @resultString |
And, the second T-SQL:
SET NOCOUNT ON; IF OBJECT_ID('TempDB..#daysOfWeek') IS NOT NULL DROP TABLE #daysOfWeek CREATE TABLE #daysOfWeek ( [dayNumber] TINYINT, [dayCode] TINYINT, [dayName] VARCHAR(11) ) INSERT INTO [#daysOfWeek] ( [dayNumber], [dayCode], [dayName] ) VALUES (1,1, 'Sunday '), (2,2, 'Monday '), (3,4, 'Tuesday '), (4,8, 'Wednesday '), (5,16, 'Thursday '), (6,32, 'Friday '), (7,64, 'Saturday ') ;WITH BaseData AS ( SELECT [SYSJOB].[job_id], [SYSJOB].[name], [SYSSCH].[freq_interval], [DOW].[dayName] FROM [msdb].[dbo].[sysjobs] AS SYSJOB INNER JOIN [msdb].[dbo].[sysjobschedules] AS SYSJOBSCH ON [SYSJOB].[job_id] = [SYSJOBSCH].[job_id] INNER JOIN [msdb].[dbo].[sysschedules] AS SYSSCH ON [SYSJOBSCH].[schedule_id] = [SYSSCH].[schedule_id] LEFT OUTER JOIN [#daysOfWeek] AS DOW ON ([SYSSCH].[freq_interval] & [DOW].[dayCode]) = [DOW].[dayCode] ) SELECT [job_id], [name], REPLACE(REPLACE([DayNames],'',''),'','') AS DayNames FROM ( SELECT [job_id], [name], ( SELECT [dayName] FROM [BaseData] WHERE [job_id] = [Grouped].[job_id] FOR XML PATH ('') ) AS DayNames FROM [BaseData] AS Grouped GROUP BY [job_id], [name] ) AS ConcatenatedData |
0 Comments.