Best practice to connect to multiple databases (Crystal Report)

[satech]

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?

Thanks!

SAP Crystal Reports 

[DellSC]

In addition to Jamie's response, you have a couple of options for creating the report.

- If you want to report on each location separately, I would use a separate sub-report for each location.  This will be the easiest way to segregate the data correctly.

- If you want to consolidate the data from all locations, I would use a Command (SQL Select statement) that Unions the same data from each linked database.  For more information about the correct way to work with commands see https://community.sap.com/t5/technology-blogs-by-members/best-practices-when-using-commands-with-cry....  The post is about 9 years old, but the information is still valid.

-Dell

[JWiseman]

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.