Home > IT Answers > SQL Server > Cross-database name change in SQL Server 2008 stored procedures
Rusty2
5 pts.
 Cross-database name change in SQL Server 2008 stored procedures
SQL 2008, SQL Server 2008 R2, SQL Server stored procedures, Stored Procedures
Hi:

The easy part is the databases will be on the same server.  However at the moment I am writing code in database SOMEDATABASE_TEST which is accessing objects in SOMEOTHERDATABASE_TEST.  When its time to go to production the names are going to change to SOMEDATABASE and SOMEOTHERDATABASE.  However the queries in SOMEDATABASE_TEST stored procedures read:

select * from SOMEOTHERDATABASE_TEST.dbo.SOMETABLE

the goal would be to have some variable @@crossdatabasename = SOMEOTHERDATABASE_TEST

then all the calls would look like

select * from @@crossdatabasename.dbo.SOMETABLE

We are using sql server 2008 r2

What is the best way around this?

Thanks



Software/Hardware used:
sql server 2008 r2
ASKED: January 30, 2011  5:03 PM
UPDATED: February 2, 2011  1:51 PM
RELATED QUESTIONS
Answer Wiki:
Notwithstanding a redesign to bring all related objects into the same database, I would think you can get where you want to be using SYNONYMs. --Use this for TestDB CREATE SYNONYM DBOther FOR SOMEOTHERDATABASE_TEST.dbo.SOMETABLE GO --Use this for ProdDB CREATE SYNONYM DBOther FOR SOMEOTHERDATABASE.dbo.SOMETABLE GO --Write your generic code using the SYNONYM SELECT * FROM DBOther GO
Last Wiki Answer Submitted:  February 2, 2011  1:51 pm  by  Fortuna   35 pts.
All Answer Wiki Contributors:  Fortuna   35 pts. , Hlx   690 pts.
To see all answers submitted to the Answer Wiki: View Answer History.


RSS - Discussions Help
Discuss This Question:
_ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _