Query with Two Different DSN

Question

I have a query:

The "X" tables are from one data source and the "Y" table is from another...but there is join between the data sources. I can't seem to figure out how to enter the two different connection strings for this to run.

ConnectionString="Dsn=Xdb;uid=xxx;pwd=xxxxxxx" ProviderName="System.Data.Odbc" ConnectionString="Dsn=Ydb;uid=xxx;pwd=xxxxxxx" ProviderName="System.Data.Odbc"

Is this possible...am I just missing something?

Answer 1

You can't JOIN tables from two different databases. Depending upon what database engine you're using, you might be able to set up a "virtual" or "linked" table in one database that points to a table in the other database, and do a JOIN that way. Otherwise, your only choice is to make a copy of the table you need to JOIN to in the other database, and JOIN to the copy.

Answer 2

The actual database query is run on the server side, not client side, so there's no "quick and easy" way to join the two tables.

Possible options are:

1) Set up a "Linked Server" (MSSQL term, not sure what the corresponding term would be in MySql or Oracle, or even if they're supported) on one of the SQL servers that points to the other server.

2) Do the individual selects separately into business objects, and then do the join in your business logic instead of in the database, either manually spinning through the records in for loops or utilizing a technology like LINQ in .NET.