Back to OIM Explorer

dbo.QBM_PDBQueueCalculateDelta

Stored ProcedureSQL_STORED_PROCEDURESandbox DB

Stored Procedure. Bulk DBQueue insert -> restore once more at line 77; References QBM_PDBQueueInsert_Bulk; References ChangeLimit

Source: sandbox-db sys.sql_modules

Source size: 13.200 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.
  • ChangeLimit relation detected. Use this with current V10 DBQueue slot execution evidence, not older 9.2-only assumptions.

Verified DBQueue Delta Calculation Notes

  • Source catalog entry dbo.QBM_PDBQueueCalculateDelta is a non-generated SQL stored procedure from sandbox-db sys.sql_modules.
  • The source definition contains ChangeLimit-related variables and uses dbo.QBM_FCVIntToString(...) before setting session context key CHANGELIMITREACHED.
  • The source definition calls QBM_PDBQueueInsert_Bulk @UID_TaskOnceMore, @DBQueueElementsOnceMore in the restore-once-more path.
  • The generated source-index flags show HasChangeLimit=1, HasDBQueueInsertBulk=1, HasDBQueueInsertSingle=0, and HasQbmPJobCreate=0 for this procedure.

Operational meaning: This routine is a concrete ChangeLimit/DBQueue delta-processing entry. It is a stronger evidence target for ChangeLimit behavior than keyword-only references because the source also records the `CHANGELIMITREACHED` session context.

Relations

  • Bulk DBQueue insert -> restore once more at line 77
  • References QBM_PDBQueueInsert_Bulk
  • References ChangeLimit

