dbo.QBM_PScheduleCheck

1     create   procedure QBM_PScheduleCheck AS begin set nocount on declare @tt QBM_YCursorBuffer     declare @GenProcID varchar(38) = newid() declare
2 @jetztInUTC datetime   declare @GenProcID_R varchar(38) = dbo.QBM_FGISessionContext('') declare @XUser_R nvarchar(64) = dbo.QBM_FGISessionContext('XUser'
3) declare @DebugSwitch int = 0   declare @DebugLevel varchar(1) = 'W'  declare @Debugmessage nvarchar(max) declare @Message nvarchar(2000) declare @Xdate
4 datetime = getutcdate() declare @XUser nvarchar(64) = object_name(@@procid) SET XACT_ABORT OFF BEGIN TRY if @DebugSwitch = 2 begin select @Debugmessage
5 = 'Aufgerufen ' exec QBM_PJournal @Debugmessage, @@procid, 'D', @DebugLevel exec QBM_PWaitForSeconds 0.01 end  if exists (select top 1 1 from DialogDatabase
6 d with (readpast) where d.UpdatePhase > 0 and d.IsMainDatabase = 1 ) and @DebugSwitch <> 1 begin select @Message = '#LDS#Schedules not processed because of DialogDatabase.UpdatePhase.|'
7 goto NoOpMessage end  if exists (select top 1 1 from DialogDatabase with (readpast) where SingleUserProcess <> 0 and IsMainDatabase = 1 ) and @DebugSwitch
8 <> 1 begin select @Message = '#LDS#Schedules not processed because of DialogDatabase.SingleUserProcess.|' goto NoOpMessage end  if exists (select top 
91 1 from DialogScriptAssembly a with (readpast) where a.IsValid = 0 ) and @DebugSwitch <> 1 begin select @Message = '#LDS#Schedules not processed because of DialogScriptAssembly.IsValid = 0.|'
10 goto NoOpMessage end  if not exists (select top 1 1 from DialogScriptAssembly a ) and @DebugSwitch <> 1 begin select @Message = '#LDS#Schedules not processed because of DialogScriptAssembly empty.|'
11 goto NoOpMessage end  if exists (select top 1 1 from DialogDatabase d with (readpast) where d.IsDBSchedulerDisabled = 1 and IsMainDatabase = 1 ) and @DebugSwitch
12 <> 1 begin select @Message = '#LDS#Schedules not processed because of DialogDatabase.IsDBSchedulerDisabled.|' goto NoOpMessage end  if exists (select 
13top 1 1 from DialogDatabase d with (readpast) where d.IsJobServiceDisabled = 1 and IsMainDatabase = 1 ) and @DebugSwitch <> 1 begin select @Message = '#LDS#Schedules not processed because of DialogDatabase.IsJobServiceDisabled.|'
14 goto NoOpMessage end  if dbo.QBM_FGIConfigparmValue('QBM\Schedules') = '' and @DebugSwitch <> 1 begin select @Message = '#LDS#Schedules not processed because of ConfigParm QBM.Schedules is disabled.|'
15 goto NoOpMessage end if @DebugSwitch = 2 begin select @Debugmessage = 'Alle Starttest überstanden ' exec QBM_PJournal @Debugmessage, @@procid, 'D', @DebugLevel
16 exec QBM_PWaitForSeconds 0.01 end  if isnull(@XUser, '') = '' begin select @XUser = left(Object_name(@@procid), 64) exec QBM_PSessionContextSet 'GenProcID'
17, @GenProcID exec QBM_PSessionContextSet 'XUser', @XUser end select @jetztInUTC = GetUTCDate()    begin transaction   insert into @tt (UID1  , Bit1  , 
18DateTime1  , Int1  ) select s.uid_DialogSchedule, 1, isnull(s.nextrun, '1899-12-30'), tz.CurrentUTCOffset from DialogSchedule s join DialogTimeZone tz 
19on s.uid_DialogTimeZone = tz.uid_DialogTimeZone where isnull(startdate, '1899-12-30') <= @jetztInUTC  and isnull(enddate, '2200-01-01') >= @jetztInUTC 
20and enabled = 1 if @DebugSwitch = 1 begin print 'nach initial laden' select UID1 as uid_DialogSchedule , Bit1 as istostart , DateTime1 as nextrun , Int1
21 as CurrentUTCOffset from @tt end update @tt set Bit1  = 0 where UID1  in (select s.uid_DialogSchedule from DialogSchedule s  where nextrun > dateadd(ss
22, Int1   , @jetztInUTC) ) if @DebugSwitch = 1 begin print 'nach nextrun test' select UID1 as uid_DialogSchedule , Bit1 as istostart , DateTime1 as nextrun
23 , Int1 as CurrentUTCOffset from @tt end update @tt set Bit1  = 0 where UID1  in (select s.uid_DialogSchedule from DialogSchedule s join DialogTimeZone
24 tz on s.UID_DialogTimeZone = tz.UID_DialogTimeZone where isnull(s.lastrun, '1899-12-30') = '1899-12-30'  or ( abs(datediff( mi, s.xdateinserted, dateadd
25(ss, tz.CurrentUTCOffset * (-1) , isnull(s.lastrun, '1899-12-30')))) < 3  and abs(datediff( mi, s.xdateinserted, GetUTCDate())) < 3  ) ) update @tt set
26 Bit1  = 0 where DateTime1  = '1899-12-30' if @DebugSwitch = 1 begin print 'vor DBQueue insert' select UID1 as uid_DialogSchedule , Bit1 as istostart ,
27 DateTime1 as nextrun , Int1 as CurrentUTCOffset from @tt end declare @DBQueueElements_01 QBM_YDBQueueRaw insert into @DBQueueElements_01(Object, SubObject
28, GenProcID) select x.uid, null, @GenProcID from ( select UID1  as uid from @tt where Bit1  = 1 ) as x exec QBM_PDBQueueInsert_Bulk 'QBM-K-ScheduleStart'
29, @DBQueueElements_01 if @DebugSwitch = 2 begin if exists (select top 1 1 from @DBQueueElements_01 ) begin select @Debugmessage = 'Übergeben an QBM-K-ScheduleStart: '
30 + STRING_AGG(e.Object, ' ') from @DBQueueElements_01 e exec QBM_PJournal @Debugmessage, @@procid, 'D', @DebugLevel end else begin select @Debugmessage
31 = 'Übergeben an QBM-K-ScheduleStart: ' + 'nichts' exec QBM_PJournal @Debugmessage, @@procid, 'D', @DebugLevel end end  update DialogSchedule set lastrun
32 = dateadd(ss, tz.CurrentUTCOffset, @jetztInUTC)  , XDateUpdated = @Xdate , XUserUpdated = @XUser from dialogSchedule s join DialogTimeZone tz on s.UID_DialogTimeZone
33 = tz.UID_DialogTimeZone join @tt tt on s.UID_DialogSchedule = tt.UID1  where tt.Bit1  = 1   set datefirst 1 update @tt set DateTime1  = dbo.QBM_FGIScheduleNextRun
34 (isnull(tt.DateTime1 , '1899-12-30') , j.lastrun , @jetztInUTC, j.starttime ,  j.FrequencyType , j.Frequency, j.Frequencysubtype, tz.CurrentUTCOffset 
35) from @tt tt join DialogSchedule j on tt.UID1  = j.uid_DialogSchedule join DialogTimeZone tz on j.uid_DialogTimeZone = tz.UID_DialogTimeZone where (tt.Bit1
36  = 1 or isnull(tt.DateTime1 , '1899-12-30') = '1899-12-30')   and isnull(j.lastrun , '1899-12-30') <> '1899-12-30'  exec QBM_PSessionContextSet 'GenProcID'
37, @GenProcID exec QBM_PSessionContextSet 'XUser', @XUser update DialogSchedule set nextrun = tt.DateTime1    , XDateUpdated = @Xdate , XUserUpdated = @XUser
38 from DialogSchedule s join @tt tt on tt.UID1  = s.UID_DialogSchedule where isnull(s.NextRun, '1899-12-30') <> isnull(tt.DateTime1 , '1899-12-30')  update
39 DialogSchedule set lastrun = '1900-01-02'   , XDateUpdated = @Xdate , XUserUpdated = @XUser where isnull(lastrun , '1899-12-30') = '1899-12-30'  commit
40 transaction END TRY BEGIN CATCH exec QBM_PSessionErrorAdd default exec QBM_PSessionContextSet 'GenProcID', @GenProcID_R exec QBM_PSessionContextSet 'XUser'
41, @XUser_R  rollback transaction RAISERROR ('', 18, 1) WITH NOWAIT END CATCH  ende: GanzEnde:  exec QBM_PSessionContextSet 'GenProcID', @GenProcID_R exec
42 QBM_PSessionContextSet 'XUser', @XUser_R return NoOpMessage: exec QBM_PJournal @Message, @@PROCID, 'W', @DebugLevel, @SuppressEntriesCount = 10 goto ende
43 end 
44