August 5, 2008 at 8:17 am
Hi,
I have 2 customers; they both have their own database that is identical to the other. What I want is a "common" database that holds the stored procedures. Something like this pseudocode:
DATABASE Cust1
Stored Procedure GetEmployees(@town)
BEGIN
--- USE Cust1
--- EXEC common.dbo.GetEmployees(@town)
END
DATABASE Cust2
Stored Procedure GetEmployees(@town)
BEGIN
--- USE Cust2
--- EXEC common.dbo.GetEmployees(@town)
END
DATABASE common
Stored Procedure GetEmployees(@town)
BEGIN
--- SELECT * FROM employees WHERE town=@town
END
Both customer 1 and customer 2 call the same GetEmployees stored procedure in the common database; obviously they each get their own list of employees in the given town.
It is not possible to use USE DATABASE in a stored procedure, I don't know how to work around this. In the real world I don't have 2 customers, but much, much more.
Thanks,
Raymond
August 5, 2008 at 8:44 am
One solution is to add the stored procedures to the master database, and add sp_ prefix to their names
(bad practise)
Another solution is to use a tool to sync the stored procedures between the various databases.
Third solution is to use dynamic sql and use three part names like customerdb1.dbo.sometable
None of the above solutions (maybe apart from the second) are nice though.
Regards,
Andras
August 5, 2008 at 9:06 am
Andras Belokosztolszki (8/5/2008)
One solution is to add the stored procedures to the master database, and add sp_ prefix to their names(bad practise)
Another solution is to use a tool to sync the stored procedures between the various databases.
Third solution is to use dynamic sql and use three part names like customerdb1.dbo.sometable
None of the above solutions (maybe apart from the second) are nice though.
Regards,
Andras
Actually, I don't think it is a bad practice to create a stored proc that resides in master. You just have to be careful in naming the proc so that you don't have to worry about Microsoft coming out with a system proc of the same name. A bad practice is naming procs with sp_ that reside in a user database, as SQL Server will first look to master then the user database.
😎
August 5, 2008 at 1:21 pm
Hi there,
Thanks for your input.... however being a newbie about where (or where not) to place SP's I'm not quite getting it. I understand that when you put a SP in the master database, the name should begin with sp_ , and that a name should be chosen that Microsoft will not likely use in the future.
Question (1)
I assume I can put any own SP in the master database Programmability|Stored Procedures... or should I put it in Programmability|Stored Procedures | System Stored Procedures ?
Question (2)
Will any SP in the master database use data in the user-database it is fired from? In my first test I could fire a SP named sp_mytest in the master database from a user-defined database; but I 'm not getting a resultset back (SELECT * FROM customers gives the error:Invalid object name 'customers')
This SP does work:
declare @dbname sysname
SET @dbname = db_name()
exec('SELECT * from '+@dbname+'.dbo.customers')
... but then I would have to rewrite all queries to a querystring.... not acceptable
Question (3)
Apart from the SP's name having to be unique, are there any other considerations?
Looking forward to any input!!
Thanks, Raymond
August 5, 2008 at 2:08 pm
Andras Belokosztolszki (8/5/2008)
One solution is to add the stored procedures to the master database, and add sp_ prefix to their names(bad practise)
Well, this works in the sense that SP_FOO will find the proc in master.dbo, no matter what database you are in. However, it does NOT work in the sense that it will act like it is running in your current database. Like all other stored procedures (except the MS supplied Master.sys procs) Master.dbo.sp_* procs are complied and bound against the database that they are stored in, as the following demonstrates:
USE MASTER
Go
create proc sp_RBY_check_db_loc
as
Select distinct TABLE_CATALOG
From INFORMATION_SCHEMA.TABLES
Go
exec sp_RBY_check_db_loc
Select distinct TABLE_CATALOG
From INFORMATION_SCHEMA.TABLES
Go
USE model
Go
exec sp_RBY_check_db_loc
Select distinct TABLE_CATALOG
From INFORMATION_SCHEMA.TABLES
Go
As you will note, no matter which DB you USE, the "exec" always returns "master".
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
August 5, 2008 at 2:37 pm
R. van Laake (8/5/2008)
Hi there,Thanks for your input.... however being a newbie about where (or where not) to place SP's I'm not quite getting it. I understand that when you put a SP in the master database, the name should begin with sp_ , and that a name should be chosen that Microsoft will not likely use in the future.
Question (1)
I assume I can put any own SP in the master database Programmability|Stored Procedures... or should I put it in Programmability|Stored Procedures | System Stored Procedures ?
Question (2)
Will any SP in the master database use data in the user-database it is fired from? In my first test I could fire a SP named sp_mytest in the master database from a user-defined database; but I 'm not getting a resultset back (SELECT * FROM customers gives the error:Invalid object name 'customers')
This SP does work:
declare @dbname sysname
SET @dbname = db_name()
exec('SELECT * from '+@dbname+'.dbo.customers')
... but then I would have to rewrite all queries to a querystring.... not acceptable
Question (3)
Apart from the SP's name having to be unique, are there any other considerations?
Looking forward to any input!!
Thanks, Raymond
One more consideration for placing stored procedures into master - and that is, you need to mark them as system stored procedures. Otherwise, they just run in the context of the master database.
You would do this:
Execute sp_MS_marksystemobject sp_mytest;
And that should setup the procedure so it runs in the context of the database where it is executed.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
August 5, 2008 at 2:46 pm
Jeffrey Williams (8/5/2008)
You would do this:Execute sp_MS_marksystemobject sp_mytest;
And that should setup the procedure so it runs in the context of the database where it is executed.
Very nice, Jeffrey! I have been wondering about this for a while...
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
August 5, 2008 at 2:48 pm
rbarryyoung (8/5/2008)
Andras Belokosztolszki (8/5/2008)
One solution is to add the stored procedures to the master database, and add sp_ prefix to their names(bad practise)
Well, this works in the sense that SP_FOO will find the proc in master.dbo, no matter what database you are in. However, it does NOT work in the sense that it will act like it is running in your current database. Like all other stored procedures (except the MS supplied Master.sys procs) Master.dbo.sp_* procs are complied and bound against the database that they are stored in, as the following demonstrates:
USE MASTER
Go
create proc sp_RBY_check_db_loc
as
Select distinct TABLE_CATALOG
From INFORMATION_SCHEMA.TABLES
Go
exec sp_RBY_check_db_loc
Select distinct TABLE_CATALOG
From INFORMATION_SCHEMA.TABLES
Go
USE model
Go
exec sp_RBY_check_db_loc
Select distinct TABLE_CATALOG
From INFORMATION_SCHEMA.TABLES
Go
As you will note, no matter which DB you USE, the "exec" always returns "master".
I think, in this particular case, it has to do with the INFORMATION_SCHEMA views. I played with them in SQL Server 2000, and for those to work correctly, you had to be in the database (USE [dbname_here]) for them to work. I have written a couple of generic procedures where I work and put them in the master database and they work okay.
😎
August 5, 2008 at 2:58 pm
rbarryyoung (8/5/2008)
Jeffrey Williams (8/5/2008)
You would do this:Execute sp_MS_marksystemobject sp_mytest;
And that should setup the procedure so it runs in the context of the database where it is executed.
Very nice, Jeffrey! I have been wondering about this for a while...
I should have also mentioned that this little procedure is undocumented. But, it has been around for a while and I don't see MS getting rid of it. Anyways, use at your own risk. 😉
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
August 5, 2008 at 3:07 pm
Hi Jeffrey (and others)
Thanks! Exactly what I needed!
I stumbled across
http://msdn.microsoft.com/en-us/library/ms187760.aspx
that handles synonyms, it was not perfect at all, but better than nothing.... of course your solution is better.
Of course sp_MS_marksystemobject being undocumented worries me.... any mention of it still being in SQL2008?
Thanks a lot again!!!!!!!!!
Raymond
August 5, 2008 at 3:14 pm
From: http://www.webtropy.com/articles/SQL.asp?SQL=sp_MS_marksystemobject
-- FOR INTERNAL USE ONLY ... DO NOT DOCUMENT --
-- This procedure sets a bit in sysobjects. This bit has no meaning, various
--groups (starfigther, davinci, replication) use it for different things
-- MSQL makes no warranty, express or implied, on what objects will or will
--not have this bit set. Use at your own risk.
--
create procedure sp_MS_marksystemobject
@objname nvarchar(517) -- 517 is max for two part name
as
-- pre-stuff --
set nocount on
-- CHECK THE OBJECT NAME --
if object_id(@objname, 'local') is null
begin
raiserror('sp_MS_marksystemobject: Invalid object name ''%ls''',0,1,@objname)
return 1
end
-- CHECK THE OBJECT OWNER (MUST BE A SYSTEM USER) --
if user_name(ObjectProperty(object_id(@objname, 'local'), 'ownerid'))
not in ('dbo','INFORMATION_SCHEMA')
begin
raiserror('sp_MS_marksystemobject: Object must be owned by a system user.',0,1)
return 1
end
-- DO THE UPDATE --
begin tran
dbcc LockObjectSchema(@objname)
update sysobjects set status = status | 0xC0000000
where id = object_id(@objname, 'local')
commit tran
return @@error -- sp_MS_marksystemobject
August 5, 2008 at 3:17 pm
Lynn Pettis (8/5/2008)I think, in this particular case, it has to do with the INFORMATION_SCHEMA views. I played with them in SQL Server 2000, and for those to work correctly, you had to be in the database (USE [dbname_here]) for them to work. I have written a couple of generic procedures where I work and put them in the master database and they work okay.
😎
They fixed that behavior of INFORMATION_SCHEMA views in SQL2005.
Anyway, the solution is the undocumented sproc "sp_MS_marksystemobject" that Jeffrey posted. If you use that, then my same test will give the desired results. Very cool!
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
August 5, 2008 at 3:27 pm
rbarryyoung (8/5/2008)
Lynn Pettis (8/5/2008)I think, in this particular case, it has to do with the INFORMATION_SCHEMA views. I played with them in SQL Server 2000, and for those to work correctly, you had to be in the database (USE [dbname_here]) for them to work. I have written a couple of generic procedures where I work and put them in the master database and they work okay.
😎
They fixed that behavior of INFORMATION_SCHEMA views in SQL2005.
Anyway, the solution is the undocumented sproc "sp_MS_marksystemobject" that Jeffrey posted. If you use that, then my same test will give the desired results. Very cool!
Okay. I haven't used the INFORMATION_SCHEMA views in SQL 2005. When I have needed info, I have used the sys tables, even if I shouldn't be, it has just been easier; plus I have been able to get additional info from the tables for other purposes.
😎
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply