Decoding the SMS_ScheduleToken in T-SQL

Edit: SMS_ScheduleToken (document with the SQL)

We wanted to have a report that shows the Assignment Schedule(s) for Task Sequences.  I found that information is contained in the vAdvertisement view; it contains the column MandatorySched, which holds the mandatory schedule information.  There could be multiple Assignment Schedules, and the data looks like:

65BC8B4000100600
00253A000008000000263A0000080000
023E3A400008000002FE3A4000080000003F3A40000800008D3F3A4000080000

One schedule, two schedules, four schedules, respectively.

This first function takes a 16 character string and converts it to the schedule information

create function dbo.ParseScheduleToken (@SMS_ScheduleToken varchar (16))

returns varchar (max)

as

begin

declare @ReturnString varchar (max)

declare @SMS_ScheduleToken1 bigint = convert(bigint, convert(varbinary, substring (@SMS_ScheduleToken, 1, 4), 2))

declare @SMS_ScheduleToken2 bigint = convert(bigint, convert(varbinary, substring (@SMS_ScheduleToken, 5, 4), 2))

declare @SMS_ScheduleToken3 bigint = convert(bigint, convert(varbinary, substring (@SMS_ScheduleToken, 9, 4), 2))

declare @SMS_ScheduleToken4 bigint = convert(bigint, convert(varbinary, substring (@SMS_ScheduleToken, 13, 4), 2))

declare @SMS_ScheduleToken00 tinyint = case when @SMS_ScheduleToken1 & cast (power (2, 15) as bigint) = 0 then 0 else 1 end

declare @SMS_ScheduleToken01 tinyint = case when @SMS_ScheduleToken1 & cast (power (2, 14) as bigint) = 0 then 0 else 1 end

declare @SMS_ScheduleToken02 tinyint = case when @SMS_ScheduleToken1 & cast (power (2, 13) as bigint) = 0 then 0 else 1 end

declare @SMS_ScheduleToken03 tinyint = case when @SMS_ScheduleToken1 & cast (power (2, 12) as bigint) = 0 then 0 else 1 end

declare @SMS_ScheduleToken04 tinyint = case when @SMS_ScheduleToken1 & cast (power (2, 11) as bigint) = 0 then 0 else 1 end

declare @SMS_ScheduleToken05 tinyint = case when @SMS_ScheduleToken1 & cast (power (2, 10) as bigint) = 0 then 0 else 1 end

declare @SMS_ScheduleToken06 tinyint = case when @SMS_ScheduleToken1 & cast (power (2, 09) as bigint) = 0 then 0 else 1 end

declare @SMS_ScheduleToken07 tinyint = case when @SMS_ScheduleToken1 & cast (power (2, 08) as bigint) = 0 then 0 else 1 end

declare @SMS_ScheduleToken08 tinyint = case when @SMS_ScheduleToken1 & cast (power (2, 07) as bigint) = 0 then 0 else 1 end

declare @SMS_ScheduleToken09 tinyint = case when @SMS_ScheduleToken1 & cast (power (2, 06) as bigint) = 0 then 0 else 1 end

declare @SMS_ScheduleToken10 tinyint = case when @SMS_ScheduleToken1 & cast (power (2, 05) as bigint) = 0 then 0 else 1 end

declare @SMS_ScheduleToken11 tinyint = case when @SMS_ScheduleToken1 & cast (power (2, 04) as bigint) = 0 then 0 else 1 end

declare @SMS_ScheduleToken12 tinyint = case when @SMS_ScheduleToken1 & cast (power (2, 03) as bigint) = 0 then 0 else 1 end

declare @SMS_ScheduleToken13 tinyint = case when @SMS_ScheduleToken1 & cast (power (2, 02) as bigint) = 0 then 0 else 1 end

declare @SMS_ScheduleToken14 tinyint = case when @SMS_ScheduleToken1 & cast (power (2, 01) as bigint) = 0 then 0 else 1 end

declare @SMS_ScheduleToken15 tinyint = case when @SMS_ScheduleToken1 & cast (power (2, 00) as bigint) = 0 then 0 else 1 end

declare @SMS_ScheduleToken16 tinyint = case when @SMS_ScheduleToken2 & cast (power (2, 15) as bigint) = 0 then 0 else 1 end

declare @SMS_ScheduleToken17 tinyint = case when @SMS_ScheduleToken2 & cast (power (2, 14) as bigint) = 0 then 0 else 1 end

