Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
AskSQLTeam
Ask SQLTeam Question
0 Posts |
 Posted - 2002-05-23 : 09:07:19
|
| Dylan writes "Hi all,I have a project which involves ten websites running off schematically identical databases. At the moment, all ten databases contain copies of the same stored procedures - which means every time I find a bug, I have to fix ten copies of the same code.Is there some way I can define the SP's independently of any of the databases? I've experimented with defining the SPs in a separate shared DB but either I'm getting the syntax wrong or it's not possible, 'cos I can't get the desired behaviour.Ideally, I'd be looking to do something like:use db_onedb_shared.sp_get_names[returns names from db_one]use db_twodb_shared.sp_get_names[returns names from db_two]where the procedure sp_get_names is defined once in 'db_shared'. Is this possible? Easy? A waste of time? Any advice greatly appreciated. :)Thanks,Dylan" |
|
|
Nazim
A custom title
1408 Posts |
Posted - 2002-05-23 : 09:14:21
|
| if you executing 'use' command thru a sp . it wouldnt workt the way you want to.on your original Q.you can create the Sp in Master database , starting with the name sp_andyourname . AFAIK , it should be accessible from globally. HTH-------------------------------------------------------------- |
 |
|
|
Page47
Master Smack Fu Yak Hacker
2878 Posts |
Posted - 2002-05-23 : 09:17:43
|
in order to do what you are talking about, the proc in db_shared would have to know in which database to execute the dml. in order to do that, you would have to pass the database name as a param, then use dynamic sql to dynamically execute the dml against the database named in the param.....that is a bad idea....suggestion one. don't use 10 identical databases. use 1 database. differentiate data between your 'websites' with some sort of indicator columns in the tables as needed.suggestion two. do nothing, make your changes to 10 copies of the same code. You've made your bed, now sleep in it  . If you wanted to mess with the thing, you could write a proc that takes a proc name as a param. Then query syscomments to get the text of the newly modified proc. Then recompile the proc on the other 9 databases....<O> |
|
|
|
DrewBurlingame
Starting Member
49 Posts |
Posted - 2002-05-23 : 09:25:53
|
| Are we feeling a little grumpy today Page47? You always seem to have such great ideas and depthful insight. I'm surprised you couldn't see business rules that could force the use of mutliple dbs.Dylan,If the databases are on the same server, I know of two options. One is Nazim's suggestion of creating the sp in Master. The second is creating another database and putting all your stored procedures in there. Then call EXEC DBName.OwnerName.SPName Params. I do this and it works fine. If they're on different servers, then you'll need to link the servers, and add the ServerName like this: EXEC ServerName.DBName.OwnerName.SPName Params. |
|
|
|
Page47
Master Smack Fu Yak Hacker
2878 Posts |
Posted - 2002-05-23 : 09:42:34
|
so let's say there is a table, users, in the ten different identitcal databases. and let's say you've got a proc, usp_loginuser defined as such . . .create proc usp_loginuser @username varchar(25), @password varchar(25), @isvalid bit outasselect @isvalid = count(*)from userswhere username = @username and pasword = @passwordgo now lets say you compile proc in db_shared. Then...use db_oneexec db_shared.dbo.usp_loginuser @username, @password, @isvalid OUT ...is going to execute the dml against the users table in db_shared correct, not the users table in db_one? How do you tell it go look for username/pass in db_one, other than dynamic sql? Maybe I'm not quite awake yet . . . Who me? Grumpy? bwahahaha!!!<O>Edited by - Page47 on 05/23/2002 09:45:30 |
|
|
|
DrewBurlingame
Starting Member
49 Posts |
Posted - 2002-05-23 : 09:49:33
|
| No, it is me who is not awake yet. You are correct Jay. I can see now how my scenerio isn't exactly the same as Dylans'.I bow to your superior intellect, and will tread more carefully in the future. |
|
|
|
DrewBurlingame
Starting Member
49 Posts |
Posted - 2002-05-23 : 09:53:57
|
| In fact, the more I look at this, the more I feel like the idiot. Perhaps 3 hours of sleep really is inadequate anymore. Or perhaps I really am just an idiot. |
|
|
|
|
|
|
|
|
'Global' stored procedures accessible from multiple DBs?