CODEWITHSUNDEEP
sql-server
JHizzal
JHizzal

Reputation: 661

Running Stored Procs on Different Databases through Agent Job

I have a list of three stored procedures that need to be ran daily on a number of separate SQL databases. Each database is named differently, but the stored procedures on each are the same.

I have extremely limited knowledge on anything other than basic queries, but was thinking I could have a SQL Server Agent job on a database I set up as Master. Then I have that server push that job to the other databases once I configure those as Targets. My issue is that in thinking through this, the database names are different and within the SQL Server Agent wizard I can only set the database to what's currently on master instance.

What would be the best approach to executing this looping through servers to run the stored procs?

Upvotes: 1

Views: 1369

Answers (1)

Tigerjz32
Tigerjz32

Reputation: 4472

What you're looking for is a linked-server. They allow servers to be linked so that you can call objects from one server to another. They are very easy to setup. In your case, you'll need to create 3 linked-servers on the main server, which will be used to schedule the job. A linked-server will allow the main server to link to listed servers. Here are tutorials of how you can create a linked-server.

Once you've created a linked-server you'll simply create a job that will execute all 3 sprocs, something like this:

EXEC [server1].[database].[schema].[sp_name];
EXEC [server2].[database].[schema].[sp_name];
EXEC [server3].[database].[schema].[sp_name];

Upvotes: 1

Related Questions