Execute a Stored procedure from different databases

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 7,2000 » T-SQL » Execute a Stored procedure from different...

Execute a Stored procedure from different databases

Rate Topic

Display Mode

Topic Options

Author

Message

tarish.velivela

Posted Thursday, November 01, 2007 8:13 AM

Forum Newbie

Group: General Forum Members
Last Login: Friday, February 08, 2008 7:11 AM
Points: 4, Visits: 25

Hi,

I am trying to write a stored procedure which can be executed from any database and with context of the calling database.

For example, I have a table 'table1' in multiple databases. I would like to create a stored procedure as
-------------------------------
CREATE PROCEDURE dbo.test
AS
select count(*) from table1
GO
-------------------------------
in one of the databases (say 'db1') and would like execute the same from different databases.
When I execute this stored procedure using "Exec db1.dbo.test" from different database 'db2' it looks for the table 'table1' in 'db1' database instead of 'db2'.

I tried creating system stored procedure and execute the same from different databases. This time it could be executed without specifying database name and owner but the procedure looks for table 'table1' in 'master' database as it is created in master database.

I need a way to execute this stored procedure dynamically from any database and execute as if it is executed locally. As this need to be executed across different databases, I can not specify database name along with table name.

So, I tried to send table name as a parameter as below,
-------------------------------
CREATE PROCEDURE dbo.test
@t as sysname (or varchar (100))
AS
select count(*) from @t
GO
-------------------------------
When I tried to execute this stored procedure with
Exec db1.dbo.test @t = 'db2.dbo.table1'
it gives an error message (Must declare the variable '@t').

Could anybody please let me know how to execute a stored procedure from a different database on local tables or use database name/ table name as parameter.
My actual stored procedure is much more complex than what I've specified in example.

Thanks in advance,
Tarish.

Post #417516

Andras Belokosztolszki

Posted Thursday, November 01, 2007 8:18 AM

Ten Centuries

Group: General Forum Members
Last Login: Thursday, January 26, 2012 5:26 AM
Points: 1,367, Visits: 1,585

You can create a stored procedure in the master database, and name it sp_whatever
the sp_ will make it available in all of your databases, and the context will be the calling database.

Example:

use master
GO
create proc sp_alma1 as
select db_id()
GO
use tempdb
GO
exec sp_alma1
GO

Regards,
Andras

Andras Belokosztolszki, MCPD, PhD
GoldenGate Software

Post #417519

Andras Belokosztolszki

Posted Thursday, November 01, 2007 8:20 AM

Ten Centuries

Group: General Forum Members
Last Login: Thursday, January 26, 2012 5:26 AM
Points: 1,367, Visits: 1,585

Another thing, you cannot use @t in the from clause if @t is not a table variable.
If you are passing only the name, you need to build up your query using dynamic SQL

declare @query varchar(1000)
set @query = 'select * from ' + @a
exec(@query)

Regards,
Andras

Andras Belokosztolszki, MCPD, PhD
GoldenGate Software

Post #417522

tarish.velivela

Posted Friday, November 02, 2007 4:17 AM

Forum Newbie

Group: General Forum Members
Last Login: Friday, February 08, 2008 7:11 AM
Points: 4, Visits: 25

Andras Belokosztolszki (11/1/2007)

You can create a stored procedure in the master database, and name it sp_whatever
the sp_ will make it available in all of your databases, and the context will be the calling database.

Example:

use master
GO
create proc sp_alma1 as
select db_id()
GO
use tempdb
GO
exec sp_alma1
GO

Regards,
Andras

Hi Andras,

Thanks for you reply.
I was able to get dbid() or db_name() by executing stored procedure which is created in different database.
But my requirement is to access tables in current database by executing stored procedure which is created in different database.
Here is an example/sample of my requirement.

--------------------------------------------------
use tempdb
GO
create table temptable1(col1 varchar(100), col2 int)
GO
use master
GO
create proc sp_alma1
as
select * from temptable1
GO
use tempdb
GO
exec sp_alma1
GO
--------------------------------------------------
Executing this stored procedure from different database gives me:

Server: Msg 208, Level 16, State 1, Procedure sp_alma1, Line 3
Invalid object name 'temptable1'.

Please suggest me a solution to get through this.

Thanks,
Tarish.

Post #417835

Roy Latham

