How to query an Oracle table from SQL Server 2000?

Question

A colleague would like to query a table in an Oracle database using SQL Server 2000, Enterprise Manager.

He knows how to do the whole DTS thing, but doesn't want to go down that route for add hoc queries.

Is there another way?

Answer 1

You should be able to add it as a Linked Server (under Security in Enterprise Manager). You can then query it in the following way:

SELECT * FROM OracleServer.Database.dbo.Table

- Never used Oracle actually, so not sure if the "dbo" bit will be correct, or if this is only a SQL Server thing.

Answer 2

Here's a step-by-step guide: Setting up an Oracle Linked Server

Note that the default Microsoft driver for Oracle RDBMS doesn't work, hence you need to install the Oracle client (or instant client)

Another Microsoft link: MSDN