Look into setting up a linked servers, pointing to your Oracle and DB2 servers, on your MS SQL Server instance. You then query the Oracle and DB2 data using your SQL Server.
Thanks for the info. I wasn't aware of creating "linked servers." I haven't tried that yet. But learned the hard way that QofQ is limited in the # of records it can handle. Even my CF7 manual says that about 1,000 records is it's max. I'm dealing with a million records per 3-day period.
So I simply extracted and converted the Oracle data, and imported to a new SQL Server table. Then extracted/converted the IBM/DB2 data, and imported into a second, new SQL Server table. All "joined" fields were made the same formats and lengths. Then I was able to run a single query with a JOIN, that worked just fine, and ran quite fast given the millions of records it was analyzing, as well as sorting on 4 fields.
But I will research "linked servers", as my company has data in multiple servers (Oracle, SQL Server, DB2) all over the place, and none of the data contains "all the pieces", and similar data (like flight number, customer name) are stored in different formats. For now, althought I hate duplicating data, it's nice to have it all in one place, in the same format.
I wish users and DB designers would think more about reporting, BEFORE they came up with some of these big initiatives. Us programmers and DBAs are left to deal with scattered, differently formatted data, to create the web reports they want AFTER new policies, procedures and initiatives are started. Oh well. Had to put my 2 cents in.
Many thanks. Look forward to learning about "linked servers." Am curious, do you know if there any advantage to "web services" in cases where data is needed from different internal (corporate) or even external (vendor) servers? I haven't used web services, just seen a demo of it several years ago. Looked promising. Thanks.
For transfering data between database servers within your network a linked server is probably the way to go. For transferring data to or from servers on another organization's network web services are an option, but with some limitations. SOAP web services add an XML wrapper around the data they send. For a small amount of data this is not a problem, but for a data set of millions of records this becomes a performance issue. If your data is flat consider using a tab delimited text file instead of XML.