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
