Back to OIM Explorer

dbo.QBM_PWorkJobQueueDelete

Stored ProcedureSQL_STORED_PROCEDURESandbox DB

Stored Procedure.

Source: sandbox-db sys.sql_modules

Source size: 2.966 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_FGIDBQueueActivityDisabled source text reference
  • references source dbo.QBM_FGIMaintenanceRunning source text reference
  • references source dbo.QBM_FGIMaintenanceRunning_M source text reference
  • references source dbo.QBM_FGISessionContext source text reference
  • references source dbo.QBM_FGISessionErrorIsDeadlock source text reference
  • references source dbo.QBM_FGISessionErrorRethrow source text reference
  • references source dbo.QBM_PDBQueueAgentInfoSet source text reference
  • references source dbo.QBM_PJobQueueDelete source text reference
  • references source dbo.QBM_PJobQueueStatsShrink source text reference
  • references source dbo.QBM_PSessionContextSet source text reference
  • references source dbo.QBM_PSessionErrorAdd source text reference
  • references source dbo.QBM_PSessionErrorClean source text reference
  • references source dbo.QBM_PTransactionLevelCheck source text reference
  • references source dbo.QBM_PWaitForSeconds source text reference

References

Referenced By

  • No direct source references extracted.

Complete Source

SQL133 lines
1CREATE PROCEDURE QBM_PWorkJobQueueDelete2AS3BEGIN4  DECLARE @ErrorMessage nvarchar(4000)5  DECLARE @ErrorSeverity int6  DECLARE @ErrorState int7  DECLARE @procname nvarchar(256) = object_name(@@procid)8  DECLARE @Starttime datetime = getutcdate()9  DECLARE @RunningMinutesMax int = 510  DECLARE @AnzahlJobQueue int = 111  DECLARE @AnzahlJobQueueStats int = 112  DECLARE @XUser nvarchar(64) = object_name(@@procid)13  DECLARE @GenProcID_R varchar(38) = dbo.QBM_FGISessionContext('')14  DECLARE @XUser_R nvarchar(64) = dbo.QBM_FGISessionContext('XUser')15  SET XACT_ABORT OFF16  BEGIN TRY17    EXEC QBM_PSessionContextSet 'XUser',18      @XUser19    IF 0 =(20      SELECT21        min(sign(len(ISNULL(p.name, '')))) AS StartBisHierGeschafft22    FROM(23    VALUES('QBMModuleDef'),('JobQueue'),('QBM_PSessionErrorAdd'),('QBM_FGISessionErrorIsDeadlock'),('QBM_FGIMaintenanceRunning_M'),24    ('QBM_FGIDBQueueActivityDisabled'),('QBM_PJobQueueDelete'),('QBM_PJobQueueStatsShrink'),('QBM_PTransactionLevelCheck'),25    ('QBM_PWaitForSeconds'),('QBMElementAffectedByJob')) AS v(ProcedureName)26    LEFT27    OUTER28    JOIN sys.objects p29      WITH(readpast)30      ON p.name = v.ProcedureName)31    BEGIN32      GOTO EndLabel33    END34    EXEC QBM_PDBQueueAgentInfoSet @procname35    IF dbo.QBM_FGIMaintenanceRunning_M(1) > ' '36    BEGIN37      GOTO endLabel38    END39    EXEC QBM_PTransactionLevelCheck 0,40      @@procid,41    'at start'42    SET deadlock_priority -1043    SET lock_timeout 5044    WHILE(@AnzahlJobQueue + @AnzahlJobQueueStats) > 0 AND DATEDIFF(mi,45    @Starttime,46    getutcdate()) <= @RunningMinutesMax AND dbo.QBM_FGIDBQueueActivityDisabled() = 047    BEGIN48      SET lock_timeout 5049      IF @AnzahlJobQueue > 050      BEGIN51        BEGIN TRY52          EXEC @AnzahlJobQueue = QBM_PJobQueueDelete53        END TRY54        BEGIN CATCH55          EXEC QBM_PSessionErrorAdd DEFAULT56          IF dbo.QBM_FGISessionErrorIsDeadlock(DEFAULT) = 057          BEGIN58            RAISERROR('',59            18,60            1)61              WITH NOWAIT62          END63          ELSE64          BEGIN65            SELECT @AnzahlJobQueue = 166            EXEC QBM_PSessionErrorClean67          END68        END CATCH69      END70      IF @AnzahlJobQueueStats > 071      BEGIN72        BEGIN TRY73          EXEC @AnzahlJobQueueStats = QBM_PJobQueueStatsShrink74        END TRY75        BEGIN CATCH76          EXEC QBM_PSessionErrorAdd DEFAULT77          IF dbo.QBM_FGISessionErrorIsDeadlock(DEFAULT) = 078          BEGIN79            RAISERROR('',80            18,81            1)82              WITH NOWAIT83          END84          ELSE85          BEGIN86            SELECT @AnzahlJobQueueStats = 187            EXEC QBM_PSessionErrorClean88          END89        END CATCH90      END91      EXEC QBM_PWaitForSeconds 0.04092    END93    IF EXISTS(94      SELECT TOP 1 195      FROM QBMElementAffectedByJob a96      WITH(readpast)97    LEFT98    OUTER99    JOIN JobQueue q100      WITH(readpast)101      ON a.UID_Job = q.UID_Job102    WHERE103      q.UID_Job IS NULL)104    BEGIN105      DELETE QBMElementAffectedByJob106      FROM QBMElementAffectedByJob a107      LEFT108      OUTER109      JOIN JobQueue q110        WITH(readpast)111        ON a.UID_Job = q.UID_Job112      WHERE113        q.UID_Job IS NULL114    END115  END TRY116  BEGIN CATCH117    EXEC QBM_PSessionErrorAdd DEFAULT118    DECLARE @Rethrow varchar(1000) = dbo.QBM_FGISessionErrorRethrow()119    RAISERROR(@Rethrow,120    18,121    1)122      WITH NOWAIT123  END CATCH124  endLabel:125  EXEC QBM_PSessionContextSet 'GenProcID',126    @GenProcID_R127  EXEC QBM_PSessionContextSet 'XUser',128    @XUser_R129  EXEC QBM_PDBQueueAgentInfoSet @procname,130  1131  SET lock_timeout -1132  RETURN133END
Open raw exported source
SQL ยท Raw20 lines
1  create   procedure QBM_PWorkJobQueueDelete as begin declare @ErrorMessage nvarchar(4000) declare @ErrorSeverity int declare @ErrorState int declare2 @procname nvarchar(256) = object_name(@@procid) declare @Starttime datetime = getutcdate() declare @RunningMinutesMax int = 5 declare @AnzahlJobQueue 3int = 1 declare @AnzahlJobQueueStats int = 1 declare @XUser nvarchar(64) = object_name(@@procid) declare @GenProcID_R varchar(38) = dbo.QBM_FGISessionContext4('') declare @XUser_R nvarchar(64) = dbo.QBM_FGISessionContext('XUser') SET XACT_ABORT OFF BEGIN TRY exec QBM_PSessionContextSet 'XUser', @XUser       5   if 0 = (select min(sign(len(ISNULL(p.name, '')))) as StartBisHierGeschafft from ( values ('QBMModuleDef')  , ('JobQueue')  , ('QBM_PSessionErrorAdd'6)   , ('QBM_FGISessionErrorIsDeadlock')   , ('QBM_FGIMaintenanceRunning_M') , ('QBM_FGIDBQueueActivityDisabled') , ('QBM_PJobQueueDelete') , ('QBM_PJobQueueStatsShrink'7)  , ('QBM_PTransactionLevelCheck') , ('QBM_PWaitForSeconds') , ('QBMElementAffectedByJob') ) as v (ProcedureName) left outer join sys.objects p with (readpast8) on p.name = v.ProcedureName ) begin goto EndLabel end   exec QBM_PDBQueueAgentInfoSet @procname    if dbo.QBM_FGIMaintenanceRunning_M(1) > ' ' begin 9goto endLabel end  exec QBM_PTransactionLevelCheck 0, @@procid, 'at start' set deadlock_priority -10  set lock_timeout 50  while (@AnzahlJobQueue + @AnzahlJobQueueStats10) > 0 and DATEDIFF(mi, @Starttime, getutcdate()) <= @RunningMinutesMax and dbo.QBM_FGIDBQueueActivityDisabled() = 0 begin set lock_timeout 50     if @AnzahlJobQueue11 > 0 begin  BEGIN TRY exec @AnzahlJobQueue = QBM_PJobQueueDelete END TRY BEGIN CATCH exec QBM_PSessionErrorAdd default if dbo.QBM_FGISessionErrorIsDeadlock12(default) = 0 begin RAISERROR ('', 18, 1) WITH NOWAIT end else begin select @AnzahlJobQueue = 1 exec QBM_PSessionErrorClean end END CATCH end    if @AnzahlJobQueueStats13 > 0 begin BEGIN TRY exec @AnzahlJobQueueStats = QBM_PJobQueueStatsShrink END TRY BEGIN CATCH exec QBM_PSessionErrorAdd default if dbo.QBM_FGISessionErrorIsDeadlock14(default) = 0 begin RAISERROR ('', 18, 1) WITH NOWAIT end else begin select @AnzahlJobQueueStats = 1 exec QBM_PSessionErrorClean end END CATCH end   exec15 QBM_PWaitForSeconds 0.040 end  if exists (select top 1 1 from QBMElementAffectedByJob a with (readpast) left outer join JobQueue q with (readpast) on 16a.UID_Job = q.UID_Job where q.UID_Job is null ) begin delete QBMElementAffectedByJob from QBMElementAffectedByJob a left outer join JobQueue q with (readpast17) on a.UID_Job = q.UID_Job where q.UID_Job is null end  END TRY BEGIN CATCH exec QBM_PSessionErrorAdd default declare @Rethrow varchar(1000) = dbo.QBM_FGISessionErrorRethrow18() RAISERROR (@Rethrow, 18, 1) WITH NOWAIT END CATCH endLabel: exec QBM_PSessionContextSet 'GenProcID', @GenProcID_R exec QBM_PSessionContextSet 'XUser'19, @XUser_R  exec QBM_PDBQueueAgentInfoSet @procname, 1 set lock_timeout -1 return end 20