How to merge 4 sets of tables together for an ADO component.

[Guest_imported]

We have 4 automated assembly lines which produce a lot of data from the quality test results. Each line stores its data on its own Dell server computer running MS SQL Server 2000. The system generates about 84 megs of data on each server daily. There are 17 tables of test results that are joined in different combinations to store the results for the 8 different part types. The part types are the same on each server.

QUESTION: How do we merge all this data together for an ADO component?

We use Infragistics Ultragrid (ADO component) to display the data based on user selected ranges (date, part type, etc.) with a ADO recordset and SHAPE and JOIN commands. This works fine for data on one server, but does not work across all of the servers at once. Parts can be run down multiple lines. QA people have to go to each line to get the results for their reports.

The VB application does talk with each server for individual results (1 part), but not for multiple parts supplying the ADO component.

Any pointers in the right direction would be greatly appreciated. I've got to much data on my hands! Thank you in advance.

Michael Shepard
clearly@idirect.com
(905) 438-0015

 

[fluteplr]

I would suggest replicating the appropriate data to one server and use that server to run the queries against.

You could also try using linked servers to create a view that would cross between the servers but I imagine the prerformance would be awful.