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-21 : 09:08:13
|
| Harvey writes "I have found lots of great info on your site, but I can't find the answer to this one.Sql 7 (SP4), W2K Server (SP2)I want to setup a master db that will hold all the sp logic and setup an undetermined number of identical client dbs that will hold various clients data - but not the sp logic.I need to write stored proceedures on the master db that will perform operations on the client db.I need to call the sp from vb.net and pass the name of the client db to perform the operation on. (important to know how)Just to make things interesting - what if I wanted to perform the operation on a db on a different server? (would be nice to know how)First, is this possible? If so, could you give me a hint as to the syntax?ThanksHarveyobtw: If it is already covered, please feel free to make fun of me :-) - then please point me to a link." |
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2002-05-21 : 09:12:29
|
| Keeping the stored procedures separate from the data they work on is not the best way to do it. The ONLY way to do what you describe is to use dynamic SQL:http://www.sqlteam.com/item.asp?ItemID=4599http://www.sqlteam.com/item.asp?ItemID=4619But you're gonna end up with some pretty complicated code that'll be very hard to maintain and won't perform as well. Just include the stored procedures in every client's database. You can simplify this by including all of them in the model system DB; each time you create a new database it uses model as its base. Keep all of your SP source code in SQL files, and run them in each database when you add or modify them. It takes maybe 10 seconds per DB. |
 |
|
|
M.E.
Aged Yak Warrior
539 Posts |
Posted - 2002-05-21 : 11:54:36
|
| I'd take a look at your database design on this one Harvey.Instead of using multiple databases, try narrowing it down to one DB. Instead of a seperate database for each client, use a single table (clients for example) that holds each of the clients. You then create a Key in this table that all other tables link to (Foriegn Key). I'd go into further but kinda have time limits.. Email me if you want more info I guess. If you've already looked into this and have decided multiple databases is the only way to go (Which I've never seen a case where this is true) well, your looking at some long pieces of code. |
|
|
|
|
|
|
Stored Proc