declare @SMS_ScheduleToken18 tinyint = case when @SMS_ScheduleToken2 & cast (power (2, 13) as bigint) = 0 then 0 else 1 end

declare @SMS_ScheduleToken19 tinyint = case when @SMS_ScheduleToken2 & cast (power (2, 12) as bigint) = 0 then 0 else 1 end

declare @SMS_ScheduleToken20 tinyint = case when @SMS_ScheduleToken2 & cast (power (2, 11) as bigint) = 0 then 0 else 1 end

declare @SMS_ScheduleToken21 tinyint = case when @SMS_ScheduleToken2 & cast (power (2, 10) as bigint) = 0 then 0 else 1 end

declare @SMS_ScheduleToken22 tinyint = case when @SMS_ScheduleToken2 & cast (power (2, 09) as bigint) = 0 then 0 else 1 end

declare @SMS_ScheduleToken23 tinyint = case when @SMS_ScheduleToken2 & cast (power (2, 08) as bigint) = 0 then 0 else 1 end

declare @SMS_ScheduleToken24 tinyint = case when @SMS_ScheduleToken2 & cast (power (2, 07) as bigint) = 0 then 0 else 1 end

declare @SMS_ScheduleToken25 tinyint = case when @SMS_ScheduleToken2 & cast (power (2, 06) as bigint) = 0 then 0 else 1 end

declare @SMS_ScheduleToken26 tinyint = case when @SMS_ScheduleToken2 & cast (power (2, 05) as bigint) = 0 then 0 else 1 end

declare @SMS_ScheduleToken27 tinyint = case when @SMS_ScheduleToken2 & cast (power (2, 04) as bigint) = 0 then 0 else 1 end

declare @SMS_ScheduleToken28 tinyint = case when @SMS_ScheduleToken2 & cast (power (2, 03) as bigint) = 0 then 0 else 1 end

declare @SMS_ScheduleToken29 tinyint = case when @SMS_ScheduleToken2 & cast (power (2, 02) as bigint) = 0 then 0 else 1 end

declare @SMS_ScheduleToken30 tinyint = case when @SMS_ScheduleToken2 & cast (power (2, 01) as bigint) = 0 then 0 else 1 end

declare @SMS_ScheduleToken31 tinyint = case when @SMS_ScheduleToken2 & cast (power (2, 00) as bigint) = 0 then 0 else 1 end

declare @SMS_ScheduleToken32 tinyint = case when @SMS_ScheduleToken3 & cast (power (2, 15) as bigint) = 0 then 0 else 1 end

declare @SMS_ScheduleToken33 tinyint = case when @SMS_ScheduleToken3 & cast (power (2, 14) as bigint) = 0 then 0 else 1 end

declare @SMS_ScheduleToken34 tinyint = case when @SMS_ScheduleToken3 & cast (power (2, 13) as bigint) = 0 then 0 else 1 end

declare @SMS_ScheduleToken35 tinyint = case when @SMS_ScheduleToken3 & cast (power (2, 12) as bigint) = 0 then 0 else 1 end

declare @SMS_ScheduleToken36 tinyint = case when @SMS_ScheduleToken3 & cast (power (2, 11) as bigint) = 0 then 0 else 1 end

declare @SMS_ScheduleToken37 tinyint = case when @SMS_ScheduleToken3 & cast (power (2, 10) as bigint) = 0 then 0 else 1 end

declare @SMS_ScheduleToken38 tinyint = case when @SMS_ScheduleToken3 & cast (power (2, 09) as bigint) = 0 then 0 else 1 end

declare @SMS_ScheduleToken39 tinyint = case when @SMS_ScheduleToken3 & cast (power (2, 08) as bigint) = 0 then 0 else 1 end

declare @SMS_ScheduleToken40 tinyint = case when @SMS_ScheduleToken3 & cast (power (2, 07) as bigint) = 0 then 0 else 1 end

declare @SMS_ScheduleToken41 tinyint = case when @SMS_ScheduleToken3 & cast (power (2, 06) as bigint) = 0 then 0 else 1 end

declare @SMS_ScheduleToken42 tinyint = case when @SMS_ScheduleToken3 & cast (power (2, 05) as bigint) = 0 then 0 else 1 end

declare @SMS_ScheduleToken43 tinyint = case when @SMS_ScheduleToken3 & cast (power (2, 04) as bigint) = 0 then 0 else 1 end

