want to start a job from another server?

Last Post 29 Jul 2008 03:35 AM by marekgorny. 8 Replies.
AddThis - Bookmarking and Sharing Button Printer Friendly
  •  
  •  
  •  
  •  
  •  
Sort:
PrevPrev NextNext
You are not authorized to post a reply.
Author Messages
sqladmin
New Member
New Member

--
25 Jan 2007 07:51 PM
search terms:
run a job from another server
start a job from a different server
execute a job from another server
execute a job on another server
xp_cmdshell to run a job
osql to run a job
remotely run a job

ever just want to run a job on a different server using the sql agent
from another server?

here's how.

just pop this script into a job step.

----------------------------------------------

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

----------------------------------------------

feel free to test it, but it works in both 2000 & 2005

cheers
sqladmin
New Member
New Member

--
31 Jan 2007 02:00 PM
here is another way; again using xp_cmdshell, and OSQL. it is a longer
read, but it shows you how to incorporate a .qry file.

for the purpose of this example lets say you have a backup job on
ServerA, and you want to run a restore job on ServerB once the
backup is finished.

there are a couple different ways of doing this, but i'm just
going to focus on one way using xp_cmdshell and OSQL

so you are currently on ServerA, and you want to run a job on ServerB

You can create a backup job as usual on ServerA, but for running the
other job remotely you need to create a .qry file with the sp_start_job
procedure in it, and lets assume you have the restore job already created on
ServerB and you have it called simply: my_restore_job

basically all you need to do is create a .txt file put your sp_start_job
script in it, and rename the file to .qry when your done. you can call
the file some thing like restore.qry

example:

exec msdb.dbo.sp_start_job @job_name = 'my_restore_job'

make sure you drop the .qry file in the following location on ServerA:

C:\WINDOWS\system32

now all you need to do is take the backup job you've created for
ServerA, and create a second step which will run the xp_cmdshell
script.

database: master

command: xp_cmdshell 'osql -E -S ServerB -i restore.qry'

fyi:
osql parameter (-E) means you are using mixed-mode authentication
and sql server will simply use what ever credentials you are logged
in with.

osql parameter (-S) is obviously the server name.

there is more parameters you can use, but to keep it simple i
just used those 2. for more information on osql try this:
http://msdn2.microsoft.com/en-us/li...62806.aspx

for more information on the xp_cmdshell try this:
http://msdn2.microsoft.com/en-us/li...L.80).aspx

in 2005 you'll need to change enable the xp_cmdshell feature
using the surface configuration utility.

now there is a bunch more you can do with the type of query
you can run, and the various permissions and security you can set,
but for the most part this is a workable solution to executing an
sql job remotely.

by the way... if/when you are testing or troubleshooting your jobs one thing to
remember is that the xp_cmdshell steps will always report 100% success regardless
of the OSQL outcome. so when your looking around for what went wrong all you
can do here is check the osql syntax within it, and the .qry file you made with
the rest of your sp_start_job script.

hope this is useful.

cheers
sqladmin
New Member
New Member

--
29 May 2007 09:58 AM
guess i forgot to mention that in original post.

glad you got it worked out.

rakeshmoza
New Member
New Member

--
08 Oct 2007 11:22 AM
Hi. I am trying to run a job from different server same way as mentioned in your first posting. Even if server and instance name is correct, I am getting the following errors.

[SQL Native Client]SQL Network Interfaces: Error Locating Server/Instance
Specified [xFFFFFFFF].
[SQL Native Client]Login timeout expired
[SQL Native Client]An error has occurred while establishing a connection to
the server. When connecting to SQL Server 2005, this failure may be caused by
the fact that under the default settings SQL Server does not allow remote
connections.
NULL

When I am running a select statement with same server and instance from different server, that works. But osql command gives the above error. Please advice.
sqladmin
New Member
New Member

--
08 Oct 2007 11:28 AM
which edition of SQL Server are you running?
sqladmin
New Member
New Member

--
08 Oct 2007 11:56 AM
i'm thinking you may want to try this:

go to:

SQL Server Configuration Tools under the SQL Server 2005 program group.

then to SQL Server Surface Area Configuration Utility.

click on Surface Area Configuration for Services and Connections

expand the 'Database Engine'
select 'Remote Connections' and then enable Local and remote connections.
sqladmin
New Member
New Member

--
08 Oct 2007 12:09 PM
Directly from SAC(Suface Area Configuration)

Note:
By default, SQL Server 2005 Express, Evaluation & Developer editions allow local
client connections only. Enterprise, Standard, and Workgroup editions will listen for
remote client connections over TCP/IP.

Go to the Suface Area Configuration Utility and configure the the
Surface Area Configurations for Services and Connections to listen for incoming client
connections. TCP/IP is preferred over named pipes because it requires fewer ports to
be opened across the firewall.

The options available are the following:

- Local connections only.
- Local and remote connections.

+-- Using TCP/IP only
+-- Using named pipes only
+-- Using both TCP/IP and named pipes


marekgorny
New Member
New Member

--
29 Jul 2008 03:35 AM
Thanx for that routine as it works great
I realize that I may be stretching this, but would it be possible to modify this, so that the job step on the originating server WAITS for the job on the distant server to complete with SUCCESS/FAILURE?

Best regards,
Marek
michael.bourgon
New Member
New Member

--
29 Sep 2008 11:23 AM
Marek, due to the way sp_start_job works, this would be pretty difficult as currently built. All you're doing is calling sp_start_job on the far server - and it'll immediately return a "job was started" message. What you could probably do job on the far side kick off a job locally, one particular job if it fails, another if it succeeds.
You are not authorized to post a reply.

Acceptable Use Policy