dbo.QBM_PJobQueueLoad

1CREATE PROCEDURE QBM_PJobQueueLoad(2  @queue nvarchar(200),3  @Sekunden int,4  @priority int,5  @SoftwareRevisionOnServer int = 0,6  @CurrentReady nvarchar(max) = N '',7  @SessionID varchar(38) = ''8)9AS10BEGIN11  DECLARE @IsQueueDisabled BIT12  DECLARE @IsQueueSuspended BIT13  DECLARE @perf2 QBM_YJobQueueTasks14  DECLARE @jobs QBM_YSingleGUID15  DECLARE @LastTimeoutCheck datetime16  DECLARE @CountItems int17  DECLARE @uid_self varchar(38)18  DECLARE @uid_jobToCheck varchar(38)19  DECLARE @InsertCheckVersion BIT20  DECLARE @uid_QBMServer varchar(38)21  DECLARE @XObjectKeyQBMServer varchar(138)22  DECLARE @UID_ParentQBMServer varchar(38)23  DECLARE @IsMasterSQL BIT = 024  DECLARE @IsNoAutoupdate BIT25  DECLARE @PhysicalServerName nvarchar(64)26  DECLARE @IsInSoftwareUpdate BIT27  DECLARE @QueueWhereClause nvarchar(255)28  DECLARE @SoftwareRevision int29  DECLARE @uid_jobReact varchar(38)30  DECLARE @ComponentClass nvarchar(255)31  DECLARE @TaskName nvarchar(64)32  DECLARE @countDeliver int33  DECLARE @AnzahlTotal int34  DECLARE @LimitTotal int35  DECLARE @LoadedJobsTimeOut int36  DECLARE @ExistsMissing BIT37  DECLARE @JustNow datetime = getutcdate()38  DECLARE @ready QBM_YJobQueueTasks39  DECLARE @jobqueue QBM_YJobQueueContent40  DECLARE @SessionID_DB varchar(38)41  DECLARE @msg nvarchar(1000)42  DECLARE @IsQBMServiceInstalled BIT43  DECLARE @GenProcID varchar(38) = dbo.QBM_FGISessionContext('')44  DECLARE @XUser nvarchar(64) = dbo.QBM_FGISessionContext('XUser')45  DECLARE @DebugSwitch int = 046  DECLARE @DebugLevel varchar(1) = 'I'47  DECLARE @XUserSpecialString nvarchar(64) = 'QBM_PJobQueueLoad'48  DECLARE @RaiseCondition nvarchar(1000) = ''49  DECLARE @SQLTagMaster varchar(38) = 'QBM-ST-Is07'50  DECLARE @QueueName nvarchar(256)51  DECLARE @ConfigparmAutoupdate int52  DECLARE @ElementLast int53  DECLARE @ElementBufferMulti QBM_YCursorBuffer54  DECLARE @ElementCount int55  DECLARE @ElementIndex int56  DECLARE @SQLLastJobFetchTime nvarchar(max)57  DECLARE @RCAppLockRequest int58  DECLARE @MyName nvarchar(64) = object_name(@@procid)59  DECLARE @IsExclusiveContained BIT = 060  DECLARE @SimilarQueueExists BIT = 061  DECLARE @BinInWiederholung BIT = 062  DECLARE @CountLoadedJobs_float float = 0.063  DECLARE @CountDeletedJobs_float float = 0.064  DECLARE @LimitForRedoExclusive float = 0.665  DECLARE @MaxRunInWorstCase int = 1566  DECLARE @GenProcID_R varchar(38) = dbo.QBM_FGISessionContext('')67  DECLARE @XUser_R nvarchar(64) = dbo.QBM_FGISessionContext('XUser')68  SET XACT_ABORT OFF69  BEGIN TRY70    BEGIN71      TRANSACTION72      IF isnull(@XUser,73      '') = ''74      BEGIN75        SELECT @XUser =76        LEFT(Object_name(@@procid),77        64)78        EXEC QBM_PSessionContextSet 'GenProcID',79          @GenProcID80        EXEC QBM_PSessionContextSet 'XUser',81          @XUser82      END83      SET nocount84        ON85      SELECT86        TOP 1 @IsInSoftwareUpdate = isnull(IsInSoftwareUpdate,87        0),88        @uid_QBMServer = rtrim(uid_QBMServer),89        @IsNoAutoupdate = isnull(IsNoAutoupdate,90        0),91        @UID_ParentQBMServer = isnull(UID_ParentQBMServer,92        ''),93        @PhysicalServerName = isnull(PhysicalServerName,94        ''),95        @IsQueueDisabled = isnull(IsJobServiceDisabled,96        0),97        @IsQueueSuspended = isnull(IsJobServiceSuspended,98        0),99        @LastTimeoutCheck = isnull(LastTimeoutCheck,100        '1899-12-30'),101        @SessionID_DB = isnull(SessionID,102        ''),103        @IsQBMServiceInstalled = isnull(IsQBMServiceInstalled,104        0),105        @XObjectKeyQBMServer = s.XObjectKey106      FROM QBMServer s107        WITH(readpast)108      WHERE109        QueueName = @queue110      SELECT111        @SQLLastJobFetchTime = CONCAT('112	updat',113        'e QBMServer114		set LastJobFetchTime = ''',115        dbo.QBM_FCVDatetimeToString(@JustNow),116        '''117		where UID_QBMServer = ''',118        @uid_QBMServer,119        '''120	')121      SELECT122        @ConfigparmAutoupdate = dbo.QBM_FCVStringToInt(dbo.QBM_FGIConfigparmValue('Common\Autoupdate'),123        0)124      INSERT INTO @ready(ComponentClass,125      TaskName,126      CountReadyOrDeliver)127      SELECT128      LEFT(parametername,129      charindex('|', parametername) -1),130      substring(parametername,131      charindex('|', parametername) +1,132      255),133      convert(int,134      N '0' + parametervalue)135      FROM dbo.QBM_FCVStringToListSplitted(@CurrentReady,136      nchar(7),137      0,138      0,139      N '=')140      IF EXISTS(141        SELECT TOP 1 1142        FROM DialogDatabase143        WITH(readpast)144      WHERE145        ismaindatabase = 1 AND(IsJobServiceDisabled = 1 OR UpdatePhase IN(3, 4)))146      BEGIN147        SELECT @RaiseCondition = '#LDS#Processing is disabled (queues globally switched off).|'148        GOTO FehlerWerfen149      END150      IF dbo.QBM_FGISingleUserRunning() = 1151      BEGIN152        SELECT @RaiseCondition = '#LDS#Processing is disabled (single user mode running).|'153        GOTO FehlerWerfen154      END155      IF EXISTS(156        SELECT TOP 1 1157        FROM DialogDatabase d158        WITH(Readpast)159      WHERE160        d.UID_CutOffTask = 'QBM-K-CommonWaitForCompiler')161      BEGIN162        SELECT @RaiseCondition = '#LDS#Processing is disabled (wait for compiler).|'163        GOTO JournalEintragen164      END165      IF EXISTS(166        SELECT TOP 1 1167        FROM DialogScriptAssembly a168        WITH(readpast)169      WHERE170        a.IsValid = 0)171      BEGIN172        SELECT @RaiseCondition = '#LDS#Processing is disabled (wait for compiler).|'173        GOTO JournalEintragen174      END175      IF dbo.QBM_FGIDBOwner() = 'CCC' AND @ConfigparmAutoupdate = 1 AND EXISTS(176        SELECT TOP 1 1177        FROM QBMFileRevision r178        WITH(readpast)179      WHERE180        (r.UID_QBMFileRevision = 'QBM-D004E74592BB32809960DB77764C1EBE' OR r.FileName = 'VI.DB.dll') AND r.FileContent IS NULL)181      BEGIN182        SELECT183          @RaiseCondition = '#LDS#Processing is disabled (wait for filled QBMFileRevision).|'184          GOTO JournalEintragen185      END186      IF EXISTS(187        SELECT TOP 1 1188        FROM sys.triggers t189        WITH(nolock)190      JOIN sys.tables ta191        WITH(nolock)192        ON t.parent_id = ta.object_id193      JOIN DialogTable dt194        WITH(readpast)195        ON ta.name = dt.TableName collate database_default196      WHERE197        t.name LIKE '___[_]%' AND t.is_disabled = 1 AND(dbo.QBM_FGIModuleExists(198      LEFT(t.name, 3)) = 1 OR t.name LIKE 'GEN[_]%'))199      BEGIN200        SELECT @RaiseCondition = '#LDS#Processing is disabled (deactivated trigger(s)).|'201        GOTO JournalEintragen202      END203      IF EXISTS(204        SELECT TOP 1 1205        FROM sys.foreign_keys c206        WITH(nolock)207      JOIN sys.objects t208        WITH(nolock)209        ON c.parent_object_id = t.object_id210      JOIN DialogTable dt211        WITH(readpast)212        ON t.name = dt.TableName collate database_default213      WHERE214        c.is_disabled = 1)215      BEGIN216        SELECT @RaiseCondition = '#LDS#Processing is disabled (deactivated constraint(s)).|'217        GOTO JournalEintragen218      END219      SELECT @InsertCheckVersion = 0220      IF @IsQueueDisabled = 1 OR @IsQueueSuspended = 1221      BEGIN222        SELECT223          @RaiseCondition = '#LDS#Processing is disabled (the queue "{0}" is switched off).|' + @queue + '|'224          GOTO FehlerWerfen225      END226      IF EXISTS(227        SELECT TOP 1 1228        FROM QBMServerHasServerTag ht229        WITH(readpast)230      WHERE231        ht.UID_QBMServer = @UID_QBMServer AND ht.UID_QBMServerTag = 'QBM-ST-Is07')232      BEGIN233        SELECT @isMasterSQL = 1234      END235      IF @uid_QBMServer > ' ' AND @SessionID > ' ' AND isnull(@SessionID_DB,236      '') = ''237      BEGIN238        EXEC QBM_PSessionContextSet 'GenProcID',239          @GenProcID240        EXEC QBM_PSessionContextSet 'XUser',241          @XUser242        UPDATE QBMServer243        SET SessionID = @SessionID,244        XDateUpdated = @JustNow,245        XUserUpdated = @XUser246        WHERE247          uid_QBMServer = @uid_QBMServer248        SELECT @SessionID_DB = @SessionID249      END250      IF @SessionID_DB <> isnull(@SessionID,251      '')252      BEGIN253        SELECT254          @RaiseCondition = '#LDS#Session ID for queue {0} does not match. DB: {1} Query:{2}.|' + @queue + N '|' + @SessionID_DB + N '|' + isnull255          (@SessionID,256          N '') + N '|'257          GOTO FehlerWerfen258      END259      IF @IsQueueDisabled = 1 OR @IsQueueSuspended = 1260      BEGIN261        GOTO ende262      END263      SELECT @QueueWhereClause = 'XObjectKey in (''' + @XObjectKeyQBMServer + ''')'264      IF @IsInSoftwareUpdate = 0 AND EXISTS(265        SELECT TOP 1 1266        FROM QBMServer js267        WITH(readpast)268      WHERE269        js.PhysicalServerName = @PhysicalServerName AND js.uid_QBMServer <> @uid_QBMServer AND js.IsInSoftwareUpdate = 1)270      BEGIN271        IF @isMasterSQL = 0272        BEGIN273          GOTO ende274        END275      END276      IF @IsNoAutoupdate = 0 AND @ConfigparmAutoupdate = 1277      BEGIN278        SELECT TOP 1 @SoftwareRevision = ChangeCounter279        FROM DialogSemaphor280          WITH(readpast)281        WHERE282          ChangeContext = 'SOFTWAREREVISION'283        IF @SoftwareRevision IS NULL284        BEGIN285          SELECT @SoftwareRevision = 0286        END287        IF(@SoftwareRevision <> @SoftwareRevisionOnServer)288        BEGIN289          IF NOT EXISTS(290            SELECT TOP 1 1291            FROM JobQueue q292            WITH(nolock)293          LEFT294          OUTER295          JOIN QBMElementAffectedByJob e296            WITH(nolock)297            ON q.UID_Job = e.UID_Job298          WHERE299            q.TaskName = 'FireGenEvent' AND q.ComponentAssembly = 'HandleObjectComponent' AND q.ParamIN LIKE N '%ObjectType~9~QBMServer%' AND q.ParamIN300          LIKE N '%EventName~12~Checkversion%' AND e.ObjectKeyAffected = @XObjectKeyQBMServer) AND NOT EXISTS(301          SELECT TOP 1 1302          FROM JobQueue q303            WITH(nolock)304          LEFT305          OUTER306          JOIN QBMElementAffectedByJob e307            WITH(nolock)308            ON q.UID_Job = e.UID_Job309          WHERE310            q.JobChainName IN('vid_jobserverUpdateCheck', 'vid_jobserverUpdateReplace') AND e.ObjectKeyAffected = @XObjectKeyQBMServer)311          BEGIN312            SELECT @InsertCheckVersion = 1313          END314        END315        IF @SoftwareRevision <> @SoftwareRevisionOnServer316        BEGIN317          SELECT @IsInSoftwareUpdate = 1318        END319        ELSE320        BEGIN321          SELECT @IsInSoftwareUpdate = 0322        END323        IF @insertCheckVersion = 1324        BEGIN325          SELECT TOP 1 @QueueName = s.QueueName326          FROM QBMServer s327            WITH(readpast)328          JOIN QBMServerHasServerTag sht329            WITH(readpast)330            ON s.UID_QBMServer = sht.UID_QBMServer331          WHERE332            sht.UID_QBMServerTag = @SQLTagMaster AND s.QueueName > ' '333          EXEC QBM_PJobCreate_HOFireEvent_B 'QBMServer',334            @QueueWhereClause,335          'Checkversion',336            @GenProcID,337            @AdditionalObjectKeysAffected = DEFAULT,338            @QueueName = @QueueName,339            @checkForExisting = 1340        END341        EXEC QBM_PSessionContextSet 'GenProcID',342          @GenProcID343        EXEC QBM_PSessionContextSet 'XUser',344          @XUser345        UPDATE QBMServer346        SET IsInSoftwareUpdate = @IsInSoftwareUpdate,347        XDateUpdated = @JustNow,348        XUserUpdated = @XUser349        WHERE350          uid_QBMServer = @uid_QBMServer AND isnull(IsInSoftwareUpdate,351        0) <> @IsInSoftwareUpdate352        IF @IsInSoftwareUpdate = 1353        BEGIN354          GOTO EchteDaten355        END356      END357      IF @IsQBMServiceInstalled = 0358      BEGIN359        EXEC QBM_PQBMServerHasJobService @uid_QBMServer360      END361      IF NOT EXISTS(362        SELECT TOP 1 1363        FROM QBMJobqueueOverview o364        WITH(readpast)365      WHERE366        o.QueueName = @queue AND(o.CountTrue > 0 OR o.CountLoaded > 0 OR o.CountOverlimt > 0 OR o.CountMissing > 0 OR o.CountProcessing > 0 OR o.CountFalse367      > 0 OR o.CountFrozen > 0) AND o.IsInitQueueRunning = 0)368      BEGIN369        GOTO ende370      END371      NachOverlimit:372      SELECT373        @LoadedJobsTimeOut = dbo.QBM_FCVStringToInt(dbo.QBM_FGIConfigparmValue('Common\Jobservice\LoadedJobsTimeOut'),374        0)375      IF EXISTS(376        SELECT TOP 1 1377        FROM QBMJobqueueOverview o378        WITH(readpast)379      WHERE380        o.QueueName = @queue AND o.CountMissing > 0)381      BEGIN382        SELECT @ExistsMissing = 1383      END384      ELSE385      BEGIN386        SELECT @ExistsMissing = 0387      END388      IF @ExistsMissing = 1389      BEGIN390        SELECT @uid_self =391        RIGHT(rtrim(newid()) + 'RA',392        38)393        INSERT INTO @jobqueue(UID_Job,394        UID_Tree,395        UID_JobSameServer,396        TaskName,397        ComponentClass,398        ComponentAssembly,399        ExecutionType,400        ParamIN,401        StartAt,402        Retries,403        Priority,404        DeferOnError,405        MinutesToDefer,406        IgnoreErrors,407        MaxInstance,408        UID_JobError,409        UID_JobSuccess,410        IsSplitOnly)411        SELECT412          @uid_self,413          newid(),414          newid(),415          'InitQueue',416          'VI.JobService.JobComponents.JobCheckComponent',417          'JobService',418          'INTERNAL',419          N '~' + dbo.QBM_FCVStringToJobParameter(N 'uid_job') + dbo.QBM_FCVStringToJobParameter(newid()) + dbo.QBM_FCVStringToJobParameter(N '') +dbo.QBM_FCVStringToJobParameter420          (N 'Queue') + dbo.QBM_FCVStringToJobParameter(@queue) + dbo.QBM_FCVStringToJobParameter(N '') +dbo.QBM_FCVStringToJobParameter(N 'uid_self') +421          dbo.QBM_FCVStringToJobParameter(@uid_self) + dbo.QBM_FCVStringToJobParameter(N ''),422          @JustNow,423          1,424          15,425          0,426          0,427          1,428          1,429          '',430          '',431          0432          GOTO Ausliefern433      END434      IF datediff(mi,435      @LastTimeoutCheck,436      @JustNow) > @LoadedJobsTimeOut437      BEGIN438        EXEC QBM_PSessionContextSet 'GenProcID',439          @GenProcID440        EXEC QBM_PSessionContextSet 'XUser',441          @XUser442        UPDATE QBMServer443        SET LastTimeoutCheck = @JustNow,444        XDateUpdated = @JustNow,445        XUserUpdated = @XUser446        WHERE447          uid_QBMServer = @uid_QBMServer448        IF EXISTS(449          SELECT TOP 1 1450          FROM QBMJobqueueOverview o451          WITH(readpast)452        WHERE453          o.QueueName = @queue AND o.CountLoaded + o.CountProcessing > 0)454        BEGIN455          IF EXISTS(456            SELECT TOP 1 1457            FROM jobqueue q458            WITH(READPAST)459          WHERE460            ready2exe IN(N 'LOADED', N 'PROCESSING') AND queue = @queue AND datediff(mi, xdateupdated, @JustNow) > @LoadedJobsTimeOut)461          BEGIN462            IF EXISTS(463              SELECT TOP 1 1464              FROM jobqueue q465              WITH(READPAST)466            WHERE467              ready2exe IN(N 'LOADED', N 'PROCESSING') AND queue = @queue AND datediff(mi, xdateupdated, @JustNow) > @LoadedJobsTimeOut AND q.uid_job468            = dbo.QBM_FGIJobQueueTopJob(q.uid_tree) AND NOT EXISTS(469            SELECT TOP 1 1470            FROM @ready p471            WHERE472              p.ComponentClass = q.componentclass AND p.taskname = q.taskname))473            BEGIN474              GOTO Reaktivierung475            END476          END477        END478      END479      GOTO EchteDaten Reaktivierung:480      DELETE @ElementBufferMulti481      INSERT INTO @ElementBufferMulti(UID1)482      SELECT uid_job483      FROM jobqueue q484        WITH(READPAST)485      WHERE486        ready2exe IN(N 'LOADED',487      N 'PROCESSING') AND queue = @queue AND q.uid_job = dbo.QBM_FGIJobQueueTopJob(q.uid_tree) AND datediff(mi,488      xdateupdated,489      @JustNow) > @LoadedJobsTimeOut AND NOT EXISTS(490      SELECT TOP 1 1491      FROM @ready p492      WHERE493        p.ComponentClass = q.componentclass AND p.taskname = q.taskname)494      SELECT @ElementCount = @@ROWCOUNT495      SELECT @ElementIndex = @@IDENTITY - @ElementCount +1496      SELECT @ElementLast = @@IDENTITY497      WHILE @ElementIndex <= @ElementLast498      BEGIN499        SELECT TOP 1 @uid_jobToCheck = bu.UID1500        FROM @ElementBufferMulti bu501        WHERE502          bu.ElementIndex = @ElementIndex503        SELECT @uid_self =504        RIGHT(rtrim(newid()) + 'RA',505        38)506        INSERT INTO @jobqueue(UID_Job,507        UID_Tree,508        UID_JobSameServer,509        TaskName,510        ComponentClass,511        ComponentAssembly,512        ExecutionType,513        ParamIN,514        StartAt,515        Retries,516        Priority,517        DeferOnError,518        MinutesToDefer,519        IgnoreErrors,520        MaxInstance,521        UID_JobError,522        UID_JobSuccess,523        IsSplitOnly,524        LogMode)525        SELECT526          @uid_self,527          newid(),528          @uid_jobToCheck,529          'CheckJob',530          'VI.JobService.JobComponents.JobCheckComponent',531          'JobService',532          'INTERNAL',533          N '~' + dbo.QBM_FCVStringToJobParameter(N 'uid_job') + dbo.QBM_FCVStringToJobParameter(q.uid_job) + dbo.QBM_FCVStringToJobParameter(N '') +dbo.QBM_FCVStringToJobParameter534          (N 'Queue') + dbo.QBM_FCVStringToJobParameter(@queue) + dbo.QBM_FCVStringToJobParameter(N '') +dbo.QBM_FCVStringToJobParameter(N 'uid_self') +535          dbo.QBM_FCVStringToJobParameter(@uid_self) + dbo.QBM_FCVStringToJobParameter(N ''),536          @JustNow,537          q.Retries,538          q.Priority,539          q.DeferOnError,540          q.MinutesToDefer,541          1,542          1,543          '',544          '',545          0,546          q.LogMode547        FROM jobqueue q548          WITH(readpast)549        WHERE550          uid_job = @uid_jobToCheck551        SELECT @ElementIndex += 1552      END553      DELETE @ElementBufferMulti554      INSERT INTO @ElementBufferMulti(UID1)555      SELECT556        DISTINCT uid_jobSameserver557      FROM @jobqueue558      SELECT @ElementCount = @@ROWCOUNT559      SELECT @ElementIndex = @@IDENTITY - @ElementCount +1560      SELECT @ElementLast = @@IDENTITY561      WHILE @ElementIndex <= @ElementLast562      BEGIN563        SELECT TOP 1 @uid_jobReact = bu.UID1564        FROM @ElementBufferMulti bu565        WHERE566          bu.ElementIndex = @ElementIndex567        UPDATE jobqueue568        SET xdateupdated = @JustNow,569        XUserUpdated = @XUserSpecialString570        WHERE571          uid_job = @uid_jobReact572        SELECT @ElementIndex += 1573      END574      GOTO Ausliefern EchteDaten:575      IF @DebugSwitch > 0576      BEGIN577        print 'EchteDaten: erreicht'578      END579      IF @sekunden <= 30580      SELECT @LimitTotal = 3000581      IF @sekunden > 30582      SELECT @LimitTotal = 4500583      IF @sekunden > 60584      SELECT @LimitTotal = 6000585      IF NOT EXISTS(586        SELECT TOP 1 1587        FROM QBMJobqueueOverview o588        WITH(readpast)589      WHERE590        o.QueueName = @queue AND o.CountTrue > 0)591      BEGIN592        GOTO ausliefern593      END594      IF NOT EXISTS(595        SELECT TOP 1 1596        FROM JobPerformance p597        WITH(nolock)598      WHERE599        p.Queue = @queue)600      BEGIN601        INSERT INTO @perf2(ComponentClass,602        TaskName,603        CountReadyOrDeliver)604        SELECT605          c.ComponentClass,606          t.TaskName,607          100608        FROM JobComponent c609          WITH(readpast)610        JOIN JobTask t611          WITH(readpast)612          ON c.UID_JobComponent = t.UID_JobComponent613        WHERE614          EXISTS(615        SELECT TOP 1 1616        FROM jobqueue q617          WITH(readpast)618        WHERE619          q.queue = @queue AND q.ready2exe = N 'TRUE' AND q.Componentclass = c.ComponentClass AND q.Taskname = t.TaskName)620      END621      ELSE622      BEGIN623        INSERT INTO @perf2(ComponentClass,624        TaskName,625        CountReadyOrDeliver)626        SELECT627          ComponentClass,628          TaskName,629          countDeliver630        FROM(631        SELECT632          c.ComponentClass,633          t.TaskName,634          dbo.QBM_FGIJobQueueCountDeliver(isnull(jp.CountPerMinute, 1), @Sekunden, isnull(r.CountReadyOrDeliver,635          0), @Priority) * 2 AS countDeliver636        FROM JobComponent c637          WITH(readpast)638        JOIN JobTask t639          WITH(readpast)640          ON c.UID_JobComponent = t.UID_JobComponent641        LEFT642        OUTER643        JOIN JobPerformance jp644          WITH(nolock)645          ON c.Componentclass = jp.ComponentClass AND t.Taskname = jp.Taskname AND jp.queue = @queue646        LEFT647        OUTER648        JOIN @ready r649          ON jp.Componentclass = r.ComponentClass AND jp.Taskname = r.Taskname) AS p650        WHERE651          p.countDeliver <> 0 AND EXISTS(652        SELECT TOP 1 1653        FROM jobqueue q654          WITH(readpast)655        WHERE656          q.queue = @queue AND q.ready2exe = N 'TRUE' AND q.Componentclass = p.componentClass AND q.Taskname = p.TaskName)657      END658      SELECT @AnzahlTotal = sum(abs(CountReadyOrDeliver))659      FROM @perf2660      IF @AnzahlTotal > @LimitTotal661      BEGIN662        UPDATE @perf2663        SET CountReadyOrDeliver = convert(int,664        ((convert(float, CountReadyOrDeliver) * convert(float, @LimitTotal)) /convert(float, @anzahlTotal)))665        WHERE666          abs(CountReadyOrDeliver) > 10667      END668      redoExclusive:669      DELETE @ElementBufferMulti670      INSERT INTO @ElementBufferMulti(ContentShort,671      ObjectKey1,672      Int1)673      SELECT674        p.ComponentClass,675        p.TaskName,676        p.CountReadyOrDeliver677      FROM @perf2 p678      SELECT @ElementCount = @@ROWCOUNT679      SELECT @ElementIndex = @@IDENTITY - @ElementCount +1680      SELECT @ElementLast = @@IDENTITY681      WHILE @ElementIndex <= @ElementLast682      BEGIN683        SELECT684          TOP 1 @ComponentClass = bu.ContentShort,685          @TaskName = bu.ObjectKey1,686          @countDeliver = bu.Int1687        FROM @ElementBufferMulti bu688        WHERE689          bu.ElementIndex = @ElementIndex690        IF @IsInSoftwareUpdate = 1691        BEGIN692          SELECT @countDeliver = abs(@countDeliver)693          INSERT INTO @jobs(UID_SingleGuid)694          SELECT TOP(@countDeliver) q.uid_job695          FROM JobQueue q696            WITH(readpast)697          LEFT698          OUTER699          JOIN QBMElementAffectedByJob e700            WITH(nolock)701            ON q.UID_Job = e.UID_Job702          WHERE703            q.queue = @queue AND q.ComponentClass = @ComponentClass AND q.TaskName = @Taskname AND ready2exe = N 'TRUE' AND priority >= @Priority AND704          isnull(startat,705          '1899-12-30') < @JustNow AND(JobChainName IN('VID_JobserverUpdateCheck', 'VID_JobserverUpdateReplace',706          'AutoUpdate', 'QBM_GetDeployTargets', 'QBM_OnDeployTargetChanged') OR(@IsMasterSQL = 1 AND TaskName = 'FireGenEvent' AND ComponentAssembly = 'HandleObjectComponent' AND707          ParamIN LIKE N '%ObjectType~9~QBMServer%' AND ParamIN LIKE N '%EventName~12~Checkversion%' AND e.ObjectKeyAffected = @XObjectKeyQBMServer)) AND708          (@IsMasterSQL = 1 OR(@IsMasterSQL = 0 AND NOT EXISTS(709          SELECT TOP 1 1710          FROM QBMServer s711            WITH(readpast)712          JOIN QBMServerHasServerTag sht713            WITH(readpast)714            ON s.UID_QBMServer = sht.UID_QBMServer715          WHERE716            sht.UID_QBMServerTag = 'QBM-ST-Is07' AND s.IsInSoftwareUpdate = 1))) AND(@IsMasterSQL = 1 OR(@IsMasterSQL = 0 AND NOT EXISTS(717          SELECT TOP 1 1718          FROM QBMServer719            WITH(readpast)720          WHERE721            uid_QBMServer = @UID_ParentQBMServer AND IsInSoftwareUpdate = 1)))722          ORDER BY priority DESC,723          startat ASC724        END725        ELSE726        BEGIN727          IF @countDeliver < 0728          BEGIN729            SELECT @countDeliver = abs(@countDeliver)730            INSERT INTO @jobs(UID_SingleGuid)731            SELECT TOP(@countDeliver) q.uid_job732            FROM jobqueue q733              WITH(readpast)734            WHERE735              q.queue = @queue AND q.ComponentClass = @ComponentClass AND q.TaskName = @Taskname AND ready2exe = N 'TRUE' AND priority >= @Priority AND736            isnull(startat,737            '1899-12-30') < @JustNow738            ORDER BY priority DESC,739            startat ASC740          END741          ELSE742          BEGIN743            INSERT INTO @jobs(UID_SingleGuid)744            SELECT TOP(@countDeliver) q.uid_job745            FROM jobqueue q746              WITH(readpast)747            WHERE748              q.queue = @queue AND q.ComponentClass = @ComponentClass AND q.TaskName = @Taskname AND ready2exe = N 'TRUE' AND isnull(startat,749            '1899-12-30') < @JustNow750            ORDER BY priority DESC,751            startat ASC752          END753        END754        SELECT @ElementIndex += 1755      END756      SELECT @CountItems = count(*)757      FROM @jobs758      IF @CountItems = 0759      BEGIN760        GOTO ausliefern761      END762      INSERT INTO @jobqueue(UID_Job,763      UID_Tree,764      UID_JobSameServer,765      TaskName,766      ComponentClass,767      ComponentAssembly,768      ExecutionType,769      ParamIN,770      StartAt,771      Retries,772      Priority,773      DeferOnError,774      MinutesToDefer,775      IgnoreErrors,776      MaxInstance,777      UID_JobError,778      UID_JobSuccess,779      IsSplitOnly,780      IsToFreezeOnError,781      GenProcID,782      LogMode)783      SELECT784        q.UID_Job,785        q.UID_Tree,786        q.UID_JobSameServer,787        q.TaskName,788        q.ComponentClass,789        q.ComponentAssembly,790        q.ExecutionType,791        q.ParamIN,792        isnull(q.StartAt,793        '1899-12-30'),794        q.Retries,795        q.Priority,796        q.DeferOnError,797        q.MinutesToDefer,798        q.IgnoreErrors,799        q.MaxInstance,800        q.UID_JobError,801        q.UID_JobSuccess,802        q.IsSplitOnly,803        q.IsToFreezeOnError,804        q.GenProcID,805        q.LogMode806      FROM jobqueue q807        WITH(readpast)808      JOIN @jobs j809        ON q.uid_jobsameserver = j.UID_SingleGuid810      SELECT811        @CountLoadedJobs_float = CONVERT(float,812        @@rowcount)813      UPDATE @jobqueue814      SET IsExclusivePerObject = 1815      FROM @jobqueue q816      JOIN JobComponent c817        WITH(readpast)818        ON q.ComponentClass = c.ComponentClass819      JOIN JobTask t820        WITH(readpast)821        ON c.UID_JobComponent = t.UID_JobComponent AND t.TaskName = q.TaskName822      WHERE823        t.IsExclusivePerObject = 1 AND q.IsExclusivePerObject = 0824      SELECT @IsExclusiveContained = sign(@@ROWCOUNT)825      IF @IsExclusiveContained = 1 AND @BinInWiederholung = 0826      BEGIN827        EXEC @RCAppLockRequest = sys.sp_getapplock @Resource = @MyName,828          @LockMode= 'Exclusive',829          @LockOwner= 'Transaction',830          @LockTimeout = 2000831        IF @RCAppLockRequest < 0832        BEGIN833          DELETE @jobqueue834          WHERE835            UID_Tree IN(836          SELECT837            DISTINCT v.UID_Tree838          FROM @jobqueue v839          WHERE840            v.IsExclusivePerObject = 1)841          SELECT @IsExclusiveContained = 0842        END843      END844      IF @IsExclusiveContained = 1845      BEGIN846        SELECT @SimilarQueueExists = 1847        WHERE848          EXISTS(849        SELECT TOP 1 1850        FROM QBMServer ich851        JOIN QBMServerHasServerTag ht852          ON ich.UID_QBMServer = ht.UID_QBMServer853        JOIN QBMServerHasServerTag htd854          ON ht.UID_QBMServerTag = htd.UID_QBMServerTag855        JOIN QBMServer du856          ON htd.UID_QBMServer = du.UID_QBMServer857        WHERE858          ich.QueueName = @QueueName AND du.QueueName <> @QueueName)859      END860      IF @IsExclusiveContained = 1861      BEGIN862        DELETE @jobqueue863        FROM @jobqueue q864        JOIN(865        SELECT qw.UID_JobSameServer866        FROM @jobqueue qw867        JOIN QBMElementAffectedByJob ew868          ON qw.UID_Job = ew.UID_Job869        JOIN(870        SELECT871          qd.ComponentClass, qd.TaskName, ad.ObjectKeyAffected, substring(min(CONCAT(dbo.QBM_FCVDatetimeToString(qd.StartAt),872          qd.UID_JobSameServer)), 24, 38) AS UID_JobSameServerToKeep873        FROM @jobqueue qd874        JOIN QBMElementAffectedByJob ad875          ON qd.UID_Job = ad.UID_Job876        WHERE877          qd.IsExclusivePerObject = 1878        GROUP BY qd.ComponentClass, qd.TaskName, ad.ObjectKeyAffected879        HAVING COUNT(*) > 1) AS bl880          ON qw.ComponentClass = bl.ComponentClass AND qw.TaskName = bl.TaskName AND ew.ObjectKeyAffected = bl.ObjectKeyAffected AND qw.UID_JobSameServer881        <> bl.UID_JobSameServerToKeep882        GROUP BY qw.UID_JobSameServer) AS y883          ON q.UID_JobSameServer = y.UID_JobSameServer884        SELECT885          @CountDeletedJobs_float = CONVERT(float,886          @@rowcount)887        IF @DebugSwitch > 0888        BEGIN889          SELECT890            'count @JobQueue 3',891            COUNT(*)892          FROM @jobqueue893        END894      END895      IF @IsExclusiveContained = 1896      BEGIN897        DELETE @jobqueue898        FROM @jobqueue q899        WHERE900          q.UID_JobSameServer IN(901        SELECT z.UID_JobSameServer902        FROM(903        SELECT904          l.UID_JobSameserver, ra.UID_Job, l.ComponentClass, l.TaskName905        FROM @jobqueue l906        JOIN QBMElementAffectedByJob la907          WITH(readpast)908          ON l.UID_Job = la.UID_Job AND l.IsExclusivePerObject = 1909        JOIN QBMElementAffectedByJob ra910          WITH(readpast)911          ON la.ObjectKeyAffected = ra.ObjectKeyAffected AND la.UID_Job <> ra.UID_Job) AS z912        JOIN JobQueue r913          WITH(nolock)914          ON z.ComponentClass = r.ComponentClass AND z.TaskName = r.TaskName AND z.UID_Job = r.UID_Job AND r.Ready2EXE IN(N 'LOADED',915        N 'PROCESSING'))916        SELECT917          @CountDeletedJobs_float += CONVERT(float,918          @@rowcount)919      END920      IF @IsExclusiveContained = 1 AND @BinInWiederholung = 0 AND @CountLoadedJobs_float > 0.0 AND @CountDeletedJobs_float > 0.0921      BEGIN922        SELECT @BinInWiederholung = 1923        IF @CountDeletedJobs_float / @CountLoadedJobs_float > @LimitForRedoExclusive924        BEGIN925          UPDATE @perf2926          SET CountReadyOrDeliver = y.Hochrechnung927          FROM @perf2 p2928          JOIN(929          SELECT930            x.ComponentClass,931            x.TaskName,932            MAX(x.Hochrechnung) AS Hochrechnung933          FROM(934          SELECT935            p.ComponentClass, p.TaskName, CASE936            WHEN @CountLoadedJobs_float = 0.0 OR @CountDeletedJobs_float = @CountLoadedJobs_float THEN937            10938          ELSE CONVERT(int, CONVERT(float, p.CountReadyOrDeliver) *(1.0 /(1.0 -(@CountDeletedJobs_float / @CountLoadedJobs_float))))939          END AS Hochrechnung940          FROM @perf2 p941          JOIN(942          SELECT943            q.ComponentClass, q.TaskName944          FROM @jobqueue q945          WHERE946            q.UID_Job = q.UID_JobSameServer AND q.UID_JobSameServer IN(947          SELECT ex.UID_JobSameServer948          FROM @jobqueue ex949          WHERE950            ex.IsExclusivePerObject = 1)951          GROUP BY q.ComponentClass, q.TaskName) ro952            ON p.ComponentClass = ro.ComponentClass AND p.TaskName = ro.TaskName953          UNION all954          SELECT955            p.ComponentClass, p.TaskName, p.CountPerMinute * @MaxRunInWorstCase AS Hochrechnung956          FROM JobPerformance p957          JOIN(958          SELECT959            q.ComponentClass, q.TaskName960          FROM @jobqueue q961          WHERE962            q.UID_Job = q.UID_JobSameServer AND q.UID_JobSameServer IN(963          SELECT ex.UID_JobSameServer964          FROM @jobqueue ex965          WHERE966            ex.IsExclusivePerObject = 1)967          GROUP BY q.ComponentClass, q.TaskName) ro968            ON p.ComponentClass = ro.ComponentClass AND p.TaskName = ro.TaskName969          WHERE970            p.Queue = @queue) AS x971          GROUP BY x.ComponentClass,972          x.TaskName) AS y973            ON p2.ComponentClass = y.ComponentClass AND p2.TaskName = y.TaskName974          DELETE @jobs975          DELETE @jobqueue976          GOTO RedoExclusive977        END978      END979      INSERT INTO @jobqueue(UID_Job,980      UID_Tree,981      ParamIN)982      SELECT983        '__jobtreeparamcoll',984        x.uid_tree,985        dbo.QBM_FCVJobParameterToString(x.uid_tree)986      FROM(987      SELECT988        DISTINCT q.uid_tree989      FROM @jobqueue q990      JOIN jobtreeparamcoll c991        WITH(readpast)992        ON q.uid_tree = c.uid_tree) AS x993      BEGIN994        TRANSACTION save TRANSACTION WirLaden995        UPDATE jobqueue996        SET ready2exe = N 'LOADED',997        xdateupdated = @JustNow,998        XUserUpdated = @XUserSpecialString999        WHERE1000          uid_job IN(1001        SELECT uid_job1002        FROM @jobqueue)1003        SELECT @CountItems = @@rowcount1004        IF @IsExclusiveContained = 1 AND @SimilarQueueExists = 11005        BEGIN1006          IF EXISTS(1007            SELECT TOP 1 11008            FROM @jobqueue q1009            WHERE1010              q.UID_JobSameServer IN(1011            SELECT z.UID_JobSameServer1012            FROM(1013            SELECT1014              l.UID_JobSameserver, ra.UID_Job, l.ComponentClass, l.TaskName1015            FROM @jobqueue l1016            JOIN QBMElementAffectedByJob la1017            WITH(readpast)1018            ON l.UID_Job = la.UID_Job AND l.IsExclusivePerObject = 11019          JOIN QBMElementAffectedByJob ra1020            WITH(readpast)1021            ON la.ObjectKeyAffected = ra.ObjectKeyAffected AND la.UID_Job <> ra.UID_Job) AS z1022          JOIN JobQueue r1023            WITH(nolock)1024            ON z.ComponentClass = r.ComponentClass AND z.TaskName = r.TaskName AND z.UID_Job = r.UID_Job AND r.Ready2EXE IN(N 'LOADED',1025          N 'PROCESSING')1026          WHERE1027            r.Queue <> @QueueName))1028          BEGIN1029            DELETE @jobqueue1030            SELECT @CountItems = 01031          END1032        END1033        IF @CountItems = 01034        BEGIN1035          IF XACT_STATE() <> -11036          BEGIN1037            ROLLBACK TRANSACTION WirLaden;1038          END1039        END1040        COMMIT TRANSACTION1041        IF @CountItems > 01042        BEGIN1043          IF '1' = dbo.QBM_FGIConfigparmValue('Common\JobQueueStats')1044          BEGIN1045            INSERT INTO JobQueueStats(UID_JobQueueStats,1046            queue,1047            jobYear,1048            jobMonth,1049            jobDay,1050            jobHour,1051            countInserted,1052            countActivated,1053            countDeleted)1054            SELECT1055              'XX' +convert(varchar(64),1056              newid()),1057              @queue,1058              year(@JustNow),1059              month(@JustNow),1060              day(@JustNow),1061              datepart(hh,1062              @JustNow),1063              0,1064              (-1) * @CountItems,1065              01066          END1067        END1068        Ausliefern:1069        EXEC QBM_PSessionContextSet 'GenProcID',1070          @GenProcID1071        EXEC QBM_PSessionContextSet 'XUser',1072          @XUser1073        IF @@TRANCOUNT > 01074        BEGIN1075          COMMIT TRANSACTION1076        END1077        EXEC QBM_PExecuteSQLWithRetry_LLP @SQLStatement = @SQLLastJobFetchTime,1078          @LockTimeout_ms = 50,1079          @MaxWaitTimeForLock_s = 0.5,1080          @ProcIDForJournal = DEFAULT,1081          @HandleErrorSilent = 11082        SELECT *1083        FROM @jobqueue1084        ORDER BY uid_tree,1085        uid_job1086        RETURN FehlerWerfen:1087        IF @@TRANCOUNT > 01088        BEGIN1089          COMMIT TRANSACTION1090        END1091        EXEC QBM_PExecuteSQLWithRetry_LLP @SQLStatement = @SQLLastJobFetchTime,1092          @LockTimeout_ms = 50,1093          @MaxWaitTimeForLock_s = 0.5,1094          @ProcIDForJournal = DEFAULT,1095          @HandleErrorSilent = 11096        IF @RaiseCondition > ' '1097        BEGIN1098          RAISERROR(@RaiseCondition,1099          18,1100          1)1101            WITH nowait1102        END1103        RETURN JournalEintragen:1104        IF @RaiseCondition > ' '1105        BEGIN1106          EXEC QBM_PJournal @RaiseCondition,1107            @@procid,1108          'W',1109            @DebugLevel,1110            @SuppressEntriesCount = 101111        END1112        DELETE @jobqueue1113        GOTO ende1114      END TRY1115      BEGIN CATCH1116        EXEC QBM_PSessionErrorAdd DEFAULT1117        EXEC QBM_PSessionContextSet 'GenProcID',1118          @GenProcID_R1119        EXEC QBM_PSessionContextSet 'XUser',1120          @XUser_R1121        RAISERROR('',1122        18,1123        1)1124          WITH NOWAIT1125        RETURN1126      END CATCH1127      ende:1128      IF @@TRANCOUNT > 01129      BEGIN1130        COMMIT TRANSACTION1131      END1132      EXEC QBM_PExecuteSQLWithRetry_LLP @SQLStatement = @SQLLastJobFetchTime,1133        @LockTimeout_ms = 50,1134        @MaxWaitTimeForLock_s = 0.5,1135        @ProcIDForJournal = DEFAULT,1136        @HandleErrorSilent = 11137      EXEC QBM_PSessionContextSet 'GenProcID',1138        @GenProcID_R1139      EXEC QBM_PSessionContextSet 'XUser',1140        @XUser_R1141      SELECT *1142      FROM @jobqueue1143      ORDER BY uid_tree,1144      uid_job1145      RETURN1146    END