TN PT122 Creating a Microsoft SQL Server Linked Server to an Oracle Database

Description

This article from InSource shows the creation of a Microsoft SQL Server Linked Server to an Oracle Database

• Author: Bruce Telford
• Published: 05/31/2018
• Applies to: Microsoft SQL Server

Details

Objective

The objective is to query an Oracle database from SQL Server, for data integration purposes. For example, you may need to correlate work order information from an Oracle ERP System with data in the SQL Server system like a historian for data warehouse/mart development (like Intelligence) for reporting.

Steps 1-4, 6, & 7 below, adds Oracle OLEDB connectivity. This also allows you to build connection strings to the Oracle from a Windows based program, like InTouch or Application Server for direct queries to Oracle as well.

Prerequisites

The Oracle database connection information in the TNS file 'tnsnames.ora' specific to the database and server required for connection. Example:

<data source alias> =

(DESCRIPTION =

(ADDRESS = (PROTOCOL = TCP)(HOST = <hostname or IP>)(PORT = <port>))

(CONNECT_DATA =

(SERVER = DEDICATED)

(SERVICE_NAME = <database service name>)

)

)

Also, the database User ID and Password for connection to this database

SQL Linked Server Steps for 64bit:

1. Download the 64-bit Oracle Data Access Components (ODAC) from the following from URL ...
   1. http://www.oracle.com/technetwork/database/windows/downloads/index-090165.html
   2. File … ‘Oracle 12.2 ODAC122010Xcopy_x64.zip’ or more recent if available.

2. Extract the ZIP file and install as follows:
   1. You need the "64-bit Oracle Provider for OLE DB 12.2.0.1.0" from above for Linked Server configurations.
   2. From Command prompt ... C:\<unzip file path>\ODAC122010Xcopy_x64\>.\install.bat oledb c:\Oracle\odac64 odac64 true

3. Then you need to add two folders to your system path: C:\oracle\odac64 and C:\oracle\odac64\bin …
   1. System > System Properties > Advanced Tab > "Environmental Variables" button > edit "Path" variable and add this to the existing (if any).
      
4. Then reboot for the system path change to be visible by services like SQL Server.

5. Make sure the "OraOLEDB.Oracle" Provider is listed under Linked Server > Providers.
   1. Under properties for the provider you select (check) Allow inprocess.

or,

exec master.dbo.sp_MSset_oledb_prop 'ORAOLEDB.Oracle', N'AllowInProcess', 1

When you use the “Allow in-process” option for Linked Server providers, SQL loads the COM DLL in its own memory process.

2. Under properties for the provider you select (check) DynamicParameters. (does work without this)

or,

exec master.dbo.sp_MSset_oledb_prop 'ORAOLEDB.Oracle', N'DynamicParameters', 1

6. Update the folder C:\Oracle\odac64\network\admin by copying the 'tnsnames.ora' file in sub-folder samples and modifying to include the servers being connected to. The file includes an example. … Example.

# JBT Test Server

JBTMTST =

(DESCRIPTION =

(ADDRESS_LIST =

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

)

(CONNECT_DATA =

(SERVICE_NAME = JBTMTST)

)

)

7. You should now be ready build a linked server and connect to the database

8. We will build a script in SQL Server (“ORAMYTEST” is the Linked Server Name)

EXEC master.dbo.sp_dropserver @server=N'ORAMYTEST', @droplogins='droplogins' -- if replacing

GO

EXEC master.dbo.sp_addlinkedserver @server = N'ORAMYTEST', @srvproduct=N'Oracle', @provider=N'OraOLEDB.Oracle', @datasrc=N'JBTMTST'

EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'ORAMYTEST',@useself=N'False',@locallogin=NULL,@rmtuser=N'<user>',@rmtpassword='<password>'

GO

EXEC master.dbo.sp_serveroption @server=N'ORAMYTEST', @optname=N'rpc out', @optvalue=N'true' 

GO

9. Query ORACLE Linked Server “ORAMYTEST” from SQL Server as follows:
   1. Slower (queried on SQL Server end (not recommended))
      
      SELECT * FROM [ORAMYTEST]..[ERPINFO].[ERP_CO_ORDERS]
       
   2. Faster runs on Oracle server and data transferred to SQL Server (recommended))

SELECT * FROM OPENQUERY(ORAMYTEST, 'SELECT * FROM ERPINFO.ERP_CO_ORDERS')

or

EXEC ('SELECT * FROM ERPINFO.ERP_CO_ORDERS') AT ORAMYTEST

OLEDB Connection String info

“Provider=OraOLEDB.Oracle.1;Password=<Password>;Persist Security Info=True;User ID=<UserID>;Data Source=<Data Source Alias>;”

Note: Replace content “<all content between and including braces>”