declare @SMS_ScheduleToken44 tinyint = case when @SMS_ScheduleToken3 & cast (power (2, 03) as bigint) = 0 then 0 else 1 end

declare @SMS_ScheduleToken45 tinyint = case when @SMS_ScheduleToken3 & cast (power (2, 02) as bigint) = 0 then 0 else 1 end

declare @SMS_ScheduleToken46 tinyint = case when @SMS_ScheduleToken3 & cast (power (2, 01) as bigint) = 0 then 0 else 1 end

declare @SMS_ScheduleToken47 tinyint = case when @SMS_ScheduleToken3 & cast (power (2, 00) as bigint) = 0 then 0 else 1 end

declare @SMS_ScheduleToken48 tinyint = case when @SMS_ScheduleToken4 & cast (power (2, 15) as bigint) = 0 then 0 else 1 end

declare @SMS_ScheduleToken49 tinyint = case when @SMS_ScheduleToken4 & cast (power (2, 14) as bigint) = 0 then 0 else 1 end

declare @SMS_ScheduleToken50 tinyint = case when @SMS_ScheduleToken4 & cast (power (2, 13) as bigint) = 0 then 0 else 1 end

declare @SMS_ScheduleToken51 tinyint = case when @SMS_ScheduleToken4 & cast (power (2, 12) as bigint) = 0 then 0 else 1 end

declare @SMS_ScheduleToken52 tinyint = case when @SMS_ScheduleToken4 & cast (power (2, 11) as bigint) = 0 then 0 else 1 end

declare @SMS_ScheduleToken53 tinyint = case when @SMS_ScheduleToken4 & cast (power (2, 10) as bigint) = 0 then 0 else 1 end

declare @SMS_ScheduleToken54 tinyint = case when @SMS_ScheduleToken4 & cast (power (2, 09) as bigint) = 0 then 0 else 1 end

declare @SMS_ScheduleToken55 tinyint = case when @SMS_ScheduleToken4 & cast (power (2, 08) as bigint) = 0 then 0 else 1 end

declare @SMS_ScheduleToken56 tinyint = case when @SMS_ScheduleToken4 & cast (power (2, 07) as bigint) = 0 then 0 else 1 end

declare @SMS_ScheduleToken57 tinyint = case when @SMS_ScheduleToken4 & cast (power (2, 06) as bigint) = 0 then 0 else 1 end

declare @SMS_ScheduleToken58 tinyint = case when @SMS_ScheduleToken4 & cast (power (2, 05) as bigint) = 0 then 0 else 1 end

declare @SMS_ScheduleToken59 tinyint = case when @SMS_ScheduleToken4 & cast (power (2, 04) as bigint) = 0 then 0 else 1 end

declare @SMS_ScheduleToken60 tinyint = case when @SMS_ScheduleToken4 & cast (power (2, 03) as bigint) = 0 then 0 else 1 end

declare @SMS_ScheduleToken61 tinyint = case when @SMS_ScheduleToken4 & cast (power (2, 02) as bigint) = 0 then 0 else 1 end

declare @SMS_ScheduleToken62 tinyint = case when @SMS_ScheduleToken4 & cast (power (2, 01) as bigint) = 0 then 0 else 1 end

declare @SMS_ScheduleToken63 tinyint = case when @SMS_ScheduleToken4 & cast (power (2, 00) as bigint) = 0 then 0 else 1 end

declare @startminute int = (@SMS_ScheduleToken00 * power (2, 05)) + (@SMS_ScheduleToken01 * power (2, 04)) + (@SMS_ScheduleToken02 * power (2, 03)) + (@SMS_ScheduleToken03 * power (2, 02)) + (@SMS_ScheduleToken04 * power (2, 01)) + (@SMS_ScheduleToken05 * power (2, 00))

declare @starthour int = (@SMS_ScheduleToken06 * power (2, 04)) + (@SMS_ScheduleToken07 * power (2, 03)) + (@SMS_ScheduleToken08 * power (2, 02)) + (@SMS_ScheduleToken09 * power (2, 01)) + (@SMS_ScheduleToken10 * power (2, 00))

declare @startday int = (@SMS_ScheduleToken11 * power (2, 04)) + (@SMS_ScheduleToken12 * power (2, 03)) + (@SMS_ScheduleToken13 * power (2, 02)) + (@SMS_ScheduleToken14 * power (2, 01)) + (@SMS_ScheduleToken15 * power (2, 00))

