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
exec msdb.dbo.sp_start_job @job_name = 'my_restore_job'
make sure you drop the .qry file in the following location on ServerA:
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
command: xp_cmdshell 'osql -E -S ServerB -i restore.qry'
osql parameter (-E) means you are using mixed-mode authentication
and sql server will simply use what ever credentials you are logged
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:
for more information on the xp_cmdshell try this:
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.