How to make inter-database queries with dynamic SQL Server database name? We have two database servers with SQL Server 2005, with two databases each. The secondary server have two SQL Server instances: one for development and other for reporting. Both instances on secondary server restore backups daily from the primary server, but only the development instance could have data modifications.


The two databases are from different applications, but one database has triggers that alter data on the other database. Triggers update the second database hardcoding its name, like UPDATE database2..thetable
One database can fetch data from another via SQL queries from one application or Stored Procedures, hardcoded as SELECT FROM database2..thetable


We are considering joining the two SQL Server instances on secondary server (reporting and development) to simplify administration and memory managing, but I couldn't find a solution to make triggers and stored procedures calls another database dynamically and automatically with another name using the same suffix. Ex:


Instance: RS


DB1 (calls DB2 with DB2..table)
DB2

Instance: DEV


DB1 (calls DB2 with DB2..table)
DB2



Into:


Instance: default

DB1_RS (calls DB2_RS with DB2_RS..table)
DB2_RS
DB1_DEV (calls DB2_DEV with DB2_DEV..table)
DB2_DEV



What are the possible solutions to this? I can think about some, but they are too convoluted:


Create dynamically T-SQL to be executed via EXEC(). This makes thing more complex because the triggers have lots of code for UPDATES and INSERTS and DELETES.
Create separate triggers and stored procedures for each group of database names (one for DB1, other for DB1_DEV, other for DB1_RS). This approach seems to not scale...
Make the database name dynamic on the query: INSERT INTO @db..table. Unfortunately SQL Server doesn't support this... :-(


Is there a simpler and effective solution to this?

Thanks!
Not sure I fully understand all of the variables but I suspect you would be able to use synonyms that are different between the two environments which would allow the code to be the same. It is a layer of abstraction that requires documentation and/or an extra layer of the onion to peel, but can definitely simplify the code you write. Here is the CREATE SYNONYM topic... I have used them extensively so please let me know if you have further questions about them.