declare @startmonth int = (@SMS_ScheduleToken16 * power (2, 03)) + (@SMS_ScheduleToken17 * power (2, 02)) + (@SMS_ScheduleToken18 * power (2, 01)) + (@SMS_ScheduleToken19 * power (2, 00))

declare @startyear int = (@SMS_ScheduleToken20 * power (2, 05)) + (@SMS_ScheduleToken21 * power (2, 04)) + (@SMS_ScheduleToken22 * power (2, 03)) + (@SMS_ScheduleToken23 * power (2, 02)) + (@SMS_ScheduleToken24 * power (2, 01)) + (@SMS_ScheduleToken25 * power (2, 00)) + 1970

declare @minuteduration int = (@SMS_ScheduleToken26 * power (2, 05)) + (@SMS_ScheduleToken27 * power (2, 04)) + (@SMS_ScheduleToken28 * power (2, 03)) + (@SMS_ScheduleToken29 * power (2, 02)) + (@SMS_ScheduleToken30 * power (2, 01)) + (@SMS_ScheduleToken31 * power (2, 00))

declare @hourduration int = (@SMS_ScheduleToken32 * power (2, 04)) + (@SMS_ScheduleToken33 * power (2, 03)) + (@SMS_ScheduleToken34 * power (2, 02)) + (@SMS_ScheduleToken35 * power (2, 01)) + (@SMS_ScheduleToken36 * power (2, 00))

declare @dayduration int = (@SMS_ScheduleToken37 * power (2, 04)) + (@SMS_ScheduleToken38 * power (2, 03)) + (@SMS_ScheduleToken39 * power (2, 02)) + (@SMS_ScheduleToken40 * power (2, 01)) + (@SMS_ScheduleToken41 * power (2, 00))

declare @recurrencetype int = (@SMS_ScheduleToken42 * power (2, 02)) + (@SMS_ScheduleToken43 * power (2, 01)) + (@SMS_ScheduleToken44 * power (2, 00))

declare @isGMT int = (@SMS_ScheduleToken63 * power (2, 00))

declare @minutespan int = 1

declare @hourspan int = 1

declare @dayspan int = 1

declare @day int = 1

declare @fornumberofweeks int = 1

declare @fornumberofmonths int = 1

declare @weekorder int = 1

declare @monthday int = 1

if @recurrencetype = 2 — 010 SMS_ST_RecurInterval

begin

set @minutespan = (@SMS_ScheduleToken45 * power (2, 05)) + (@SMS_ScheduleToken46 * power (2, 04)) + (@SMS_ScheduleToken47 * power (2, 03)) + (@SMS_ScheduleToken48 * power (2, 02)) + (@SMS_ScheduleToken49 * power (2, 01)) + (@SMS_ScheduleToken50 * power (2, 00))

set @hourspan = (@SMS_ScheduleToken51 * power (2, 04)) + (@SMS_ScheduleToken52 * power (2, 03)) + (@SMS_ScheduleToken53 * power (2, 02)) + (@SMS_ScheduleToken54 * power (2, 01)) + (@SMS_ScheduleToken55 * power (2, 00))

set @dayspan = (@SMS_ScheduleToken56 * power (2, 04)) + (@SMS_ScheduleToken57 * power (2, 03)) + (@SMS_ScheduleToken58 * power (2, 02)) + (@SMS_ScheduleToken59 * power (2, 01)) + (@SMS_ScheduleToken60 * power (2, 00))

end

if @recurrencetype = 3 — 011 SMS_ST_RecurWeekly

begin

set @day = (@SMS_ScheduleToken45 * power (2, 02)) + (@SMS_ScheduleToken46 * power (2, 01)) + (@SMS_ScheduleToken47 * power (2, 00))

set @fornumberofweeks = (@SMS_ScheduleToken48 * power (2, 02)) + (@SMS_ScheduleToken49 * power (2, 01)) + (@SMS_ScheduleToken50 * power (2, 00))

end

if @recurrencetype = 4 — 100 SMS_ST_RecurMonthlyByWeekday

begin

set @day = (@SMS_ScheduleToken45 * power (2, 02)) + (@SMS_ScheduleToken46 * power (2, 01)) + (@SMS_ScheduleToken47 * power (2, 00))

