OK, here's a challenge for you guys.
I am tasked with setting up reporting for two companies being run as a single company. For reasons I won't get into, they are implicitly split but (for our purposes) are run identically. They each have two software implementations for which I have access to the databases.
What is being done currently is we have a suite of queries that output to reports in various formats, and the logic is replicated between the two companies, making for 4 sets of queries.
This graphic represents the DB summary, and for each of them there are reports. We are using MS SQL Server.
We may add additional software in the future, turning 4 suites of queries into 6, 8, etc. I need to ensure that the query logic has no variation between the two companies in order to minimize the risk of discrepancy and for ease of development.
So here's the question:
Is it possible to share queries somehow between companies without using Dynamic SQL?
Best Answer
Some other ideas:
Use readily available tools to compare two databases after a change is deployed (you can automate this perhaps, depending on your existing deployment process), and synchronize them
Automate the deployment of changes such that changes to procedures always occur against both databases (again this depends on what your current deployment process entails)
Implement dynamic SQL carefully such that data is pulled from the right database depending on the user connecting.
Use a central database with views or synonyms under different schemas, each set pointing to the two different databases. Construct the calls to procedures dynamically, or leave off the schema prefix and set the default schema per user (you can double up on the security by granting database A's user access to B's schema and vice versa).