?
Solved

One database with stored procs and views to dynamically target multiple databases

Posted on 2007-10-13
15
Medium Priority
?
617 Views
Last Modified: 2012-06-27
Is there a way to have a common set of stored procs and views which can run againsts a specified database dynamically. We want to keep a common set of SP and Views in one master DB and have many instances of a database with just tables.
The web applcation (ASP.NET)  will run these SPs and views against different databases bases upon who is logged in.
So each client can have their own database full of data tables. Each of the client databases would have exactly the same structure and table names.
Is this at all possible with SQL server 2005, and if so can you point me in the right direction for a solution or further reading.
Thanks. Much appreciated.
0
Comment
Question by:tapasya
  • 5
  • 5
  • 3
13 Comments
 
LVL 19

Expert Comment

by:frankytee
ID: 20072916
if the db are on the same server, just make sure the user login in the other db has access to your "master" db. assuming your master db is called "myMaster", replace names accordingly
exec myMaster.dbo.spWhatever

if on different server then add the other sql server as a linked server and then add the server name in front of the db, something like:
exec othersqlLinkedServer.myMaster.dbo.spWhatever
0
 
LVL 19

Expert Comment

by:frankytee
ID: 20072922
hang on , are u talking about exec the sp against the original db or against each users db? if the latter then i think you'll have to add each sp to each user sp and dont execute against the original.
the only other alternative i can think of is that your original master builds a string dynamically which includes the users db in that string and then execute but that would be messy and performance wise there will be overheads.
0
 

Author Comment

by:tapasya
ID: 20072939
yes I want to run the storded proc on the user databases.
I was hoping there was something that could be done once when the user logged in and stored in the web app session. Something like putting USE @userdbname at the beginning of all the stored procs and passing the @userdbname as a parameter or having the SP look it up from  somewhere.
Anyway I was aware that I could use EXEC and/or generate the SP dynamically with the userdbname and then run it.
Yes that is messy. I was hoping there was a T-SQL command that could dynamically set the target db for a batch of commands....
I am aware of the performance issues that would raise but in light of the fact that these are simple SPs and Views and the greatest bottleneck is still likely to be the internet...
Any other ideas?
0
NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

 
LVL 19

Expert Comment

by:frankytee
ID: 20073227
>yes I want to run the storded proc on the user databases.

in that case the stored proc has to exist on said user db or you can execute a stored proc that exists on your "master" db that in turn dynamically points back to the user db and inserts/updates whatever.
i dont know of any other way, but maybe someone in EE might.

how often are your stored procs updated? if they are not, then just create them into each user db.
0
 

Author Comment

by:tapasya
ID: 20073472
ok - the issue here with having sp in the user dbs is that there may be hundreds/thousands of user dbs with 100 more SPs and views. I was just hoping to avoid the maintenance problem if we descide to make changes.
But, as you say, if we're not going to change them that often then running a script across the dbs is a small price vs the price of bad performance etc associated with having them in a master db.
Wishful thinking....I was just hoping that SQL Server 2005 had some way of linking up a bunch of separate DBs as though they were one. Like the way you can have multiple files making up the DB, but grouped dynamically.....
Anyway, unless anyone else has a better solution I agree with you. There just is no other way to do it. And having to dynamically generate the SP source and EXEC it is just too inefficient.
I agree that copying the SPs and Views into the user DBs is the best solution.

Any other suggestions.... Anyone... before I assign a winner!
 
0
 
LVL 18

Expert Comment

by:Yveau
ID: 20075045
You can create sp_YourProc stored procedures in the master database.
Those special named stored procedure can be called from any other database as if they were in that database.

I think that is what you want !

Hope this helps ...
0
 
LVL 18

Expert Comment

by:Yveau
ID: 20075057
so little script example with two databases (say db1 and db2):

create procedure master.dbo.sp_YourProc
begin
        select count(*) from dbo.Yourtable
end
go

use db1
go

exec dbo.sp_yourproc
-- can result in lets say 10.

