Back to OIM Explorer

dbo.QBM_PWorkHistoryDelete

Stored ProcedureSQL_STORED_PROCEDURESandbox DB

Stored Procedure.

Source: sandbox-db sys.sql_modules

Source size: 7.567 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_FCVStringToInt source text reference
  • references source dbo.QBM_FGIConfigparmValue source text reference
  • references source dbo.QBM_FGIDBQueueActivityDisabled source text reference
  • references source dbo.QBM_FGIMaintenanceRunning 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_PDeleteBulk source text reference
  • references source dbo.QBM_PJournal source text reference
  • references source dbo.QBM_PJournalShrink source text reference
  • references source dbo.QBM_PLoginAuditShrink 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

SQL318 lines
1CREATE PROCEDURE QBM_PWorkHistoryDelete2AS3BEGIN4  DECLARE @procname nvarchar(256) = object_name(@@procid)5  DECLARE @Starttime datetime = getutcdate()6  DECLARE @RunningMinutesMax int = 57  DECLARE @AnzahlDialogWatchOperation int = 18  DECLARE @AnzahlDialogProcessChain int = 19  DECLARE @AnzahlDialogProcess int = 110  DECLARE @AnzahlDialogProcessSubst int = 111  DECLARE @AnzahlDialogJournal int = 112  DECLARE @AnzahlJobHistory int = 113  DECLARE @AnzahlCEFMessage int = 114  DECLARE @AnzahlLoginAudit int = 115  DECLARE @BulkCountDialogWatchOperation int16  DECLARE @TotalCountDialogWatchOperation int17  DECLARE @BulkCountDialogProcessChain int18  DECLARE @TotalCountDialogProcessChain int19  DECLARE @BulkCountDialogProcess int20  DECLARE @TotalCountDialogProcess int21  DECLARE @BulkCountJobHistory int22  DECLARE @TotalCountJobHistory int23  DECLARE @BulkCountDialogProcessSubst int24  DECLARE @TotalCountDialogProcessSubst int25  DECLARE @XUser nvarchar(64) = object_name(@@procid)26  DECLARE @GenProcID_R varchar(38) = dbo.QBM_FGISessionContext('')27  DECLARE @XUser_R nvarchar(64) = dbo.QBM_FGISessionContext('XUser')28  SET XACT_ABORT OFF29  BEGIN TRY30    EXEC QBM_PSessionContextSet 'XUser',31      @XUser32    IF 0 =(33      SELECT34        min(sign(len(ISNULL(p.name, '')))) AS StartBisHierGeschafft35    FROM(36    VALUES('QBMModuleDef'),('QBM_FCVStringToInt'),('QBM_FGIConfigparmValue'),('QBM_PSessionErrorAdd'),('QBM_FGISessionErrorIsDeadlock'),37    ('QBM_FGIMaintenanceRunning'),('QBM_FGIDBQueueActivityDisabled'),('QBM_PDeleteBulk'),('QBM_PJournalShrink'),38    ('QBM_PTransactionLevelCheck'),('QBM_PWaitForSeconds')) AS v(ProcedureName)39    LEFT40    OUTER41    JOIN sys.objects p42      WITH(readpast)43      ON p.name = v.ProcedureName)44    BEGIN45      GOTO EndLabel46    END47    EXEC QBM_PDBQueueAgentInfoSet @procname48    IF dbo.QBM_FGIMaintenanceRunning() > ' '49    BEGIN50      GOTO endLabel51    END52    EXEC QBM_PTransactionLevelCheck 0,53      @@procid,54    'at start'55    SET deadlock_priority -1056    SET lock_timeout 5057    SELECT58      @BulkCountJobHistory = dbo.QBM_FCVStringToInt(dbo.QBM_FGIConfigparmValue('Common\ProcessState\JobHistory\Delete\BulkCount'),59      200)60    SELECT61      @TotalCountJobHistory = dbo.QBM_FCVStringToInt(dbo.QBM_FGIConfigparmValue('Common\ProcessState\JobHistory\Delete\TotalCount'),62      10000)63    SELECT64      @BulkCountDialogProcess = dbo.QBM_FCVStringToInt(dbo.QBM_FGIConfigparmValue('Common\ProcessState\Delete\BulkCount'),65      500)66    SELECT67      @TotalCountDialogProcess = dbo.QBM_FCVStringToInt(dbo.QBM_FGIConfigparmValue('Common\ProcessState\Delete\TotalCount'),68      10000)69    SELECT @BulkCountDialogProcessSubst = @BulkCountDialogProcess70    SELECT @TotalCountDialogProcessSubst = @TotalCountDialogProcess71    SELECT72      @BulkCountDialogProcessChain = dbo.QBM_FCVStringToInt(dbo.QBM_FGIConfigparmValue('Common\ProcessState\ProgressView\Delete\BulkCount'),73      200)74    SELECT75      @TotalCountDialogProcessChain = dbo.QBM_FCVStringToInt(dbo.QBM_FGIConfigparmValue('Common\ProcessState\ProgressView\Delete\TotalCount'76      ),77      10000)78    SELECT79      @BulkCountDialogWatchOperation = dbo.QBM_FCVStringToInt(dbo.QBM_FGIConfigparmValue('Common\ProcessState\PropertyLog\Delete\BulkCount'),80      200)81    SELECT82      @TotalCountDialogWatchOperation = dbo.QBM_FCVStringToInt(dbo.QBM_FGIConfigparmValue('Common\ProcessState\PropertyLog\Delete\TotalCount'83      ),84      10000)85      WHILE(@AnzahlDialogWatchOperation + @AnzahlDialogProcessChain + @AnzahlDialogProcess + @AnzahlDialogProcessSubst + @AnzahlDialogJournal + @AnzahlJobHistory86      + @AnzahlCEFMessage + @AnzahlLoginAudit) > 0 AND DATEDIFF(mi,87      @Starttime,88      getutcdate()) <= @RunningMinutesMax AND dbo.QBM_FGIDBQueueActivityDisabled() = 089    BEGIN90      SET lock_timeout 5091      IF @AnzahlDialogWatchOperation > 092      BEGIN93        BEGIN TRY94          EXEC @AnzahlDialogWatchOperation = QBM_PDeleteBulk 'DialogWatchOperation',95          'ReadyForDeleteOrExport = 1',96            @BulkCountDialogWatchOperation,97            @TotalCountDialogWatchOperation98        END TRY99        BEGIN CATCH100          EXEC QBM_PSessionErrorAdd DEFAULT101          IF dbo.QBM_FGISessionErrorIsDeadlock(DEFAULT) = 0102          BEGIN103            RAISERROR('',104            18,105            1)106              WITH NOWAIT107          END108          ELSE109          BEGIN110            SELECT @AnzahlDialogWatchOperation = 1111            EXEC QBM_PSessionErrorClean112          END113        END CATCH114      END115      IF @AnzahlDialogProcessChain > 0116      BEGIN117        BEGIN TRY118          EXEC @AnzahlDialogProcessChain = QBM_PDeleteBulk 'dialogProcesschain',119          'ReadyForDeleteOrExport = 1',120            @BulkCountDialogProcessChain,121            @TotalCountDialogProcessChain122        END TRY123        BEGIN CATCH124          EXEC QBM_PSessionErrorAdd DEFAULT125          IF dbo.QBM_FGISessionErrorIsDeadlock(DEFAULT) = 0126          BEGIN127            RAISERROR('',128            18,129            1)130              WITH NOWAIT131          END132          ELSE133          BEGIN134            SELECT @AnzahlDialogProcessChain = 1135            EXEC QBM_PSessionErrorClean136          END137        END CATCH138      END139      IF @AnzahlDialogProcessSubst > 0140      BEGIN141        BEGIN TRY142          EXEC @AnzahlDialogProcessSubst = QBM_PDeleteBulk 'DialogProcessSubstitute',143          'ReadyForDeleteOrExport = 1 ',144            @BulkCountDialogProcessSubst,145            @TotalCountDialogProcessSubst146        END TRY147        BEGIN CATCH148          EXEC QBM_PSessionErrorAdd DEFAULT149          IF dbo.QBM_FGISessionErrorIsDeadlock(DEFAULT) = 0150          BEGIN151            RAISERROR('',152            18,153            1)154              WITH NOWAIT155          END156          ELSE157          BEGIN158            SELECT @AnzahlDialogProcessSubst = 1159            EXEC QBM_PSessionErrorClean160          END161        END CATCH162      END163      IF @AnzahlDialogProcess > 0164      BEGIN165        BEGIN TRY166          EXEC @AnzahlDialogProcess = QBM_PDeleteBulk 'DialogProcess',167          'ReadyForDeleteOrExport = 1 ',168            @BulkCountDialogProcess,169            @TotalCountDialogProcess170        END TRY171        BEGIN CATCH172          EXEC QBM_PSessionErrorAdd DEFAULT173          IF dbo.QBM_FGISessionErrorIsDeadlock(DEFAULT) = 0174          BEGIN175            RAISERROR('',176            18,177            1)178              WITH NOWAIT179          END180          ELSE181          BEGIN182            SELECT @AnzahlDialogProcess = 1183            EXEC QBM_PSessionErrorClean184          END185        END CATCH186      END187      IF @AnzahlDialogJournal > 0188      BEGIN189        BEGIN TRY190          EXEC @AnzahlDialogJournal = QBM_PJournalShrink191        END TRY192        BEGIN CATCH193          EXEC QBM_PSessionErrorAdd DEFAULT194          IF dbo.QBM_FGISessionErrorIsDeadlock(DEFAULT) = 0195          BEGIN196            RAISERROR('',197            18,198            1)199              WITH NOWAIT200          END201          ELSE202          BEGIN203            SELECT @AnzahlDialogJournal = 1204            EXEC QBM_PSessionErrorClean205          END206        END CATCH207      END208      IF @AnzahlJobHistory > 0209      BEGIN210        BEGIN TRY211          EXEC @AnzahlJobHistory = QBM_PDeleteBulk 'JobHistory',212          'ReadyForDeleteOrExport = 1',213            @BulkCountJobHistory,214            @TotalCountJobHistory215        END TRY216        BEGIN CATCH217          EXEC QBM_PSessionErrorAdd DEFAULT218          IF dbo.QBM_FGISessionErrorIsDeadlock(DEFAULT) = 0219          BEGIN220            RAISERROR('',221            18,222            1)223              WITH NOWAIT224          END225          ELSE226          BEGIN227            SELECT @AnzahlJobHistory = 1228            EXEC QBM_PSessionErrorClean229          END230        END CATCH231      END232      IF @AnzahlCEFMessage > 0233      BEGIN234        BEGIN TRY235          EXEC @AnzahlCEFMessage = QBM_PDeleteBulk 'QBMCEFMessage',236          'IsProcessed = 1',237          500,238          10000239        END TRY240        BEGIN CATCH241          EXEC QBM_PSessionErrorAdd DEFAULT242          IF dbo.QBM_FGISessionErrorIsDeadlock(DEFAULT) = 0243          BEGIN244            RAISERROR('',245            18,246            1)247              WITH NOWAIT248          END249          ELSE250          BEGIN251            SELECT @AnzahlCEFMessage = 1252            EXEC QBM_PSessionErrorClean253          END254        END CATCH255      END256      IF @AnzahlLoginAudit > 0257      BEGIN258        BEGIN TRY259          EXEC @AnzahlLoginAudit = QBM_PLoginAuditShrink260        END TRY261        BEGIN CATCH262          EXEC QBM_PSessionErrorAdd DEFAULT263          IF dbo.QBM_FGISessionErrorIsDeadlock(DEFAULT) = 0264          BEGIN265            RAISERROR('',266            18,267            1)268              WITH NOWAIT269          END270          ELSE271          BEGIN272            SELECT @AnzahlLoginAudit = 1273            EXEC QBM_PSessionErrorClean274          END275        END CATCH276      END277      EXEC QBM_PWaitForSeconds 0.040278    END279    IF EXISTS(280      SELECT TOP 1 1281      FROM jobhistory282      GROUP BY uid_job283      HAVING count(*) > 1)284    BEGIN285      DELETE JobHistory286      WHERE287        UID_JobHistory IN(288      SELECT max(UID_JobHistory)289      FROM JobHistory290      GROUP BY UID_Job291      HAVING count(*) > 1)292    END293  END TRY294  BEGIN CATCH295    EXEC QBM_PSessionErrorAdd DEFAULT296    IF dbo.QBM_FGISessionErrorIsDeadlock(DEFAULT) = 0297    BEGIN298      DECLARE @Rethrow varchar(1000) = dbo.QBM_FGISessionErrorRethrow()299      RAISERROR(@Rethrow,300      18,301      1)302        WITH NOWAIT303    END304    ELSE305    BEGIN306      EXEC QBM_PSessionErrorClean307    END308  END CATCH309  endLabel:310  EXEC QBM_PSessionContextSet 'GenProcID',311    @GenProcID_R312  EXEC QBM_PSessionContextSet 'XUser',313    @XUser_R314  EXEC QBM_PDBQueueAgentInfoSet @procname,315  1316  SET lock_timeout -1317  RETURN318END
Open raw exported source
SQL ยท Raw50 lines
1  create   procedure QBM_PWorkHistoryDelete as begin declare @procname nvarchar(256) = object_name(@@procid) declare @Starttime datetime = getutcdate2() declare @RunningMinutesMax int = 5 declare @AnzahlDialogWatchOperation int = 1 declare @AnzahlDialogProcessChain int = 1 declare @AnzahlDialogProcess3 int = 1 declare @AnzahlDialogProcessSubst int = 1 declare @AnzahlDialogJournal int = 1 declare @AnzahlJobHistory int = 1 declare @AnzahlCEFMessage int4 = 1 declare @AnzahlLoginAudit int = 1 declare @BulkCountDialogWatchOperation int declare @TotalCountDialogWatchOperation int declare @BulkCountDialogProcessChain5 int declare @TotalCountDialogProcessChain int declare @BulkCountDialogProcess int declare @TotalCountDialogProcess int declare @BulkCountJobHistory int6 declare @TotalCountJobHistory int declare @BulkCountDialogProcessSubst int declare @TotalCountDialogProcessSubst int declare @XUser nvarchar(64) = object_name7(@@procid) declare @GenProcID_R varchar(38) = dbo.QBM_FGISessionContext('') declare @XUser_R nvarchar(64) = dbo.QBM_FGISessionContext('XUser') SET XACT_ABORT8 OFF BEGIN TRY exec QBM_PSessionContextSet 'XUser', @XUser         if 0 = (select min(sign(len(ISNULL(p.name, '')))) as StartBisHierGeschafft from ( values9 ('QBMModuleDef')  , ('QBM_FCVStringToInt') , ('QBM_FGIConfigparmValue')  , ('QBM_PSessionErrorAdd')   , ('QBM_FGISessionErrorIsDeadlock')   , ('QBM_FGIMaintenanceRunning'10) , ('QBM_FGIDBQueueActivityDisabled') , ('QBM_PDeleteBulk') , ('QBM_PJournalShrink')  , ('QBM_PTransactionLevelCheck') , ('QBM_PWaitForSeconds') ) as 11v (ProcedureName) left outer join sys.objects p with (readpast) on p.name = v.ProcedureName ) begin goto EndLabel end   exec QBM_PDBQueueAgentInfoSet @procname12  if dbo.QBM_FGIMaintenanceRunning() > ' ' begin goto endLabel end  exec QBM_PTransactionLevelCheck 0, @@procid, 'at start' set deadlock_priority -10  13set lock_timeout 50     select @BulkCountJobHistory = dbo.QBM_FCVStringToInt(dbo.QBM_FGIConfigparmValue( 'Common\ProcessState\JobHistory\Delete\BulkCount'14), 200) select @TotalCountJobHistory = dbo.QBM_FCVStringToInt(dbo.QBM_FGIConfigparmValue( 'Common\ProcessState\JobHistory\Delete\TotalCount'), 10000) select15 @BulkCountDialogProcess = dbo.QBM_FCVStringToInt(dbo.QBM_FGIConfigparmValue( 'Common\ProcessState\Delete\BulkCount'), 500) select @TotalCountDialogProcess16 = dbo.QBM_FCVStringToInt(dbo.QBM_FGIConfigparmValue( 'Common\ProcessState\Delete\TotalCount'), 10000) select @BulkCountDialogProcessSubst = @BulkCountDialogProcess17 select @TotalCountDialogProcessSubst = @TotalCountDialogProcess select @BulkCountDialogProcessChain = dbo.QBM_FCVStringToInt(dbo.QBM_FGIConfigparmValue18( 'Common\ProcessState\ProgressView\Delete\BulkCount'), 200) select @TotalCountDialogProcessChain = dbo.QBM_FCVStringToInt(dbo.QBM_FGIConfigparmValue( 19'Common\ProcessState\ProgressView\Delete\TotalCount'), 10000) select @BulkCountDialogWatchOperation = dbo.QBM_FCVStringToInt(dbo.QBM_FGIConfigparmValue20( 'Common\ProcessState\PropertyLog\Delete\BulkCount'), 200) select @TotalCountDialogWatchOperation = dbo.QBM_FCVStringToInt(dbo.QBM_FGIConfigparmValue(21 'Common\ProcessState\PropertyLog\Delete\TotalCount'), 10000) while ( @AnzahlDialogWatchOperation + @AnzahlDialogProcessChain + @AnzahlDialogProcess + 22@AnzahlDialogProcessSubst + @AnzahlDialogJournal + @AnzahlJobHistory + @AnzahlCEFMessage + @AnzahlLoginAudit) > 0 and DATEDIFF(mi, @Starttime, getutcdate23()) <= @RunningMinutesMax and dbo.QBM_FGIDBQueueActivityDisabled() = 0 begin set lock_timeout 50     if @AnzahlDialogWatchOperation > 0 begin BEGIN TRY24 exec @AnzahlDialogWatchOperation = QBM_PDeleteBulk 'DialogWatchOperation', 'ReadyForDeleteOrExport = 1', @BulkCountDialogWatchOperation, @TotalCountDialogWatchOperation25 END TRY BEGIN CATCH exec QBM_PSessionErrorAdd default if dbo.QBM_FGISessionErrorIsDeadlock(default) = 0   begin RAISERROR ('', 18, 1) WITH NOWAIT end 26else begin select @AnzahlDialogWatchOperation = 1 exec QBM_PSessionErrorClean end END CATCH end     if @AnzahlDialogProcessChain > 0 begin BEGIN TRY exec27 @AnzahlDialogProcessChain = QBM_PDeleteBulk 'dialogProcesschain', 'ReadyForDeleteOrExport = 1', @BulkCountDialogProcessChain, @TotalCountDialogProcessChain28 END TRY BEGIN CATCH exec QBM_PSessionErrorAdd default if dbo.QBM_FGISessionErrorIsDeadlock(default) = 0 begin RAISERROR ('', 18, 1) WITH NOWAIT end else29 begin select @AnzahlDialogProcessChain = 1 exec QBM_PSessionErrorClean end END CATCH end     if @AnzahlDialogProcessSubst > 0 begin BEGIN TRY exec @AnzahlDialogProcessSubst30 = QBM_PDeleteBulk 'DialogProcessSubstitute', 'ReadyForDeleteOrExport = 1 ', @BulkCountDialogProcessSubst, @TotalCountDialogProcessSubst END TRY BEGIN 31CATCH exec QBM_PSessionErrorAdd default if dbo.QBM_FGISessionErrorIsDeadlock(default) = 0 begin RAISERROR ('', 18, 1) WITH NOWAIT end else begin select32 @AnzahlDialogProcessSubst = 1 exec QBM_PSessionErrorClean end END CATCH end     if @AnzahlDialogProcess > 0 begin BEGIN TRY exec @AnzahlDialogProcess 33= QBM_PDeleteBulk 'DialogProcess', 'ReadyForDeleteOrExport = 1 ', @BulkCountDialogProcess, @TotalCountDialogProcess END TRY BEGIN CATCH exec QBM_PSessionErrorAdd34 default if dbo.QBM_FGISessionErrorIsDeadlock(default) = 0 begin RAISERROR ('', 18, 1) WITH NOWAIT end else begin select @AnzahlDialogProcess = 1 exec 35QBM_PSessionErrorClean end END CATCH end     if @AnzahlDialogJournal > 0 begin BEGIN TRY exec @AnzahlDialogJournal = QBM_PJournalShrink END TRY BEGIN CATCH36 exec QBM_PSessionErrorAdd default if dbo.QBM_FGISessionErrorIsDeadlock(default) = 0 begin RAISERROR ('', 18, 1) WITH NOWAIT end else begin select @AnzahlDialogJournal37 = 1 exec QBM_PSessionErrorClean end END CATCH end     if @AnzahlJobHistory > 0 begin BEGIN TRY exec @AnzahlJobHistory = QBM_PDeleteBulk 'JobHistory', 38'ReadyForDeleteOrExport = 1', @BulkCountJobHistory, @TotalCountJobHistory END TRY BEGIN CATCH exec QBM_PSessionErrorAdd default if dbo.QBM_FGISessionErrorIsDeadlock39(default) = 0 begin RAISERROR ('', 18, 1) WITH NOWAIT end else begin select @AnzahlJobHistory = 1 exec QBM_PSessionErrorClean end END CATCH end     if 40@AnzahlCEFMessage > 0 begin BEGIN TRY exec @AnzahlCEFMessage = QBM_PDeleteBulk 'QBMCEFMessage', 'IsProcessed = 1', 500  , 10000  END TRY BEGIN CATCH exec41 QBM_PSessionErrorAdd default if dbo.QBM_FGISessionErrorIsDeadlock(default) = 0 begin RAISERROR ('', 18, 1) WITH NOWAIT end else begin select @AnzahlCEFMessage42 = 1 exec QBM_PSessionErrorClean end END CATCH end     if @AnzahlLoginAudit > 0 begin BEGIN TRY exec @AnzahlLoginAudit = QBM_PLoginAuditShrink END TRY 43BEGIN CATCH exec QBM_PSessionErrorAdd default if dbo.QBM_FGISessionErrorIsDeadlock(default) = 0 begin RAISERROR ('', 18, 1) WITH NOWAIT end else begin 44select @AnzahlLoginAudit = 1 exec QBM_PSessionErrorClean end END CATCH end   exec QBM_PWaitForSeconds 0.040 end   if exists (select top 1 1 from jobhistory45 group by uid_job having count (*) > 1) begin delete JobHistory where UID_JobHistory in (select max(UID_JobHistory) from JobHistory group by UID_Job having46 count(*) > 1 ) end  END TRY BEGIN CATCH exec QBM_PSessionErrorAdd default if dbo.QBM_FGISessionErrorIsDeadlock(default) = 0 begin declare @Rethrow varchar47(1000) = dbo.QBM_FGISessionErrorRethrow() RAISERROR (@Rethrow, 18, 1) WITH NOWAIT end else begin exec QBM_PSessionErrorClean end END CATCH endLabel: exec48 QBM_PSessionContextSet 'GenProcID', @GenProcID_R exec QBM_PSessionContextSet 'XUser', @XUser_R  exec QBM_PDBQueueAgentInfoSet @procname, 1 set lock_timeout49 -1 return end 50