Calling a SQL Agent Job from another Job on a remote server?

Question

Is there a way to trigger a job on a from another job on a remote server without using Linked Servers?

The reasoning is that the job being triggered executes an SSIS package on 2008. The calling job resides on a 2005 server, so cannot execute the job directly.

The servers are not linked, and I was hoping there was a way to call one from the other.

Solution 1

Use the type "cmdexec (operating system)" in the SQL Agent and then use the dtexec \f "....." command line utily to execute SSIS 2008 package. This shud work !

Export the dtsx file to the 2005 server box and call the dtsx from command line using dtexec utility.

Solution 2

Not sure if this will work. Create a http end point that calls sp_start_job

http://technet.microsoft.com/en-us/library/ms181591.aspx

http://msdn.microsoft.com/en-us/library/ms186757.aspx

Solution 3

The folllowing code should work, assuming you have authority execute xp_cmdshell. Just need to replace text for @job_name and @server_name.

USE master 
GO 
-- To allow advanced options to be changed.
EXEC sp_configure 'show advanced options', 1 
GO 
-- To update the currently configured value for advanced options.
RECONFIGURE WITH OVERRIDE 
GO
-- To disable the feature.
EXEC sp_configure 'xp_cmdshell', 1 
GO 
-- To update the currently configured value for this feature.
RECONFIGURE WITH OVERRIDE 
GO 


declare @retcode int 
declare @job_name varchar(300) 
declare @server_name varchar(200) 
declare @query varchar(8000) 
declare @cmd varchar(8000) 

set @job_name = 'hodes - grant user permissions' ------------------Job name goes here. 
set @server_name = 'msc-dbs04' ------------------Server name goes here. 

set @query = 'exec msdb.dbo.sp_start_job @job_name = ''' + @job_name + '''' 
set @cmd = 'osql -E -S ' + @server_name + ' -Q "' + @query + '"' 

print ' @job_name = ' +isnull(@job_name,'NULL @job_name') 
print ' @server_name = ' +isnull(@server_name,'NULL @server_name') 
print ' @query = ' +isnull(@query,'NULL @query') 
print ' @cmd = ' +isnull(@cmd,'NULL @cmd') 

exec @retcode = xp_cmdshell @cmd 

if @retcode <> 0 or @retcode is null 
begin 
print 'xp_cmdshell @retcode = '+isnull(convert(varchar(20),@retcode),'NULL @retcode') 
end 

USE MASTER
GO
-- To update the currently configured value for advanced options.
RECONFIGURE WITH OVERRIDE
GO
-- To disable the feature.
EXEC sp_configure 'xp_cmdshell', 0
GO
-- To update the currently configured value for this feature.
RECONFIGURE WITH OVERRIDE
GO
-- To do not allow advanced options to be changed.
EXEC sp_configure 'show advanced options', 0
GO
-- To update the currently configured value for advanced options.
RECONFIGURE WITH OVERRIDE
GO