Back to OIM Explorer

dbo.QBM_PScheduleCheck

Stored ProcedureSQL_STORED_PROCEDURESandbox DB

Stored Procedure. Bulk DBQueue insert -> QBM-K-ScheduleStart / QBM_ZScheduleStart at line 28; References QBM_PDBQueueInsert_Bulk

Source: sandbox-db sys.sql_modules

Source size: 6.810 characters

Interpretation

  • Database routine. Review parameters, called procedures, DBQueue inserts, and QBM_PJobCreate helper calls before assuming side effects.
  • DBQueue relation detected. Follow the task procedure and referenced-by list for async processing.

Relations

  • Bulk DBQueue insert -> QBM-K-ScheduleStart / QBM_ZScheduleStart at line 28
  • References QBM_PDBQueueInsert_Bulk

Typed Edges

  • queues DBQueue task QBM_ZScheduleStart at line 28 Bulk DBQueue insert -> QBM-K-ScheduleStart / QBM_ZScheduleStart at line 28
  • references source dbo.QBM_FGIConfigparmValue source text reference
  • references source dbo.QBM_FGIScheduleNextRun source text reference
  • references source dbo.QBM_FGISessionContext source text reference
  • references source dbo.QBM_PDBQueueInsert_Bulk source text reference
  • references source dbo.QBM_PJournal source text reference
  • references source dbo.QBM_PSessionContextSet source text reference
  • references source dbo.QBM_PSessionErrorAdd source text reference
  • references source dbo.QBM_PWaitForSeconds source text reference
  • queues DBQueue task QBM-K-ScheduleStart -> QBM_ZScheduleStart QBM_PDBQueueInsert_Bulk 'QBM-K-ScheduleStart' , @DBQueueElements_01 if @DebugSwitch = 2 begin if exists (select top 1 1 from @DBQueueElements_01 ) begin select @Debugmessage = 'Übergeben an QBM-K-ScheduleStart: ' + STRING_AGG(e.Object, ' '…

References

Referenced By

Complete Source

SQL307 lines
1CREATE PROCEDURE QBM_PScheduleCheck2AS3BEGIN4  SET nocount5    ON6  DECLARE @tt QBM_YCursorBuffer7  DECLARE @GenProcID varchar(38) = newid()8  DECLARE @jetztInUTC datetime9  DECLARE @GenProcID_R varchar(38) = dbo.QBM_FGISessionContext('')10  DECLARE @XUser_R nvarchar(64) = dbo.QBM_FGISessionContext('XUser')11  DECLARE @DebugSwitch int = 012  DECLARE @DebugLevel varchar(1) = 'W'13  DECLARE @Debugmessage nvarchar(max)14  DECLARE @Message nvarchar(2000)15  DECLARE @Xdate datetime = getutcdate()16  DECLARE @XUser nvarchar(64) = object_name(@@procid)17  SET XACT_ABORT OFF18  BEGIN TRY19    IF @DebugSwitch = 220    BEGIN21      SELECT @Debugmessage = 'Aufgerufen '22      EXEC QBM_PJournal @Debugmessage,23        @@procid,24      'D',25        @DebugLevel26      EXEC QBM_PWaitForSeconds 0.0127    END28    IF EXISTS(29      SELECT TOP 1 130      FROM DialogDatabase d31      WITH(readpast)32    WHERE33      d.UpdatePhase > 0 AND d.IsMainDatabase = 1) AND @DebugSwitch <> 134    BEGIN35      SELECT36        @Message = '#LDS#Schedules not processed because of DialogDatabase.UpdatePhase.|'37        GOTO NoOpMessage38    END39    IF EXISTS(40      SELECT TOP 1 141      FROM DialogDatabase42      WITH(readpast)43    WHERE44      SingleUserProcess <> 0 AND IsMainDatabase = 1) AND @DebugSwitch <> 145    BEGIN46      SELECT47        @Message = '#LDS#Schedules not processed because of DialogDatabase.SingleUserProcess.|'48        GOTO NoOpMessage49    END50    IF EXISTS(51      SELECT TOP 1 152      FROM DialogScriptAssembly a53      WITH(readpast)54    WHERE55      a.IsValid = 0) AND @DebugSwitch <> 156    BEGIN57      SELECT58        @Message = '#LDS#Schedules not processed because of DialogScriptAssembly.IsValid = 0.|'59        GOTO NoOpMessage60    END61    IF NOT EXISTS(62      SELECT TOP 1 163      FROM DialogScriptAssembly a) AND @DebugSwitch <> 164    BEGIN65      SELECT66        @Message = '#LDS#Schedules not processed because of DialogScriptAssembly empty.|'67        GOTO NoOpMessage68    END69    IF EXISTS(70      SELECT TOP 1 171      FROM DialogDatabase d72      WITH(readpast)73    WHERE74      d.IsDBSchedulerDisabled = 1 AND IsMainDatabase = 1) AND @DebugSwitch <> 175    BEGIN76      SELECT77        @Message = '#LDS#Schedules not processed because of DialogDatabase.IsDBSchedulerDisabled.|'78        GOTO NoOpMessage79    END80    IF EXISTS(81      SELECT TOP 1 182      FROM DialogDatabase d83      WITH(readpast)84    WHERE85      d.IsJobServiceDisabled = 1 AND IsMainDatabase = 1) AND @DebugSwitch <> 186    BEGIN87      SELECT88        @Message = '#LDS#Schedules not processed because of DialogDatabase.IsJobServiceDisabled.|'89        GOTO NoOpMessage90    END91    IF dbo.QBM_FGIConfigparmValue('QBM\Schedules') = '' AND @DebugSwitch <> 192    BEGIN93      SELECT94        @Message = '#LDS#Schedules not processed because of ConfigParm QBM.Schedules is disabled.|'95        GOTO NoOpMessage96    END97    IF @DebugSwitch = 298    BEGIN99      SELECT @Debugmessage = 'Alle Starttest überstanden '100      EXEC QBM_PJournal @Debugmessage,101        @@procid,102      'D',103        @DebugLevel104      EXEC QBM_PWaitForSeconds 0.01105    END106    IF isnull(@XUser,107    '') = ''108    BEGIN109      SELECT @XUser =110      LEFT(Object_name(@@procid),111      64)112      EXEC QBM_PSessionContextSet 'GenProcID',113        @GenProcID114      EXEC QBM_PSessionContextSet 'XUser',115        @XUser116    END117    SELECT @jetztInUTC = GetUTCDate()118    BEGIN119      TRANSACTION120      INSERT INTO @tt(UID1,121      Bit1,122      DateTime1,123      Int1)124      SELECT125        s.uid_DialogSchedule,126        1,127        isnull(s.nextrun,128        '1899-12-30'),129        tz.CurrentUTCOffset130      FROM DialogSchedule s131      JOIN DialogTimeZone tz132        ON s.uid_DialogTimeZone = tz.uid_DialogTimeZone133      WHERE134        isnull(startdate,135      '1899-12-30') <= @jetztInUTC AND isnull(enddate,136      '2200-01-01') >= @jetztInUTC AND enabled = 1137      IF @DebugSwitch = 1138      BEGIN139        print 'nach initial laden'140        SELECT141          UID1 AS uid_DialogSchedule,142          Bit1 AS istostart,143          DateTime1 AS nextrun,144          Int1 AS CurrentUTCOffset145        FROM @tt146      END147      UPDATE @tt148      SET Bit1 = 0149      WHERE150        UID1 IN(151      SELECT s.uid_DialogSchedule152      FROM DialogSchedule s153      WHERE154        nextrun > dateadd(ss, Int1, @jetztInUTC))155      IF @DebugSwitch = 1156      BEGIN157        print 'nach nextrun test'158        SELECT159          UID1 AS uid_DialogSchedule,160          Bit1 AS istostart,161          DateTime1 AS nextrun,162          Int1 AS CurrentUTCOffset163        FROM @tt164      END165      UPDATE @tt166      SET Bit1 = 0167      WHERE168        UID1 IN(169      SELECT s.uid_DialogSchedule170      FROM DialogSchedule s171      JOIN DialogTimeZone tz172        ON s.UID_DialogTimeZone = tz.UID_DialogTimeZone173      WHERE174        isnull(s.lastrun, '1899-12-30') = '1899-12-30' OR(abs(datediff(mi, s.xdateinserted, dateadd(ss,175      tz.CurrentUTCOffset *(-1), isnull(s.lastrun, '1899-12-30')))) < 3 AND abs(datediff(mi, s.xdateinserted,176      GetUTCDate())) < 3))177      UPDATE @tt178      SET Bit1 = 0179      WHERE180        DateTime1 = '1899-12-30'181      IF @DebugSwitch = 1182      BEGIN183        print 'vor DBQueue insert'184        SELECT185          UID1 AS uid_DialogSchedule,186          Bit1 AS istostart,187          DateTime1 AS nextrun,188          Int1 AS CurrentUTCOffset189        FROM @tt190      END191      DECLARE @DBQueueElements_01 QBM_YDBQueueRaw192      INSERT INTO @DBQueueElements_01(Object,193      SubObject,194      GenProcID)195      SELECT196        x.uid,197        NULL,198        @GenProcID199      FROM(200      SELECT UID1 AS uid201      FROM @tt202      WHERE203        Bit1 = 1) AS x204      EXEC QBM_PDBQueueInsert_Bulk 'QBM-K-ScheduleStart',205        @DBQueueElements_01206      IF @DebugSwitch = 2207      BEGIN208        IF EXISTS(209          SELECT TOP 1 1210          FROM @DBQueueElements_01)211        BEGIN212          SELECT213            @Debugmessage = 'Übergeben an QBM-K-ScheduleStart: ' + STRING_AGG(e.Object,214            ' ')215          FROM @DBQueueElements_01 e216          EXEC QBM_PJournal @Debugmessage,217            @@procid,218          'D',219            @DebugLevel220        END221        ELSE222        BEGIN223          SELECT @Debugmessage = 'Übergeben an QBM-K-ScheduleStart: ' + 'nichts'224          EXEC QBM_PJournal @Debugmessage,225            @@procid,226          'D',227            @DebugLevel228        END229      END230      UPDATE DialogSchedule231      SET lastrun = dateadd(ss,232      tz.CurrentUTCOffset,233      @jetztInUTC),234      XDateUpdated = @Xdate,235      XUserUpdated = @XUser236      FROM dialogSchedule s237      JOIN DialogTimeZone tz238        ON s.UID_DialogTimeZone = tz.UID_DialogTimeZone239      JOIN @tt tt240        ON s.UID_DialogSchedule = tt.UID1241      WHERE242        tt.Bit1 = 1243      SET datefirst 1244      UPDATE @tt245      SET DateTime1 = dbo.QBM_FGIScheduleNextRun(isnull(tt.DateTime1, '1899-12-30'),246      j.lastrun,247      @jetztInUTC,248      j.starttime,249      j.FrequencyType,250      j.Frequency,251      j.Frequencysubtype,252      tz.CurrentUTCOffset)253      FROM @tt tt254      JOIN DialogSchedule j255        ON tt.UID1 = j.uid_DialogSchedule256      JOIN DialogTimeZone tz257        ON j.uid_DialogTimeZone = tz.UID_DialogTimeZone258      WHERE259        (tt.Bit1 = 1 OR isnull(tt.DateTime1, '1899-12-30') = '1899-12-30') AND isnull(j.lastrun,260      '1899-12-30') <> '1899-12-30'261      EXEC QBM_PSessionContextSet 'GenProcID',262        @GenProcID263      EXEC QBM_PSessionContextSet 'XUser',264        @XUser265      UPDATE DialogSchedule266      SET nextrun = tt.DateTime1,267      XDateUpdated = @Xdate,268      XUserUpdated = @XUser269      FROM DialogSchedule s270      JOIN @tt tt271        ON tt.UID1 = s.UID_DialogSchedule272      WHERE273        isnull(s.NextRun,274      '1899-12-30') <> isnull(tt.DateTime1,275      '1899-12-30')276      UPDATE DialogSchedule277      SET lastrun = '1900-01-02',278      XDateUpdated = @Xdate,279      XUserUpdated = @XUser280      WHERE281        isnull(lastrun,282      '1899-12-30') = '1899-12-30' COMMIT TRANSACTION283    END TRY284    BEGIN CATCH285      EXEC QBM_PSessionErrorAdd DEFAULT286      EXEC QBM_PSessionContextSet 'GenProcID',287        @GenProcID_R288      EXEC QBM_PSessionContextSet 'XUser',289        @XUser_R ROLLBACK TRANSACTION290      RAISERROR('',291      18,292      1)293        WITH NOWAIT294    END CATCH295    ende: GanzEnde:296    EXEC QBM_PSessionContextSet 'GenProcID',297      @GenProcID_R298    EXEC QBM_PSessionContextSet 'XUser',299      @XUser_R300    RETURN NoOpMessage:301    EXEC QBM_PJournal @Message,302      @@PROCID,303    'W',304      @DebugLevel,305      @SuppressEntriesCount = 10306    GOTO ende307  END
Open raw exported source
SQL · Raw44 lines
1     create   procedure QBM_PScheduleCheck AS begin set nocount on declare @tt QBM_YCursorBuffer     declare @GenProcID varchar(38) = newid() declare2 @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 @Xdate4 datetime = getutcdate() declare @XUser nvarchar(64) = object_name(@@procid) SET XACT_ABORT OFF BEGIN TRY if @DebugSwitch = 2 begin select @Debugmessage5 = 'Aufgerufen ' exec QBM_PJournal @Debugmessage, @@procid, 'D', @DebugLevel exec QBM_PWaitForSeconds 0.01 end  if exists (select top 1 1 from DialogDatabase6 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 @DebugSwitch8 <> 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 @DebugSwitch12 <> 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', @DebugLevel16 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 , Int121 as CurrentUTCOffset from @tt end update @tt set Bit1  = 0 where UID1  in (select s.uid_DialogSchedule from DialogSchedule s  where nextrun > dateadd(ss22, Int1   , @jetztInUTC) ) if @DebugSwitch = 1 begin print 'nach nextrun test' select UID1 as uid_DialogSchedule , Bit1 as istostart , DateTime1 as nextrun23 , Int1 as CurrentUTCOffset from @tt end update @tt set Bit1  = 0 where UID1  in (select s.uid_DialogSchedule from DialogSchedule s join DialogTimeZone24 tz on s.UID_DialogTimeZone = tz.UID_DialogTimeZone where isnull(s.lastrun, '1899-12-30') = '1899-12-30'  or ( abs(datediff( mi, s.xdateinserted, dateadd25(ss, tz.CurrentUTCOffset * (-1) , isnull(s.lastrun, '1899-12-30')))) < 3  and abs(datediff( mi, s.xdateinserted, GetUTCDate())) < 3  ) ) update @tt set26 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, SubObject28, 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 @Debugmessage31 = 'Übergeben an QBM-K-ScheduleStart: ' + 'nichts' exec QBM_PJournal @Debugmessage, @@procid, 'D', @DebugLevel end end  update DialogSchedule set lastrun32 = dateadd(ss, tz.CurrentUTCOffset, @jetztInUTC)  , XDateUpdated = @Xdate , XUserUpdated = @XUser from dialogSchedule s join DialogTimeZone tz on s.UID_DialogTimeZone33 = 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_FGIScheduleNextRun34 (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.Bit136  = 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 = @XUser38 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')  update39 DialogSchedule set lastrun = '1900-01-02'   , XDateUpdated = @Xdate , XUserUpdated = @XUser where isnull(lastrun , '1899-12-30') = '1899-12-30'  commit40 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 exec42 QBM_PSessionContextSet 'XUser', @XUser_R return NoOpMessage: exec QBM_PJournal @Message, @@PROCID, 'W', @DebugLevel, @SuppressEntriesCount = 10 goto ende43 end 44