September 18, 2002 at 11:59 am
On my SQL Server 2000 server I manage several databases with the same structure. Number and names of them are changing regularly. As part of that I have to write number of administrative routines that work against choosen database (either through the input parameter of the routine itself or using 'use' statement).
Because of I cannot add these routines to the database structure as they are built based on externally provided scripts, I took approach to create these routines in master database with sp_ prefix at the beginning of the name. This would give me an ability to run these stored procs while any of the databases are current.
However, I hit the problem that is best described with the following code:
use master
go
if exists (select * from sysobjects where name='sp_Test' and type='P')
drop procedure sp_Test
go
create procedure sp_Test
AS
begin
print db_name() -- will print pubs in example
select * from sysobjects where type='U' and name in ('titles','authors') -- returns 2 rows taken from pubs database
exec (N'select * from authors') -- will run properly against pubs
exec sp_tables -- returns selectable tables from pubs
select * from spt_monitor -- table from master => will run properly
select * from titles -- table from pubs => will fail to run
end
go
use pubs
go
exec sp_Test
Dynamically created statements or ones which use run-time information work just fine, while regular statements in the routine refer to tables in master database.
I tried number of options (including WITH RECOMPILE clause etc) but this problem remains.
September 18, 2002 at 12:35 pm
If you want your regular select statement to run against the database from where the SP is executed your store procedure needs to be a system stored procedures, instead of a user stored proc. To create a system stored procedure do the following:
1) Run this command:
sp_MS_upd_sysobj_category 1
2) create you proc in master
3) Run this command:
sp_MS_upd_sysobj_category 2
Note "sp_MS_update_sysobj_category" is undocumented.
Gregory Larsen, DBA
If you looking for SQL Server Examples check out my website at http://www.geocities.com/sqlserverexamples
Gregory A. Larsen, MVP
September 19, 2002 at 2:03 pm
Thanks. It really did work!
Where did you find such things out?
September 19, 2002 at 2:10 pm
To be honest with you I had the same problem a few years ago, and some kind sole told me how to get this to work. If I could remember who it was I would give them credit.
Gregory Larsen, DBA
If you looking for SQL Server Examples check out my website at http://www.geocities.com/sqlserverexamples
Gregory A. Larsen, MVP
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply