Любые другие возможные способы сделать соединение, как это @parameterServerName.msdb.dbo.sysjobs?

Question

Создайте этот Proc, который проверит, выполняется ли задание на одном сервере, и запустит другое задание на другом сервере. Проблема, с которой я столкнулся, заключается в следующем: я не могу сделать соединение таким образом. INNER JOIN @parameterServerName.msdb.dbo.sysjobs Есть ли способ, которым я могу сделать это соединение, не написав кучу операторов 'if ' для каждого возможного ввода.

CREATE Proc [dbo].[JobCheckStarter]
 @JobToCheck sysname
,@CheckServer varchar(128)
,@JobToStart sysname
,@StartServer varchar(128)
AS
Begin
SET NOCOUNT ON

     -- Create a temporary table to hold job info
        CREATE TABLE #JobCheck 
        (
        job_id UNIQUEIDENTIFIER NOT NULL, 
        last_run_date INT NOT NULL, 
        last_run_time INT NOT NULL, 
        next_run_date INT NOT NULL, 
        next_run_time INT NOT NULL, 
        next_run_schedule_id INT NOT NULL, 
        requested_to_run INT NOT NULL, -- BOOL 
        request_source INT NOT NULL, 
        request_source_id sysname COLLATE database_default NULL, 
        running INT NOT NULL, -- BOOL 
        current_step INT NOT NULL, 
        current_retry_attempt INT NOT NULL, 
        job_state INT NOT NULL
        )

            CREATE TABLE #JobStart 
        (
        job_id UNIQUEIDENTIFIER NOT NULL, 
        last_run_date INT NOT NULL, 
        last_run_time INT NOT NULL, 
        next_run_date INT NOT NULL, 
        next_run_time INT NOT NULL, 
        next_run_schedule_id INT NOT NULL, 
        requested_to_run INT NOT NULL, -- BOOL 
        request_source INT NOT NULL, 
        request_source_id sysname COLLATE database_default NULL, 
        running INT NOT NULL, -- BOOL 
        current_step INT NOT NULL, 
        current_retry_attempt INT NOT NULL, 
        job_state INT NOT NULL
        )

IF @CheckServer = 'serverOne'
       Begin
            INSERT INTO  #JobCheck 
            EXEC serverOne.master.dbo.xp_sqlagent_enum_jobs 1,dbo
       END 
          Else 
       BEGIN
            INSERT INTO  #JobCheck 
            EXEC serverTwo.master.dbo.xp_sqlagent_enum_jobs 1,dbo
       End 

If @StartServer = 'serverOne'
        Begin
            INSERT INTO  #JobStart 
            EXEC serverOne.master.dbo.xp_sqlagent_enum_jobs 1,dbo
        END 
            Else 
        BEGIN
            INSERT INTO  #JobStart 
            EXEC serverTwo.master.dbo.xp_sqlagent_enum_jobs 1,dbo
        End 


