News

Copyright © 2008-2018 Paula DiTallo

Tag Cloud



SQL Server: How do I start an agent job on a remote server?

This sql script will start an agent job on a remote server.  If you're running this as a step in another agent job, keep in mind that the job you are running it from will be determined to be successful, even if the remote job fails--as this is an asynchronous kick off only.


declare @returnCode int 
declare @JobName varchar(300) 
declare @ServerName varchar(200) 
declare @query varchar(8000) 
declare @cmd varchar(8000) 

set @JobName = 'TheJobNameYouWantToRun' 
set @ServerName = 'TheRemoteServerWhereTheJobIs' 

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

print ' @JobName = ' +isnull(@JobName,'NULL @JobName') 
print ' @ServerName = ' +isnull(@ServerName,'NULL @ServerName') 
print ' @query = ' +isnull(@query,'NULL @query') 
print ' @cmd = ' +isnull(@cmd,'NULL @cmd') 

exec @returnCode = master.dbo.xp_cmdshell @cmd 

if @returnCode <> 0 or @returnCode is null 
begin 
print 'xp_cmdshell @returnCode = '+isnull(convert(varchar(20),@returnCode),'NULL @returnCode') 
end 
Monday, September 14, 2015 7:53 AM

Feedback

No comments posted yet.


Post A Comment
Title:
Name:
Email:
Comment:
Verification: