want to use multiple databases

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

want to use multiple databases

Author

Topic

rama108
Posting Yak Master

115 Posts

Posted - 2013-05-15 : 09:03:39

All,
I want to use multiple databases on a stored proc. How do i do that in a single execution?

Thanks.

djj55
Constraint Violating Yak Guru

352 Posts

Posted - 2013-05-15 : 09:20:55

Are all databases on the same instance of SQL Server?

djj

MIK_2008
Master Smack Fu Yak Hacker

1054 Posts

Posted - 2013-05-15 : 09:46:36

do you mean you want to perform DML or Select statments on the table(s) exist in multiple database? If so, you will need to use four part naming convension .. e.g.

SELECT * FROM ServerName.DatabaseName.SchemaName.TableName -- ServerName needs to be specified when the table is on other instance.

Cheers
MIK

rama108
Posting Yak Master

115 Posts

Posted - 2013-05-15 : 09:53:45

The databases are on the same instance. I want to use multiple databases to run a stored proc.

Thanks.

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2013-05-15 : 10:01:45

You can refer objects (such as table etc) of multiple databases on same instance by usinh following format
DatabaseName.SchemaName.TableName

--
Chandu

rama108
Posting Yak Master

115 Posts

Posted - 2013-05-15 : 12:56:41

I understand that but here i have a stored proc like this:

USE DB1
GO
CREATE PROCEDURE dbo.GetClient
as
.....

How do I run above for all the databases on the instance?

Thanks

djj55
Constraint Violating Yak Guru

352 Posts

Posted - 2013-05-15 : 13:58:57

Execute DB1.dbo.GetClient

djj

rama108
Posting Yak Master

115 Posts

Posted - 2013-05-15 : 18:10:45

Did not understand your code. I don't think you understand my problem either.

Bustaz Kool
Master Smack Fu Yak Hacker

1834 Posts

Posted - 2013-05-15 : 18:23:23

You want to create the same object(s) on a set of databases. You do this at the application level by creating four separate connections and executing your script against each connection.

=================================================
I am not one of those who in expressing opinions confine themselves to facts. (Mark Twain)

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-05-15 : 18:28:15

If your objective is to install the dbo.GetClient stored procedure on each database, repeating the code that you have for each DB and executing that DDL script would be the most straightforward.

Assuming you have the same stored procedure installed on all the databases, and you want to execute them all, then use what djj55 suggested:


EXEC DB1.dbo.GetClient;
EXEC DB2.dbo.GetClient;
EXEC DB2.dbo.GetClient;

rama108
Posting Yak Master

115 Posts

Posted - 2013-05-15 : 21:01:26

I do not want to execute the stored procedure but want to create the same stored procedure for all databases on one database instance. If someone knows how to explain better, then please explain what I want to accomplish.

Thanks.

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2013-05-15 : 21:42:35

One way is to write a script to generate another script.

Let's say the entire stored procedure looks like this:


Create Proc dbo.MySoredProcedure
	@param int
AS

SELECT	a.col1, a.col2, b.col3
FROM	TableA a
JOIN	TableB b
On	b.col1 = a.col1
WHERE	a.col4 = @param;

Then you can set the output to text, or to file, and run this:


SELECT	'use [' + name + '];
GO
Create Proc dbo.MySoredProcedure
	@param int
AS

SELECT	a.col1, a.col2, b.col3
FROM	TableA a
JOIN	TableB b
On	b.col1 = a.col1
WHERE	a.col4 = @param;
GO'
FROM	sys.databases
WHERE	database_id > 4;

Just replace my sample stored procedure with your actual one, then copy/paste the results back into your query window and execute that.

rama108
Posting Yak Master

115 Posts

Posted - 2013-05-17 : 10:01:19

This is not working I am getting the following error:
Msg 207, Level 16, State 1, Line 3
Invalid column name 'name'.
Msg 156, Level 15, State 1, Line 2
Incorrect syntax near the keyword 'from'.

rama108
Posting Yak Master

115 Posts

Posted - 2013-05-17 : 10:32:42

Sorry, I replied too soon. It is working where it is creating the Use database statement for each db but not creating the stored proc with it. so it creates this:

use [account_airtran];
GO
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO
THERE SHOULD BE A STORED PROC HERE, BUT NOT THERE

Any way even with the stored proc not there, it has helped me tremendously. I just pasted the stored proc for each use db. Thank you Russel, have a nice day.

Thanks again.

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2013-05-17 : 12:36:42

Only paste create proc statement. Not the SET operations and especially not any "GO"s

Or...paste the whole proc here and I'll tidy it up for you

EDIT: ahh, glad you got it. Have a great day!

rama108
Posting Yak Master

115 Posts

Posted - 2013-05-18 : 08:44:20

Got it. Thanks so much for your help, Russel.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources