Joining oracle and SQL Server data

[Joel Zinn]

I am working on a project where we need to join data from a SQL Server db
with data from Oracle. I have searched and have not found any good method
to accomplish this.

Here is what I have to work with:

User table in SQL Server with Names, addresses, etc. The key is UserID

Asset table in Oracle with info about a specific asset. One of the columns
is UserID.

I need to query the Asset table and return a set of rows, but I also need
the User Name, etc from the User table. I assume I can get the return set
from the Asset table and then in the same dataset create a table for the
User info and then do a "Relationship join" in the dataset. I am not sure
the best way to write the quey to pull the rows from the User table. I
assume it would be to use "Where UserID in (....)", but not even sure the
best way to create the string and plug it into the query, but I am concerned
about the overhead.

If anyone has any ideas on how best to do this, I would certainly appreciate
it.

 

[William \(Bill\) Vaughn]

T'is easy. Create a link from SQL Server to the Oracle database (linked
server). Once done, you can address the Oracle table in the query by
including the name of the link.
SELECT col, col2 FROM myLinkedServerName.mydatabase.myowner.mytable ...

--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
INETA Speaker
www.betav.com/blog/billva
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________
Visit www.hitchhikerguides.net to get more information on my latest book:
Hitchhiker's Guide to Visual Studio and SQL Server (7th Edition)
and Hitchhiker's Guide to SQL Server 2005 Compact Edition (EBook)