Monday, September 29, 2008

[Jobs] Starting a job on a foreign server

Man, I'm lazy right now, copying useful code from other people.

This comes from "SQLAdmin" on the SQL Server Mag forums. Set this as a job step, and it'll run a job on a different server. Check your perms. Note that all this does is kick off the job and tell you if it kicked off successfully.

http://sqlforums.windowsitpro.com/web/forum/messageview.aspx?catid=60&threadid=83712&enterthread=y


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

set @job_name = 'My Test Job' ------------------Job name goes here.
set @server_name = 'MyRemoteServer' ------------------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 = master.dbo.xp_cmdshell @cmd

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

No comments: