SQL Server with Oracle linked server

Hello,

last days I spent a lot of time to establish a link server to Oracle from SQL Server.
I thought that using instant client was enough but I was definitely wrong.
... what to install then for making it working?

I had to search a long time before I saw somewhere on a forum (sorry I lost the link) that we should install this one:
32-bit Oracle Data Access Components (ODAC)
with Oracle Developer Tools for Visual Studio

you can find it here: http://www.oracle.com/technetwork/topics/dotnet/utilsoft-086879.html

please read the install doc here: http://www.oracle.com/technetwork/topics/dotnet/downloads/install112021-200037.html

now it is easy:

add a tsnnames.ora in the folder ORACLE_HOME\Network\Admin\

and add your server like:

MY_SERVER =

 (DESCRIPTION = 

   (ADDRESS_LIST =

     (ADDRESS = (PROTOCOL = TCP)(HOST = 200.200.1.1)(PORT = 1521))

   )

 (CONNECT_DATA =

   (SERVICE_NAME = myserviceSID)

 )

)

now you should be able to connect with:

sqlplus MYUSER/MYPASSWORD@MY_SERVER

then create an ODBC connection to Oracle, choosing the driver "ORACLE in ../instant client/..."

the name of the ODBC connection will be for instance MY_SERVER_ODBC

then go to SQLServer and add the linked server:

exec sp_addlinkedserver @server='MY_SERVER', 

    @srvproduct='Oracle',

    @provider='OraOLEDB.Oracle', 

    @datasrc='MY_SERVER_ODBC'

EXEC sp_addlinkedsrvlogin 

     @rmtsrvname        = 'MY_SERVER'

    ,@useself           = 'False'

    ,@locallogin        = NULL

    ,@rmtuser           = 'ORACLE_USER'

    ,@rmtpassword       = 'ORACLE_PWD'

congratulations, now you should be able to make a select by typing:

SELECT TOP 10 * FROM MY_SERVER..MY_SCHEMA.MY_TABLE