Differences

This shows you the differences between two versions of the page.

sql-server:oracle-linked-server [July 06, 2012 6:52 am] (current)
sqlines created
Line 1: Line 1:
 +===== Configuring Connection from SQL Server to Oracle using Linked Server =====
 +SQL Server Linked Servers technology allows you to access non-SQL Server databases from a SQL Server database using OLE DB providers.
 +
 +You can connect to SQL Server and execute commands against OLE DB data sources on remote servers.
 +
 +===== OLE DB Provider for Oracle ====
 +
 +To create a linked server, you need an OLE DB provider and OLE DB data source.
 +
 +  * **Microsoft OLE DB Provider for Oracle** (MSDAORA)
 +
 +Microsoft provides OLE DB Provider for Oracle with SQL Server installation but you also need to install Oracle Client software and configure connection to Oracle. The name of this provider is //MSDAORA//.
 +
 +  * **Oracle OLE DB Provider** (OraOLEDB.Oracle)
 +
 +OLE DB provider provided by Oracle that also requires Oracle Client software. The name of this provider is //OraOLEDB.Oracle//.
 +
 +===== Creating Linked Server =====
 +
 +Use //sp_addlinkedserver// procedure to create the linked server, specifying //MSDAORA// as //provider_name//, and the SQL*Net alias name for the Oracle database instance as //data_ source//.
 +
 +<code sql>
 +   sp_addlinkedserver @server = 'ORASRV', @srvproduct = 'Oracle', @provider = 'MSDAORA', @datasrc = 'ORCL'
 +</code>  
 +
 +//@server// specifies the server name that will be used in the SQL statements.
 +
 +//@datasrc// is an alias from //tnsnames.ora// file (//%ORACLE_NOME%//\network\admin):
 +
 +<code>
 +    ORCL =
 +     (DESCRIPTION =
 +       (ADDRESS_LIST =
 +         (ADDRESS = (PROTOCOL = TCP)(HOST = dbsrv)(PORT = 1521))
 +       )
 +       (CONNECT_DATA =
 +         (SERVICE_NAME = orcl.domain.com) ) )
 +</code>
 +
 +Then use //sp_addlinkedsrvlogin// to create a login to Oracle:
 +
 +<code sql>
 +   sp_addlinkedsrvlogin @rmtsrvname = 'ORASRV', @useself = 'False', @locallogin = NULL,
 +       @rmtuser = 'SCOTT', @rmtpassword ='TIGER'
 +</code>
 +
 +After you have created the linked server and login, you can query an Oracle table from SQL Server:
 +
 +<code sql>
 +   SELECT * FROM ORASRV..SCOTT.DEPT
 +</code>
 +
 +**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.
 +
 +===== Creating Linked Server using OraOLEDB.Oracle Provider =====
 +
 +You can also create a linked server using Oracle OLE DB provider.
 +
 +Use //sp_addlinkedserver// procedure and specify //OraOLEDB.Oracle// as //provider_name//, and the SQL*Net alias name for the Oracle database instance as //data_ source//.
 +
 +<code sql>
 +   sp_addlinkedserver @server = 'ORASRV2', @srvproduct = 'Oracle', @provider = 'OraOLEDB.Oracle',
 +       @datasrc = 'ORCL'
 +</code>  
 +
 +//@server// specifies the server name that will be used in the SQL statements. //@datasrc// is an alias from //tnsnames.ora// file (//%ORACLE_NOME%//\network\admin).
 +
 +Then use //sp_addlinkedsrvlogin// to create a login to Oracle:
 +
 +<code sql>
 +   sp_addlinkedsrvlogin @rmtsrvname = 'ORASRV2', @useself = 'False', @locallogin = NULL,
 +       @rmtuser = 'SCOTT', @rmtpassword ='TIGER'
 +</code>
 +
 +Note that before you can use Oracle OLE DB provider, you have to set //Allow inprocess// option (SSMS->Instance->Server Objects->Linked Server->Providers->OraOLEDB.Oracle) and **restart** the SQL Server instance.
 +
 +
 +===== Resources =====
 +
 +  * [[/oracle|Oracle to SQL Server Migration Knowledge Base]]
 +
 +===== Author =====
 +
 +Dmitry Tolpeko, [[dmtolpeko@sqlines.com]] - July 2012 (Updated)
 +
 +Please contact me if you are interested in Oracle to SQL Server migration services, consulting, tools or training.
 +
 +~~NOTOC~~
 +~~DISCUSSION~~
 +
 +~~DISCUSSION~~