Dynamic SP and one database

Last Post 06 Jun 2006 03:53 AM by eramgarden_SQL. 3 Replies.
AddThis - Bookmarking and Sharing Button Printer Friendly
  •  
  •  
  •  
  •  
  •  
Sort:
PrevPrev NextNext
You are not authorized to post a reply.
Author Messages
eramgarden_SQL
New Member
New Member

--
05 Jun 2006 04:32 PM


So this is what we have now:
A "utility" database that only holds Stored Procs. No user tables. The stored procs will all be dynamic as they need to be pointing to different databases with the same schema tho...

For example, databaseA and databaseB have the same schema but different data. Since clients can have more than one database... "utility" database holds all the SPs that will be used by the databases. We pass the DB name to the SPs and then the SP can point to the right DB.

This makes all the SPs dynamic.

I know I can create the SPs in all DBs but what other alternatives are there besides the 2 mentioned??
mwesch
New Member
New Member

--
05 Jun 2006 05:28 PM
Do you feel there is a big maintenance savings by doing this? One thing I think might be an issue is network traffic. If SP databases and TABLE databases are on different servers could you be generating twice the network traffic? Or if on same server, could you be consuming more memory resources on server (or tempdb).

Normally web server queries database server and database server returns data to web server. Now you have web server query SP database, which queries TABLE database, which returns data to SP database, which finally returns data to web server.

Possibly this isn't an issue, but my instinct tells me that there could be resources overhead to do it as you designed.
eramgarden_SQL
New Member
New Member

--
06 Jun 2006 03:53 AM

What you say about the webserver makes sense...

But what are my other options?

If I put the SPs in a separate database..then they're going to be dynamic as I have to pass them the "database name". And the resource overhead you're talking about.

If I put them in every database...then I think that's a maintenance issue..if we make a change to one SP in one DB...that change has to be made in all...


I have no other options besides : have them on another Db and make them dynamic OR have the SPs repeated in all the databases? No other way to have them in ONE database (as they have now) but avoid passing the database name to the SP which causes it to be dynamic?
mwesch
New Member
New Member

--
06 Jun 2006 03:46 PM
Not necessarily an endorsement, but you could look into replicating objects so that changes to SP's are automatically pushed out to subscriber databases.
You are not authorized to post a reply.

Acceptable Use Policy