Use sp_add link ed server procedure to create the link ed server , specifying MSDAORA as provider_name , and the SQL *Net alias name for the Oracle database instance as data_ source . sp_add link ed server @ server = 'ORASRV' , @ srvproduc
Use sp_addlinkedserver procedure to create thelinkedserver, specifyingMSDAORA as provider_name, and theSQL*Net alias name for theOracle database instance as data_ source.
sp_addlinkedserver @server = 'ORASRV', @srvproduct = 'Oracle', @provider = 'MSDAORA', @datasrc = 'ORCL'
@server specifies the server name that will be used in the SQLstatements.
@datasrc is an alias from tnsnames.ora file (%ORACLE_NOME%\network\admin):
ORCL = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = dbsrv)(PORT = 1521)) ) (CONNECT_DATA = (SERVICE_NAME = orcl.domain.com) ) )
Then use sp_addlinkedsrvlogin to create a login toOracle:
sp_addlinkedsrvlogin @rmtsrvname = 'ORASRV', @useself = 'False', @locallogin = NULL, @rmtuser = 'SCOTT', @rmtpassword ='TIGER'
After you have created the linked server and login, you can query an Oracle table fromSQLServer:
SELECT * FROM ORASRV..SCOTT.DEPT
Note. If the table and column names were created in Oracle without quoted identifiers, use them in uppercase. Otherwise you will receive “The table either does not exist or the current user does not have permissions on that table” error.
