How to run SQL server stored procedure remotely to control ETL job

Check list of all posts

When ETL server and Database server and database server is separated, Job in ETL server can’t be directly called by stored procedure from database server.  This solution is to create a separated stored procedure in MSDB in ETL server, and then make a link server to Database server, and then the stored procedure in database server calls remotely the real stored procedure ETL server

In database server

create procedure [dbo].[usp_RunSALESjob] ( @JobID as varchar(50),@ActivityID  as integer, @bManualload as bit = 0)
as
begin
…………….
declare @Job_last_run_outcome int
exec @Job_last_run_outcome=[ETL_SERVER].msdb.dbo.usp_RunWST_etl
………………..
End

In ETL server
create procedure [dbo].[usp_RunWST_etl]
as
begin
  SET NOCOUNT ON

     DECLARE
           @JobStatus INT,
           @Job_last_run_outcome INT
           /*Outcome of the job the last time it ran:
                 0 = Failed
                 1 = Succeeded
                 3 = Canceled
                 5 = Unknown
           */

     SET @JobStatus = 0

     EXEC msdb.dbo.Sp_start_job @Job_Name = 'WST_etl'

     WAITFOR delay '00:00:01'

     SELECT @JobStatus = current_execution_status
     FROM   OPENROWSET('SQLNCLI',
                               'Server=localhost;Trusted_Connection=yes;',
                               'EXEC MSDB.dbo.sp_help_job @job_name = ''WST_etl'', @job_aspect = ''JOB'' ')
     WHILE @JobStatus <> 4
       BEGIN
             WAITFOR delay '00:00:03'
             SELECT @JobStatus = current_execution_status
             FROM   OPENROWSET('SQLNCLI',
                                         'Server=localhost;Trusted_Connection=yes;',
                                         'EXEC MSDB.dbo.sp_help_job @job_name = ''WST_etl'', @job_aspect = ''JOB'' ')
       END
       
     SELECT @Job_last_run_outcome = last_run_outcome
     FROM   OPENROWSET('SQLNCLI',
                               'Server=localhost;Trusted_Connection=yes;',
                               'EXEC MSDB.dbo.sp_help_job @job_name = ''WST_etl'', @job_aspect = ''JOB'' ')


return @Job_last_run_outcome

end