use db2
go

exec dbo.sp_yourproc
-- can result in lets say 3.

So just one stored procedure (name needs to start with 'sp_') can be called from any database as if it were local in that database. If you make sure that each user has its default database correct, you should have this up and running in a few hours.

Hope this helps ...


0
 

Author Comment

by:tapasya
ID: 20077244
create procedure master.dbo.sp_YourProc
begin
        select count(*) from dbo.Yourtable
end
go

'CREATE/ALTER PROCEDURE' does not allow specifying the database name as a prefix to the object name.
------------------------------------------------------------

The tables that I reference in the sp_YourProc, do they have to exist in the master db so that the SP can compile?

Using you approach, how do we deal with Views?

I'm a bit reluctant to mix all our production SPs and Views in with system procedures. And the server tools do not allow me to create tables and views in the master db. I havn't tried using script.

0
 

Author Comment

by:tapasya
ID: 20077523
sorry... you can create these things in master db using the tools.
but the syntax for the creation of the SP should read:
create procedure master.dbo.sp_YourProc
AS
begin
        select count(*) from dbo.Yourtable
end
go

if the table does not exist in the master db I get an error when the SP runs saying it can't find the table.
if the table is created in the master db (no data in it) then when I switch context to db1 and run the SP, it runs, but it returns the results from the table in the master db.

what am I missing?

thanks heaps for the help. much appreciated
0
 
LVL 18

Expert Comment

by:Yveau
ID: 20080477
let me test that ... hang on !
0
 
LVL 18

Accepted Solution

by:
Yveau earned 1600 total points
ID: 20080893
You're right. the sp_ stored procedures can be called from any database, but when you refer to a specific table in the SQL code, that table is referred to by it's ID and not by it's name. But ... this might work:

use Yveau
go

create table Y1 (Col1 int)
go

insert into Y1 values (1)
insert into Y1 values (2)
insert into Y1 values (3)
insert into Y1 values (4)
insert into Y1 values (5)
insert into Y1 values (6)

use master
go

create procedure dbo.sp_Y1
as
begin
    declare @SQL varchar(max)
    select  @SQL = replace('select count(*) from <DB>.dbo.Y1', '<DB>', '['+db_name()+']')
    exec (@SQL)
end
go

use Yveau
go

exec dbo.sp_Y1
go

I have to agree that it is not a very 'clean' way. It is both all stashed into the master database, one that I like to keep clean as well, and it's dynamic SQL which is not famous for its good performance ...

... but it can be done with only one set of procedures :-)
To be honest, I would do that myself.

Maybe you should not create a database for every user and look for a solution in that direction. You could add a column in every table called [UserID] and make sure that everything a user does in any table will always be based on the records for the users own [UserID] ...

Hope this helps ...

0
 

Author Comment

by:tapasya
ID: 20081259
thnks for all you advice and time.
we already have the solution based upon user id in all tables and I was hoping to move things to a common place.
THe real answer is that it CAN be done, but its not really practicle.

I will have a go at implementing your solution before assigning points. I just want to make sure that what we have here works and is complete before accepting it as a solution.

Thanks again......
0
 
LVL 18

Expert Comment

by:Yveau
ID: 20081279
You're welcome and good luck !
If you need any more assistance, just use this thread. I'll keep monitoring it ...
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article will describe one method to parse a delimited string into a table of data.   Why would I do that you ask?  Let's say that you need to pass multiple parameters into a stored procedure to search for.  For our sake, we'll say that we wa…
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
Please read the paragraph below before following the instructions in the video — there are important caveats in the paragraph that I did not mention in the video. If your PaperPort 12 or PaperPort 14 is failing to start, or crashing, or hanging, …
Whether it be Exchange Server Crash Issues, Dirty Shutdown Errors or Failed to mount error, Stellar Phoenix Mailbox Exchange Recovery has always got your back. With the help of its easy to understand user interface and 3 simple steps recovery proced…

850 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question