MSDB - Decode MSDB Job Frequency Interval

Applicability:

                 SQL Server 2000:        Tested
                 SQL Server 2005:        Tested
                 SQL Server 2008:        Tested
                 SQL Server 2008R2:    Tested
                 SQL Server 2012:        Not Tested        

Credits:

Author:    Solihin Ho
Date:        18 May 2009

Description

Decodes the integer bitmap represneation ised in MSDB for job frequency interval into a string

eg 65 => 'Saturday, Sunday' 

Code

Function:


DROP FUNCTION dbo.udf_DecodeJobFequencyInterval
GO


CREATE  FUNCTION udf_DecodeJobFequencyInterval(@freq_interval INT)  
RETURNS VARCHAR(1000)
/*************************************************************
Purpose:   Takes a an integer representing MSDB job frequency
          and decodes it into a description string (one or many days)

Author:        Solihin Ho
History:   18 May 2009

***************************************************************/
  
AS  
BEGIN  
   DECLARE
@result VARCHAR(1000)  

  
SET @result = ''
      
  
IF (@freq_interval & 1 = 1)  
      
SET @result = 'Sunday, '  
  
IF (@freq_interval & 2 = 2)  
      
SET @result = @result + 'Monday, '  
  
IF (@freq_interval & 4 = 4)  
      
SET @result = @result + 'Tuesday, '  
  
IF (@freq_interval & 8 = 8)  
      
SET @result = @result + 'Wednesday, '  
  
IF (@freq_interval & 16 = 16)  
      
SET @result = @result + 'Thursday, '  
  
IF (@freq_interval & 32 = 32)  
      
SET @result = @result + 'Friday, '  
  
IF (@freq_interval & 64 = 64)  
      
SET @result = @result + 'Saturday, '  

  
RETURN(LEFT(@result,LEN(@result)-1))  
END  

-- select  dbo.udf_DecodeJobFequencyInterval (65)
-- select  dbo.udf_DecodeJobFequencyInterval (39)

ċ
udf_DecodeJobFrequencyInterval.sql
(1k)
Andy Hughes,
Jul 20, 2012, 10:44 AM
Comments