Calling an SQL agent job from another job on a remote server?

Question

Is there a way to run a task on another job on a remote server without using linked servers?

The rationale is that the running job runs the SSIS package in 2008. The calling task is located on the 2005 server, so it cannot perform the task directly.

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

Answer 1

Use the cmdexec type (operating system) in the SQL agent, and then use the dtexec \ f "....." command line to execute the SSIS 2008 package. This naughty job!

dtsx 2005 dtsx dtexec.

Answer 2

, . http, sp_start_job

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

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

Answer 3

The following code should work if you have permission to execute xp_cmdshell. You just need to replace the 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