Back to OIM Explorer

dbo.QBM_PJobCreate

Stored ProcedureSQL_STORED_PROCEDURESandbox DB

Stored Procedure. References QBM_PJobCreate*

Source: sandbox-db sys.sql_modules

Source size: 12.135 characters

Interpretation

  • Database routine. Review parameters, called procedures, DBQueue inserts, and QBM_PJobCreate helper calls before assuming side effects.
  • Object-layer bridge detected through QBM_PJobCreate helper usage.

Relations

  • References QBM_PJobCreate*

Typed Edges

  • references source dbo.QBM_FTJobCreateDBParameter source text reference
  • references source dbo.QBM_FCVListToJobParameter source text reference
  • references source dbo.QBM_FCVParameter1AsOneInClause source text reference
  • references source dbo.QBM_FCVStringToGUID source text reference
  • references source dbo.QBM_FCVStringToInt source text reference
  • references source dbo.QBM_FGIConfigparmValue source text reference
  • references source dbo.QBM_FGIJobCreateName source text reference
  • references source dbo.QBM_FGISessionContext source text reference
  • references source dbo.QBM_FGISessionErrorRethrow source text reference
  • references source dbo.QBM_PSessionContextSet source text reference
  • references source dbo.QBM_PSessionErrorAdd source text reference

References

Referenced By

Complete Source

