WordPress.com



/*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:65BC8B400010060000253A000008000000263A0000080000023E3A400008000002FE3A4000080000003F3A40000800008D3F3A4000080000One schedule, two schedules, four schedules, respectively.This first function takes a 16 character string and converts it to the schedule information,and the 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.ParseScheduleToken (@SMS_ScheduleToken varchar (16))returns varchar (max)asbegindeclare @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 enddeclare @SMS_ScheduleToken01 tinyint = case when @SMS_ScheduleToken1 & cast (power (2, 14) as bigint) = 0 then 0 else 1 enddeclare @SMS_ScheduleToken02 tinyint = case when @SMS_ScheduleToken1 & cast (power (2, 13) as bigint) = 0 then 0 else 1 enddeclare @SMS_ScheduleToken03 tinyint = case when @SMS_ScheduleToken1 & cast (power (2, 12) as bigint) = 0 then 0 else 1 enddeclare @SMS_ScheduleToken04 tinyint = case when @SMS_ScheduleToken1 & cast (power (2, 11) as bigint) = 0 then 0 else 1 enddeclare @SMS_ScheduleToken05 tinyint = case when @SMS_ScheduleToken1 & cast (power (2, 10) as bigint) = 0 then 0 else 1 enddeclare @SMS_ScheduleToken06 tinyint = case when @SMS_ScheduleToken1 & cast (power (2, 09) as bigint) = 0 then 0 else 1 enddeclare @SMS_ScheduleToken07 tinyint = case when @SMS_ScheduleToken1 & cast (power (2, 08) as bigint) = 0 then 0 else 1 enddeclare @SMS_ScheduleToken08 tinyint = case when @SMS_ScheduleToken1 & cast (power (2, 07) as bigint) = 0 then 0 else 1 enddeclare @SMS_ScheduleToken09 tinyint = case when @SMS_ScheduleToken1 & cast (power (2, 06) as bigint) = 0 then 0 else 1 enddeclare @SMS_ScheduleToken10 tinyint = case when @SMS_ScheduleToken1 & cast (power (2, 05) as bigint) = 0 then 0 else 1 enddeclare @SMS_ScheduleToken11 tinyint = case when @SMS_ScheduleToken1 & cast (power (2, 04) as bigint) = 0 then 0 else 1 enddeclare @SMS_ScheduleToken12 tinyint = case when @SMS_ScheduleToken1 & cast (power (2, 03) as bigint) = 0 then 0 else 1 enddeclare @SMS_ScheduleToken13 tinyint = case when @SMS_ScheduleToken1 & cast (power (2, 02) as bigint) = 0 then 0 else 1 enddeclare @SMS_ScheduleToken14 tinyint = case when @SMS_ScheduleToken1 & cast (power (2, 01) as bigint) = 0 then 0 else 1 enddeclare @SMS_ScheduleToken15 tinyint = case when @SMS_ScheduleToken1 & cast (power (2, 00) as bigint) = 0 then 0 else 1 enddeclare @SMS_ScheduleToken16 tinyint = case when @SMS_ScheduleToken2 & cast (power (2, 15) as bigint) = 0 then 0 else 1 enddeclare @SMS_ScheduleToken17 tinyint = case when @SMS_ScheduleToken2 & cast (power (2, 14) as bigint) = 0 then 0 else 1 enddeclare @SMS_ScheduleToken18 tinyint = case when @SMS_ScheduleToken2 & cast (power (2, 13) as bigint) = 0 then 0 else 1 enddeclare @SMS_ScheduleToken19 tinyint = case when @SMS_ScheduleToken2 & cast (power (2, 12) as bigint) = 0 then 0 else 1 enddeclare @SMS_ScheduleToken20 tinyint = case when @SMS_ScheduleToken2 & cast (power (2, 11) as bigint) = 0 then 0 else 1 enddeclare @SMS_ScheduleToken21 tinyint = case when @SMS_ScheduleToken2 & cast (power (2, 10) as bigint) = 0 then 0 else 1 enddeclare @SMS_ScheduleToken22 tinyint = case when @SMS_ScheduleToken2 & cast (power (2, 09) as bigint) = 0 then 0 else 1 enddeclare @SMS_ScheduleToken23 tinyint = case when @SMS_ScheduleToken2 & cast (power (2, 08) as bigint) = 0 then 0 else 1 enddeclare @SMS_ScheduleToken24 tinyint = case when @SMS_ScheduleToken2 & cast (power (2, 07) as bigint) = 0 then 0 else 1 enddeclare @SMS_ScheduleToken25 tinyint = case when @SMS_ScheduleToken2 & cast (power (2, 06) as bigint) = 0 then 0 else 1 enddeclare @SMS_ScheduleToken26 tinyint = case when @SMS_ScheduleToken2 & cast (power (2, 05) as bigint) = 0 then 0 else 1 enddeclare @SMS_ScheduleToken27 tinyint = case when @SMS_ScheduleToken2 & cast (power (2, 04) as bigint) = 0 then 0 else 1 enddeclare @SMS_ScheduleToken28 tinyint = case when @SMS_ScheduleToken2 & cast (power (2, 03) as bigint) = 0 then 0 else 1 enddeclare @SMS_ScheduleToken29 tinyint = case when @SMS_ScheduleToken2 & cast (power (2, 02) as bigint) = 0 then 0 else 1 enddeclare @SMS_ScheduleToken30 tinyint = case when @SMS_ScheduleToken2 & cast (power (2, 01) as bigint) = 0 then 0 else 1 enddeclare @SMS_ScheduleToken31 tinyint = case when @SMS_ScheduleToken2 & cast (power (2, 00) as bigint) = 0 then 0 else 1 enddeclare @SMS_ScheduleToken32 tinyint = case when @SMS_ScheduleToken3 & cast (power (2, 15) as bigint) = 0 then 0 else 1 enddeclare @SMS_ScheduleToken33 tinyint = case when @SMS_ScheduleToken3 & cast (power (2, 14) as bigint) = 0 then 0 else 1 enddeclare @SMS_ScheduleToken34 tinyint = case when @SMS_ScheduleToken3 & cast (power (2, 13) as bigint) = 0 then 0 else 1 enddeclare @SMS_ScheduleToken35 tinyint = case when @SMS_ScheduleToken3 & cast (power (2, 12) as bigint) = 0 then 0 else 1 enddeclare @SMS_ScheduleToken36 tinyint = case when @SMS_ScheduleToken3 & cast (power (2, 11) as bigint) = 0 then 0 else 1 enddeclare @SMS_ScheduleToken37 tinyint = case when @SMS_ScheduleToken3 & cast (power (2, 10) as bigint) = 0 then 0 else 1 enddeclare @SMS_ScheduleToken38 tinyint = case when @SMS_ScheduleToken3 & cast (power (2, 09) as bigint) = 0 then 0 else 1 enddeclare @SMS_ScheduleToken39 tinyint = case when @SMS_ScheduleToken3 & cast (power (2, 08) as bigint) = 0 then 0 else 1 enddeclare @SMS_ScheduleToken40 tinyint = case when @SMS_ScheduleToken3 & cast (power (2, 07) as bigint) = 0 then 0 else 1 enddeclare @SMS_ScheduleToken41 tinyint = case when @SMS_ScheduleToken3 & cast (power (2, 06) as bigint) = 0 then 0 else 1 enddeclare @SMS_ScheduleToken42 tinyint = case when @SMS_ScheduleToken3 & cast (power (2, 05) as bigint) = 0 then 0 else 1 enddeclare @SMS_ScheduleToken43 tinyint = case when @SMS_ScheduleToken3 & cast (power (2, 04) as bigint) = 0 then 0 else 1 enddeclare @SMS_ScheduleToken44 tinyint = case when @SMS_ScheduleToken3 & cast (power (2, 03) as bigint) = 0 then 0 else 1 enddeclare @SMS_ScheduleToken45 tinyint = case when @SMS_ScheduleToken3 & cast (power (2, 02) as bigint) = 0 then 0 else 1 enddeclare @SMS_ScheduleToken46 tinyint = case when @SMS_ScheduleToken3 & cast (power (2, 01) as bigint) = 0 then 0 else 1 enddeclare @SMS_ScheduleToken47 tinyint = case when @SMS_ScheduleToken3 & cast (power (2, 00) as bigint) = 0 then 0 else 1 enddeclare @SMS_ScheduleToken48 tinyint = case when @SMS_ScheduleToken4 & cast (power (2, 15) as bigint) = 0 then 0 else 1 enddeclare @SMS_ScheduleToken49 tinyint = case when @SMS_ScheduleToken4 & cast (power (2, 14) as bigint) = 0 then 0 else 1 enddeclare @SMS_ScheduleToken50 tinyint = case when @SMS_ScheduleToken4 & cast (power (2, 13) as bigint) = 0 then 0 else 1 enddeclare @SMS_ScheduleToken51 tinyint = case when @SMS_ScheduleToken4 & cast (power (2, 12) as bigint) = 0 then 0 else 1 enddeclare @SMS_ScheduleToken52 tinyint = case when @SMS_ScheduleToken4 & cast (power (2, 11) as bigint) = 0 then 0 else 1 enddeclare @SMS_ScheduleToken53 tinyint = case when @SMS_ScheduleToken4 & cast (power (2, 10) as bigint) = 0 then 0 else 1 enddeclare @SMS_ScheduleToken54 tinyint = case when @SMS_ScheduleToken4 & cast (power (2, 09) as bigint) = 0 then 0 else 1 enddeclare @SMS_ScheduleToken55 tinyint = case when @SMS_ScheduleToken4 & cast (power (2, 08) as bigint) = 0 then 0 else 1 enddeclare @SMS_ScheduleToken56 tinyint = case when @SMS_ScheduleToken4 & cast (power (2, 07) as bigint) = 0 then 0 else 1 enddeclare @SMS_ScheduleToken57 tinyint = case when @SMS_ScheduleToken4 & cast (power (2, 06) as bigint) = 0 then 0 else 1 enddeclare @SMS_ScheduleToken58 tinyint = case when @SMS_ScheduleToken4 & cast (power (2, 05) as bigint) = 0 then 0 else 1 enddeclare @SMS_ScheduleToken59 tinyint = case when @SMS_ScheduleToken4 & cast (power (2, 04) as bigint) = 0 then 0 else 1 enddeclare @SMS_ScheduleToken60 tinyint = case when @SMS_ScheduleToken4 & cast (power (2, 03) as bigint) = 0 then 0 else 1 enddeclare @SMS_ScheduleToken61 tinyint = case when @SMS_ScheduleToken4 & cast (power (2, 02) as bigint) = 0 then 0 else 1 enddeclare @SMS_ScheduleToken62 tinyint = case when @SMS_ScheduleToken4 & cast (power (2, 01) as bigint) = 0 then 0 else 1 enddeclare @SMS_ScheduleToken63 tinyint = case when @SMS_ScheduleToken4 & cast (power (2, 00) as bigint) = 0 then 0 else 1 enddeclare @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)) + 1970declare @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 = -1declare @hourspan int = -1declare @dayspan int = -1declare @day int = -1declare @fornumberofweeks int = -1declare @fornumberofmonths int = -1declare @weekorder int = -1declare @monthday int = -1if @recurrencetype = 2 -- 010 SMS_ST_RecurIntervalbeginset @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))endif @recurrencetype = 3 -- 011 SMS_ST_RecurWeeklybeginset @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))endif @recurrencetype = 4 -- 100 SMS_ST_RecurMonthlyByWeekdaybeginset @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))endif @recurrencetype = 5 -- 101 SMS_ST_RecurMonthlyByDatebeginset @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))endset @ReturnString =case @recurrencetypewhen 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' endreturn (@ReturnString)endgo-- 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)asbegindeclare @Counter int = 0declare @TokenString varchar (16) = @InputStringdeclare @ReturnString varchar (max) = ''while len (@TokenString) = 16beginset @ReturnString = @ReturnString + dbo.ParseScheduleToken (@TokenString)set @Counter = @Counter + 1set @TokenString = SUBSTRING (@InputString, 16 * @Counter + 1, 16)endreturn (@ReturnString)endgo ................
................

In order to avoid copyright disputes, this page is only a partial summary.

Google Online Preview   Download