We have Crystal Reports 8.5 and want to integrate with our SQL Server back-end. The question is what is the best method to use. We have 15 different databases all with 15-25 tables. Crossing a database boundry in SQL does not seem as straight forward as FoxPro DBFS. In fox, we created SQL VIEW(s) from a Remote Connection, then used these views in combination with local views CREATE VIEW to combine data from various database containers.

In SQL Server (2000), this does not seem possible using SQL Server views. We of course want the view to the data (from any combination of databases) to be contained within SQL Server to speed up processing. What should we be using in SQL Server, Stored Procedures, Data Transformation Services or some other technique.

Any guidance would be greatly appreciated.