Distributed Stored Procedures

I have an SQL Server that has a structure as follows.
1. There is a common database that contains hundreds of stored
procedures.
2. There are also 50+ identical databases, with the same number of
tables and table structures.
3. The common stored procedures must be able to read/write data to any
of these 50+ databases. How can this be done? I can pass the database
names as a parameter in the stored procedures but that means changing
hundreds of stored procedures and over 500K lines of application code.
Is there a way I can let the stored procedures read/write to these
databases without doing all of this?
Any help will be really appreciatedIf your stored procedure starts with the prefix sp_ SQL server will check
the master database first before the local database.
If you put your stored procedures in the master database and run them within
the context of the destination database without any database prefix doesn't
that work ?
Just an idea, and against the rules of clean coding.
Nico De Greef
Belgium
Freelance Software Architect
MCP, MCSD, .NET certified
"Kofi" <kofi@.ibeaminc.com> wrote in message
news:fc429752.0402260800.61d32398@.posting.google.com...
> I have an SQL Server that has a structure as follows.
> 1. There is a common database that contains hundreds of stored
> procedures.
> 2. There are also 50+ identical databases, with the same number of
> tables and table structures.
> 3. The common stored procedures must be able to read/write data to any
> of these 50+ databases. How can this be done? I can pass the database
> names as a parameter in the stored procedures but that means changing
> hundreds of stored procedures and over 500K lines of application code.
> Is there a way I can let the stored procedures read/write to these
> databases without doing all of this?
> Any help will be really appreciated|||"Kofi" <kofi@.ibeaminc.com> wrote in message
news:fc429752.0402260800.61d32398@.posting.google.com...
> I have an SQL Server that has a structure as follows.
> 1. There is a common database that contains hundreds of stored
> procedures.
> 2. There are also 50+ identical databases, with the same number of
> tables and table structures.
> 3. The common stored procedures must be able to read/write data to any
> of these 50+ databases. How can this be done? I can pass the database
> names as a parameter in the stored procedures but that means changing
> hundreds of stored procedures and over 500K lines of application code.
> Is there a way I can let the stored procedures read/write to these
> databases without doing all of this?
A stored procedure in say database 1 is able to be written such that
it can update one, any or all of the other 50 databases you reference
in 2 above. What I am not clear on is what the operational conditions
are like at the moment. How is this currently working (eg: the 500K
lines of app code looks at the procedures in database 1?).
If this is the case, dependent upon your needs for change, it should
be possible only to alter the base stored procs. However parameter
passing may become mandatory.
To have a sproc in database_1 write to database_n you preface the
table names with the database names
eg: select * from database_52..sysobjects
Pete Brown
Falls Creek
Oz
www.mountainman.com.au