Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Execute a Stored procedure from different databases Expand / Collapse
Author
Message
Posted Thursday, November 1, 2007 8:13 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, February 8, 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
Posted Thursday, November 1, 2007 8:18 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen 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
Posted Thursday, November 1, 2007 8:20 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen 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
Posted Friday, November 2, 2007 4:17 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, February 8, 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
Posted Friday, November 2, 2007 7:44 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, August 25, 2014 5:06 PM
Points: 8, Visits: 1,073
I think this answers your question:

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

Roy
Post #417918
Posted Monday, November 12, 2007 3:48 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen 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
Posted Monday, November 12, 2007 4:46 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, February 8, 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
Posted Monday, November 12, 2007 4:48 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

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

Thanks for your post. This worked

Thanks,
Tarish.
Post #420989
Posted Wednesday, November 14, 2007 10:15 PM


Keeper of the Duck

Keeper of the Duck

Group: Moderators
Last Login: Monday, August 18, 2014 8:24 AM
Points: 6,634, Visits: 1,871
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 »

Add to briefcase

Permissions Expand / Collapse