Typed Edges

  • queues DBQueue task restore once more at line 77 Bulk DBQueue insert -> restore once more at line 77
  • references source dbo.QBM_FCVAnyToHash source text reference
  • references source dbo.QBM_FCVIntToString source text reference
  • references source dbo.QBM_FCVStringToInt source text reference
  • references source dbo.QBM_FGIConfigparmValue source text reference
  • references source dbo.QBM_FGIDBQueueSlotResetType source text reference
  • references source dbo.QBM_FGISessionContext source text reference
  • references source dbo.QBM_FGISessionErrorRethrow 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_PJournal source text reference
  • references source dbo.QBM_PSessionContextSet source text reference
  • references source dbo.QBM_PSessionErrorAdd source text reference
  • queues DBQueue task restore once more -> unresolved procedure QBM_PDBQueueInsert_Bulk @UID_TaskOnceMore, @DBQueueElementsOnceMore if @DebugSwitch > 0 begin select @DebugMessage = 'restore once more ' + @UID_TaskOnceMore + '@CountDeltaOrigin ' + str(@CountDeltaOrigin) + ' @CountDeltaQantity ' + str(@C…

References

Referenced By

Complete Source

SQL564 lines
1CREATE PROCEDURE QBM_PDBQueueCalculateDelta(2  @SourceDataOrig QBM_YDataForDelta READONLY,3  @SlotNumber int,4  @DeltaQuantity BIT,5  @DeltaDelete BIT,6  @DeltaInsert BIT,7  @DeltaOrigin BIT,8  @CountDeltaQantity int OUTPUT,9  @CountDeltaOrigin int OUTPUT,10  @UseIsInEffect BIT,11  @DBQueueCurrentExtern QBM_YDBQueueCurrent READONLY12)13AS14BEGIN15  DECLARE @DBQueueCurrent QBM_YDBQueueCurrent16  DECLARE @Overload_RowCountCurrent int17  DECLARE @Overload_RowCountLimit int = dbo.QBM_FCVStringToInt(dbo.QBM_FGIConfigparmValue('QBM\DBQueue\OverloadLimit'),18  200000)19  DECLARE @Overload_RowCountLimitReset int = @Overload_RowCountLimit * 620  DECLARE @Overload_UID_TaskToReset varchar(38)21  DECLARE @Overload_ElementsInSlot int22  DECLARE @Overload_Maxbulk int23  DECLARE @Overload_IsBulkEnabled BIT24  DECLARE @ChangeLimitReached_String varchar(64)25  DECLARE @Medium QBM_YDBQueueIntermediate26  DECLARE @CountItems int27  DECLARE @DebugSwitch int = 028  DECLARE @DebugLevel char(1) = 'W'29  DECLARE @DebugMessage nvarchar(1000)30  DECLARE @SourceData QBM_YDataForDelta31  DECLARE @ErrorMessage nvarchar(4000)32  DECLARE @ErrorSeverity int33  DECLARE @ErrorState int34  DECLARE @Switch int = 035  SELECT @Switch +=(@DeltaQuantity | @DeltaInsert | @DeltaDelete)36  SELECT @Switch += @DeltaOrigin * 237  DECLARE @DeleteMaskForXOrigin int = 038  IF @DeltaOrigin = 1 AND @DeltaDelete = 039  BEGIN40    SELECT @DeleteMaskForXOrigin = 0x0241  END42  DECLARE @ChangeLimit_internal int43  DECLARE @OnceMore BIT = 044  DECLARE @DBQueueElementsOnceMore QBM_YDBQueueRaw45  DECLARE @UID_TaskOnceMore varchar(38)46  DECLARE @SQLLockIgnore nvarchar(max)47  DECLARE @StartTime datetime = getutcdate()48  DECLARE @ChangeLimitTimeForDelta_string varchar(64)49  DECLARE @SlotNumberSource int50  DECLARE @SlotNumberTarget int51  DECLARE @DBQueueToMove QBM_YDBQCSCurrentToMove52  DECLARE @RowsMoved int53  SET XACT_ABORT OFF54  BEGIN TRY55    IF EXISTS(56      SELECT TOP 1 157      FROM @DBQueueCurrentExtern)58    BEGIN59      INSERT INTO @DBQueueCurrent(UID_DialogDBQueue,60      UID_Parameter,61      UID_SubParameter,62      GenProcID)63      SELECT64        UID_DialogDBQueue,65        UID_Parameter,66        UID_SubParameter,67        GenProcID68      FROM @DBQueueCurrentExtern69    END70    ELSE71    BEGIN72      INSERT INTO @DBQueueCurrent(UID_DialogDBQueue,73      UID_Parameter,74      UID_SubParameter,75      GenProcID)76      SELECT77        UID_DialogDBQueue,78        UID_Parameter,79        UID_SubParameter,80        GenProcID81      FROM QBMDBQueueCurrent cu82        WITH(readpast)83      WHERE84        cu.SlotNumber = @SlotNumber85    END86    SELECT87      @ChangeLimit_internal = dbo.QBM_FCVStringToInt(dbo.QBM_FGISessionContext('CHANGELIMIT'),88      -1111)89    IF @ChangeLimit_internal = -111190    BEGIN91      SELECT92        @ChangeLimit_internal = dbo.QBM_FCVStringToint(dbo.QBM_FGIConfigparmValue('QBM\DBQueue\ChangeLimitDefault'),93        3000)94    END95    IF isnull(@ChangeLimit_internal,96    0) < 1097    BEGIN98      SELECT @ChangeLimit_internal = 1099    END100    INSERT INTO @SourceData(Element,101    AssignedElement,102    HashKey,103    IsUpcommingContent,104    XIsInEffectBefore,105    XIsInEffectAfter,106    XOriginBefore,107    XOriginAfter)108    SELECT109      o.Element,110      o.AssignedElement,111      dbo.QBM_FCVAnyToHash(CONCAT(o.Element, o.AssignedElement)),112      isnull(o.IsUpcommingContent,113      0),114      isnull(o.XIsInEffectBefore,115      0),116      isnull(o.XIsInEffectAfter,117      0),118      isnull(o.XOriginBefore,119      0),120      isnull(o.XOriginAfter,121      0)122    FROM @SourceDataOrig o123    SELECT @Overload_RowCountCurrent = @@ROWCOUNT124    IF @Overload_RowCountCurrent = 0 OR @Switch = 0125    BEGIN126      IF @DebugSwitch > 0127      BEGIN128        print 'na gar nix'129      END130      SELECT131        @CountDeltaQantity = 0,132        @CountDeltaOrigin = 0133        GOTO endLabel134    END135    IF @Overload_RowCountCurrent > @Overload_RowCountLimit136    BEGIN137      SELECT @ChangeLimitReached_String = dbo.QBM_FCVIntToString(@Overload_RowCountCurrent)138      EXEC QBM_PSessionContextSet 'OVERLOADREACHED',139        @ChangeLimitReached_String140      IF @DebugSwitch > 0141      BEGIN142        SELECT143          @DebugMessage = 'am Overload vorbei gekommen, füllstand: ' + str(@Overload_RowCountCurrent)144        EXEC QBM_PJournal @DebugMessage,145          @@procid,146        'D',147          @DebugLevel148      END149      IF @Overload_RowCountCurrent > @Overload_RowCountLimitReset150      BEGIN151        SELECT152          @DebugMessage = CONCAT('#LDS#Rowcount Limit for reset {0} reached with {1}, Task {2} will be resetted.|',153          str(@Overload_RowCountLimitReset),154          '|',155          str(@Overload_RowCountCurrent),156          '|',157          @Overload_UID_TaskToReset,158          '|')159        EXEC QBM_PJournal @DebugMessage,160          @@procid,161        'I',162          @DebugLevel163        DELETE @DBQueueToMove164        INSERT INTO @DBQueueToMove(UID_DialogDBQueue)165        SELECT cul.UID_DialogDBQueue166        FROM @DBQueueCurrent cul167        IF @@ROWCOUNT > 0168        BEGIN169          SELECT @SlotNumberSource = @SlotNumber170          SELECT @SlotnumberTarget = dbo.QBM_FGIDBQueueSlotResetType('Error05')171          EXEC @RowsMoved = QBM_PDBQCS_CurrentMoveSlot @DBQueueToMove,172            @SlotNumberSource,173            @SlotnumberTarget174        END175        GOTO endlabel176      END177    END178    IF @DebugSwitch > 0179    BEGIN180      print '@Switch ' + str(@Switch)181    END182    IF @Switch = 1183    BEGIN184      INSERT INTO @Medium(HashKey,185      IsToInsert,186      IsToDelete,187      IsIntersect,188      IsInheritChanged,189      XOriginNew,190      XIsInEffectNew)191      SELECT192        HashKey,193      CASE IsInsertDeleteIntersect194        WHEN 2 THEN195      1196      ELSE 0197      END AS IsToInsert,198      CASE IsInsertDeleteIntersect199        WHEN 1 THEN200      1201      ELSE 0202      END AS IsToDelete,203      CASE IsInsertDeleteIntersect204        WHEN 3 THEN205      1206      ELSE 0207      END AS IsIntersect,208      0 AS IsInheritChanged,209      0 AS XOriginNew,210      0 AS XIsInEffectNew211      FROM(212      SELECT213        HashKey,214        sum(DISTINCT IsUpcommingContent +1) AS IsInsertDeleteIntersect215      FROM @SourceData s216      GROUP BY s.HashKey) AS x217      WHERE218        IsInsertDeleteIntersect IN(1,219      2)220    END221    IF @Switch = 2222    BEGIN223      INSERT INTO @Medium(HashKey,224      IsToInsert,225      IsToDelete,226      IsIntersect,227      IsInheritChanged,228      XOriginNew,229      XIsInEffectNew)230      SELECT231        HashKey,232        0 AS IsToInsert,233        0 AS IsToDelete,234        1 AS IsIntersect,235        1 AS IsInheritChanged,236        XOriginNew,237        sign(XIsInEffectNew * XOriginNew)238      FROM(239      SELECT240        HashKey,241        sum(DISTINCT IsUpcommingContent +1) | @DeleteMaskForXOrigin AS IsInsertDeleteIntersect,242        max(s.XOriginBefore) AS XOriginOld,243        MAX(s.XOriginAfter & 0x0001) + MAX(s.XOriginAfter & 0x0002) + MAX(s.XOriginAfter & 0x0004) + MAX(s.XOriginAfter & 0x0008) + MAX(s.XOriginAfter244        & 0xfff0) AS XOriginNew,245      CASE @UseIsInEffect246        WHEN 1 THEN247      min(CASE248        WHEN s.IsUpcommingContent = 1 THEN249      sign(s.XIsInEffectAfter)250      ELSE 1251      END)252      ELSE 1253      END AS XIsInEffectNew,254      CASE @UseIsInEffect255        WHEN 1 THEN256      MAX(convert(int, s.XIsInEffectBefore))257      ELSE 1258      END AS XIsInEffectOld259      FROM @SourceData s260      GROUP BY s.HashKey) AS x261      WHERE262        IsInsertDeleteIntersect IN(3) AND((x.XOriginOld ^ x.XOriginNew) +(x.XIsInEffectOld ^ x.XIsInEffectNew) > 0)263    END264    IF @Switch = 3265    BEGIN266      INSERT INTO @Medium(HashKey,267      IsToInsert,268      IsToDelete,269      IsIntersect,270      IsInheritChanged,271      XOriginNew,272      XIsInEffectNew)273      SELECT274        HashKey,275      CASE IsInsertDeleteIntersect276        WHEN 2 THEN277      1278      ELSE 0279      END AS IsToInsert,280      CASE IsInsertDeleteIntersect281        WHEN 1 THEN282      1283        WHEN 3 THEN284      @DeltaDelete *(x.XOriginNew ^1)285      ELSE 0286      END AS IsToDelete,287      CASE IsInsertDeleteIntersect288        WHEN 3 THEN289      1290      ELSE 0291      END AS IsIntersect,292      CASE IsInsertDeleteIntersect293        WHEN 3 THEN294      sign((x.XOriginOld ^ x.XOriginNew) +(x.XIsInEffectOld ^ x.XIsInEffectNew))295      ELSE 0296      END AS IsInheritChanged,297      XOriginNew,298      sign(XIsInEffectNew * XOriginNew)299      FROM(300      SELECT301        HashKey,302        sum(DISTINCT s.IsUpcommingContent +1) | @DeleteMaskForXOrigin AS IsInsertDeleteIntersect,303        max(s.XOriginBefore) AS XOriginOld,304        MAX(s.XOriginAfter & 0x0001) + MAX(s.XOriginAfter & 0x0002) + MAX(s.XOriginAfter & 0x0004) + MAX(s.XOriginAfter & 0x0008) + MAX(s.XOriginAfter305        & 0xfff0) AS XOriginNew,306      CASE @UseIsInEffect307        WHEN 1 THEN308      min(CASE309        WHEN s.IsUpcommingContent = 1 THEN310      sign(s.XIsInEffectAfter)311      ELSE 1312      END)313      ELSE 1314      END AS XIsInEffectNew,315      CASE @UseIsInEffect316        WHEN 1 THEN317      MAX(convert(int, s.XIsInEffectBefore))318      ELSE 1319      END AS XIsInEffectOld320      FROM @SourceData s321      GROUP BY s.HashKey) AS x322      WHERE323        IsInsertDeleteIntersect = 1 OR(IsInsertDeleteIntersect = 2 AND x.XOriginNew > 0) OR((x.XOriginOld ^ x.XOriginNew) +(x.XIsInEffectOld ^ x.XIsInEffectNew324      ) > 0)325      IF @DebugSwitch > 0326      BEGIN327        print 'Zwischenstand von X'328        SELECT329          HashKey,330          sum(DISTINCT s.IsUpcommingContent +1) | @DeleteMaskForXOrigin AS IsInsertDeleteIntersect,331          max(s.XOriginBefore) AS XOriginOld,332          MAX(s.XOriginAfter & 0x01) + MAX(s.XOriginAfter & 0x02) + MAX(s.XOriginAfter & 0x04) + MAX(s.XOriginAfter & 0x08) AS XOriginNew,333        CASE @UseIsInEffect334          WHEN 1 THEN335        min(CASE336          WHEN s.IsUpcommingContent = 1 THEN337        sign(s.XIsInEffectAfter)338        ELSE 1339        END)340        ELSE 1341        END AS XIsInEffectNew,342        CASE @UseIsInEffect343          WHEN 1 THEN344        MAX(convert(int, s.XIsInEffectBefore))345        ELSE 1346        END AS XIsInEffectOld347        FROM @SourceData s348        GROUP BY s.HashKey349      END350    END351    UPDATE @Medium352    SET element = x.element,353    AssignedElement = x.AssignedElement354    FROM @Medium y355    JOIN @sourceData x356      ON y.HashKey = x.HashKey357    WHERE358      y.IsToInsert = 1 AND(@DeltaInsert = 1 OR @DeltaQuantity = 1) OR y.IsToDelete = 1 AND(@DeltaDelete = 1 OR @DeltaQuantity = 1) OR y.IsInheritChanged359    = 1 AND @DeltaOrigin > 0360    IF @DebugSwitch > 0361    BEGIN362      print 'content of @Medium'363      SELECT *364      FROM @Medium365    END366    SELECT @CountDeltaQantity = 0367    SELECT @CountDeltaOrigin = 0368    IF @DeltaDelete =1369    BEGIN370      truncate TABLE #QBMDeltaDelete371      INSERT INTO #QBMDeltaDelete(Element,372      AssignedElement)373      SELECT374        TOP(@ChangeLimit_internal) m.Element,375        m.AssignedElement376      FROM @Medium m377      WHERE378        m.IsToDelete = 1 OR(m.IsInheritChanged = 1 AND m.XOriginNew = 0)379      SELECT @CountItems = @@ROWCOUNT380      IF @CountItems = @ChangeLimit_internal381      BEGIN382        SELECT @OnceMore = 1383      END384      SELECT @CountDeltaQantity += @CountItems385      IF @CountItems > 0 AND @SlotNumber > 0386      BEGIN387        UPDATE #QBMDeltaDelete388        SET GenProcID = c.GenProcID389        FROM #QBMDeltaDelete d390        JOIN @DBQueueCurrent c391          ON d.element = c.UID_Parameter392      END393    END394    IF @DeltaInsert =1395    BEGIN396      truncate TABLE #QBMDeltaInsert397      INSERT INTO #QBMDeltaInsert(Element,398      AssignedElement,399      XOrigin,400      XIsInEffect)401      SELECT402        TOP(@ChangeLimit_internal) m.Element,403        m.AssignedElement,404        m.XOriginNew,405        m.XIsInEffectNew406      FROM @Medium m407      WHERE408        m.IsToInsert = 1409      SELECT @CountItems = @@ROWCOUNT410      IF @CountItems = @ChangeLimit_internal411      BEGIN412        SELECT @OnceMore = 1413      END414      SELECT @CountDeltaQantity += @CountItems415      IF @CountItems > 0 AND @SlotNumber > 0416      BEGIN417        UPDATE #QBMDeltaInsert418        SET GenProcID = c.GenProcID419        FROM #QBMDeltaInsert d420        JOIN @DBQueueCurrent c421          ON d.element = c.UID_Parameter422      END423    END424    IF @DeltaQuantity =1425    BEGIN426      truncate TABLE #QBMDeltaQuantity427      INSERT INTO #QBMDeltaQuantity(Element)428      SELECT429        DISTINCT TOP(@ChangeLimit_internal) m.Element430      FROM @Medium m431      WHERE432        m.IsToDelete = 1 OR m.IsToInsert = 1 OR(m.IsInheritChanged = 1 AND m.XOriginNew = 0)433      SELECT @CountItems = @@ROWCOUNT434      IF @CountItems = @ChangeLimit_internal435      BEGIN436        SELECT @OnceMore = 1437      END438      SELECT @CountDeltaQantity += @CountItems439      IF @CountItems > 0 AND @SlotNumber > 0440      BEGIN441        UPDATE #QBMDeltaQuantity442        SET GenProcID = c.GenProcID443        FROM #QBMDeltaQuantity d444        JOIN @DBQueueCurrent c445          ON d.element = c.UID_Parameter446      END447    END448    IF @DeltaOrigin =1449    BEGIN450      truncate TABLE #QBMDeltaOrigin451      INSERT INTO #QBMDeltaOrigin(Element,452      AssignedElement,453      XOrigin,454      XIsInEffect)455      SELECT456        TOP(@ChangeLimit_internal) m.Element,457        m.AssignedElement,458        m.XOriginNew,459        m.XIsInEffectNew460      FROM @Medium m461      WHERE462        m.IsInheritChanged = 1463      SELECT @CountItems = @@ROWCOUNT464      IF @CountItems = @ChangeLimit_internal465      BEGIN466        SELECT @OnceMore = 1467      END468      SELECT @CountDeltaOrigin += @CountItems469      IF @CountItems > 0 AND @SlotNumber > 0470      BEGIN471        UPDATE #QBMDeltaOrigin472        SET GenProcID = c.GenProcID473        FROM #QBMDeltaOrigin d474        JOIN @DBQueueCurrent c475          ON d.element = c.UID_Parameter476      END477    END478    IF @OnceMore = 1479    BEGIN480      DELETE @DBQueueElementsOnceMore481      INSERT INTO @DBQueueElementsOnceMore(Object,482      SubObject,483      GenProcID)484      SELECT485        cu.UID_Parameter,486        cu.UID_SubParameter,487        cu.GenProcID488      FROM @DBQueueCurrent cu489      SELECT TOP 1 @UID_TaskOnceMore = cu.UID_Task490      FROM QBMDBQueueCurrent cu491        WITH(readpast)492      WHERE493        cu.SlotNumber = @SlotNumber AND cu.UID_Task > ' '494      IF @UID_TaskOnceMore IS NULL495      BEGIN496        SELECT TOP 1 @UID_TaskOnceMore = f.UID_Task497        FROM QBMDBQueueCurrent f498          WITH(readpast)499        WHERE500          f.SlotNumber = @SlotNumber AND f.UID_Task > ' '501        IF @UID_TaskOnceMore IS NULL502        BEGIN503          SELECT TOP 1 @UID_TaskOnceMore = c.UID_Task504          FROM @DBQueueCurrent cu505          JOIN QBMDBQueueCurrent c506            WITH(readpast)507            ON cu.UID_DialogDBQueue = c.UID_DialogDBQueue508          WHERE509            c.UID_Task > ' '510          IF @UID_TaskOnceMore IS NULL511          BEGIN512            SELECT TOP 1 @UID_TaskOnceMore = c.UID_Task513            FROM @DBQueueCurrent cu514            JOIN DialogDBQueue c515              WITH(readpast)516              ON cu.UID_DialogDBQueue = c.UID_DialogDBQueue517            WHERE518              c.UID_Task > ' '519          END520        END521      END522      EXEC QBM_PDBQueueInsert_Bulk @UID_TaskOnceMore,523        @DBQueueElementsOnceMore524      IF @DebugSwitch > 0525      BEGIN526        SELECT527          @DebugMessage = 'restore once more ' + @UID_TaskOnceMore + '@CountDeltaOrigin ' + str(@CountDeltaOrigin) + ' @CountDeltaQantity ' + str528          (@CountDeltaQantity)529        EXEC QBM_PJournal @Debugmessage,530          @@procid,531        'D',532          @DebugLevel533      END534    END535    SELECT536      @ChangeLimitReached_String = dbo.QBM_FCVIntToString(@CountDeltaOrigin + @CountDeltaQantity)537    EXEC QBM_PSessionContextSet 'CHANGELIMITREACHED',538      @ChangeLimitReached_String539    SELECT540      @ChangeLimitTimeForDelta_string = dbo.QBM_FCVIntToString(DATEDIFF(ms, @StartTime, getutcdate()))541    IF DATEDIFF(ms,542    @StartTime,543    getutcdate()) > 10544    BEGIN545      EXEC QBM_PSessionContextSet 'CHANGELIMITTIMEFORDELTA',546        @ChangeLimitTimeForDelta_string547    END548    ELSE549    BEGIN550      EXEC QBM_PSessionContextSet 'CHANGELIMITTIMEFORDELTA',551      ''552    END553  END TRY554  BEGIN CATCH555    EXEC QBM_PSessionErrorAdd DEFAULT556    DECLARE @Rethrow varchar(1000) = dbo.QBM_FGISessionErrorRethrow()557    RAISERROR(@Rethrow,558    18,559    1)560      WITH NOWAIT561  END CATCH562  endLabel:563  RETURN564END
Open raw exported source
SQL · Raw84 lines
1  create   procedure QBM_PDBQueueCalculateDelta ( @SourceDataOrig QBM_YDataForDelta readonly  , @SlotNumber int , @DeltaQuantity bit , @DeltaDelete2 bit , @DeltaInsert bit , @DeltaOrigin bit , @CountDeltaQantity  int output , @CountDeltaOrigin  int output , @UseIsInEffect bit    , @DBQueueCurrentExtern3 QBM_YDBQueueCurrent readonly       ) as begin declare @DBQueueCurrent QBM_YDBQueueCurrent declare @Overload_RowCountCurrent int declare @Overload_RowCountLimit4 int = dbo.QBM_FCVStringToInt(dbo.QBM_FGIConfigparmValue('QBM\DBQueue\OverloadLimit'), 200000) declare @Overload_RowCountLimitReset int = @Overload_RowCountLimit5 * 6 declare @Overload_UID_TaskToReset varchar(38) declare @Overload_ElementsInSlot int declare @Overload_Maxbulk int declare @Overload_IsBulkEnabled bit6  declare @ChangeLimitReached_String varchar(64) declare @Medium QBM_YDBQueueIntermediate declare @CountItems int  declare @DebugSwitch int = 0 declare7 @DebugLevel char(1) = 'W' declare @DebugMessage nvarchar(1000) declare @SourceData QBM_YDataForDelta declare @ErrorMessage nvarchar(4000) declare @ErrorSeverity8 int declare @ErrorState int  declare @Switch int = 0 select @Switch += (@DeltaQuantity | @DeltaInsert | @DeltaDelete) select @Switch += @DeltaOrigin *9 2   declare @DeleteMaskForXOrigin int = 0 if @DeltaOrigin = 1 and @DeltaDelete = 0  begin select @DeleteMaskForXOrigin = 0x02 end declare @ChangeLimit_internal10 int declare @OnceMore bit = 0 declare @DBQueueElementsOnceMore QBM_YDBQueueRaw declare @UID_TaskOnceMore varchar(38) declare @SQLLockIgnore nvarchar(max11)  declare @StartTime datetime = getutcdate() declare @ChangeLimitTimeForDelta_string varchar(64) declare @SlotNumberSource int declare @SlotNumberTarget12 int declare @DBQueueToMove QBM_YDBQCSCurrentToMove declare @RowsMoved int SET XACT_ABORT OFF BEGIN TRY if exists (select top 1 1 from @DBQueueCurrentExtern13 ) begin insert into @DBQueueCurrent(UID_DialogDBQueue, UID_Parameter, UID_SubParameter, GenProcID) select UID_DialogDBQueue, UID_Parameter, UID_SubParameter14, GenProcID from @DBQueueCurrentExtern end else begin insert into @DBQueueCurrent(UID_DialogDBQueue, UID_Parameter, UID_SubParameter, GenProcID) select15 UID_DialogDBQueue, UID_Parameter, UID_SubParameter, GenProcID from QBMDBQueueCurrent cu with (readpast) where cu.SlotNumber = @SlotNumber end select @ChangeLimit_internal16 = dbo.QBM_FCVStringToInt( dbo.QBM_FGISessionContext('CHANGELIMIT'), -1111) if @ChangeLimit_internal = -1111 begin select @ChangeLimit_internal = dbo.QBM_FCVStringToint17( dbo.QBM_FGIConfigparmValue('QBM\DBQueue\ChangeLimitDefault'), 3000)  end if isnull(@ChangeLimit_internal, 0) < 10 begin select @ChangeLimit_internal 18= 10 end insert into @SourceData(Element, AssignedElement ,HashKey , IsUpcommingContent , XIsInEffectBefore, XIsInEffectAfter , XOriginBefore, XOriginAfter19 )     select o.Element, o.AssignedElement , dbo.QBM_FCVAnyToHash(concat(o.Element, o.AssignedElement)) , isnull(o.IsUpcommingContent, 0) , isnull(o.XIsInEffectBefore20, 0), isnull(o.XIsInEffectAfter, 0) , isnull(o.XOriginBefore, 0), isnull(o.XOriginAfter, 0) from @SourceDataOrig o select @Overload_RowCountCurrent = @@ROWCOUNT21 if @Overload_RowCountCurrent = 0 or @Switch = 0 begin if @DebugSwitch > 0 begin print 'na gar nix' end select @CountDeltaQantity = 0 , @CountDeltaOrigin22 = 0 goto endLabel end if @Overload_RowCountCurrent > @Overload_RowCountLimit  begin   select @ChangeLimitReached_String = dbo.QBM_FCVIntToString(@Overload_RowCountCurrent23) exec QBM_PSessionContextSet 'OVERLOADREACHED', @ChangeLimitReached_String if @DebugSwitch > 0 begin select @DebugMessage = 'am Overload vorbei gekommen, füllstand: '24 + str(@Overload_RowCountCurrent) exec QBM_PJournal @DebugMessage, @@procid, 'D', @DebugLevel end    if @Overload_RowCountCurrent > @Overload_RowCountLimitReset25  begin select @DebugMessage = concat('#LDS#Rowcount Limit for reset {0} reached with {1}, Task {2} will be resetted.|' , str(@Overload_RowCountLimitReset26) , '|', str(@Overload_RowCountCurrent), '|', @Overload_UID_TaskToReset , '|') exec QBM_PJournal @DebugMessage, @@procid, 'I', @DebugLevel delete @DBQueueToMove27  insert into @DBQueueToMove(UID_DialogDBQueue) select cul.UID_DialogDBQueue from @DBQueueCurrent cul  if @@ROWCOUNT > 0 begin select @SlotNumberSource28 = @SlotNumber  select @SlotnumberTarget = dbo.QBM_FGIDBQueueSlotResetType('Error05') exec @RowsMoved = QBM_PDBQCS_CurrentMoveSlot @DBQueueToMove, @SlotNumberSource29, @SlotnumberTarget    end   goto endlabel end  end  if @DebugSwitch > 0 begin print '@Switch ' + str(@Switch) end if @Switch = 1 begin    insert into 30@Medium(HashKey, IsToInsert, IsToDelete, IsIntersect, IsInheritChanged, XOriginNew, XIsInEffectNew) select HashKey ,case IsInsertDeleteIntersect when 231 then 1 else 0 end as IsToInsert , case IsInsertDeleteIntersect when 1 then 1 else 0 end as IsToDelete , case IsInsertDeleteIntersect when 3 then 1 else32 0 end as IsIntersect , 0 as IsInheritChanged , 0 as XOriginNew , 0 as XIsInEffectNew from ( select HashKey , sum( distinct IsUpcommingContent +1) as IsInsertDeleteIntersect33  from @SourceData s group by s.HashKey ) as x where IsInsertDeleteIntersect in (1, 2 )  end if @Switch = 2 begin    insert into @Medium(HashKey, IsToInsert34, IsToDelete, IsIntersect, IsInheritChanged, XOriginNew, XIsInEffectNew) select HashKey , 0 as IsToInsert , 0 as IsToDelete , 1 as IsIntersect , 1 as IsInheritChanged35 , XOriginNew , sign(XIsInEffectNew * XOriginNew )  from ( select HashKey , sum( distinct IsUpcommingContent +1) | @DeleteMaskForXOrigin as IsInsertDeleteIntersect36 , max( s.XOriginBefore) as XOriginOld , MAX(s.XOriginAfter & 0x0001) + MAX(s.XOriginAfter & 0x0002) + MAX(s.XOriginAfter & 0x0004) + MAX(s.XOriginAfter37 & 0x0008)   + MAX(s.XOriginAfter & 0xfff0) as XOriginNew  , case @UseIsInEffect when 1 then min(case when s.IsUpcommingContent = 1 then sign(s.XIsInEffectAfter38) else 1 end ) else 1 end as XIsInEffectNew , case @UseIsInEffect when 1 then MAX(convert(int, s.XIsInEffectBefore)) else 1 end as XIsInEffectOld from 39@SourceData s group by s.HashKey ) as x where IsInsertDeleteIntersect in (3 )  and ((x.XOriginOld ^ x.XOriginNew) + (x.XIsInEffectOld ^ x.XIsInEffectNew40) > 0)  end if @Switch = 3 begin    insert into @Medium(HashKey, IsToInsert, IsToDelete, IsIntersect, IsInheritChanged, XOriginNew, XIsInEffectNew) select41 HashKey ,case IsInsertDeleteIntersect when 2 then 1 else 0 end as IsToInsert , case IsInsertDeleteIntersect when 1 then 1 when 3 then @DeltaDelete * (x.XOriginNew42 ^1)  else 0 end as IsToDelete , case IsInsertDeleteIntersect when 3 then 1 else 0 end as IsIntersect , case IsInsertDeleteIntersect when 3 then sign((x.XOriginOld43 ^ x.XOriginNew) + (x.XIsInEffectOld ^ x.XIsInEffectNew) ) else 0 end as IsInheritChanged , XOriginNew , sign(XIsInEffectNew * XOriginNew )  from ( select44 HashKey , sum( distinct s.IsUpcommingContent +1) | @DeleteMaskForXOrigin as IsInsertDeleteIntersect , max( s.XOriginBefore) as XOriginOld , MAX(s.XOriginAfter45 & 0x0001) + MAX(s.XOriginAfter & 0x0002) + MAX(s.XOriginAfter & 0x0004) + MAX(s.XOriginAfter & 0x0008)   + MAX(s.XOriginAfter & 0xfff0) as XOriginNew 46 , case @UseIsInEffect when 1 then min(case when s.IsUpcommingContent = 1 then sign(s.XIsInEffectAfter) else 1 end ) else 1 end as XIsInEffectNew , case47 @UseIsInEffect when 1 then MAX(convert(int, s.XIsInEffectBefore)) else 1 end as XIsInEffectOld from @SourceData s group by s.HashKey ) as x where IsInsertDeleteIntersect48 = 1  or (IsInsertDeleteIntersect = 2 and x.XOriginNew > 0)  or ((x.XOriginOld ^ x.XOriginNew) + (x.XIsInEffectOld ^ x.XIsInEffectNew) > 0)  if @DebugSwitch49 > 0 begin print 'Zwischenstand von X' select HashKey , sum( distinct s.IsUpcommingContent +1) | @DeleteMaskForXOrigin as IsInsertDeleteIntersect , max50( s.XOriginBefore) as XOriginOld , MAX(s.XOriginAfter & 0x01) + MAX(s.XOriginAfter & 0x02) + MAX(s.XOriginAfter & 0x04) + MAX(s.XOriginAfter & 0x08) as51 XOriginNew  , case @UseIsInEffect when 1 then min(case when s.IsUpcommingContent = 1 then sign(s.XIsInEffectAfter) else 1 end ) else 1 end as XIsInEffectNew52 , case @UseIsInEffect when 1 then MAX(convert(int, s.XIsInEffectBefore)) else 1 end as XIsInEffectOld from @SourceData s group by s.HashKey end end  update53 @Medium set element = x.element , AssignedElement = x.AssignedElement  from @Medium y join @sourceData x on y.HashKey = x.HashKey  where y.IsToInsert 54= 1 and (@DeltaInsert = 1 or @DeltaQuantity = 1) or y.IsToDelete = 1 and (@DeltaDelete = 1 or @DeltaQuantity = 1) or y.IsInheritChanged = 1 and @DeltaOrigin55 > 0 if @DebugSwitch > 0 begin print 'content of @Medium' select * from @Medium end select @CountDeltaQantity = 0 select @CountDeltaOrigin = 0   if @DeltaDelete56 =1 begin    truncate table #QBMDeltaDelete insert into #QBMDeltaDelete (Element, AssignedElement) select top (@ChangeLimit_internal)  m.Element, m.AssignedElement57 from @Medium m  where m.IsToDelete = 1  or ( m.IsInheritChanged = 1 and m.XOriginNew = 0 ) select @CountItems = @@ROWCOUNT  if @CountItems = @ChangeLimit_internal58 begin select @OnceMore = 1 end  select @CountDeltaQantity += @CountItems if @CountItems > 0 and @SlotNumber > 0 begin  update #QBMDeltaDelete set GenProcID59 = c.GenProcID from #QBMDeltaDelete d join @DBQueueCurrent c on d.element = c.UID_Parameter end end   if @DeltaInsert =1 begin    truncate table #QBMDeltaInsert60 insert into #QBMDeltaInsert (Element, AssignedElement, XOrigin, XIsInEffect) select top (@ChangeLimit_internal)  m.Element, m.AssignedElement, m.XOriginNew61, m.XIsInEffectNew from @Medium m  where m.IsToInsert = 1 select @CountItems = @@ROWCOUNT  if @CountItems = @ChangeLimit_internal begin select @OnceMore62 = 1 end  select @CountDeltaQantity += @CountItems if @CountItems > 0 and @SlotNumber > 0 begin  update #QBMDeltaInsert set GenProcID = c.GenProcID from63 #QBMDeltaInsert d join @DBQueueCurrent c on d.element = c.UID_Parameter end end if @DeltaQuantity =1 begin    truncate table #QBMDeltaQuantity insert 64into #QBMDeltaQuantity (Element) select distinct top (@ChangeLimit_internal) m.Element from @Medium m  where m.IsToDelete = 1 or m.IsToInsert = 1  or (65 m.IsInheritChanged = 1 and m.XOriginNew = 0 ) select @CountItems = @@ROWCOUNT  if @CountItems = @ChangeLimit_internal begin select @OnceMore = 1 end  66select @CountDeltaQantity += @CountItems  if @CountItems > 0 and @SlotNumber > 0 begin  update #QBMDeltaQuantity set GenProcID = c.GenProcID from #QBMDeltaQuantity67 d join @DBQueueCurrent c on d.element = c.UID_Parameter end end   if @DeltaOrigin =1  begin    truncate table #QBMDeltaOrigin insert into #QBMDeltaOrigin68 (Element, AssignedElement, XOrigin, XIsInEffect) select top (@ChangeLimit_internal)  m.Element, m.AssignedElement, m.XOriginNew, m.XIsInEffectNew from69 @Medium m  where m.IsInheritChanged = 1 select @CountItems = @@ROWCOUNT  if @CountItems = @ChangeLimit_internal begin select @OnceMore = 1 end  select70 @CountDeltaOrigin += @CountItems if @CountItems > 0 and @SlotNumber > 0 begin  update #QBMDeltaOrigin set GenProcID = c.GenProcID from #QBMDeltaOrigin71 d join @DBQueueCurrent c on d.element = c.UID_Parameter end end    if @OnceMore = 1 begin delete  @DBQueueElementsOnceMore  insert into @DBQueueElementsOnceMore72(Object, SubObject, GenProcID) select cu.UID_Parameter, cu.UID_SubParameter, cu.GenProcID from @DBQueueCurrent cu select top 1 @UID_TaskOnceMore = cu.UID_Task73 from QBMDBQueueCurrent cu with (readpast)  where cu.SlotNumber = @SlotNumber and cu.UID_Task > ' '  if @UID_TaskOnceMore is null begin select top 1 @UID_TaskOnceMore74 = f.UID_Task from QBMDBQueueCurrent  f with (readpast) where f.SlotNumber = @SlotNumber and f.UID_Task > ' '  if @UID_TaskOnceMore is null begin  select75 top 1 @UID_TaskOnceMore = c.UID_Task from @DBQueueCurrent cu join QBMDBQueueCurrent c with (readpast) on cu.UID_DialogDBQueue = c.UID_DialogDBQueue where76 c.UID_Task > ' ' if @UID_TaskOnceMore is null begin  select top 1 @UID_TaskOnceMore = c.UID_Task from @DBQueueCurrent cu join DialogDBQueue c with (readpast77) on cu.UID_DialogDBQueue = c.UID_DialogDBQueue where c.UID_Task > ' ' end  end  end  exec QBM_PDBQueueInsert_Bulk @UID_TaskOnceMore, @DBQueueElementsOnceMore78 if @DebugSwitch > 0 begin select @DebugMessage = 'restore once more ' + @UID_TaskOnceMore + '@CountDeltaOrigin ' + str(@CountDeltaOrigin) + ' @CountDeltaQantity '79 + str(@CountDeltaQantity) exec QBM_PJournal @Debugmessage, @@procid, 'D', @DebugLevel end    end  select @ChangeLimitReached_String = dbo.QBM_FCVIntToString80(@CountDeltaOrigin + @CountDeltaQantity) exec QBM_PSessionContextSet 'CHANGELIMITREACHED', @ChangeLimitReached_String select @ChangeLimitTimeForDelta_string81 = dbo.QBM_FCVIntToString(DATEDIFF(ms, @StartTime, getutcdate())) if DATEDIFF(ms, @StartTime, getutcdate()) > 10 begin exec QBM_PSessionContextSet 'CHANGELIMITTIMEFORDELTA'82, @ChangeLimitTimeForDelta_string end else begin exec QBM_PSessionContextSet 'CHANGELIMITTIMEFORDELTA', ''  end END TRY BEGIN CATCH exec QBM_PSessionErrorAdd83 default declare @Rethrow varchar(1000) = dbo.QBM_FGISessionErrorRethrow() RAISERROR (@Rethrow, 18, 1) WITH NOWAIT END CATCH endLabel: return end 84