How to use SQL Agent to run a job which executes SPs on another server?

Question

How to use SQL Agent to run a job which executes SPs on another server?

paul-941374

Say Hey Kid

Points: 701
More actions
November 20, 2008 at 10:30 am

#196322

I have a dedicated server with SQL 2005 and a shared server with access to a single SQL 2005 DB. I have created on job on my dedicated server which runs a series of stored procedures. I want to schedule this job on my dedicated server to run the stored procedures on the shared server.
I assume I need to add a step to the job which connects to the remote SQL DB. If this is the best way could someone please advise me how to do this?

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Elisabeth Rédei Hall of Fame Points: 3873 More actions · Answer 1

Hi,

There is a concept of Master and Target servers (or Multiserver administrtion) in SQL Server. It's been around a long time, I have actually never used it myself, but it should do the job for you.

Look up "Automating Administration Across an Enterprise" in BoL (ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/44d8365b-42bd-4955-b5b2-74a8a9f4a75f.htm).

HTH

/Elisabeth

elisabeth@sqlserverland.com
MCITP | MCT
http://sqlblog.com/blogs/elisabeth_redei/
http://linkedin.com/in/elisabethredei

Rudy Panigas SSChampion Points: 10702 More actions · Answer 2

Hello,

Just looking around and saw your question. I would use linked connection and then execute your stored procedure.

sp_addlinkedserver and sp_addlinkedsrvlogin are the 2 stored procedure you use or you can create a link connection through the Management Studio. Once connected you can execute something like this

EXEC ('SELECT * FROM [' + @InstanceName + '].MASTER.dbo.SYSDATABASES')

where [' + @InstanceName + '] is the name of the instance/server you are linked too.

Hope this helps.

Look for my article on Remote Execution here at SQL ServerCentral

Rudy

paul-941374 Say Hey Kid Points: 701 More actions · Answer 3

Hi Rudy

Thanks for your post. I had trouble with permissions trying to the Master and Target servers solution from Elizabeth. I think what you've suggest is just what I need as have permissions to link the remote server.

Will give it a go.

Many thanks.

If you're any good with XML and SQL there's another post of mine which no-ones managed to solve. http://www.sqlservercentral.com/Forums/FindPost605164.aspx

Rudy Panigas SSChampion Points: 10702 More actions · Answer 4

Glad to help when I can. Books On Line (BOL) is a good place to start looking for information on Link connections and it also gives example.

Not good with XML, sorry.

If you want to do remote execution on many SQL servers. Have looks at my article called

"Execution of Code on Multiple Servers Remotely"

Anyway good luck and let us know how it all turns out.

Rudy 🙂

Rudy