Posted Friday, November 02, 2007 7:44 AM

Forum Newbie

Group: General Forum Members
Last Login: Yesterday @ 1:08 PM
Points: 8, Visits: 753

I think this answers your question:

http://weblogs.sqlteam.com/mladenp/archive/2007/01/18/58287.aspx

Roy

Post #417918

Andras Belokosztolszki

Posted Monday, November 12, 2007 3:48 AM

Ten Centuries

Group: General Forum Members
Last Login: Thursday, January 26, 2012 5:26 AM
Points: 1,367, Visits: 1,585

Roy's link is the solution that you could use for the above. Just to include the solution here too:
On 2000 when you create the stored procedure in the master database, you can create it like:

use master
GO
EXEC master.dbo.sp_MS_upd_sysobj_category 1
GO
create proc sp_alma1
as
select * from dbo.temptable1
GO
EXEC master.dbo.sp_MS_upd_sysobj_category 2
GO
use tempdb

On 2005 you can just change an existing stored procedure (in my example sp_alma1) to a system proc with:

use master
GO
EXEC sys.sp_MS_marksystemobject sp_alma1
GO
use tempdb

Regards,
Andras

Andras Belokosztolszki, MCPD, PhD
GoldenGate Software

Post #420966

tarish.velivela

Posted Monday, November 12, 2007 4:46 AM

Forum Newbie

Group: General Forum Members
Last Login: Friday, February 08, 2008 7:11 AM
Points: 4, Visits: 25

Roy Latham (11/2/2007)

I think this answers your question:

http://weblogs.sqlteam.com/mladenp/archive/2007/01/18/58287.aspx

Roy

Hello Roy,

Thanks a lot. Your solution is what I was looking for. This makes my stored procedures work according to my requirement.
I have another concern here. An article in SQLServerCentral says there are some pitfalls with System Stored procedures.

The 2nd point in the article "http://www.sqlservercentral.com/articles/Administering/creatingasystemstoredprocedure/1358/" says
--------------------------------------------------------------------------------------------
2. You must keep a copy of the SP somewhere else because when the master database gets rebuilt your system stored procedures will be gone. This could also happen when applying a service pack and/or during an upgrade to a different version of SQL Server.
--------------------------------------------------------------------------------------------
Is this true? if so, how can I overcome this issue? I will be having more than 100 or nearly 200 SPs this way. Even if I keep a copy of the queries used in these SPs, it will be a tedious job to create so many SPs again in future .

Please suggest me a solution/ work around for this issue.

Thanks a lot in advance,
Tarish.

Post #420986

tarish.velivela

Posted Monday, November 12, 2007 4:48 AM

Forum Newbie

Group: General Forum Members
Last Login: Friday, February 08, 2008 7:11 AM
Points: 4, Visits: 25

Hi Andras,

Thanks for your post. This worked

Thanks,
Tarish.

Post #420989

K. Brian Kelley

Posted Wednesday, November 14, 2007 10:15 PM

Keeper of the Duck

Group: Moderators
Last Login: Yesterday @ 1:13 PM
Points: 6,584, Visits: 1,790

tarish.velivela (11/12/2007)

--------------------------------------------------------------------------------------------
2. You must keep a copy of the SP somewhere else because when the master database gets rebuilt your system stored procedures will be gone. This could also happen when applying a service pack and/or during an upgrade to a different version of SQL Server.
--------------------------------------------------------------------------------------------
Is this true? if so, how can I overcome this issue? I will be having more than 100 or nearly 200 SPs this way. Even if I keep a copy of the queries used in these SPs, it will be a tedious job to create so many SPs again in future .

If you ever have to rebuild your master database (say, due to corruption), this is true. However, you should have a backup to apply after you get SQL Server up and running again. With respect to service packs, your own stored procedures aren't very likely to be affected. If you modify a system stored procedure belonging to Microsoft, that's a different story.

With that said, it's still better to stay clear of the master database as much as possible.

K. Brian Kelley, CISA, MCSE, Security+, MVP - SQL Server
Regular Columnist (Security), SQLServerCentral.com
Author of Introduction to SQL Server: Basic Skills for Any SQL Server User
| Professional Development blog | Technical Blog |

LinkedIn | Twitter

Post #422456

« Prev Topic | Next Topic »

Permissions