Back to OIM Explorer

dbo.QBM_PDBQCS_CurrentRestore

Stored ProcedureSQL_STORED_PROCEDURESandbox DB

Stored Procedure. Bulk DBQueue insert -> at line 16; Single DBQueue insert -> at line 16; Single DBQueue insert -> at line 16; Single DBQueue insert -> at line 23

Source: sandbox-db sys.sql_modules

Source size: 6.370 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 -> at line 16
  • Single DBQueue insert -> at line 16
  • Single DBQueue insert -> at line 16
  • Single DBQueue insert -> at line 23
  • Single DBQueue insert -> at line 23
  • Single DBQueue insert -> at line 24
  • References QBM_PDBQueueInsert_Single
  • References QBM_PDBQueueInsert_Bulk

Typed Edges

  • queues DBQueue task at line 16 Bulk DBQueue insert -> at line 16
  • queues DBQueue task at line 23 Single DBQueue insert -> at line 23
  • queues DBQueue task at line 24 Single DBQueue insert -> at line 24
  • references source dbo.QBM_FGIDBQueueCurrRestoreAble source text reference
  • references source dbo.QBM_FGISessionContext source text reference
  • references source dbo.QBM_FGISessionErrorIsDeadlock source text reference
  • references source dbo.QBM_FGISessionErrorIsMissingCo source text reference
  • references source dbo.QBM_FGISessionErrorRethrow source text reference
  • references source dbo.QBM_FGITableCountAll source text reference
  • references source dbo.QBM_PDBQCS_CurrentMoveSlot source text reference
  • references source dbo.QBM_PDBQueueInsert_Bulk source text reference
  • references source dbo.QBM_PDBQueueInsert_Single 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_PTransactionLevelCheck source text reference
  • references source dbo.QBM_PWaitForSeconds source text reference

References

Referenced By

Complete Source

