November 1, 2007 at 8:13 am
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.
November 1, 2007 at 8:18 am
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
November 1, 2007 at 8:20 am
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
November 2, 2007 at 4:17 am
Andras Belokosztolszki (11/1/2007)
You can create a stored procedure in the master database, and name it sp_whateverthe 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.
November 2, 2007 at 7:44 am
I think this answers your question:
http://weblogs.sqlteam.com/mladenp/archive/2007/01/18/58287.aspx
Roy
November 12, 2007 at 3:48 am
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
November 12, 2007 at 4:46 am
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.
November 12, 2007 at 4:48 am
Hi Andras,
Thanks for your post. This worked
Thanks,
Tarish.
November 14, 2007 at 10:15 pm
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
@kbriankelley
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply