There are eight locations, each operating the same client/server application with identical SQL Server database schemas, including tables and stored procedures.
Currently, the same report is run at each location, utilizing the same fields. Locally, each site accesses the report through a 32-bit ODBC connection.
The objective is to generate this report at the company level to create a consolidated version. A site-to-site VPN is in place to facilitate this.
Is it possible to set up a blank MSSQL database on my local machine and then link all eight MSSQL databases to this central database to create the consolidated report?
What are the typical methods for achieving this type of task?
Hi satech, I'm not sure if this would be considered a "best practice" but perhaps look into Linked Servers to accomplish this and base the new consolidated report on that configuration. Here is a fairly comprehensive article on the DB server part.
Thanks. I ended up writing new SQL and building an intermediary db. The application's SQL was badly written, too many SELECTs that should be consolidated.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.