how to execute stored procedure in multiple databases in sql server

Question

0.00/5 (No votes)

See more:

SQL-Server

Hello,

how to execute stored procedure in multiple databases in sql server

thanks

Posted 11-Oct-11 3:15am

K N R

Add a Solution

Comments

André Kraak 11-Oct-11 9:21am

Do you have the same stored procedure in multiple database, which you want to execute?

or

Do you have a stored procedure that needs to access multiple databases?

K N R 11-Oct-11 9:58am

hi andre,

Yes, i am access to multiple databases

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Corporal Agarn · Answer 1 · 2011-10-11T09:10:00

Solution 1 will work for multiple stored procedures in multiple databases.

For a stored procedure to access a different database the database should be linked. Then to access the tables in that database use the database in the name.

SQL

SELECT myColumn1 FROM [OtherServer\OtherInstance].[database].[schema].[table]

Mehdi Gholam · Answer 2 · 2011-10-11T04:46:00

Solution 1

You can do the following :

SQL

exec database1.dbo.usp_name1
exec database2.dbo.usp_name2
exec database3.dbo.usp_name3
-- continue like this as many times as you need

Posted 11-Oct-11 4:46am

Mehdi Gholam

Comments

K N R 14-Oct-11 0:33am

hey mehdi,

I have only one stored procedure but it run in different databases.

faizan saand 21-Apr-14 4:36am

if the other database has security login, how to call such sp from the other database?

Mehdi Gholam 21-Apr-14 5:05am

Then you have to have access first.

faizan saand 21-Apr-14 7:55am

Then how to access with permission

Mehdi Gholam 21-Apr-14 7:59am

Contact your DBA and ask for permission.