Back to OIM Explorer

dbo.QBM_PJobqueueoverViewFill

Stored ProcedureSQL_STORED_PROCEDURESandbox DB

Stored Procedure.

Source: sandbox-db sys.sql_modules

Source size: 6.985 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_FCVElementToObjectKey1 source text reference
  • references source dbo.QBM_FCVStringToGUID source text reference
  • references source dbo.QBM_FGISessionContext source text reference
  • references source dbo.QBM_FGISessionErrorIsDeadlock source text reference
  • references source dbo.QBM_FGISessionErrorIsMissingCo source text reference
  • references source dbo.QBM_PSessionContextSet source text reference
  • references source dbo.QBM_PSessionErrorAdd source text reference

References

Referenced By

  • No direct source references extracted.

Complete Source

SQL325 lines
1CREATE PROCEDURE QBM_PJobqueueoverViewFill(2  @FullSearch BIT = 03)4AS5BEGIN6  DECLARE @ret int = 07  DECLARE @DebugSwitch int = 08  DECLARE @CountRows int9  DECLARE @QBMJobqueueOverview QBM_YJobqueueOverview10  DECLARE @GenProcID_R varchar(38) = dbo.QBM_FGISessionContext('')11  DECLARE @XUser_R nvarchar(64) = dbo.QBM_FGISessionContext('XUser')12  DECLARE @GenProcID varchar(38) = dbo.QBM_FGISessionContext('')13  DECLARE @XUser nvarchar(64) = dbo.QBM_FGISessionContext('XUser')14  DECLARE @QueuesToUpdate QBM_YCursorBuffer15  DECLARE @ElementCount int,16  @ElementIndex int,17  @UID_JobQueue varchar(38)18  SET XACT_ABORT OFF19  BEGIN TRY20    IF isnull(@XUser,21    '') = ''22    BEGIN23      SELECT @XUser =24      LEFT(Object_name(@@procid),25      64)26      EXEC QBM_PSessionContextSet 'GenProcID',27        @GenProcID28      EXEC QBM_PSessionContextSet 'XUser',29        @XUser30    END31    IF @DebugSwitch > 032    BEGIN33      print 'Ausgangslage '34      SELECT35        isinvalid,36        *37      FROM QBMJobqueueOverview38    END39    INSERT INTO @QBMJobqueueOverview(UID_QBMJobqueueOverview,40    XObjectKey,41    QueueName,42    CountTrue,43    CountLoaded,44    CountOverlimt,45    CountMissing,46    CountDelete,47    CountHistory,48    CountProcessing,49    CountFalse,50    CountFrozen,51    CountFinished)52    SELECT53      dbo.QBM_FCVStringToGUID('',54      y.QueueName) AS UID_QBMJobqueueOverview,55      dbo.QBM_FCVElementToObjectKey1('QBMJobqueueOverview',56      'UID_QBMJobqueueOverview',57      dbo.QBM_FCVStringToGUID('', y.QueueName)) AS XObjectKey,58      y.QueueName,59      y.CountTrue,60      y.CountLoaded,61      y.CountOverlimt,62      y.CountMissing,63      y.CountDelete,64      y.CountHistory,65      y.CountProcessing,66      y.CountFalse,67      y.CountFrozen,68      y.CountFinished69    FROM(70    SELECT71      x.Queue AS QueueName,72      SUM(x.CountTrue) AS CountTrue,73      SUM(x.CountLoaded) AS CountLoaded,74      SUM(x.CountOverlimt) AS CountOverlimt,75      SUM(x.CountMissing) AS CountMissing,76      SUM(x.CountDelete) AS CountDelete,77      SUM(x.CountHistory) AS CountHistory,78      SUM(x.CountProcessing) AS CountProcessing,79      SUM(x.CountFalse) AS CountFalse,80      SUM(x.CountFrozen) AS CountFrozen,81      SUM(x.CountFinished) AS CountFinished82    FROM(83    SELECT84      q.Queue, CASE q.Ready2EXE85      WHEN N 'TRUE' THEN86      187    ELSE 088    END AS CountTrue, CASE q.Ready2EXE89    WHEN N 'LOADED' THEN90    191    ELSE 092    END AS CountLoaded, CASE q.Ready2EXE93    WHEN N 'OVERLIMT' THEN94    195    ELSE 096    END AS CountOverlimt, CASE q.Ready2EXE97    WHEN N 'MISSING' THEN98    199    ELSE 0100    END AS CountMissing, CASE q.Ready2EXE101    WHEN N 'DELETE' THEN102    1103    ELSE 0104    END AS CountDelete, CASE q.Ready2EXE105    WHEN N 'HISTORY' THEN106    1107    ELSE 0108    END AS CountHistory, CASE q.Ready2EXE109    WHEN N 'PROCESSING' THEN110    1111    ELSE 0112    END AS CountProcessing, CASE q.Ready2EXE113    WHEN N 'FALSE' THEN114    1115    ELSE 0116    END AS CountFalse, CASE q.Ready2EXE117    WHEN N 'FROZEN' THEN118    1119    ELSE 0120    END AS CountFrozen, CASE q.Ready2EXE121    WHEN N 'FINISHED' THEN122    1123    ELSE 0124    END AS CountFinished125    FROM JobQueue q126      WITH(readpast)127    LEFT128    OUTER129    JOIN QBMJobqueueOverview o130      WITH(readpast)131      ON q.Queue = o.QueueName132    WHERE133      ISNULL(o.IsInvalid, 1) = 1 OR @FullSearch = 1) AS x134    GROUP BY x.Queue) AS y135    SELECT @CountRows = @@ROWCOUNT136    IF @DebugSwitch > 0137    BEGIN138      print 'queues summiert ' + str(@CountRows)139      SELECT *140      FROM @QBMJobqueueOverview141    END142    BEGIN TRY143      SET lock_timeout 5 merge INTO QBMJobqueueOverview AS t using @QBMJobqueueOverview AS s144        ON t.UID_QBMJobqueueOverview = s.UID_QBMJobqueueOverview WHEN matched THEN145      UPDATE146      SET t.CountTrue = s.CountTrue,147      t.CountLoaded = s.CountLoaded,148      t.CountOverlimt = s.CountOverlimt,149      t.CountMissing = s.CountMissing,150      t.CountDelete = s.CountDelete,151      t.CountHistory = s.CountHistory,152      t.CountProcessing = s.CountProcessing,153      t.CountFalse = s.CountFalse,154      t.CountFrozen = s.CountFrozen,155      t.CountFinished = s.CountFinished,156      t.IsInvalid = 0 WHEN NOT matched BY target THEN157      INSERT(UID_QBMJobqueueOverview,158      XObjectKey,159      QueueName,160      CountTrue,161      CountLoaded,162      CountOverlimt,163      CountMissing,164      CountDelete,165      CountHistory,166      CountProcessing,167      CountFalse,168      CountFrozen,169      CountFinished,170      IsInvalid)171      VALUES(s.UID_QBMJobqueueOverview,172      s.XObjectKey,173      s.QueueName,174      s.CountTrue,175      s.CountLoaded,176      s.CountOverlimt,177      s.CountMissing,178      s.CountDelete,179      s.CountHistory,180      s.CountProcessing,181      s.CountFalse,182      s.CountFrozen,183      s.CountFinished,184      0) WHEN NOT matched BY source AND(t.IsInvalid = 1 OR @FullSearch = 1) THEN185      UPDATE186      SET t.CountTrue = 0,187      t.CountLoaded = 0,188      t.CountOverlimt = 0,189      t.CountMissing = 0,190      t.CountDelete = 0,191      t.CountHistory = 0,192      t.CountProcessing = 0,193      t.CountFalse = 0,194      t.CountFrozen = 0,195      t.CountFinished = 0,196      t.IsInvalid = 0;197      SELECT @CountRows = @@ROWCOUNT198    END TRY199    BEGIN CATCH200      SELECT @CountRows = 0201      IF @DebugSwitch > 0202      BEGIN203        print 'catch vom merge'204      END205      INSERT INTO @QueuesToUpdate(UID1)206      SELECT o.UID_QBMJobqueueOverview207      FROM @QBMJobqueueOverview o208      SELECT @ElementCount = @@ROWCOUNT209      SELECT @ElementIndex = 1210      IF @DebugSwitch > 0211      BEGIN212        print 'Zu ändern im Einzelschuß' + str(@ElementCount)213      END214      WHILE @ElementIndex <= @ElementCount215      BEGIN216        SELECT TOP 1 @UID_JobQueue = u.UID1217        FROM @QueuesToUpdate u218        WHERE219          u.ElementIndex = @ElementIndex220        IF @DebugSwitch > 0221        BEGIN222          print 'einzelupdate für ' + @UID_JobQueue223        END224        SET lock_timeout 5225        BEGIN TRY226          UPDATE QBMJobqueueOverview227          SET CountTrue = s.CountTrue,228          CountLoaded = s.CountLoaded,229          CountOverlimt = s.CountOverlimt,230          CountMissing = s.CountMissing,231          CountDelete = s.CountDelete,232          CountHistory = s.CountHistory,233          CountProcessing = s.CountProcessing,234          CountFalse = s.CountFalse,235          CountFrozen = s.CountFrozen,236          CountFinished = s.CountFinished,237          IsInvalid = 0238          FROM QBMJobqueueOverview AS t239          JOIN @QBMJobqueueOverview AS s240            ON t.UID_QBMJobqueueOverview = s.UID_QBMJobqueueOverview241          WHERE242            t.UID_QBMJobqueueOverview = @UID_JobQueue243          SELECT @CountRows += @@ROWCOUNT244        END TRY245        BEGIN CATCH246          IF @DebugSwitch > 0247          BEGIN248            print 'catch vom einzel update'249          END250        END CATCH251        SET lock_timeout -1252        SELECT @ElementIndex += 1253      END254    END CATCH255    IF @DebugSwitch > 0256    BEGIN257      print 'queues geändert ' + str(@CountRows)258      SELECT259        isinvalid,260        *261      FROM QBMJobqueueOverview o262        WITH(nolock)263    END264    IF @FullSearch = 1265    BEGIN266      IF EXISTS(267        SELECT TOP 1 1268        FROM QBMJobqueueOverview o269      LEFT270      OUTER271        JOIN QBMServer d272          ON o.UID_QBMJobqueueOverview = dbo.QBM_FCVStringToGUID('', d.QueueName)273      WHERE274        d.UID_QBMServer IS NULL)275      BEGIN276        SET lock_timeout 5277        BEGIN TRY278          DELETE QBMJobqueueOverview279          FROM QBMJobqueueOverview o280          LEFT281          OUTER282          JOIN QBMServer d283            ON o.UID_QBMJobqueueOverview = dbo.QBM_FCVStringToGUID('',284          d.QueueName)285          WHERE286            d.UID_QBMServer IS NULL287        END TRY288        BEGIN CATCH289          IF @DebugSwitch > 0290          BEGIN291            print 'catch vom egal, wir kommen wieder mal vorbei'292          END293        END CATCH294        SET lock_timeout -1295      END296    END297  END TRY298  BEGIN CATCH299    DECLARE @Message nvarchar(4000) = CONCAT(N '#LDS#Error running QBM_PJobqueueoverViewFill with FullSearch = {0}.|',300    str(@FullSearch),301    '|')302    IF @DebugSwitch > 0303    BEGIN304      print 'catch von gesamt'305    END306    EXEC QBM_PSessionErrorAdd DEFAULT307    IF dbo.QBM_FGISessionErrorIsDeadlock(DEFAULT) = 1 OR dbo.QBM_FGISessionErrorIsMissingCo(DEFAULT) = 1308    BEGIN309      SELECT @ret = 0310    END311    ELSE312    BEGIN313      RAISERROR(@Message,314      18,315      1)316        WITH NOWAIT317    END318  END CATCH319  endLabel:320  EXEC QBM_PSessionContextSet 'GenProcID',321    @GenProcID_R322  EXEC QBM_PSessionContextSet 'XUser',323    @XUser_R324  RETURN @ret325END
Open raw exported source
SQL · Raw45 lines
1   create   procedure QBM_PJobqueueoverViewFill (@FullSearch bit = 0  ) as begin declare @ret int = 0  declare @DebugSwitch int = 0 declare @CountRows2 int declare @QBMJobqueueOverview QBM_YJobqueueOverview  declare @GenProcID_R varchar(38) = dbo.QBM_FGISessionContext('') declare @XUser_R nvarchar(64)3 = dbo.QBM_FGISessionContext('XUser') declare @GenProcID varchar(38) = dbo.QBM_FGISessionContext('') declare @XUser nvarchar(64) = dbo.QBM_FGISessionContext4('XUser')  declare @QueuesToUpdate QBM_YCursorBuffer declare @ElementCount int , @ElementIndex int , @UID_JobQueue varchar(38) SET XACT_ABORT OFF BEGIN5 TRY  if isnull(@XUser, '') = '' begin select @XUser = left(Object_name(@@procid), 64) exec QBM_PSessionContextSet 'GenProcID', @GenProcID exec QBM_PSessionContextSet6 'XUser', @XUser end if @DebugSwitch > 0 begin print 'Ausgangslage ' select isinvalid, * from QBMJobqueueOverview end insert into @QBMJobqueueOverview(UID_QBMJobqueueOverview7 , XObjectKey , QueueName , CountTrue , CountLoaded , CountOverlimt , CountMissing , CountDelete , CountHistory , CountProcessing , CountFalse , CountFrozen8 , CountFinished ) select dbo.QBM_FCVStringToGUID('', y.QueueName) as UID_QBMJobqueueOverview , dbo.QBM_FCVElementToObjectKey1('QBMJobqueueOverview', 'UID_QBMJobqueueOverview'9, dbo.QBM_FCVStringToGUID('', y.QueueName)) as XObjectKey , y.QueueName , y.CountTrue , y.CountLoaded , y.CountOverlimt , y.CountMissing , y.CountDelete10 , y.CountHistory , y.CountProcessing , y.CountFalse , y.CountFrozen , y.CountFinished from ( select x.Queue as QueueName, SUM(x.CountTrue ) as CountTrue11 , SUM(x.CountLoaded ) as CountLoaded , SUM(x.CountOverlimt ) as CountOverlimt , SUM(x.CountMissing ) as CountMissing , SUM(x.CountDelete ) as CountDelete12 , SUM(x.CountHistory ) as CountHistory , SUM(x.CountProcessing ) as CountProcessing , SUM(x.CountFalse ) as CountFalse , SUM(x.CountFrozen ) as CountFrozen13 , SUM(x.CountFinished ) as CountFinished from ( select q.Queue, case q.Ready2EXE when N'TRUE' then 1 else 0 end as CountTrue , case q.Ready2EXE when N'LOADED'14 then 1 else 0 end as CountLoaded , case q.Ready2EXE when N'OVERLIMT' then 1 else 0 end as CountOverlimt , case q.Ready2EXE when N'MISSING' then 1 else15 0 end as CountMissing , case q.Ready2EXE when N'DELETE' then 1 else 0 end as CountDelete , case q.Ready2EXE when N'HISTORY' then 1 else 0 end as CountHistory16 , case q.Ready2EXE when N'PROCESSING' then 1 else 0 end as CountProcessing , case q.Ready2EXE when N'FALSE' then 1 else 0 end as CountFalse , case q.Ready2EXE17 when N'FROZEN' then 1 else 0 end as CountFrozen , case q.Ready2EXE when N'FINISHED' then 1 else 0 end as CountFinished from JobQueue q with (readpast)18 left outer join QBMJobqueueOverview o with (readpast) on q.Queue = o.QueueName where ISNULL(o.IsInvalid, 1 ) = 1 or @FullSearch = 1 ) as x group by x.Queue19 ) as y select @CountRows = @@ROWCOUNT if @DebugSwitch > 0 begin print 'queues summiert ' + str(@CountRows) select * from @QBMJobqueueOverview end   BEGIN20 TRY set lock_timeout 5  merge into QBMJobqueueOverview as t using @QBMJobqueueOverview as s on t.UID_QBMJobqueueOverview = s.UID_QBMJobqueueOverview when21 matched then update set t.CountTrue = s.CountTrue , t.CountLoaded = s.CountLoaded , t.CountOverlimt = s.CountOverlimt , t.CountMissing = s.CountMissing22 , t.CountDelete = s.CountDelete , t.CountHistory = s.CountHistory , t.CountProcessing = s.CountProcessing , t.CountFalse = s.CountFalse , t.CountFrozen23 = s.CountFrozen , t.CountFinished = s.CountFinished , t.IsInvalid = 0 when not matched by target then insert (UID_QBMJobqueueOverview , XObjectKey , QueueName24 , CountTrue , CountLoaded , CountOverlimt , CountMissing , CountDelete , CountHistory , CountProcessing , CountFalse , CountFrozen , CountFinished , IsInvalid25 ) values (s.UID_QBMJobqueueOverview , s.XObjectKey , s.QueueName , s.CountTrue , s.CountLoaded , s.CountOverlimt , s.CountMissing , s.CountDelete , s.CountHistory26 , s.CountProcessing , s.CountFalse , s.CountFrozen , s.CountFinished , 0 ) when not matched by source and ( t.IsInvalid = 1 or @FullSearch = 1) then update27 set t.CountTrue = 0 , t.CountLoaded = 0 , t.CountOverlimt = 0 , t.CountMissing = 0 , t.CountDelete = 0 , t.CountHistory = 0 , t.CountProcessing = 0 , 28t.CountFalse = 0 , t.CountFrozen = 0 , t.CountFinished = 0 , t.IsInvalid = 0 ; select @CountRows = @@ROWCOUNT END TRY BEGIN CATCH select @CountRows = 029 if @DebugSwitch > 0 begin print 'catch vom merge' end  insert into @QueuesToUpdate (UID1) select o.UID_QBMJobqueueOverview from @QBMJobqueueOverview o30 select @ElementCount = @@ROWCOUNT select @ElementIndex = 1 if @DebugSwitch > 0 begin print 'Zu ändern im Einzelschuß' + str(@ElementCount) end while @ElementIndex31 <= @ElementCount begin select top 1 @UID_JobQueue = u.UID1 from @QueuesToUpdate u where u.ElementIndex = @ElementIndex if @DebugSwitch > 0 begin print32 'einzelupdate für ' + @UID_JobQueue end set lock_timeout 5 BEGIN TRY update QBMJobqueueOverview set CountTrue = s.CountTrue , CountLoaded = s.CountLoaded33 , CountOverlimt = s.CountOverlimt , CountMissing = s.CountMissing , CountDelete = s.CountDelete , CountHistory = s.CountHistory , CountProcessing = s.CountProcessing34 , CountFalse = s.CountFalse , CountFrozen = s.CountFrozen , CountFinished = s.CountFinished , IsInvalid = 0 from QBMJobqueueOverview as t join @QBMJobqueueOverview35 as s on t.UID_QBMJobqueueOverview = s.UID_QBMJobqueueOverview where t.UID_QBMJobqueueOverview = @UID_JobQueue select @CountRows += @@ROWCOUNT END TRY 36BEGIN CATCH if @DebugSwitch > 0 begin print 'catch vom einzel update' end END CATCH set lock_timeout -1 select @ElementIndex += 1 end END CATCH if @DebugSwitch37 > 0 begin print 'queues geändert ' + str(@CountRows) select isinvalid, * from QBMJobqueueOverview o with (nolock) end if @FullSearch = 1 begin if exists38 (select top 1 1 from QBMJobqueueOverview o left outer join QBMServer d on o.UID_QBMJobqueueOverview = dbo.QBM_FCVStringToGUID('', d.QueueName) where d.UID_QBMServer39 is null ) begin  set lock_timeout 5 BEGIN TRY delete QBMJobqueueOverview from QBMJobqueueOverview o left outer join QBMServer d on o.UID_QBMJobqueueOverview40 = dbo.QBM_FCVStringToGUID('', d.QueueName) where d.UID_QBMServer is null END TRY BEGIN CATCH  if @DebugSwitch > 0 begin print 'catch vom egal, wir kommen wieder mal vorbei'41 end END CATCH set lock_timeout -1 end end END TRY BEGIN CATCH declare @Message nvarchar(4000) = concat(N'#LDS#Error running QBM_PJobqueueoverViewFill with FullSearch = {0}.|'42 , str(@FullSearch), '|' ) if @DebugSwitch > 0 begin print 'catch von gesamt' end exec QBM_PSessionErrorAdd default if dbo.QBM_FGISessionErrorIsDeadlock43(default) = 1 or dbo.QBM_FGISessionErrorIsMissingCo(default) = 1 begin  select @ret = 0  end else begin RAISERROR (@Message, 18, 1) WITH NOWAIT end END44 CATCH endLabel: exec QBM_PSessionContextSet 'GenProcID', @GenProcID_R exec QBM_PSessionContextSet 'XUser', @XUser_R return @ret end 45