dimanche 24 juillet 2011

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

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




Aucun commentaire:

Enregistrer un commentaire