ZeBerg (ad**@alumni.northwestern.edu) writes:
Is it possible to have a stored procedure in database A while calling
it from database B and have it manipulate the tables in database B
(whatever the calling database happens to be)?
We have a large-scale app that uses many complex stored procedures,
and as of now, we're copying the SPs to every new database that is
created, and it will soon become a nightmare for propagating updates
and fixes. We'd like to keep a master set of the SPs in one DB and
"use" them from other DBs so that they only query data and manipulate
tables in the calling DB. I hope someone has some suggestions. Thanks.
While this is possible, this will bring out of the frying pan, and into
the fire.
You would have to use dynamic SQL to manipulate the tables, and then
many of the advantages of stored procedures would go out the window.
OK, so there is the possibility of adding your own system procedures in
the master database, but this is unsupported, has security issues and
and I am not even sure whether it works with next version of SQL Server.
And neither of these schemes works the day you decide to scale out and
have databases one more than one server. Or the day you want to a test
version of the app running against a test database on the same server.
So what is left? Do as you do know, that is the way to go. What you
apparently need, is to invest time in how to deploy changes in a
controlled way. A foundation for this is to use a version-control
system. It is from the VCS you build update scripts to deploy the
changes. You might higher levels of sophistication, and there are
a number of third-party tools out there which aim specifically at
configuration management with SQL Server.
Since my shop in the same as yours - except that our multiple databases
are both in-house and remote customer sites - we have developed our
own routines, and the tool we use is actually available as freeware,
see
http://www.abaris.se/abaperls/.
--
Erland Sommarskog, SQL Server MVP,
so****@algonet.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp