Back to OIM Explorer

dbo.QBM_PWorkMaintenance

Stored ProcedureSQL_STORED_PROCEDURESandbox DB

Stored Procedure.

Source: sandbox-db sys.sql_modules

Source size: 3.289 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_FGISessionContext source text reference
  • references source dbo.QBM_FGISessionErrorForLog 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_PJournal 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_FCVJobParameterToList source text reference

References

Referenced By

Complete Source

SQL134 lines
1CREATE PROCEDURE QBM_PWorkMaintenance2AS3BEGIN4  DECLARE @procname nvarchar(256) = object_name(@@procid)5  DECLARE @UID_Job varchar(38)6  DECLARE @ParamIn nvarchar(max)7  DECLARE @SQLCmd nvarchar(max)8  DECLARE @DebugSwitch int = 09  DECLARE @angefangen datetime = getutcdate()10  DECLARE @DebugMessage nvarchar(max)11  DECLARE @DebugLevel varchar(1) = 'W'12  DECLARE @XUser nvarchar(64) = object_name(@@procid)13  DECLARE @Xdate datetime = getutcdate()14  DECLARE @StatementBuffer QBM_YCursorbuffer15  DECLARE @ElementCount int16  DECLARE @ElementIndex int = 117  DECLARE @Rethrow varchar(1000) = dbo.QBM_FGISessionErrorRethrow()18  DECLARE @GenProcID_R varchar(38) = dbo.QBM_FGISessionContext('')19  DECLARE @XUser_R nvarchar(64) = dbo.QBM_FGISessionContext('XUser')20  SET XACT_ABORT OFF21  BEGIN TRY22    EXEC QBM_PSessionContextSet 'XUser',23      @XUser24    IF 0 =(25      SELECT26        min(sign(len(ISNULL(p.name, '')))) AS StartBisHierGeschafft27    FROM(28    VALUES('QBMModuleDef'),('JobQueue'),('QBM_FCVJobParameterToList'),('QBM_PSessionErrorAdd'),('QBM_FGIDBQueueActivityDisabled'),29    ('QBM_PJournal'),('QBM_PTransactionLevelCheck')) AS v(ProcedureName)30    LEFT31    OUTER32    JOIN sys.objects p33      WITH(readpast)34      ON p.name = v.ProcedureName)35    BEGIN36      GOTO EndLabel37    END38    EXEC QBM_PDBQueueAgentInfoSet @procname39    EXEC QBM_PTransactionLevelCheck 0,40      @@procid,41    'at start'42    SET deadlock_priority 043    SET lock_timeout 100044    IF @DebugSwitch > 045    BEGIN46      IF EXISTS(47        SELECT TOP 1 148        FROM JobQueue q49        WITH(readpast)50      WHERE51        q.IsRootJob = 1 AND q.ComponentClass = 'VI.JobService.JobComponents.SQLComponent' AND q.TaskName = 'Execute SQL' AND q.Queue = @procname AND52      q.Ready2EXE = 'TRUE')53      BEGIN54        SELECT @DebugMessage = 'Entry QBM_PWorkMaintenance with jobs'55        EXEC QBM_PJournal @DebugMessage,56          @@procid,57        'D',58          @DebugLevel59      END60    END61    INSERT INTO @StatementBuffer(UID1,62    ContentFull)63    SELECT64      TOP 500 q.UID_Job,65      q.ParamIN66    FROM JobQueue q67      WITH(readpast)68    WHERE69      q.IsRootJob = 1 AND q.ComponentClass = 'VI.JobService.JobComponents.SQLComponent' AND q.TaskName = 'Execute SQL' AND q.Queue = @procname AND70    q.Ready2EXE = 'TRUE'71    ORDER BY q.StartAt72    SELECT @ElementCount = @@ROWCOUNT73    WHILE DATEDIFF(ss,74    @angefangen,75    getutcdate()) < 59 AND dbo.QBM_FGIDBQueueActivityDisabled() = 0 AND @ElementIndex <= @ElementCount76    BEGIN77      SELECT78        TOP 1 @UID_Job = bu.UID1,79        @ParamIn = bu.ContentFull80      FROM @StatementBuffer bu81      WHERE82        bu.ElementIndex = @ElementIndex83      SELECT TOP 1 @SQLCmd = x.ParameterValue84      FROM dbo.QBM_FCVJobParameterToList(@paramin) x85      WHERE86        x.ParameterName = 'SQLStmt'87      IF @DebugSwitch > 088      BEGIN89        print @sqlcmd90      END91      BEGIN TRY92        EXEC QBM_PSessionErrorClean93        EXEC sp_executesql @SQLCmd94        UPDATE JobQueue95        SET Ready2EXE = 'DELETE',96        XDateUpdated = @Xdate,97        XUserUpdated = @XUser98        WHERE99          UID_Job = @UID_Job100      END TRY101      BEGIN CATCH102        IF dbo.QBM_FGISessionErrorIsDeadlock(DEFAULT) = 0103        BEGIN104          IF @DebugSwitch > 0105          BEGIN106            SELECT dbo.QBM_FGISessionErrorForLog()107          END108          EXEC QBM_PSessionErrorAdd DEFAULT109          RAISERROR(@Rethrow,110          18,111          1)112            WITH NOWAIT113        END114      END CATCH115      SELECT @ElementIndex += 1116    END117  END TRY118  BEGIN CATCH119    EXEC QBM_PSessionErrorAdd DEFAULT120    RAISERROR(@Rethrow,121    18,122    1)123      WITH NOWAIT124  END CATCH125  endLabel:126  EXEC QBM_PSessionContextSet 'GenProcID',127    @GenProcID_R128  EXEC QBM_PSessionContextSet 'XUser',129    @XUser_R130  EXEC QBM_PDBQueueAgentInfoSet @procname,131  1132  SET lock_timeout -1133  RETURN134END
Open raw exported source
SQL ยท Raw22 lines
1     create   procedure QBM_PWorkMaintenance  as begin declare @procname nvarchar(256) = object_name(@@procid) declare @UID_Job varchar(38) declare2 @ParamIn nvarchar(max) declare @SQLCmd nvarchar(max) declare @DebugSwitch int = 0 declare @angefangen datetime = getutcdate() declare @DebugMessage nvarchar3(max) declare @DebugLevel varchar(1) = 'W' declare @XUser nvarchar(64) = object_name(@@procid) declare @Xdate datetime = getutcdate() declare @StatementBuffer4 QBM_YCursorbuffer declare @ElementCount int declare @ElementIndex int = 1 declare @Rethrow varchar(1000) = dbo.QBM_FGISessionErrorRethrow() declare @GenProcID_R5 varchar(38) = dbo.QBM_FGISessionContext('') declare @XUser_R nvarchar(64) = dbo.QBM_FGISessionContext('XUser') SET XACT_ABORT OFF BEGIN TRY exec QBM_PSessionContextSet6 'XUser', @XUser         if 0 = (select min(sign(len(ISNULL(p.name, '')))) as StartBisHierGeschafft from ( values ('QBMModuleDef')  , ('JobQueue') , ('QBM_FCVJobParameterToList'7)  , ('QBM_PSessionErrorAdd')   , ('QBM_FGIDBQueueActivityDisabled') , ('QBM_PJournal')  , ('QBM_PTransactionLevelCheck') ) as v (ProcedureName) left outer8 join sys.objects p with (readpast) on p.name = v.ProcedureName ) begin goto EndLabel end   exec QBM_PDBQueueAgentInfoSet @procname exec QBM_PTransactionLevelCheck9 0, @@procid, 'at start' set deadlock_priority 0  set lock_timeout 1000  if @DebugSwitch > 0 begin if exists ( select top 1 1 from JobQueue q with (readpast10) where q.IsRootJob = 1 and q.ComponentClass = 'VI.JobService.JobComponents.SQLComponent' and q.TaskName = 'Execute SQL' and q.Queue = @procname and q.Ready2EXE11 = 'TRUE' ) begin select @DebugMessage = 'Entry QBM_PWorkMaintenance with jobs' exec QBM_PJournal @DebugMessage, @@procid, 'D', @DebugLevel end end insert12 into @StatementBuffer(UID1, ContentFull) select top 500 q.UID_Job, q.ParamIN from JobQueue q with (readpast) where q.IsRootJob = 1 and q.ComponentClass13 = 'VI.JobService.JobComponents.SQLComponent' and q.TaskName = 'Execute SQL' and q.Queue = @procname and q.Ready2EXE = 'TRUE' order by q.StartAt select14 @ElementCount = @@ROWCOUNT while DATEDIFF(ss, @angefangen, getutcdate()) < 59  and dbo.QBM_FGIDBQueueActivityDisabled() = 0 and @ElementIndex <= @ElementCount15 begin select top 1 @UID_Job = bu.UID1 , @ParamIn = bu.ContentFull from @StatementBuffer bu where bu.ElementIndex = @ElementIndex select top 1 @SQLCmd 16= x.ParameterValue from dbo.QBM_FCVJobParameterToList(@paramin)x where x.ParameterName = 'SQLStmt' if @DebugSwitch > 0 begin print @sqlcmd end BEGIN TRY17 exec QBM_PSessionErrorClean exec sp_executesql @SQLCmd  update JobQueue set Ready2EXE = 'DELETE'  , XDateUpdated = @Xdate , XUserUpdated = @XUser where18 UID_Job = @UID_Job END TRY BEGIN CATCH if dbo.QBM_FGISessionErrorIsDeadlock(default) = 0 begin if @DebugSwitch > 0 begin select dbo.QBM_FGISessionErrorForLog19() end  exec QBM_PSessionErrorAdd default RAISERROR (@Rethrow, 18, 1) WITH NOWAIT end END CATCH select @ElementIndex += 1 end  END TRY BEGIN CATCH exec20 QBM_PSessionErrorAdd default RAISERROR (@Rethrow, 18, 1) WITH NOWAIT END CATCH endLabel: exec QBM_PSessionContextSet 'GenProcID', @GenProcID_R exec QBM_PSessionContextSet21 'XUser', @XUser_R  exec QBM_PDBQueueAgentInfoSet @procname, 1 set lock_timeout -1 return end 22