473,325 Members | 2,442 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,325 software developers and data experts.

calling Stored Procedures from different DBs

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.
Jul 20 '05 #1
1 3128
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
Jul 20 '05 #2

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

1
by: Robert Scheer | last post by:
Hi. I wrote some stored procedures used by a web application. As I am fairly new to Oracle, I am missing some concepts when creating these procedures, as a result, the application is suffering...
3
by: Matik | last post by:
Hi, I alredy tried to search this problem in last posts, but I couldn't find the answer. I try to access via Extended SP the method in a dll. I registered the dll as a ExSP, with a name of...
1
by: Lauren Quantrell | last post by:
I'm using an Access2K front end on a SQL Server2K backend. I use Scope_Identity() in a lot of stored procedures to pass the newwly inserted record's unique ID to the next select statement in the...
4
by: Minh Tran | last post by:
In order to minimize the number of connection strings I have to use to access different databases on the same Sql Server, I was considering storing all stored procedures in just one database. I...
11
by: jrefactors | last post by:
I want to know the differences between SQL Server 2000 stored procedures and oracle stored procedures? Do they have different syntax? The concept should be the same that the stored procedures...
2
by: Kent Lewandowski | last post by:
hi all, Recently I wrote some stored procedures using java jdbc code (admittedly my first stab) and then tried to implement the same within java packages (for code reuse). I encountered...
0
by: Amber | last post by:
Stored procedures are faster and more efficient than in-line SQL statements. In this article we will look at two SQL Server stored procedures; one using an input parameter and one not, and see how...
2
by: singlal | last post by:
Hi, my question was not getting any attention because it moved to 2nd page; so posting it again. Sorry for any inconvenience but I need to get it resolved fast. Need your help! ...
8
by: rocketboy2000 | last post by:
could anyone provide me with some simple example code of asp classic calling adodb? specifically, i'd like the code to: - call a couple of stored procedures that have optional parameters - share a...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.