Create Oracle Linked Server to Query data from Oracle to SQL Server

This SQL Server tutorial shows how to create SQL Server linked server to Oracle database, for SQL Server administrators and t-sql developers to execute sql statements over the target Oracle database tables. By creating a linked server, SQL Server database administrators or T-SQL developers can connect Oracle database from SQL Server Management Studio (SSMS) and query data stored in Oracle database tables.

I install Oracle 10g Express edition on my laptop where a default instance of Microsoft SQL Server 2008 R2 instance is also running. I sometimes require to read data from Oracle database application and display on SQL Server application. So I tried to create linked server from SQL Server to Oracle database. Actually it is somehow tricky if you are new to Oracle database configuration. So I think it will be useful for some sql developers like me trying to create linked server for Oracle in SQL Server 2008 database.

T-SQL developers can also use SQL Server linked server created from SQL Server to Oracle database to query data from SQL Server to Oracle. Developers can read data from Oracle to SQL Server instances using this Oracle linked server.

In order to create linked server for Oracle 10g Express database in Microsoft SQL Server 2008 R2 database server, open SQL Server Management Studio first.
Then connect to target SQL Server database instance.
Drill down to Server Objects in Object Explorer windows.
Right-click on Linked Servers folder and choose New Linked Server... from context menu.

add new linked server from SQL Server to Oracle

When New Linked Server wizard screen is displayed, first choose "Other data source" for the "Server type" option then enter the following field values in order to define a linked server from SQL Server to Oracle database.

Linked server : Give a descriptive short name to Oracle linked server object. This name will take place in sql queries with database and table names from Oracle.
Provider : Choose Oracle Provider for OLE DB from the dropdown list where valid data providers are listed.
Product name : Write "Oracle" in product name textbox.
Data source : Open tnsnames.ora file which is valid for the related Oracle database instance. Type the SERVICE_NAME parameter value from the tnsnames.ora Oracle configuration file into the Data source textbox. Please check "C:\oraclexe\app\oracle\product\10.2.0\server\NETWORK\ADMIN" for tnsnames.ora file.
Default data source name for Oracle 10g Express is XE
Provider string : Leave the provider string setting blank for Oracle Provider for OLE DB.

Oracle 10g Express as SQL Server Linked Server

For the security of the connection to Oracle database for SQL Server linked server, you can choose "Be made using this security context" option. And provide the Remote login and With password with a valid username and password.

For this SQL Server linked server example, I used the Oracle SYSTEM username and corresponding password for Oracle linked server creation.

Oracle Linked Server Security context for SQL Server

Here is the first sql select statement, sql developers can test using linked server to Oracle.


SELECT

 [FIRST_NAME], [LAST_NAME]

FROM [ORACLE10GEXP]..[HR].[EMPLOYEES]

Code

Although the above sql select statement is very simple, T-SQL developers or database administrators can get the following SQL engine error.

Msg 7399, Level 16, State 1, Line 1
The OLE DB provider "OraOLEDB.Oracle" for linked server "ORACLE10GEXP" reported an error. The provider did not give any information about the error.
Msg 7303, Level 16, State 1, Line 1
Cannot initialize the data source object of OLE DB provider "OraOLEDB.Oracle" for linked server "ORACLE10GEXP".

The solution to troubleshoot error is to set OraOleDB.Oracle provider Allow inprocess option.
In order to configure Allow inprocess option goto OraOleDB Oracle provider properties.

OraOleDB.Oracle Provider properties

Be sure that the Allow inprocess option is marked.

Oracle Provider for OLEDB Allow inprocess option

Here is detailed information about Allow inprocess from Books OnLine (BOL).

Allow inprocess
SQL Server allows the provider to be instantiated as an in-process server. When this option is not set, the default behavior is to instantiate the provider outside the SQL Server process. Instantiating the provider outside the SQL Server process protects the SQL Server process from errors in the provider. When the provider is instantiated outside the SQL Server process, updates or inserts referencing long columns (text, ntext, or image) are not allowed.

After the last step is also done, sql programmers can execute sql statements on Oracle databases using SQL Server Linked Server.

List Oracle data from SQL Server Linked Server

These are the first steps to define a linked server to Oracle database from SQL Server instance where Oracle Provider for OLE DB is installed. I hope t-sql developers will find useful this SQL Server tutorial which can be used to connect Oracle using SSMS (SQL Server Management Studio).