SQL570 lines
1CREATE PROCEDURE QBM_PJobCreate(2  @ComponentClass nvarchar(1024),3  @TaskName nvarchar(256),4  @Parameters QBM_YParameterList READONLY,5  @GenProcID varchar(38),6  @ObjectKeysAffected QBM_YParameterList READONLY,7  @isToFreezeOnError BIT = 0,8  @Retries int = 1,9  @priority int = 5,10  @Jobchainname nvarchar(256) = '',11  @StartAt datetime = NULL,12  @BasisObjectKey varchar(138) = NULL,13  @XUser nvarchar(64) = NULL,14  @QueueName nvarchar(256) = NULL,15  @checkForExisting BIT = 016)17AS18BEGIN19  DECLARE @DeferOnError BIT = 020  DECLARE @MinutesToDefer int = 021  DECLARE @UID_Job varchar(38) = newid()22  DECLARE @ParametersIntern QBM_YParameterList23  DECLARE @ExistingToReplace QBM_YParameterList24  DECLARE @ObjectKeysAffectedNetto QBM_YParameterList25  DECLARE @ObjectKeysAffected_intern QBM_YParameterList26  DECLARE @ReplaceRunner int27  DECLARE @ReplaceWhereClause nvarchar(max)28  DECLARE @DebugSwitch int = 029  DECLARE @SQLTagMaster varchar(38) = 'QBM-ST-Is07'30  DECLARE @SQLTagAddOn varchar(38) = 'QBM-ST-SQLProcessingEnabled'31  DECLARE @isToFreezeOnErrorConfig BIT = 032  DECLARE @GenProcID_R varchar(38) = dbo.QBM_FGISessionContext('')33  DECLARE @XUser_R nvarchar(64) = dbo.QBM_FGISessionContext('XUser')34  DECLARE @RelevantServer QBM_YParameterList35  DECLARE @CountRelevantServer int = 036  DECLARE @TempQueueAvailable BIT = 037  DECLARE @SimilarJobExists int = 038  DECLARE @zerlegt QBM_YParameterList39  DECLARE @WhereClauseAdditional nvarchar(max)40  SET XACT_ABORT OFF41  BEGIN TRY42    IF @DebugSwitch > 043    BEGIN44      print 'entry create job ' + isnull(@Jobchainname,45      '<no @Jobchainname>')46    END47    IF object_id('tempdb..#JobQueue') IS NOT NULL AND object_id('tempdb..#QBMElementAffectedByJob') IS NOT NULL48    BEGIN49      SELECT @TempQueueAvailable = 150    END51    SELECT @isToFreezeOnErrorConfig = CASE52    WHEN dbo.QBM_FCVStringToInt(dbo.QBM_FGIConfigparmValue('Common\DBJobCreateWithFreeze'),53    0) = 1 THEN54    155    ELSE @isToFreezeOnError56    END57    IF @BasisObjectKey > ' '58    BEGIN59      INSERT INTO @ObjectKeysAffected_intern(Parameter1)60      SELECT a.Parameter161      FROM @ObjectKeysAffected a62      WHERE63        a.Parameter1 > ' '64      UNION65      SELECT @BasisObjectKey66    END67    ELSE68    BEGIN69      INSERT INTO @ObjectKeysAffected_intern(Parameter1)70      SELECT a.Parameter171      FROM @ObjectKeysAffected a72      WHERE73        a.Parameter1 > ' '74    END75    IF isnull(@Jobchainname,76    '') = ''77    BEGIN78      SELECT @Jobchainname = dbo.QBM_FGIJobCreateName(NULL)79    END80    IF @Retries > 081    BEGIN82      SELECT @DeferOnError = 183      SELECT @MinutesToDefer = 284    END85    IF @StartAt IS NULL86    BEGIN87      SELECT @StartAt = GETUTCDATE()88    END89    IF @XUser IS NULL90    BEGIN91      SELECT @XUser = OBJECT_NAME(@@procid)92    END93    EXEC QBM_PSessionContextSet 'GenProcID',94      @GenProcID95    EXEC QBM_PSessionContextSet 'XUser',96      @XUser97    IF @QueueName IS NULL98    BEGIN99      IF EXISTS(100        SELECT TOP 1 1101        FROM QBMServerHasServerTag sht102        WITH(readpast)103      WHERE104        sht.UID_QBMServerTag = @SQLTagAddOn)105      BEGIN106        INSERT INTO @RelevantServer(Parameter1,107        ContentShort,108        Parameter2)109        SELECT110          y.UID_QBMServer,111          s.QueueName,112          dbo.QBM_FCVStringToGUID('',113          s.QueueName)114        FROM(115        SELECT x.UID_QBMServer116        FROM QBMServerHasServerTag x117          WITH(readpast)118        JOIN QBMServer s119          WITH(readpast)120          ON x.UID_QBMServer = s.UID_QBMServer121        WHERE122          x.UID_QBMServerTag IN(@SQLTagAddOn) AND s.NotUsedForJobCreation = 0 AND s.IsInSoftwareUpdate = 0 AND s.IsQBMServiceInstalled = 1 AND s.QueueName123        > ' '124        GROUP BY x.UID_QBMServer) AS y125        JOIN QBMServer s126          WITH(readpast)127          ON y.UID_QBMServer = s.UID_QBMServer128        SELECT @CountRelevantServer = @@ROWCOUNT129        IF @CountRelevantServer = 0130        BEGIN131          SELECT TOP 1 @QueueName = s.QueueName132          FROM QBMServer s133            WITH(readpast)134          JOIN QBMServerHasServerTag sht135            WITH(readpast)136            ON s.UID_QBMServer = sht.UID_QBMServer137          WHERE138            sht.UID_QBMServerTag = @SQLTagMaster AND s.QueueName > ' ' AND s.NotUsedForJobCreation = 0139        END140        IF @CountRelevantServer = 1141        BEGIN142          SELECT TOP 1 @QueueName = sht.ContentShort143          FROM @RelevantServer sht144        END145        IF @CountRelevantServer > 1146        BEGIN147          SELECT TOP 1 @QueueName = sht.ContentShort148          FROM @RelevantServer sht149          LEFT150          OUTER151          JOIN QBMJobqueueOverview o152            WITH(readpast)153            ON sht.Parameter2 = o.UID_QBMJobqueueOverview154          ORDER BY o.CountTrue + o.CountLoaded + o.CountProcessing + o.CountFalse ASC155        END156      END157      ELSE158      BEGIN159        SELECT TOP 1 @QueueName = s.QueueName160        FROM QBMServer s161          WITH(readpast)162        JOIN QBMServerHasServerTag sht163          WITH(readpast)164          ON s.UID_QBMServer = sht.UID_QBMServer165        WHERE166          sht.UID_QBMServerTag = @SQLTagMaster AND s.QueueName > ' ' AND s.NotUsedForJobCreation = 0167      END168    END169    INSERT INTO @ParametersIntern(Parameter1,170    Parameter2,171    Parameter3,172    ContentFull)173    SELECT174      Parameter1,175      Parameter2,176      Parameter3,177      ContentFull178    FROM @Parameters179    UNION all180    SELECT181      db.ParameterName,182      NULL,183      NULL,184      db.ParameterValue185    FROM dbo.QBM_FTJobCreateDBParameter(@ComponentClass,186    @TaskName,187    @Parameters) db188    IF @checkForExisting = 1189    BEGIN190      SELECT TOP 1 @SimilarJobExists = 1191      FROM JobQueue q192        WITH(readpast)193      JOIN QBMElementAffectedByJob e194        WITH(readpast)195        ON q.UID_Job = e.UID_Job196      JOIN @ObjectKeysAffected_intern a197        ON e.ObjectKeyAffected = a.Parameter1198      WHERE199        q.ComponentClass = @ComponentClass AND q.TaskName = @TaskName AND q.IsRootJob = 1 AND q.Ready2EXE IN(N 'TRUE',200      N 'FROZEN') AND q.JobChainName = @Jobchainname201      IF @TempQueueAvailable = 1 AND @SimilarJobExists = 0202      BEGIN203        SELECT TOP 1 @SimilarJobExists += 1204        FROM #JobQueue q205          WITH(nolock)206        JOIN #QBMElementAffectedByJob e207          WITH(nolock)208          ON q.UID_Job = e.UID_Job209        JOIN @ObjectKeysAffected_intern a210          ON e.ObjectKeyAffected = a.Parameter1211        WHERE212          q.ComponentClass = @ComponentClass AND q.TaskName = @TaskName AND q.JobChainName = @Jobchainname213      END214      IF @SimilarJobExists > 0215      BEGIN216        IF @TempQueueAvailable = 0217        BEGIN218          INSERT INTO @ExistingToReplace(Parameter1)219          SELECT220            DISTINCT a.Parameter1221          FROM @ObjectKeysAffected_intern a222          JOIN QBMElementAffectedByJob e223            WITH(readpast)224            ON e.ObjectKeyAffected = a.Parameter1225          JOIN JobQueue q226            WITH(readpast)227            ON q.UID_Job = e.UID_Job228          WHERE229            q.ComponentClass = @ComponentClass AND q.TaskName = @TaskName AND q.IsRootJob = 1 AND q.Ready2EXE IN(N 'TRUE',230          N 'FROZEN') AND q.JobChainName = @Jobchainname231          SELECT @ReplaceRunner = @@ROWCOUNT232        END233        ELSE234        BEGIN235          INSERT INTO @ExistingToReplace(Parameter1)236          SELECT a.Parameter1237          FROM @ObjectKeysAffected_intern a238          JOIN QBMElementAffectedByJob e239            WITH(readpast)240            ON e.ObjectKeyAffected = a.Parameter1241          JOIN JobQueue q242            WITH(readpast)243            ON q.UID_Job = e.UID_Job244          WHERE245            q.ComponentClass = @ComponentClass AND q.TaskName = @TaskName AND q.IsRootJob = 1 AND q.Ready2EXE IN(N 'TRUE',246          N 'FROZEN') AND q.JobChainName = @Jobchainname247          UNION248          SELECT a.Parameter1249          FROM @ObjectKeysAffected_intern a250          JOIN #QBMElementAffectedByJob e251            WITH(nolock)252            ON e.ObjectKeyAffected = a.Parameter1253          JOIN #JobQueue q254            WITH(nolock)255            ON q.UID_Job = e.UID_Job256          WHERE257            q.ComponentClass = @ComponentClass AND q.TaskName = @TaskName AND q.JobChainName = @Jobchainname258          SELECT @ReplaceRunner = @@ROWCOUNT259        END260        IF @ReplaceRunner > 0261        BEGIN262          IF @DebugSwitch > 0263          BEGIN264            print ' --- start replacing'265          END266          SELECT TOP 1 @ReplaceWhereClause = i.ContentFull267          FROM @ParametersIntern i268          WHERE269            i.Parameter1 = 'whereclause'270          IF @ReplaceWhereClause LIKE 'XObjectKey in (%' OR @ReplaceWhereClause LIKE '(XObjectKey in (%'271          BEGIN272            INSERT INTO @zerlegt(Parameter1)273            SELECT274              substring(s.value,275              2,276              len(s.value) -2) AS Parameter1277            FROM string_split(CASE278            WHEN @ReplaceWhereClause LIKE 'XObjectKey in (%' THEN279            substring(@ReplaceWhereClause, 16, len(@ReplaceWhereClause) -16)280            WHEN @ReplaceWhereClause LIKE '(XObjectKey in (%' THEN281            substring(@ReplaceWhereClause, 17, len(@ReplaceWhereClause) -18)282            ELSE ''283            END,284            ',') s285            DELETE @zerlegt286            FROM @zerlegt z287            JOIN @ExistingToReplace i288              ON z.Parameter1 = i.Parameter1289            IF EXISTS(290              SELECT TOP 1 1291              FROM @zerlegt)292            BEGIN293              SELECT294                @ReplaceWhereClause = CONCAT('XObjectKey in ',295                dbo.QBM_FCVParameter1AsOneInClause(@zerlegt))296            END297            ELSE298            BEGIN299              GOTO endLabel300            END301            UPDATE @ParametersIntern302            SET ContentFull = @ReplaceWhereClause303            WHERE304              Parameter1 = 'whereclause'305          END306        END307      END308      IF @DebugSwitch > 0309      BEGIN310        print '@ExistingToReplace'311        SELECT r.Parameter1312        FROM @ExistingToReplace r313      END314      IF @DebugSwitch > 0315      BEGIN316        print 'nach replace'317        SELECT *318        FROM @ParametersIntern319      END320      IF @BasisObjectKey > ' '321      BEGIN322        INSERT INTO @ObjectKeysAffectedNetto(Parameter1)323        SELECT a.Parameter1324        FROM @ObjectKeysAffected_intern a325        LEFT326        OUTER327        JOIN @ExistingToReplace r328          ON a.Parameter1 = r.Parameter1329        WHERE330          r.Parameter1 IS NULL331        UNION332        SELECT @BasisObjectKey333      END334      ELSE335      BEGIN336        INSERT INTO @ObjectKeysAffectedNetto(Parameter1)337        SELECT a.Parameter1338        FROM @ObjectKeysAffected_intern a339        LEFT340        OUTER341        JOIN @ExistingToReplace r342          ON a.Parameter1 = r.Parameter1343        WHERE344          r.Parameter1 IS NULL345      END346    END347    ELSE348    BEGIN349      IF @BasisObjectKey > ' '350      BEGIN351        INSERT INTO @ObjectKeysAffectedNetto(Parameter1)352        SELECT a.Parameter1353        FROM @ObjectKeysAffected_intern a354        UNION355        SELECT @BasisObjectKey356      END357      ELSE358      BEGIN359        INSERT INTO @ObjectKeysAffectedNetto(Parameter1)360        SELECT a.Parameter1361        FROM @ObjectKeysAffected_intern a362      END363    END364    IF EXISTS(365      SELECT TOP 1 1366      FROM @ParametersIntern i367      WHERE368        i.Parameter1 = 'WhereClauseAdditional')369    BEGIN370      SELECT TOP 1 @WhereClauseAdditional = i.ContentFull371      FROM @ParametersIntern i372      WHERE373        i.Parameter1 = 'WhereClauseAdditional'374      UPDATE @ParametersIntern375      SET ContentFull = CASE376      WHEN ISNULL(@WhereClauseAdditional,377      '') = '' THEN378      i.ContentFull379      ELSE CONCAT(CONVERT(nvarchar(max), '('),380      i.ContentFull,381      ') and (',382      @WhereClauseAdditional,383      ')')384      END385      FROM @ParametersIntern i386      WHERE387        i.Parameter1 = 'WhereClause'388      DELETE @ParametersIntern389      FROM @ParametersIntern i390      WHERE391        i.Parameter1 = 'WhereClauseAdditional'392    END393    IF @TempQueueAvailable = 0394    BEGIN395      INSERT INTO jobqueue(ComponentAssembly,396      ComponentClass,397      DeferOnError,398      ErrorNotify,399      ExecutionType,400      IgnoreErrors,401      IsSplitOnly,402      JobChainName,403      MaxInstance,404      MinutesToDefer,405      Retries,406      ParamIN,407      Priority,408      Queue,409      Ready2EXE,410      StartAt,411      TaskName,412      UID_Job,413      UID_JobError,414      UID_JobSameServer,415      UID_JobSuccess,416      UID_Tree,417      WasError,418      XDateInserted,419      XDateUpdated,420      XUserInserted,421      XUserUpdated,422      IsRootJob,423      GenProcID,424      isToFreezeOnError,425      BasisObjectKey)426      SELECT427        c.ComponentAssembly,428        c.ComponentClass,429        @DeferOnError,430        0,431        t.ExecutionType,432        0,433        0,434        @Jobchainname,435        t.MaxInstance,436        @MinutesToDefer,437        @Retries,438        dbo.QBM_FCVListToJobParameter(@ComponentClass,439        @TaskName,440        @ParametersIntern),441        @priority,442        @QueueName,443        N 'TRUE',444        @startat,445        @TaskName,446        @uid_job,447        N '',448        @uid_job,449        N '',450        newid(),451        0,452        GetUTCDate(),453        GetUTCDate(),454        @XUser,455        @XUser,456        1,457        @GenProcID,458        @isToFreezeOnErrorConfig,459        @BasisObjectKey460      FROM JobComponent c461        WITH(readpast)462      JOIN JobTask t463        WITH(readpast)464        ON c.UID_JobComponent = t.UID_JobComponent465      WHERE466        c.ComponentClass = @ComponentClass AND t.TaskName = @TaskName467      INSERT INTO QBMElementAffectedByJob(UID_Job,468      ObjectKeyAffected)469      SELECT470        @uid_job,471        e.Parameter1472      FROM @ObjectKeysAffectedNetto e473    END474    ELSE475    BEGIN476      INSERT INTO #JobQueue(ComponentAssembly,477      ComponentClass,478      DeferOnError,479      ErrorNotify,480      ExecutionType,481      IgnoreErrors,482      IsSplitOnly,483      JobChainName,484      MaxInstance,485      MinutesToDefer,486      Retries,487      ParamIN,488      Priority,489      Queue,490      Ready2EXE,491      StartAt,492      TaskName,493      UID_Job,494      UID_JobError,495      UID_JobSameServer,496      UID_JobSuccess,497      UID_Tree,498      WasError,499      XDateInserted,500      XDateUpdated,501      XUserInserted,502      XUserUpdated,503      IsRootJob,504      GenProcID,505      isToFreezeOnError,506      BasisObjectKey)507      SELECT508        c.ComponentAssembly,509        c.ComponentClass,510        @DeferOnError,511        0,512        t.ExecutionType,513        0,514        0,515        @Jobchainname,516        t.MaxInstance,517        @MinutesToDefer,518        @Retries,519        dbo.QBM_FCVListToJobParameter(@ComponentClass,520        @TaskName,521        @ParametersIntern),522        @priority,523        @QueueName,524        N 'TRUE',525        @startat,526        @TaskName,527        @uid_job,528        N '',529        @uid_job,530        N '',531        newid(),532        0,533        GetUTCDate(),534        GetUTCDate(),535        @XUser,536        @XUser,537        1,538        @GenProcID,539        @isToFreezeOnErrorConfig,540        @BasisObjectKey541      FROM JobComponent c542        WITH(readpast)543      JOIN JobTask t544        WITH(readpast)545        ON c.UID_JobComponent = t.UID_JobComponent546      WHERE547        c.ComponentClass = @ComponentClass AND t.TaskName = @TaskName548      INSERT INTO #QBMElementAffectedByJob(UID_Job,549      ObjectKeyAffected)550      SELECT551        @uid_job,552        e.Parameter1553      FROM @ObjectKeysAffectedNetto e554    END555  END TRY556  BEGIN CATCH557    EXEC QBM_PSessionErrorAdd DEFAULT558    DECLARE @Rethrow varchar(1000) = dbo.QBM_FGISessionErrorRethrow()559    RAISERROR(@Rethrow,560    18,561    1)562      WITH NOWAIT563  END CATCH564  endLabel:565  EXEC QBM_PSessionContextSet 'GenProcID',566    @GenProcID_R567  EXEC QBM_PSessionContextSet 'XUser',568    @XUser_R569  RETURN570END
Open raw exported source
SQL ยท Raw78 lines
1 create   procedure QBM_PJobCreate (@ComponentClass nvarchar(1024) , @TaskName nvarchar(256) , @Parameters QBM_YParameterList readOnly     , @GenProcID2 varchar(38) , @ObjectKeysAffected QBM_YParameterList readOnly  , @isToFreezeOnError bit = 0 , @Retries int = 1 , @priority int = 5 , @Jobchainname nvarchar3(256) = '' , @StartAt datetime = null , @BasisObjectKey varchar(138) = null , @XUser nvarchar(64) = null , @QueueName nvarchar(256) = null  , @checkForExisting4 bit = 0 ) as begin declare @DeferOnError bit = 0 declare @MinutesToDefer int = 0 declare @UID_Job varchar(38) = newid() declare @ParametersIntern QBM_YParameterList5 declare @ExistingToReplace QBM_YParameterList declare @ObjectKeysAffectedNetto QBM_YParameterList declare @ObjectKeysAffected_intern QBM_YParameterList6 declare @ReplaceRunner int declare @ReplaceWhereClause nvarchar(max) declare @DebugSwitch int = 0 declare @SQLTagMaster varchar(38) = 'QBM-ST-Is07' declare7 @SQLTagAddOn varchar(38)= 'QBM-ST-SQLProcessingEnabled' declare @isToFreezeOnErrorConfig bit = 0 declare @GenProcID_R varchar(38) = dbo.QBM_FGISessionContext8('') declare @XUser_R nvarchar(64) = dbo.QBM_FGISessionContext('XUser') declare @RelevantServer QBM_YParameterList    declare @CountRelevantServer int 9= 0 declare @TempQueueAvailable bit = 0 declare @SimilarJobExists int = 0  declare @zerlegt QBM_YParameterList Declare @WhereClauseAdditional nvarchar(max10) SET XACT_ABORT OFF BEGIN TRY if @DebugSwitch > 0 begin print 'entry create job ' + isnull(@Jobchainname, '<no @Jobchainname>') end if object_id('tempdb..#JobQueue'11) is not null and object_id('tempdb..#QBMElementAffectedByJob') is not null begin select @TempQueueAvailable = 1 end select @isToFreezeOnErrorConfig = 12case when dbo.QBM_FCVStringToInt( dbo.QBM_FGIConfigparmValue('Common\DBJobCreateWithFreeze'), 0) = 1 then 1 else @isToFreezeOnError end if @BasisObjectKey13 > ' ' begin insert into @ObjectKeysAffected_intern (Parameter1) select a.Parameter1 from @ObjectKeysAffected a where a.Parameter1 > ' ' union select @BasisObjectKey14 end else begin insert into @ObjectKeysAffected_intern (Parameter1) select a.Parameter1 from @ObjectKeysAffected a where a.Parameter1 > ' ' end if isnull15(@Jobchainname, '') = '' begin select @Jobchainname = dbo.QBM_FGIJobCreateName (null) end if @Retries > 0 begin select @DeferOnError = 1 select @MinutesToDefer16 = 2 end if @StartAt is null begin select @StartAt = GETUTCDATE() end if @XUser is null begin select @XUser = OBJECT_NAME(@@procid) end exec QBM_PSessionContextSet17 'GenProcID', @GenProcID exec QBM_PSessionContextSet 'XUser', @XUser if @QueueName is null begin   if exists (select top 1 1 from QBMServerHasServerTag18 sht with (readpast) where sht.UID_QBMServerTag = @SQLTagAddOn ) begin    insert into @RelevantServer(Parameter1, ContentShort, Parameter2) select y.UID_QBMServer19, s.QueueName, dbo.QBM_FCVStringToGUID('', s.QueueName) from ( select x.UID_QBMServer from QBMServerHasServerTag x with (readpast) join QBMServer s with20 (readpast) on x.UID_QBMServer = s.UID_QBMServer where x.UID_QBMServerTag in( @SQLTagAddOn) and s.NotUsedForJobCreation = 0  and s.IsInSoftwareUpdate =21 0 and s.IsQBMServiceInstalled = 1 and s.QueueName > ' '  group by x.UID_QBMServer ) as y join QBMServer s with (readpast) on y.UID_QBMServer = s.UID_QBMServer22 select @CountRelevantServer = @@ROWCOUNT   if @CountRelevantServer = 0 begin select top 1 @QueueName = s.QueueName from QBMServer s with (readpast) join23 QBMServerHasServerTag sht with (readpast) on s.UID_QBMServer = sht.UID_QBMServer where sht.UID_QBMServerTag = @SQLTagMaster and s.QueueName > ' ' and 24s.NotUsedForJobCreation = 0 end if @CountRelevantServer = 1 begin select top 1 @QueueName = sht.ContentShort from @RelevantServer sht end  if @CountRelevantServer25 > 1 begin select top 1 @QueueName = sht.ContentShort from @RelevantServer sht left outer join QBMJobqueueOverview o with (readpast) on sht.Parameter2 26= o.UID_QBMJobqueueOverview order by o.CountTrue + o.CountLoaded + o.CountProcessing + o.CountFalse asc end  end else begin  select top 1 @QueueName = 27s.QueueName from QBMServer s with (readpast) join QBMServerHasServerTag sht with (readpast) on s.UID_QBMServer = sht.UID_QBMServer where sht.UID_QBMServerTag28 = @SQLTagMaster and s.QueueName > ' ' and s.NotUsedForJobCreation = 0 end end insert into @ParametersIntern(Parameter1, Parameter2, Parameter3, ContentFull29) select Parameter1, Parameter2, Parameter3, ContentFull from @Parameters union all select db.ParameterName, null, null, db.ParameterValue from dbo.QBM_FTJobCreateDBParameter30(@ComponentClass, @TaskName, @Parameters) db  if @checkForExisting = 1 begin    select top 1 @SimilarJobExists = 1 from JobQueue q with (readpast) join31 QBMElementAffectedByJob e with (readpast) on q.UID_Job = e.UID_Job join @ObjectKeysAffected_intern a on e.ObjectKeyAffected = a.Parameter1 where q.ComponentClass32 = @ComponentClass and q.TaskName = @TaskName   and q.IsRootJob = 1 and q.Ready2EXE in( N'TRUE', N'FROZEN' ) and q.JobChainName = @Jobchainname if @TempQueueAvailable33 = 1 and @SimilarJobExists = 0  begin select top 1 @SimilarJobExists += 1 from #JobQueue q with (nolock) join #QBMElementAffectedByJob e with (nolock) 34on q.UID_Job = e.UID_Job join @ObjectKeysAffected_intern a on e.ObjectKeyAffected = a.Parameter1 where q.ComponentClass = @ComponentClass and q.TaskName35 = @TaskName     and q.JobChainName = @Jobchainname end if @SimilarJobExists > 0 begin if @TempQueueAvailable = 0  begin insert into @ExistingToReplace36(Parameter1) select distinct a.Parameter1 from @ObjectKeysAffected_intern a join QBMElementAffectedByJob e with (readpast) on e.ObjectKeyAffected = a.Parameter137 join JobQueue q with (readpast) on q.UID_Job = e.UID_Job where q.ComponentClass = @ComponentClass and q.TaskName = @TaskName and q.IsRootJob = 1 and q.Ready2EXE38 in( N'TRUE', N'FROZEN' ) and q.JobChainName = @Jobchainname select @ReplaceRunner = @@ROWCOUNT end  else begin  insert into @ExistingToReplace(Parameter139) select a.Parameter1 from @ObjectKeysAffected_intern a join QBMElementAffectedByJob e with (readpast) on e.ObjectKeyAffected = a.Parameter1 join JobQueue40 q with (readpast) on q.UID_Job = e.UID_Job where q.ComponentClass = @ComponentClass and q.TaskName = @TaskName and q.IsRootJob = 1 and q.Ready2EXE in(41 N'TRUE', N'FROZEN' ) and q.JobChainName = @Jobchainname union select a.Parameter1 from @ObjectKeysAffected_intern a join #QBMElementAffectedByJob e with42 (nolock) on e.ObjectKeyAffected = a.Parameter1 join #JobQueue q with (nolock) on q.UID_Job = e.UID_Job where q.ComponentClass = @ComponentClass and q.TaskName43 = @TaskName   and q.JobChainName = @Jobchainname select @ReplaceRunner = @@ROWCOUNT end     if @ReplaceRunner > 0 begin if @DebugSwitch > 0 begin print44 ' --- start replacing' end   select top 1 @ReplaceWhereClause = i.ContentFull from @ParametersIntern i where i.Parameter1 = 'whereclause' if @ReplaceWhereClause45 like 'XObjectKey in (%' or @ReplaceWhereClause like '(XObjectKey in (%'  begin              insert into @zerlegt (Parameter1) select substring(s.value46, 2, len(s.value) -2) as Parameter1 from string_split(case when @ReplaceWhereClause like 'XObjectKey in (%' then substring( @ReplaceWhereClause, 16, len47(@ReplaceWhereClause) -16) when @ReplaceWhereClause like '(XObjectKey in (%' then substring( @ReplaceWhereClause, 17, len(@ReplaceWhereClause) -18) else48 '' end ,',' ) s  delete @zerlegt from @zerlegt z join @ExistingToReplace i on z.Parameter1 = i.Parameter1   if exists (select top 1 1 from @zerlegt ) 49begin  select @ReplaceWhereClause = concat( 'XObjectKey in ' , dbo.QBM_FCVParameter1AsOneInClause(@zerlegt) )  end else begin   goto endLabel end update50 @ParametersIntern set ContentFull = @ReplaceWhereClause where Parameter1 = 'whereclause' end  end  end  if @DebugSwitch > 0 begin print '@ExistingToReplace'51 select r.Parameter1 from @ExistingToReplace r end if @DebugSwitch > 0 begin print 'nach replace' select * from @ParametersIntern end  if @BasisObjectKey52 > ' ' begin insert into @ObjectKeysAffectedNetto(Parameter1) select a.Parameter1 from @ObjectKeysAffected_intern a left outer join @ExistingToReplace 53r on a.Parameter1 = r.Parameter1 where r.Parameter1 is null union select @BasisObjectKey end else begin insert into @ObjectKeysAffectedNetto(Parameter154) select a.Parameter1 from @ObjectKeysAffected_intern a left outer join @ExistingToReplace r on a.Parameter1 = r.Parameter1 where r.Parameter1 is null 55end end else begin  if @BasisObjectKey > ' ' begin insert into @ObjectKeysAffectedNetto(Parameter1) select a.Parameter1 from @ObjectKeysAffected_intern56 a union select @BasisObjectKey end else begin insert into @ObjectKeysAffectedNetto(Parameter1) select a.Parameter1 from @ObjectKeysAffected_intern a end57 end   if exists (select top 1 1 from @ParametersIntern i where i.Parameter1 = 'WhereClauseAdditional' ) begin select top 1 @WhereClauseAdditional = i.ContentFull58 from @ParametersIntern i where i.Parameter1 = 'WhereClauseAdditional' update @ParametersIntern set ContentFull = case when ISNULL(@WhereClauseAdditional59, '') = '' then i.ContentFull  else concat( CONVERT(nvarchar(max), '(') , i.ContentFull  , ') and (' , @WhereClauseAdditional , ')' ) end from @ParametersIntern60 i where i.Parameter1 = 'WhereClause' delete @ParametersIntern from @ParametersIntern i where i.Parameter1 = 'WhereClauseAdditional' end  if @TempQueueAvailable61 = 0  begin insert into jobqueue ( ComponentAssembly, ComponentClass, DeferOnError, ErrorNotify , ExecutionType, IgnoreErrors, IsSplitOnly, JobChainName62, MaxInstance, MinutesToDefer, Retries, ParamIN, Priority, Queue, Ready2EXE, StartAt, TaskName, UID_Job, UID_JobError, UID_JobSameServer, UID_JobSuccess63, UID_Tree, WasError, XDateInserted, XDateUpdated, XUserInserted, XUserUpdated, IsRootJob, GenProcID , isToFreezeOnError, BasisObjectKey) select c.ComponentAssembly64, c.ComponentClass, @DeferOnError , 0 , t.ExecutionType, 0, 0, @Jobchainname, t.MaxInstance, @MinutesToDefer , @Retries , dbo.QBM_FCVListToJobParameter65(@ComponentClass, @TaskName, @ParametersIntern ) , @priority, @QueueName, N'TRUE', @startat, @TaskName, @uid_job, N'', @uid_job, N'', newid(), 0, GetUTCDate66(), GetUTCDate(), @XUser, @XUser, 1, @GenProcID, @isToFreezeOnErrorConfig, @BasisObjectKey from JobComponent c with (readpast) join JobTask t with (readpast67) on c.UID_JobComponent = t.UID_JobComponent where c.ComponentClass = @ComponentClass and t.TaskName = @TaskName insert into QBMElementAffectedByJob(UID_Job68, ObjectKeyAffected) select @uid_job, e.Parameter1 from @ObjectKeysAffectedNetto e end else begin insert into #JobQueue ( ComponentAssembly, ComponentClass69, DeferOnError, ErrorNotify , ExecutionType, IgnoreErrors, IsSplitOnly, JobChainName, MaxInstance, MinutesToDefer, Retries, ParamIN, Priority, Queue, Ready2EXE70, StartAt, TaskName, UID_Job, UID_JobError, UID_JobSameServer, UID_JobSuccess, UID_Tree, WasError, XDateInserted, XDateUpdated, XUserInserted, XUserUpdated71, IsRootJob, GenProcID , isToFreezeOnError, BasisObjectKey) select c.ComponentAssembly, c.ComponentClass, @DeferOnError , 0 , t.ExecutionType, 0, 0, @Jobchainname72, t.MaxInstance, @MinutesToDefer , @Retries , dbo.QBM_FCVListToJobParameter(@ComponentClass, @TaskName, @ParametersIntern ) , @priority, @QueueName, N'TRUE'73, @startat, @TaskName, @uid_job, N'', @uid_job, N'', newid(), 0, GetUTCDate(), GetUTCDate(), @XUser, @XUser, 1, @GenProcID, @isToFreezeOnErrorConfig, @BasisObjectKey74 from JobComponent c with (readpast) join JobTask t with (readpast) on c.UID_JobComponent = t.UID_JobComponent where c.ComponentClass = @ComponentClass75 and t.TaskName = @TaskName insert into #QBMElementAffectedByJob(UID_Job, ObjectKeyAffected) select @uid_job, e.Parameter1 from @ObjectKeysAffectedNetto76 e end  END TRY BEGIN CATCH exec QBM_PSessionErrorAdd default declare @Rethrow varchar(1000) = dbo.QBM_FGISessionErrorRethrow() RAISERROR (@Rethrow, 1877, 1) WITH NOWAIT END CATCH endLabel: exec QBM_PSessionContextSet 'GenProcID', @GenProcID_R exec QBM_PSessionContextSet 'XUser', @XUser_R return end 78