SQL298 lines
1CREATE PROCEDURE QBM_PDBQCS_CurrentRestore(2  @ExecutionType int,3  @IgnoreGeneration BIT = 0,4  @UID_TaskPattern varchar(38) = '%',5  @RowsMoved int = 0 OUTPUT6)7AS8BEGIN9  DECLARE @Trans int10  DECLARE @UID_TaskToMatch varchar(38)11  DECLARE @GenProcID varchar(38) = newid()12  DECLARE @PathLength int13  DECLARE @DebugStarttime datetime = getutcdate()14  DECLARE @DebugLevel char(1) = 'I'15  DECLARE @DebugMessage nvarchar(4000)16  DECLARE @DebugSwitch int = 017  DECLARE @warFehler int18  DECLARE @SecondsToAdd int19  DECLARE @einRuck int = 100020  DECLARE @AktuelleEintraege QBM_YHelperQueueRepair21  DECLARE @UID_TaskAutomatedFollower varchar(38)22  DECLARE @UID_TaskAutomatedPredecessor varchar(38)23  SELECT @RowsMoved = 024  DECLARE @DBQueueToMove QBM_YDBQCSCurrentToMove25  DECLARE @RowsMovedDelete int26  DECLARE @RestoreToGeneration0 int = -50027  DECLARE @GenProcID_R varchar(38) = dbo.QBM_FGISessionContext('')28  DECLARE @XUser_R nvarchar(64) = dbo.QBM_FGISessionContext('XUser')29  DECLARE @validTasks QBM_YCursorBuffer30  DECLARE @ElementCount int,31  @ElementIndex int32  DECLARE @UIDDBQueueMerken QBM_YSingleGUID33  DECLARE @rows int34  DECLARE @InMigration BIT = 035  SET XACT_ABORT OFF36  BEGIN TRY37    SELECT @Trans = @@TRANCOUNT38    SET nocount39      ON40    IF @IgnoreGeneration = 141    BEGIN42      UPDATE QBMDBQueueCurrent43      SET SlotNumber = -144      WHERE45        SlotNumber > 046      SELECT @rows = @@rowcount47      IF @rows > 048      BEGIN49        SELECT @DebugMessage = 'set Current Slotnumber = -1: ' + str(@rows)50        EXEC QBM_PJournal @DebugMessage,51          @@PROCID,52        'T',53          @DebugLevel54      END55    END56    INSERT INTO @validTasks(UID1)57    SELECT x.UID_Task58    FROM(59    SELECT60      mo.UID_Task,61      mo.PathLength62    FROM QBMDBQueueCurrent cu63      WITH(readpast, forceSeek)64    JOIN QBMDBQueueTaskMetric mo65      WITH(readpast)66      ON cu.UID_Task = mo.UID_Task67    WHERE68      cu.SlotNumber < 0 AND cu.UID_Task LIKE @UID_TaskPattern69    GROUP BY mo.UID_Task,70    mo.PathLength) AS x71    ORDER BY x.PathLength72    SELECT @ElementCount = @@ROWCOUNT73    IF @ElementCount = 074    BEGIN75      IF @DebugSwitch > 076      BEGIN77        print 'definitiv nix gefunden'78      END79      GOTO endLabel80    END81    IF EXISTS(82      SELECT TOP 1 183      FROM DialogDatabase d84      WITH(readpast)85    WHERE86      d.UpdatePhase > 0)87    BEGIN88      SELECT @InMigration = 189    END90    IF dbo.QBM_FGITableCountAll('QBMDBQueueCurrent') = 091    BEGIN92      IF @DebugSwitch > 093      BEGIN94        print 'QBMDBQueueCurrent is leer'95      END96      GOTO endLabel97    END98    IF @ExecutionType <= 99799    BEGIN100      EXEC QBM_PTransactionLevelCheck 0,101        @@procid,102      'entry'103    END104    EXEC QBM_PSessionContextSet 'XUser',105    'QBM_PDBQueueInsert_Bulk'106    SELECT @ElementIndex = 1107    WHILE @ElementIndex <= @ElementCount108    BEGIN109      SELECT TOP 1 @UID_TaskToMatch = v.UID1110      FROM @validTasks v111      WHERE112        v.ElementIndex = @ElementIndex113      SELECT TOP 1 @SecondsToAdd = CASE114      WHEN @InMigration = 1 THEN115      0116      WHEN mo.CountInDBQueue IS NULL THEN117      0118      WHEN mo.CountInDBQueue < 20000 THEN119      0120      ELSE mo.CountInDBQueue / 1000121      END + CASE122      WHEN @InMigration = 1 THEN123      0124      ELSE isnull(mo.RestoreDelay,125      0)126      END,127      @UID_TaskAutomatedPredecessor = mo.UID_TaskAutomatedPredecessor,128      @UID_TaskAutomatedFollower = mo.UID_TaskAutomatedFollower,129      @PathLength = mo.PathLength130      FROM QBMDBQueueTaskMetric mo131        WITH(readpast)132      WHERE133        mo.UID_Task = @UID_TaskToMatch134      DELETE @UIDDBQueueMerken135      INSERT INTO @UIDDBQueueMerken(UID_SingleGuid)136      SELECT cu.UID_DialogDBQueue137      FROM QBMDBQueueCurrent cu138        WITH(readpast)139      WHERE140        (cu.SlotNumber < 0 AND cu.UID_Task = @UID_TaskToMatch) AND(dbo.QBM_FGIDBQueueCurrRestoreAble(cu.Generation,141      cu.StartedAt, @SecondsToAdd) = 1 OR @IgnoreGeneration = 1 OR cu.SlotNumber <= @RestoreToGeneration0 OR @InMigration = 1)142      IF @@ROWCOUNT = 0143      BEGIN144        GOTO zyklusende145      END146      IF @UID_TaskAutomatedFollower > ' '147      BEGIN148        EXEC QBM_PDBQueueInsert_Single @UID_TaskAutomatedFollower,149        '',150        '',151          @GenProcID152      END153      IF @UID_TaskAutomatedPredecessor > ' '154      BEGIN155        EXEC QBM_PDBQueueInsert_Single @UID_TaskAutomatedPredecessor,156        '',157        '',158          @GenProcID159      END160      SET lock_timeout 800161      BEGIN162        TRANSACTION163        BEGIN TRY164          SELECT @warFehler = 0165          DELETE @AktuelleEintraege166          INSERT INTO @AktuelleEintraege(UID_DialogDBQueue,167          Object,168          SubObject,169          Generation,170          GenProcID,171          PathLength,172          UID_Task)173          SELECT174            TOP(@einRuck) cu.UID_DialogDBQueue,175            isnull(cu.UID_Parameter,176            ''),177            isnull(cu.UID_SubParameter,178            ''),179          CASE180            WHEN cu.SlotNumber <= @RestoreToGeneration0 THEN181          -1182          ELSE cu.Generation183          END,184          cu.GenProcID,185          @PathLength,186          cu.UID_Task187          FROM @UIDDBQueueMerken me188          JOIN QBMDBQueueCurrent cu189            WITH(readpast,190          forceseek)191            ON me.UID_SingleGuid = cu.UID_DialogDBQueue merge INTO DialogDBQueue AS t using @AktuelleEintraege AS s192            ON t.UID_DialogDBQueue = s.UID_DialogDBQueue AND t.Generation >= 0 WHEN matched THEN193          UPDATE194          SET t.generation = s.Generation + 1 WHEN NOT matched BY target THEN195          INSERT(UID_DialogDBQueue,196          Object,197          SubObject,198          Generation,199          GenProcID,200          PathLength,201          UID_Task)202          VALUES(convert(varchar(38), newid()),203          CASE204            WHEN s.Object = '' THEN205          NULL206          ELSE s.Object207          END,208          CASE209            WHEN s.SubObject = '' THEN210          NULL211          ELSE s.SubObject212          END,213          s.Generation +1,214          CASE215            WHEN len(isnull(s.GenProcID, '')) < 36 THEN216          convert(varchar(38), newid())217          ELSE s.GenProcID218          END,219          s.PathLength,220          s.UID_Task);221          DELETE @DBQueueToMove222          INSERT INTO @DBQueueToMove(UID_DialogDBQueue)223          SELECT cul.UID_DialogDBQueue224          FROM @AktuelleEintraege cul225          EXEC @RowsMovedDelete = QBM_PDBQCS_CurrentMoveSlot @DBQueueToMove,226            @SlotNumberSource = -1,227            @SlotnumberTarget = 0228          SELECT @RowsMoved += @RowsMovedDelete229        END TRY230        BEGIN CATCH231          SELECT @warFehler = -1232          EXEC QBM_PSessionErrorAdd DEFAULT233          IF dbo.QBM_FGISessionErrorIsDeadlock(DEFAULT) = 1234          BEGIN235            SELECT @warFehler = 1236          END237        END CATCH238        IF @warFehler = 0239        BEGIN240          COMMIT TRANSACTION241        END242        ELSE243        BEGIN244          ROLLBACK245          IF @warFehler = 1246          BEGIN247            EXEC QBM_PWaitForSeconds 0.05248          END249          ELSE250          BEGIN251            RAISERROR('',252            18,253            1)254              WITH nowait255          END256        END257        SET lock_timeout -1258        IF @warFehler = 0259        BEGIN260          SELECT @DebugMessage = 'restore from Current ' + @UID_TaskToMatch261          EXEC QBM_PJournal @DebugMessage,262            @@PROCID,263          'T',264            @DebugLevel265        END266        zyklusende:267        SELECT @ElementIndex += 1268      END269      EXEC QBM_PSessionContextSet 'XUser',270      ''271      EXEC QBM_PTransactionLevelCheck @trans,272        @@procid,273      'exit'274    END TRY275    BEGIN CATCH276      SET lock_timeout -1277      EXEC QBM_PSessionErrorAdd DEFAULT278      IF dbo.QBM_FGIsessionErrorIsMissingCO(DEFAULT) = 0279      BEGIN280        ROLLBACK TRANSACTION281        DECLARE @Rethrow varchar(1000) = dbo.QBM_FGISessionErrorRethrow()282        RAISERROR(@Rethrow,283        18,284        1)285          WITH NOWAIT286      END287    END CATCH288    endLabel:289    EXEC QBM_PJournal @Debugstarttime,290      @@procid,291    'T',292      @Debuglevel293    EXEC QBM_PSessionContextSet 'GenProcID',294      @GenProcID_R295    EXEC QBM_PSessionContextSet 'XUser',296      @XUser_R297    RETURN298  END
Open raw exported source
SQL ยท Raw42 lines
1        create   procedure QBM_PDBQCS_CurrentRestore ( @ExecutionType int ,@IgnoreGeneration bit = 0 , @UID_TaskPattern varchar(38) = '%'  , @RowsMoved2 int = 0 output ) as begin declare @Trans int declare @UID_TaskToMatch varchar(38) declare @GenProcID varchar(38) = newid() declare @PathLength int declare3 @DebugStarttime datetime = getutcdate() declare @DebugLevel char(1) = 'I' declare @DebugMessage nvarchar(4000) declare @DebugSwitch int = 0 declare @warFehler4 int declare @SecondsToAdd int declare @einRuck int = 1000 declare @AktuelleEintraege QBM_YHelperQueueRepair declare @UID_TaskAutomatedFollower varchar5(38) declare @UID_TaskAutomatedPredecessor varchar(38) select @RowsMoved = 0 declare @DBQueueToMove QBM_YDBQCSCurrentToMove declare @RowsMovedDelete int6 declare @RestoreToGeneration0 int = -500 declare @GenProcID_R varchar(38) = dbo.QBM_FGISessionContext('') declare @XUser_R nvarchar(64) = dbo.QBM_FGISessionContext7('XUser') declare @validTasks QBM_YCursorBuffer declare @ElementCount int ,@ElementIndex int declare @UIDDBQueueMerken QBM_YSingleGUID  declare @rows int8 declare @InMigration bit = 0 SET XACT_ABORT OFF BEGIN TRY select @Trans = @@TRANCOUNT set nocount on if @IgnoreGeneration = 1 begin update QBMDBQueueCurrent9 set SlotNumber = -1 where SlotNumber > 0 select @rows = @@rowcount if @rows > 0 begin select @DebugMessage = 'set Current Slotnumber = -1: ' + str(@rows10) exec QBM_PJournal @DebugMessage, @@PROCID, 'T', @DebugLevel end  end  insert into @validTasks(UID1) select x.UID_Task from ( select mo.UID_Task, mo.PathLength11 from QBMDBQueueCurrent cu with (readpast, forceSeek) join QBMDBQueueTaskMetric mo with (readpast) on cu.UID_Task = mo.UID_Task where cu.SlotNumber < 012 and cu.UID_Task like @UID_TaskPattern group by mo.UID_Task, mo.PathLength ) as x order by x.PathLength select @ElementCount = @@ROWCOUNT if @ElementCount13 = 0 begin if @DebugSwitch > 0 begin print 'definitiv nix gefunden' end goto endLabel end if exists (select top 1 1 from DialogDatabase d with (readpast14) where d.UpdatePhase > 0 ) begin select @InMigration = 1 end if dbo.QBM_FGITableCountAll('QBMDBQueueCurrent') = 0 begin if @DebugSwitch > 0 begin print15 'QBMDBQueueCurrent is leer' end goto endLabel end if @ExecutionType <= 997 begin  exec QBM_PTransactionLevelCheck 0, @@procid, 'entry'  end exec QBM_PSessionContextSet16 'XUser', 'QBM_PDBQueueInsert_Bulk'   select @ElementIndex = 1 while @ElementIndex <= @ElementCount begin select top 1 @UID_TaskToMatch = v.UID1 from @validTasks17 v where v.ElementIndex = @ElementIndex  select top 1 @SecondsToAdd = case when @InMigration = 1 then 0 when mo.CountInDBQueue is null then 0 when mo.CountInDBQueue18 < 20000 then 0 else mo.CountInDBQueue / 1000 end  + case when @InMigration = 1 then 0 else isnull(mo.RestoreDelay, 0) end , @UID_TaskAutomatedPredecessor19 = mo.UID_TaskAutomatedPredecessor , @UID_TaskAutomatedFollower = mo.UID_TaskAutomatedFollower , @PathLength = mo.PathLength from QBMDBQueueTaskMetric 20mo with (readpast) where mo.UID_Task = @UID_TaskToMatch delete @UIDDBQueueMerken insert into @UIDDBQueueMerken(UID_SingleGuid) select cu.UID_DialogDBQueue21 from QBMDBQueueCurrent cu with (readpast) where (cu.SlotNumber < 0 and cu.UID_Task = @UID_TaskToMatch ) and (dbo.QBM_FGIDBQueueCurrRestoreAble(cu.Generation22, cu.StartedAt, @SecondsToAdd)= 1 or @IgnoreGeneration = 1 or cu.SlotNumber <= @RestoreToGeneration0 or @InMigration = 1 ) if @@ROWCOUNT = 0 begin goto23 zyklusende end if @UID_TaskAutomatedFollower > ' ' begin exec QBM_PDBQueueInsert_Single @UID_TaskAutomatedFollower, '', '', @GenProcID end if @UID_TaskAutomatedPredecessor24 > ' ' begin exec QBM_PDBQueueInsert_Single @UID_TaskAutomatedPredecessor, '', '', @GenProcID end   set lock_timeout 800 begin transaction   BEGIN TRY 25 select @warFehler = 0   delete  @AktuelleEintraege  insert into @AktuelleEintraege (UID_DialogDBQueue, Object, SubObject , Generation, GenProcID, PathLength26, UID_Task) select top (@einRuck) cu.UID_DialogDBQueue, isnull(cu.UID_Parameter, ''), isnull(cu.UID_SubParameter, '') , case  when cu.SlotNumber <= @RestoreToGeneration027 then -1 else cu.Generation end , cu.GenProcID, @PathLength, cu.UID_Task from @UIDDBQueueMerken me join QBMDBQueueCurrent cu with (readpast, forceseek)28 on me.UID_SingleGuid = cu.UID_DialogDBQueue  merge into DialogDBQueue as t using @AktuelleEintraege as s on t.UID_DialogDBQueue = s.UID_DialogDBQueue 29and t.Generation >= 0 when matched then update set t.generation = s.Generation + 1 when not matched by target then insert (UID_DialogDBQueue, Object, SubObject30 , Generation , GenProcID , PathLength, UID_Task ) values (convert(varchar(38), newid()) , case when s.Object = '' then null else s.Object end , case when31 s.SubObject = '' then null else s.SubObject end , s.Generation +1 , case when len(isnull(s.GenProcID, '') ) < 36 then convert(varchar(38), newid()) else32 s.GenProcID end , s.PathLength, s.UID_Task ) ; delete @DBQueueToMove  insert into @DBQueueToMove(UID_DialogDBQueue) select cul.UID_DialogDBQueue from 33@AktuelleEintraege cul exec @RowsMovedDelete = QBM_PDBQCS_CurrentMoveSlot @DBQueueToMove , @SlotNumberSource = -1  , @SlotnumberTarget = 0 select @RowsMoved34 += @RowsMovedDelete END TRY BEGIN CATCH select @warFehler = -1  exec QBM_PSessionErrorAdd default if dbo.QBM_FGISessionErrorIsDeadlock(default) = 1 begin35 select @warFehler = 1  end END CATCH if @warFehler = 0 begin commit transaction end else begin rollback if @warFehler = 1 begin exec QBM_PWaitForSeconds36 0.05 end else begin raiserror ('', 18, 1) with nowait end end  set lock_timeout -1 if @warFehler = 0 begin select @DebugMessage = 'restore from Current '37 + @UID_TaskToMatch exec QBM_PJournal @DebugMessage, @@PROCID, 'T', @DebugLevel end zyklusende: select @ElementIndex += 1 end  exec QBM_PSessionContextSet38'XUser', '' exec QBM_PTransactionLevelCheck @trans, @@procid, 'exit' END TRY BEGIN CATCH  set lock_timeout -1 exec QBM_PSessionErrorAdd default if dbo.QBM_FGIsessionErrorIsMissingCO39(default)= 0 begin rollback transaction declare @Rethrow varchar(1000) = dbo.QBM_FGISessionErrorRethrow() RAISERROR (@Rethrow, 18, 1) WITH NOWAIT end END40 CATCH endLabel:  exec QBM_PJournal @Debugstarttime, @@procid, 'T', @Debuglevel exec QBM_PSessionContextSet 'GenProcID', @GenProcID_R exec QBM_PSessionContextSet41 'XUser', @XUser_R return end 42