set @fornumberofmonths = (@SMS_ScheduleToken48 * power (2, 03)) + (@SMS_ScheduleToken49 * power (2, 02)) + (@SMS_ScheduleToken50 * power (2, 01)) + (@SMS_ScheduleToken60 * power (2, 00))

set @weekorder = (@SMS_ScheduleToken51 * power (2, 02)) + (@SMS_ScheduleToken52 * power (2, 01)) + (@SMS_ScheduleToken52 * power (2, 00))

end

if @recurrencetype = 5 — 101 SMS_ST_RecurMonthlyByDate

begin

set @monthday = (@SMS_ScheduleToken45 * power (2, 04)) + (@SMS_ScheduleToken46 * power (2, 03)) + (@SMS_ScheduleToken47 * power (2, 02)) + (@SMS_ScheduleToken48 * power (2, 01)) + (@SMS_ScheduleToken49 * power (2, 00))

set @fornumberofmonths = (@SMS_ScheduleToken50 * power (2, 03)) + (@SMS_ScheduleToken51 * power (2, 02)) + (@SMS_ScheduleToken52 * power (2, 01)) + (@SMS_ScheduleToken63 * power (2, 00))

end

set @ReturnString =

case @recurrencetype

when 1 then ‘SMS_ST_NonRecurring’

when 2 then ‘SMS_ST_RecurInterval’

when 3 then ‘SMS_ST_RecurWeekly’

when 4 then ‘SMS_ST_RecurMonthlyByWeekday’

when 5 then ‘SMS_ST_RecurMonthlyByDate’

end +

Start Time : ‘ + convert (varchar, format (@startmonth, ’00’) + ‘/’ + format (@startday, ’00’) + ‘/’ + format (@startyear, ‘0000’) + ‘ ‘ + format (@starthour, ’00’) + ‘:’ + format (@startminute, ’00’))+

+ case when @minutespan = 1 then else ‘Minute span : ‘ + format (@minutespan, ’00’) +

end

+ case when @hourspan = 1 then else ‘Hour span : ‘ + format (@hourspan, ’00’) +

end

+ case when @dayspan = 1 then else ‘Day span : ‘ + format (@dayspan, ’00’) +

end

+ case when @day = 1 then

when @day = 1 then ‘Day : Sunday

when @day = 2 then ‘Day : Monday

when @day = 3 then ‘Day : Tueday

when @day = 4 then ‘Day : Wednesday

when @day = 5 then ‘Day : Thursday

when @day = 6 then ‘Day : Friday

when @day = 7 then ‘Day : Saturday

end

+ case when @fornumberofweeks = 1 then else ‘For number of weeks : ‘ + convert (char (1), @fornumberofweeks) +

end

+ case when @fornumberofmonths = 1 then else ‘For number of months: ‘ + convert (char (2), @fornumberofmonths) +

end

+ case when @weekorder = 1 then when @weekorder = 0 then ‘Week order : Last

else ‘Week order : ‘ + format (@weekorder, ’00’) +

end

+ case when @monthday = 1 then when @monthday = 0 then ‘Month day : Last

else ‘Month day : ‘ + format (@monthday, ’00’) +

end

+ case when @isGMT = 0 then ‘IsGMT : False

else ‘IsGMT : True

end

return (@ReturnString)

end

To use this function, I have a second function that takes the Schedule string and breaks it off in 16 character chunks, and then returns all the schedules in one varchar (max).

create function dbo.ParseScheduleTokenString (@InputString varchar (max))

returns varchar (max)

as

begin

declare @Counter int = 0

declare @TokenString varchar (16) = @InputString

declare @ReturnString varchar (max) =

while len (@TokenString) = 16

begin

set @ReturnString = @ReturnString + dbo.ParseScheduleToken (@TokenString)

set @Counter = @Counter + 1

set @TokenString = SUBSTRING (@InputString, 16 * @Counter + 1, 16)

end

return (@ReturnString)

end

Use this function in SQL to give results like:

select dbo.ParseScheduleTokenString (‘023E3A400008000002FE3A4000080000003F3A40000800008D3F3A4000080000’)

SMS_ST_NonRecurring

Start Time : 03/30/2011 17:00

IsGMT : False

SMS_ST_NonRecurring

Start Time : 03/30/2011 23:00

IsGMT : False

SMS_ST_NonRecurring

Start Time : 03/31/2011 01:00

IsGMT : False

SMS_ST_NonRecurring

Start Time : 03/31/2011 09:35

IsGMT : False

 

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s