SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 want to use multiple databases
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

rama108
Posting Yak Master

109 Posts

Posted - 05/15/2013 :  09:03:39  Show Profile  Reply with Quote
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

USA
320 Posts

Posted - 05/15/2013 :  09:20:55  Show Profile  Reply with Quote
Are all databases on the same instance of SQL Server?

djj
Go to Top of Page

MIK_2008
Flowing Fount of Yak Knowledge

Pakistan
1052 Posts

Posted - 05/15/2013 :  09:46:36  Show Profile  Reply with Quote
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
Go to Top of Page

rama108
Posting Yak Master

109 Posts

Posted - 05/15/2013 :  09:53:45  Show Profile  Reply with Quote
The databases are on the same instance. I want to use multiple databases to run a stored proc.

Thanks.
Go to Top of Page

bandi
Flowing Fount of Yak Knowledge

India
2206 Posts

Posted - 05/15/2013 :  10:01:45  Show Profile  Reply with Quote
You can refer objects (such as table etc) of multiple databases on same instance by usinh following format
DatabaseName.SchemaName.TableName

--
Chandu
Go to Top of Page

rama108
Posting Yak Master

109 Posts

Posted - 05/15/2013 :  12:56:41  Show Profile  Reply with Quote
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
Go to Top of Page

djj55
Constraint Violating Yak Guru

USA
320 Posts

Posted - 05/15/2013 :  13:58:57  Show Profile  Reply with Quote
Execute DB1.dbo.GetClient

djj
Go to Top of Page

rama108
Posting Yak Master

109 Posts

Posted - 05/15/2013 :  18:10:45  Show Profile  Reply with Quote
Did not understand your code. I don't think you understand my problem either.
Go to Top of Page

Bustaz Kool
Flowing Fount of Yak Knowledge

USA
1677 Posts

Posted - 05/15/2013 :  18:23:23  Show Profile  Reply with Quote
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)
Go to Top of Page

James K
Flowing Fount of Yak Knowledge

3589 Posts

Posted - 05/15/2013 :  18:28:15  Show Profile  Reply with Quote
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;
Go to Top of Page

rama108
Posting Yak Master

109 Posts

Posted - 05/15/2013 :  21:01:26  Show Profile  Reply with Quote
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.
Go to Top of Page

russell
Pyro-ma-ni-yak

USA
5072 Posts

Posted - 05/15/2013 :  21:42:35  Show Profile  Visit russell's Homepage  Reply with Quote
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.
Go to Top of Page

rama108
Posting Yak Master

109 Posts

Posted - 05/17/2013 :  10:01:19  Show Profile  Reply with Quote
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'.
Go to Top of Page

rama108
Posting Yak Master

109 Posts

Posted - 05/17/2013 :  10:32:42  Show Profile  Reply with Quote
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.

Go to Top of Page

russell
Pyro-ma-ni-yak

USA
5072 Posts

Posted - 05/17/2013 :  12:36:42  Show Profile  Visit russell's Homepage  Reply with Quote
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!

Edited by - russell on 05/17/2013 12:37:56
Go to Top of Page

rama108
Posting Yak Master

109 Posts

Posted - 05/18/2013 :  08:44:20  Show Profile  Reply with Quote
Got it. Thanks so much for your help, Russel.
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.12 seconds. Powered By: Snitz Forums 2000