Back to OIM Explorer

dbo.QBM_PJobQueueDelete_Bulk

Stored ProcedureSQL_STORED_PROCEDURESandbox DB

Stored Procedure.

Source: sandbox-db sys.sql_modules

Source size: 3.357 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_FCVJobParamDeflate source text reference
  • references source dbo.QBM_FCVJobParameterToString source text reference
  • references source dbo.QBM_FGIConfigparmValue source text reference
  • references source dbo.QBM_FGIJobQueueMessageAdd source text reference
  • references source dbo.QBM_FGIMaintenanceRunning source text reference
  • references source dbo.QBM_FGIMaintenanceRunning_M source text reference
  • references source dbo.QBM_FGISessionErrorRethrow source text reference
  • references source dbo.QBM_PJobQueueDelete source text reference
  • references source dbo.QBM_PJournal source text reference
  • references source dbo.QBM_PSessionErrorAdd source text reference

References

Referenced By

Complete Source

SQL172 lines
1CREATE PROCEDURE QBM_PJobQueueDelete_Bulk(2  @TreesToDelete QBM_YParameterList READONLY3)4AS5BEGIN6  DECLARE @Trees QBM_YParameterList7  DECLARE @AnzahlLoeschGesamt int8  DECLARE @DebugSwitch int = 09  DECLARE @DebugLevel char(1) = 'I'10  DECLARE @DebugMessage nvarchar(1000)11  DECLARE @Message nvarchar(1000) = '#LDS#Jobqueue entry deleted by procedure QBM_PJobQueueDelete_Bulk. Last state was {0}.|%Ready2EXE%|'12  DECLARE @ToHistory BIT = 013  SET XACT_ABORT OFF14  BEGIN TRY15    IF dbo.QBM_FGIMaintenanceRunning_M(1) > ' '16    BEGIN17      RAISERROR('#LDS#Database is in maintenance mode, action could not be executed.|',18      18,19      1)20        WITH nowait21    END22    IF dbo.QBM_FGIConfigparmValue('Common\ProcessState\JobHistory') > ' '23    BEGIN24      SELECT @ToHistory = 125    END26    INSERT INTO @Trees(Parameter1,27    ContentFull)28    SELECT29      t.Parameter1,30      ''31    FROM @TreesToDelete t32    WHERE33      NOT EXISTS(34    SELECT TOP 1 135    FROM JobQueue q36      WITH(readpast)37    WHERE38      q.UID_Tree = t.Parameter1 AND q.Ready2EXE IN(N 'HISTORY', N 'DELETE'))39    SELECT @AnzahlLoeschGesamt = @@rowcount40    IF EXISTS(41      SELECT TOP 1 142      FROM @Trees t43      JOIN JobTreeParamColl co44        ON co.UID_Tree = t.Parameter1)45    BEGIN46      UPDATE @Trees47      SET HasContentFull = 148      FROM @Trees t49      WHERE50        EXISTS(51      SELECT TOP 1 152      FROM JobTreeParamColl co53      WHERE54        co.UID_Tree = t.Parameter1)55      UPDATE @Trees56      SET ContentFull = dbo.QBM_FCVJobParameterToString(t.Parameter1)57      FROM @Trees t58      WHERE59        t.HasContentFull = 160      UPDATE @Trees61      SET ContentFull = SUBSTRING(t.ContentFull,62      2,63      LEN(t.ContentFull) -1)64      FROM @Trees t65      WHERE66        t.ContentFull > ' '67      UPDATE @Trees68      SET ContentFull = ''69      FROM @Trees t70      WHERE71        t.ContentFull IS NULL72    END73    IF @ToHistory = 174    BEGIN75      INSERT INTO JobHistory(ComponentAssembly,76      ComponentClass,77      EndedAt,78      JobChainName,79      ParamIN,80      Priority,81      Queue,82      StartAt,83      TaskName,84      UID_Job,85      UID_JobHistory,86      UID_Tree,87      WasError,88      XDateInserted,89      XDateUpdated,90      XUserInserted,91      XUserUpdated,92      IsRootJob,93      UID_JobError,94      UID_JobSuccess,95      GenProcID,96      UID_JobOrigin,97      BasisObjectKey,98      ErrorMessages,99      LogMode)100      SELECT101        q.ComponentAssembly,102        q.ComponentClass,103        isnull(q.xdateupdated,104        getutcdate()) AS EndedAt,105        q.JobChainName,106        dbo.QBM_FCVJobParamDeflate(q.ComponentClass,107        q.TaskName,108        q.ParamIN + t.ContentFull),109        q.Priority,110        q.Queue,111        q.StartAt,112        q.TaskName,113        q.UID_Job,114        newid(),115        q.UID_Tree,116        q.WasError,117        q.XDateInserted,118        q.XDateUpdated,119        q.XUserInserted,120        q.XUserUpdated,121        q.IsRootJob,122        e.UID_Job,123        s.UID_Job,124        q.GenProcID,125        q.UID_JobOrigin,126        q.BasisObjectKey,127        dbo.QBM_FGIJobQueueMessageAdd(q.ErrorMessages,128        replace(@Message, '%Ready2EXE%', q.Ready2EXE)),129        q.LogMode130      FROM @Trees t131      JOIN JobQueue q132        ON t.Parameter1 = q.UID_Tree133      LEFT134      OUTER135      JOIN JobQueue e136        ON q.UID_Tree = e.UID_Tree AND q.UID_JobError = e.UID_Job137      LEFT138      OUTER139      JOIN JobQueue s140        ON q.UID_Tree = s.UID_Tree AND q.UID_JobSuccess = s.UID_Job141      WHERE142        NOT EXISTS(143      SELECT TOP 1 1144      FROM JobHistory h145      WHERE146        h.UID_Tree = q.UID_Tree AND h.UID_Job = q.UID_Job)147    END148    DELETE Jobqueue149    WHERE150      UID_Tree IN(151    SELECT t.Parameter1152    FROM @Trees t)153    IF @DebugSwitch > 0154    BEGIN155      SELECT @DebugMessage = 'count entries in Jobqueue deleted ' + STR(@AnzahlLoeschGesamt)156      EXEC QBM_PJournal @debugmessage,157        @@PROCID,158      'I',159        @debuglevel160    END161  END TRY162  BEGIN CATCH163    EXEC QBM_PSessionErrorAdd DEFAULT164    DECLARE @Rethrow varchar(1000) = dbo.QBM_FGISessionErrorRethrow()165    RAISERROR(@Rethrow,166    18,167    1)168      WITH NOWAIT169  END CATCH170  endLabel:171  RETURN(@AnzahlLoeschGesamt)172END
Open raw exported source
SQL ยท Raw23 lines
1   create   procedure QBM_PJobQueueDelete_Bulk (@TreesToDelete QBM_YParameterList readonly  ) as begin declare @Trees QBM_YParameterList declare2 @AnzahlLoeschGesamt int   declare @DebugSwitch int = 0 declare @DebugLevel char(1) = 'I' declare @DebugMessage nvarchar(1000) declare @Message nvarchar3(1000) = '#LDS#Jobqueue entry deleted by procedure QBM_PJobQueueDelete_Bulk. Last state was {0}.|%Ready2EXE%|' declare @ToHistory bit = 0 SET XACT_ABORT4 OFF BEGIN TRY    if dbo.QBM_FGIMaintenanceRunning_M(1) > ' ' begin raiserror ('#LDS#Database is in maintenance mode, action could not be executed.|', 518, 1) with nowait end  if dbo.QBM_FGIConfigparmValue('Common\ProcessState\JobHistory') > ' ' begin select @ToHistory = 1 end insert into @Trees (Parameter16 , ContentFull ) select t.Parameter1, '' from @TreesToDelete t  where not exists (select top 1 1 from JobQueue q with (readpast) where q.UID_Tree = t.Parameter17 and q.Ready2EXE in (N'HISTORY', N'DELETE') ) select @AnzahlLoeschGesamt = @@rowcount  if exists (select top 1 1 from @Trees t join JobTreeParamColl co8 on co.UID_Tree = t.Parameter1  ) begin  update @Trees set HasContentFull = 1 from @Trees t where exists (select top 1 1 from JobTreeParamColl co where9 co.UID_Tree = t.Parameter1 )  update @Trees set ContentFull  = dbo.QBM_FCVJobParameterToString(t.Parameter1 ) from @Trees t where t.HasContentFull = 110  update @Trees set ContentFull  = SUBSTRING(t.ContentFull , 2 , LEN(t.ContentFull ) -1) from @Trees t where t.ContentFull  > ' ' update @Trees set ContentFull11  = '' from @Trees t where t.ContentFull  is null end if @ToHistory = 1 begin  insert into JobHistory ( ComponentAssembly, ComponentClass , EndedAt , JobChainName12, ParamIN, Priority, Queue, StartAt, TaskName, UID_Job, UID_JobHistory, UID_Tree, WasError, XDateInserted, XDateUpdated, XUserInserted, XUserUpdated, IsRootJob13, UID_JobError, UID_JobSuccess, GenProcID, UID_JobOrigin, BasisObjectKey , ErrorMessages  , LogMode)  select q.ComponentAssembly, q.ComponentClass   , 14isnull(q.xdateupdated, getutcdate()) as EndedAt     , q.JobChainName, dbo.QBM_FCVJobParamDeflate (q.ComponentClass, q.TaskName, q.ParamIN + t.ContentFull15 ), q.Priority, q.Queue, q.StartAt, q.TaskName, q.UID_Job, newid(), q.UID_Tree, q.WasError, q.XDateInserted, q.XDateUpdated, q.XUserInserted, q.XUserUpdated16, q.IsRootJob, e.UID_Job, s.UID_Job, q.GenProcID, q.UID_JobOrigin, q.BasisObjectKey , dbo.QBM_FGIJobQueueMessageAdd(q.ErrorMessages, replace(@Message, 17'%Ready2EXE%', q.Ready2EXE))  , q.LogMode  from @Trees t join JobQueue q on t.Parameter1  = q.UID_Tree left outer join JobQueue e on q.UID_Tree = e.UID_Tree18 and q.UID_JobError = e.UID_Job  left outer join JobQueue s on q.UID_Tree = s.UID_Tree and q.UID_JobSuccess = s.UID_Job  where not exists (select top 119 1 from JobHistory h where h.UID_Tree = q.UID_Tree and h.UID_Job = q.UID_Job ) end   delete Jobqueue where UID_Tree in ( select t.Parameter1 from @Trees20 t ) if @DebugSwitch > 0 begin select @DebugMessage = 'count entries in Jobqueue deleted ' + STR(@AnzahlLoeschGesamt) exec QBM_PJournal @debugmessage, 21@@PROCID, 'I', @debuglevel end END TRY BEGIN CATCH exec QBM_PSessionErrorAdd default declare @Rethrow varchar(1000) = dbo.QBM_FGISessionErrorRethrow() 22RAISERROR (@Rethrow, 18, 1) WITH NOWAIT END CATCH endLabel: return (@AnzahlLoeschGesamt) end 23