Back to OIM Explorer

dbo.QBM_PDBQCS_CurrentMoveSlot

Stored ProcedureSQL_STORED_PROCEDURESandbox DB

Stored Procedure.

Source: sandbox-db sys.sql_modules

Source size: 6.463 characters

Interpretation

  • Database routine. Review parameters, called procedures, DBQueue inserts, and QBM_PJobCreate helper calls before assuming side effects.

Relations

  • No extracted relations.

Typed Edges

  • references source dbo.QBM_FCVDatetimeToString source text reference
  • references source dbo.QBM_FCVFloatToString source text reference
  • references source dbo.QBM_FGISessionErrorCount source text reference
  • references source dbo.QBM_FGISessionErrorIsDeadlock source text reference
  • references source dbo.QBM_FGISessionErrorRethrow source text reference
  • references source dbo.QBM_PJournal source text reference
  • references source dbo.QBM_PSessionErrorAdd source text reference
  • references source dbo.QBM_PSessionErrorClean source text reference
  • references source dbo.QBM_PWaitForSeconds source text reference

References

Referenced By

Complete Source

SQL297 lines
1CREATE PROCEDURE QBM_PDBQCS_CurrentMoveSlot(2  @EntriesToMove QBM_YDBQCSCurrentToMove READONLY,3  @SlotNumberSource int,4  @SlotNumberTarget int5)6AS7BEGIN8  DECLARE @LockTimeout_ms int = 500,9  @MaxWaitTimeForLock_s float = 20.0,10  @HandleErrorSilent BIT = 0,11  @DeadlockPriority int = -512  DECLARE @SQLCmd nvarchar(max)13  DECLARE @RowsMoved int = 014  DECLARE @Rethrow varchar(1000) = dbo.QBM_FGISessionErrorRethrow()15  DECLARE @Starttime datetime = getutcdate()16  DECLARE @CurrentDeadlock_priority int17  DECLARE @waittime float18  DECLARE @Success BIT = 019  DECLARE @ErrorMessage nvarchar(4000)20  DECLARE @ErrorSeverity int21  DECLARE @ErrorState int22  DECLARE @TransactionCount int23  DECLARE @erg int = 024  DECLARE @MyName nvarchar(256) = object_name(@@procid)25  DECLARE @VersuchNummer int = 126  DECLARE @FinalMessage nvarchar(1000) = '#LDS#No success after all retries.|'27  DECLARE @JournalMessage nvarchar(max)28  DECLARE @ExecuteWithTransact BIT = 129  DECLARE @ErrorBuffer QBM_YSessionError30  DECLARE @tran1 int31  DECLARE @tran2 int32  DECLARE @CountEntriesSessionError int33  DECLARE @DebugSwitch int = 034  DECLARE @DebugLevel varchar(1) = 'W'35  DECLARE @OurOwnMessage nvarchar(max)36  DECLARE @FinalMisserfolg nvarchar(1000) = '#LDS#No success after all retries.|'37  DECLARE @DebugRetries int = -138  DECLARE @DebugWaitSum float = 0.039  SET XACT_ABORT OFF40  BEGIN TRY41    IF @DebugSwitch = 2 print ' trancount eingang' + str(@@trancount)42    IF @ExecuteWithTransact = 143    BEGIN44      BEGIN45        TRANSACTION46      END47      IF @DebugSwitch = 2 print ' trancount eingang 2' + str(@@trancount)48      SET nocount49        ON50      SELECT TOP 1 @CurrentDeadlock_priority = s.deadlock_priority51      FROM sys.dm_exec_sessions s52      WHERE53        s.session_id = @@spid54      SELECT @TransactionCount = @@trancount55      SET deadlock_priority @DeadlockPriority56      SELECT57        @SQLCmd = CONCAT('set lock_timeout ',58        str(@LockTimeout_ms),59        ';',60        '61SET XACT_ABORT OFF;62update QBMDBQueueCurrent63	set Slotnumber = ',64        str(@SlotnumberTarget),65        '66	from QBMDBQueueCurrent cu join @EntriesToMove mo on cu.UID_DialogDBQueue = mo.UID_DialogDBQueue67	where ',68      CASE69        WHEN @SlotNumberSource >= 0 THEN70      ' cu.Slotnumber = @SlotNumberSource'71      ELSE 'cu.Slotnumber < 0 '72      END,73      '74							')75      IF @DebugSwitch = 2 print @SQLCmd76      SELECT @CountEntriesSessionError = dbo.QBM_FGISessionErrorCount()77      WHILE @Success = 0 AND convert(float,78      datediff(ms, @starttime, getutcdate())) < @MaxWaitTimeForLock_s * 1000.079      BEGIN80        SELECT @waittime =(RAND() / 2.0) + 0.0581        SELECT @DebugRetries += 182        IF @ExecuteWithTransact = 183        BEGIN84          save TRANSACTION IchVersuche85        END86        BEGIN TRY87          IF @DebugSwitch = 2 print ' trancount vor execute ' + str(@@trancount)88          SELECT @tran1 = @@TRANCOUNT89          EXEC sp_executesql @SQLCmd,90          N '@EntriesToMove QBM_YDBQCSCurrentToMove  readonly, @SlotNumberSource int',91            @EntriesToMove=@EntriesToMove,92            @SlotNumberSource = @SlotNumberSource93          SELECT @RowsMoved = @@ROWCOUNT94          SELECT @Success = 195        END TRY96        BEGIN CATCH97          IF @DebugSwitch = 2 print 'catch 1'98          IF @DebugSwitch = 2 print ' trancount catch 1' + str(@@trancount)99          IF @DebugSwitch = 2 print ERROR_MESSAGE()100          SELECT @tran2 = @@TRANCOUNT101          IF @tran1 = @tran2 -1102          BEGIN103            COMMIT TRANSACTION104          END105          DELETE @ErrorBuffer106          INSERT INTO @ErrorBuffer(ErrorMessage,107          ErrorSeverity,108          ErrorState,109          ErrorNumber,110          ProcedureName,111          ProcedureLine,112          MessageDate,113          GenProcID,114          RepeatCounter,115          IsReThrow,116          SourceCode)117          SELECT118            ERROR_MESSAGE(),119            ERROR_SEVERITY(),120            ERROR_STATE(),121            ERROR_NUMBER(),122            @MyName,123            ERROR_LINE(),124            GETUTCDATE(),125            NULL,126            0,127            0,128            @SQLCmd129          EXEC QBM_PSessionErrorAdd @ErrorBuffer130        END CATCH131        IF @Success = 0132        BEGIN133          IF @DebugSwitch = 2 print '@Success ' + str(@Success)134          BEGIN TRY135            IF @DebugSwitch = 2 print 'try 1'136            IF @DebugSwitch = 2 print ' trancount 1' + str(@@trancount)137            IF @ExecuteWithTransact = 1138            BEGIN139              ROLLBACK TRANSACTION IchVersuche140            END141            IF @DebugSwitch = 2 print 'try 2'142            IF @DebugSwitch = 2 print ' trancount 2' + str(@@trancount)143          END TRY144          BEGIN CATCH145            IF @DebugSwitch = 2 print 'catch 2'146          END CATCH147        END148        IF @Success = 1149        BEGIN150          IF @DebugSwitch > 0151          BEGIN152            print dbo.QBM_FCVDatetimeToString(GETUTCDATE()) + ' Versuch geschafft'153          END154        END155        ELSE156        BEGIN157          IF dbo.QBM_FGISessionErrorIsDeadlock(@ErrorBuffer) = 1158          BEGIN159            EXEC QBM_PWaitForSeconds @waittime160            SELECT @DebugWaitSum += @waittime161          END162          ELSE163          BEGIN164            SELECT @Success = -1 CONTINUE165          END166        END167        IF @TransactionCount <> @@TRANCOUNT AND @ExecuteWithTransact = 1168        BEGIN169          IF @DebugSwitch = 2 print 'transaction Differenz ' + str(@TransactionCount) + str(@@TRANCOUNT)170          SELECT171            @OurOwnMessage = CONCAT('#LDS#Expected transaction count {0} does not match existing {1}, called from {2}.|',172            str(isnull(@TransactionCount, 0)),173            '|',174            str(@@TRANCOUNT),175            '|',176            @MyName,177            '|')178          DELETE @ErrorBuffer179          INSERT INTO @ErrorBuffer(ErrorMessage,180          ErrorSeverity,181          ErrorState,182          ErrorNumber,183          ProcedureName,184          ProcedureLine,185          MessageDate,186          GenProcID,187          RepeatCounter,188          IsReThrow,189          SourceCode)190          SELECT191            @OurOwnMessage,192            18,193            1,194            50000,195            @MyName,196            0,197            GETUTCDATE(),198            NULL,199            0,200            0,201            @SQLCmd202          EXEC QBM_PSessionErrorAdd @ErrorBuffer203          RAISERROR(@OurOwnMessage,204          18,205          1)206            WITH NOWAIT207        END208        SELECT @VersuchNummer += 1209      END210      IF @ExecuteWithTransact = 1211      BEGIN212        COMMIT TRANSACTION213      END214      IF @Success IN(0,215      -1)216      BEGIN217        IF @Success = 0218        BEGIN219          DELETE @ErrorBuffer220          INSERT INTO @ErrorBuffer(ErrorMessage,221          ErrorSeverity,222          ErrorState,223          ErrorNumber,224          ProcedureName,225          ProcedureLine,226          MessageDate,227          GenProcID,228          RepeatCounter,229          IsReThrow,230          SourceCode)231          SELECT232            @FinalMisserfolg,233            18,234            1,235            50000,236            @MyName,237            229,238            GETUTCDATE(),239            NULL,240            0,241            0,242            @SQLCmd243          EXEC QBM_PSessionErrorAdd @ErrorBuffer244          SELECT @OurOwnMessage = @FinalMisserfolg245        END246        ELSE247        BEGIN248          SELECT @OurOwnMessage = '#LDS#Error exececuting direct sql command.|'249        END250        IF @HandleErrorSilent = 0251        BEGIN252          IF @DebugSwitch = 2 print 'raise ' + @OurOwnMessage253          RAISERROR(@OurOwnMessage,254          18,255          1)256            WITH NOWAIT257        END258      END259      ELSE260      BEGIN261        WHILE dbo.QBM_FGISessionErrorCount() > @CountEntriesSessionError262        BEGIN263          EXEC QBM_PSessionErrorClean @LastEntryOnly = 1264        END265      END266    END TRY267    BEGIN CATCH268      EXEC QBM_PSessionErrorAdd DEFAULT,269        @SQLCmd270      RAISERROR(@Rethrow,271      18,272      1)273        WITH NOWAIT274    END CATCH275    endLabel:276    IF @DebugRetries > 0 OR @SlotNumberSource < 0 OR @SlotNumberTarget < 0277    BEGIN278      SELECT279        @ErrorMessage = CONCAT(OBJECT_NAME(@@procid),280        ' ',281        ' retries',282        RIGHT(str(@DebugRetries), 5),283        ' waitsum ',284        dbo.QBM_FCVFloatToString(@DebugWaitSum),285        ' FromSlot',286        RIGHT(str(@SlotNumberSource), 5),287        ' ToSlot',288        RIGHT(str(@SlotNumberTarget), 5),289        ' RowsMoved',290        RIGHT(str(@RowsMoved), 5))291      EXEC QBM_PJournal @ErrorMessage,292        @@procid,293      'T',294        @DebugLevel295    END296    RETURN @RowsMoved297  END
Open raw exported source
SQL ยท Raw48 lines
1   create   procedure QBM_PDBQCS_CurrentMoveSlot( @EntriesToMove QBM_YDBQCSCurrentToMove readonly , @SlotNumberSource int  , @SlotNumberTarget int2    ) as begin declare  @LockTimeout_ms int = 500 , @MaxWaitTimeForLock_s float = 20.0  , @HandleErrorSilent bit = 0  , @DeadlockPriority int = -5  declare3 @SQLCmd nvarchar(max) declare @RowsMoved int = 0 declare @Rethrow varchar(1000) = dbo.QBM_FGISessionErrorRethrow() declare @Starttime datetime = getutcdate4() declare @CurrentDeadlock_priority int declare @waittime float declare @Success bit = 0  declare @ErrorMessage nvarchar(4000) declare @ErrorSeverity 5int declare @ErrorState int declare @TransactionCount int declare @erg int = 0 declare @MyName nvarchar(256) = object_name(@@procid) declare @VersuchNummer6 int = 1 declare @FinalMessage nvarchar(1000) = '#LDS#No success after all retries.|' declare @JournalMessage nvarchar(max) declare @ExecuteWithTransact7 bit = 1  declare @ErrorBuffer QBM_YSessionError declare @tran1 int declare @tran2 int declare @CountEntriesSessionError int declare @DebugSwitch int =8 0 declare @DebugLevel varchar(1) = 'W' declare @OurOwnMessage nvarchar(max) declare @FinalMisserfolg nvarchar(1000) = '#LDS#No success after all retries.|'9 declare @DebugRetries int = -1 declare @DebugWaitSum float = 0.0 SET XACT_ABORT OFF BEGIN TRY if @DebugSwitch = 2 print ' trancount eingang' + str(@@trancount10) if @ExecuteWithTransact = 1 begin begin transaction  end if @DebugSwitch = 2 print ' trancount eingang 2' + str(@@trancount) set nocount on select top11 1 @CurrentDeadlock_priority = s.deadlock_priority FROM sys.dm_exec_sessions s where s.session_id = @@spid select @TransactionCount = @@trancount set deadlock_priority12 @DeadlockPriority     select @SQLCmd = concat('set lock_timeout ' , str(@LockTimeout_ms) , ';', '13SET XACT_ABORT OFF;14update QBMDBQueueCurrent15	set Slotnumber = '16, str(@SlotnumberTarget) , '17	from QBMDBQueueCurrent cu join @EntriesToMove mo on cu.UID_DialogDBQueue = mo.UID_DialogDBQueue18	where ' ,case when @SlotNumberSource19 >= 0 then ' cu.Slotnumber = @SlotNumberSource'  else 'cu.Slotnumber < 0 '  end , '20							') if @DebugSwitch = 2 print @SQLCmd  select @CountEntriesSessionError21 = dbo.QBM_FGISessionErrorCount() while @Success = 0 and convert(float, datediff(ms, @starttime, getutcdate())) < @MaxWaitTimeForLock_s * 1000.0 begin 22select @waittime = (RAND() / 2.0 ) + 0.05 select @DebugRetries += 1 if @ExecuteWithTransact = 1 begin save transaction IchVersuche end BEGIN TRY if @DebugSwitch23 = 2 print ' trancount vor execute ' + str(@@trancount)  select @tran1 = @@TRANCOUNT     exec sp_executesql @SQLCmd , N'@EntriesToMove QBM_YDBQCSCurrentToMove  readonly, @SlotNumberSource int'24 , @EntriesToMove=@EntriesToMove , @SlotNumberSource = @SlotNumberSource select @RowsMoved = @@ROWCOUNT    select @Success = 1 END TRY BEGIN CATCH if @DebugSwitch25 = 2 print 'catch 1' if @DebugSwitch = 2 print ' trancount catch 1' + str(@@trancount) if @DebugSwitch = 2 print ERROR_MESSAGE() select @tran2 = @@TRANCOUNT26 if @tran1 = @tran2 -1  begin commit transaction end delete @ErrorBuffer    insert into @ErrorBuffer (ErrorMessage, ErrorSeverity, ErrorState, ErrorNumber27 , ProcedureName, ProcedureLine, MessageDate , GenProcID, RepeatCounter, IsReThrow, SourceCode) select ERROR_MESSAGE(),ERROR_SEVERITY(), ERROR_STATE(),28 ERROR_NUMBER() , @MyName , ERROR_LINE(), GETUTCDATE() , null, 0, 0, @SQLCmd exec QBM_PSessionErrorAdd @ErrorBuffer END CATCH if @Success = 0 begin if 29@DebugSwitch = 2 print '@Success ' + str(@Success) BEGIN TRY if @DebugSwitch = 2 print 'try 1' if @DebugSwitch = 2 print ' trancount 1' + str(@@trancount30) if @ExecuteWithTransact = 1 begin rollback transaction IchVersuche end if @DebugSwitch = 2 print 'try 2' if @DebugSwitch = 2 print ' trancount 2' + str31(@@trancount) END TRY BEGIN CATCH if @DebugSwitch = 2 print 'catch 2'  END CATCH end  if @Success = 1 begin if @DebugSwitch > 0 begin print dbo.QBM_FCVDatetimeToString32(GETUTCDATE()) + ' Versuch geschafft' end end else begin if dbo.QBM_FGISessionErrorIsDeadlock(@ErrorBuffer) = 1 begin exec QBM_PWaitForSeconds @waittime33 select @DebugWaitSum += @waittime end else begin   select @Success = -1 continue end end  if @TransactionCount <> @@TRANCOUNT and @ExecuteWithTransact34 = 1 begin if @DebugSwitch = 2 print 'transaction Differenz ' + str(@TransactionCount) + str(@@TRANCOUNT) select @OurOwnMessage = concat('#LDS#Expected transaction count {0} does not match existing {1}, called from {2}.|'35 , str(isnull(@TransactionCount, 0)) , '|' , str( @@TRANCOUNT) , '|' , @MyName , '|' ) delete @ErrorBuffer insert into @ErrorBuffer (ErrorMessage, ErrorSeverity36, ErrorState, ErrorNumber , ProcedureName, ProcedureLine, MessageDate , GenProcID, RepeatCounter, IsReThrow, SourceCode) select @OurOwnMessage,18, 1, 5000037 , @MyName, 0, GETUTCDATE() , null, 0, 0, @SQLCmd exec QBM_PSessionErrorAdd @ErrorBuffer RAISERROR (@OurOwnMessage, 18, 1) WITH NOWAIT end select @VersuchNummer38 += 1 end  if @ExecuteWithTransact = 1 begin commit transaction  end  if @Success in ( 0, -1) begin   if @Success = 0  begin delete @ErrorBuffer insert39 into @ErrorBuffer (ErrorMessage, ErrorSeverity, ErrorState, ErrorNumber , ProcedureName, ProcedureLine, MessageDate , GenProcID, RepeatCounter, IsReThrow40, SourceCode) select @FinalMisserfolg, 18, 1, 50000 , @MyName, 229, GETUTCDATE() , null, 0, 0, @SQLCmd exec QBM_PSessionErrorAdd @ErrorBuffer select @OurOwnMessage41 = @FinalMisserfolg end else  begin select @OurOwnMessage = '#LDS#Error exececuting direct sql command.|' end if @HandleErrorSilent = 0 begin if @DebugSwitch42 = 2 print 'raise ' + @OurOwnMessage RAISERROR (@OurOwnMessage, 18, 1) WITH NOWAIT end end else begin   while dbo.QBM_FGISessionErrorCount() > @CountEntriesSessionError43 begin exec QBM_PSessionErrorClean @LastEntryOnly = 1 end end END TRY BEGIN CATCH exec QBM_PSessionErrorAdd default , @SQLCmd RAISERROR (@Rethrow, 18, 441) WITH NOWAIT END CATCH endLabel: if @DebugRetries > 0 or @SlotNumberSource < 0 or @SlotNumberTarget < 0 begin select @ErrorMessage = concat( OBJECT_NAME45(@@procid), ' ' , ' retries' , right(str(@DebugRetries), 5) , ' waitsum ', dbo.QBM_FCVFloatToString(@DebugWaitSum) , ' FromSlot' , right(str(@SlotNumberSource46), 5) , ' ToSlot' , right(str(@SlotNumberTarget), 5) , ' RowsMoved', right(str(@RowsMoved), 5) ) exec QBM_PJournal @ErrorMessage, @@procid, 'T', @DebugLevel47 end return @RowsMoved end 48