Linking Sql Server 2005 to Oracle

This is copied article but I think it is usefull.

Oracle-SS2005 Linked ServersHere is a quick guide to setting up linked servers so you can query Oracle through Sql Server.

For my testing of this I used Sql Server Express 2005, and Oracle 9.2. This all uses simple commands that can be run via Query Analyzer or Query Tool.
First, if you've been doing this and having issues, you need to clear out any of your linked server's logins:
sp_droplinkedsrvlogin
@rmtsrvname = 'LINKED_ORA',
@locallogin = nullNext,
drop the server

EXEC sp_dropserver LINKED_ORAOk,

now let's add the linked server to Sql Server.
The linked server will be called LINKED_ORA. Once this is done you should see this listed in SS Management Studio under Server Objects->Linked Servers-- add server
EXEC sp_addlinkedserver
@server= 'LINKED_ORA',
@provider='MSDASQL',
@srvproduct='Oracle',
@datasrc= 'myOracle'
@server - this is the name to use.
It can be anything
@provider - can be either MSDASQL and MSDAORA.
Both seem to both work fine. There is talk of another provider from Oracle, but I have not gotten this one to work.
@srvproduct - must be "Oracle". I do not know why this matters
@datasrc - this is a DSN that you have set up using Oracle's .NET managerNow,
the Oracle system does not implicitly let Sql Server connect.

You need to add a login for this linked server to use.

-- add login - the DSN is protected by a password
EXEC sp_addlinkedsrvlogin
@rmtsrvname='LINKED_ORA',
@useself='false',
@rmtuser='oracleUserId',
@rmtpassword='oraclePassword'

Once this is set, then you can test using this command:
--verify tables OK
exec sp_tables_ex
@table_server = 'LINKED_ORA',
@table_schema='MySchema'
@table_schema is optional. If not provided, you will get a list of all tables in all schemas.

This will return information about the tables in the linked server.

I use it as a basic diagnostic to verify that the linked server is working. if it's not, then I use the scripts above to delete the login and linked server, then re-create using other inputs.

Now, the big pain with Oracle is that you cannot just query into the database.

I know, it is ridiculous, but I found that only OPENQUERY works.
Here is how it looks:

SELECT * FROM OPENQUERY( LINKED_ORA, 'SELECT * FROM Exp.Proj')

But from there you can now do handy things in Sql Server. One of my favorites is to re-create the Oracle schema in a Sql Server database, then use INSERT to pull in all of the Oracle data. Then you can just work with Sql Server!