2

I have a C# application which needs to pull data from two different databases: Database 1 is a MYOB database (which uses ODBC to connect using MYOB's driver) Database 2 is an Access database, again I'm using ODBC to connect to it.

Eg. this is the query I would like to run:

$query = "SELECT * FROM [" + myobConnectString + "].Accounts, ["+accessConnectString+"].tblTest WHERE tblTest.ID = Accounts.AccountID";

My question is how can I run this (or if it's even possible)?

Thanks!

James
  • 23
  • 3

3 Answers3

0

In the way that you have written the query, the answer is no. The simple reason is because the query itself is run on a single engine. Which engine would be running the query the way you've written it?

If either database server had the ability to provide direct access to the other (SQL Server has this functionality but I forgot the name at the moment) then a single engine could do this because it has access to the other.

Most likely, your only solution is to load the data from both tables into the application locally, then join the data in memory. Depending on the number of records, this might be an acceptable solution.

Erik Philips
  • 53,428
  • 11
  • 128
  • 150
0

One thing to remember is that the query executes on the database by the database, not your application. Therefore the database that will be executing the query needs to have access to the other database.

In Oracle, this functionality is called a Database Link. Here is an article that provides details on how to do this from Oracle to SQL Server: http://www.dba-oracle.com/t_heterogeneous_database_connections_sql_server.htm

In SQL Server has the same functionality is called Linked Servers. Here is an article that explains the concept and provides details on how to set this up: http://msdn.microsoft.com/en-us/library/ms188279.aspx

Otherwise you can run two separate queries using the drivers for each specific database and perform the join in the application's memory. You can also selection from one DB, insert into a temp table in the other DB and then run the join on that DB. This, of course, can be a costly operation in terms of performance.

Nickoli Roussakov
  • 3,434
  • 16
  • 22
0

In addition to the other answers you can use an ODBC Join Engine like the one at ODBC-ODBC Join Engine. However, this will pull the data locally and perform the join for you so it has similar disadvantages as Nickoli outlines at the end of his answer.

bohica
  • 5,932
  • 3
  • 23
  • 28
  • Thanks, that's the sort of thing I was after, however, I think I'll just load them locally and do the join. – James May 14 '12 at 11:03