if  exists (
        SELECT Running
        FROM #JobCheck J INNER JOIN @CheckServer.msdb.dbo.sysjobs SJ ON
        J.job_ID = SJ.job_ID
        WHERE [name] = @JobToCheck and J.running = 0
        )
            Begin 
                if  exists (
                SELECT Running,*
                FROM #JobStart J INNER JOIN @StartServer.msdb.dbo.sysjobs SJ ON
                J.job_ID = SJ.job_ID
                WHERE [name] = @JobToStart and J.running = 0
                )
                BEGIN
                    PRINT 'Starting job ''' + @JobToStart + '''';
                    EXEC @StartServer.msdb.dbo.sp_start_job @JobToStart;
                END
                   Else 
                       BEGIN
                            PRINT 'Cannot Start ''' + @JobToStart + ''' Because it is Already Running ';
                       END
            END
                ELSE
                    BEGIN 
                         PRINT 'Job ''' + @JobToCheck + ''' is Running ';
                    END
 Drop TABLE #Job
END

GO

Постройте динамический и все работает, спасибо всем, кто помог:D

Create Proc [dbo].[JobCheckStarter]
 @JobToCheck  Varchar(250)
,@CheckServer varchar(128)
,@JobToStart  Varchar(250)
,@StartServer varchar(128)
AS
Begin
SET NOCOUNT ON


Declare @Sql nVarchar(max)
Declare @IFSql nvarchar(max)

--Declare @JobToCheck varchar(200)
--Declare @CheckServer varchar(200)
--Declare @JobToStart  varchar(200) 
--Declare @StartServer varchar(200)

--Set @JobToCheck = ''
--Set @CheckServer =''
--Set @JobToStart = ''
--Set @StartServer =''

Set @Sql = '
        CREATE TABLE #JobCheck 
        (
        job_id UNIQUEIDENTIFIER NOT NULL, 
        last_run_date INT NOT NULL, 
        last_run_time INT NOT NULL, 
        next_run_date INT NOT NULL, 
        next_run_time INT NOT NULL, 
        next_run_schedule_id INT NOT NULL, 
        requested_to_run INT NOT NULL, -- BOOL 
        request_source INT NOT NULL, 
        request_source_id sysname COLLATE database_default NULL, 
        running INT NOT NULL, -- BOOL 
        current_step INT NOT NULL, 
        current_retry_attempt INT NOT NULL, 
        job_state INT NOT NULL
        )

            CREATE TABLE #JobStart 
        (
        job_id UNIQUEIDENTIFIER NOT NULL, 
        last_run_date INT NOT NULL, 
        last_run_time INT NOT NULL, 
        next_run_date INT NOT NULL, 
        next_run_time INT NOT NULL, 
        next_run_schedule_id INT NOT NULL, 
        requested_to_run INT NOT NULL, -- BOOL 
        request_source INT NOT NULL, 
        request_source_id sysname COLLATE database_default NULL, 
        running INT NOT NULL, -- BOOL 
        current_step INT NOT NULL, 
        current_retry_attempt INT NOT NULL, 
        job_state INT NOT NULL
        )

            INSERT INTO  #JobCheck 
            EXEC '+@CheckServer+'.master.dbo.xp_sqlagent_enum_jobs 1,dbo;

            INSERT INTO  #JobStart 
            EXEC '+@StartServer+'.master.dbo.xp_sqlagent_enum_jobs 1,dbo;
'

Set @ifSQL = 'if  exists (
        SELECT Running
        FROM #JobCheck J INNER JOIN '+ @CheckServer+ '.msdb.dbo.sysjobs SJ ON
        J.job_ID = SJ.job_ID
        WHERE SJ.name = '+'''' +@JobToCheck+ '''' +' and J.running = 0
        )
            Begin 
                if  exists (
                SELECT Running
                FROM #JobStart J INNER JOIN '+@StartServer+ '.msdb.dbo.sysjobs SJ ON
                J.job_ID = SJ.job_ID
                WHERE SJ.name = ' +''''+@JobToStart+''''+' and J.running = 0
                )'+'

BEGIN
PRINT '+ '''Starting job ''' +'+'+ ''''+@JobToStart+''''+';'+
'                                                            
 Exec '+ @StartServer+'.msdb.dbo.sp_start_job '+''''+ @JobToStart +''''
+'

END

                   Else 
                       BEGIN
                            PRINT '+'''Cannot Start ''' +'+'+ ''''+@JobToStart+'''' +'+'+ ''' Because it is Already Running '''
                            +'
                       END
            END
                ELSE
                    BEGIN 
                         PRINT '+ '''Job ''' +'+'+ ''''+@JobToCheck+'''' +'+'+ ''' is Running ''' +'
                    END '

Set @Sql= @Sql+@IFSql

--print @sql

--EXECUTE sp_executesql @Sql

EXEC sp_executesql @Sql, N' @JobToCheck  Varchar(250),@CheckServer varchar(128),@JobToStart  Varchar(250),@StartServer varchar(128)',@JobToCheck  ,@CheckServer,@JobToStart ,@StartServer
END

GO