BI Blog,  Oracle,  SQL Server

SQL Server: add a linked server to Oracle Database

/

A linked server allows us to read, from the instance we are using SQL Server, tables stored on a different instance or even on a different DBMS.

To do this you must use OLEDB (Object Linking and Embedding Database), API developed by Microsoft that allow access uniformly to a wide variety of data sources.

In our case we want to be able to access an Oracle database tables directly from a SQL Server instance. To do this you must first have access to the Oracle database by installing the appropriate client on the machine hosting our SQL Server instance.

Depending on the version to connect to (32 or 64 bit) you must download its client. It’s possible to install both versions of the client (which I recommend). We start with the download of Oracle Instant Client from:

http://www.oracle.com/technetwork/database/features/instant-client/index-097480.html

We download and install the client 32 and 64 bits. The installer will create the two Oracle Home.

Now you need to configure the tnsnames.ora and sqlnet.ora files in the folder

\percorso_di_installazione\client_1\network\admin.

If you do not find in the folder the two files, you can copy them from the subfolder sample. The sqlnet.ora file typically just leave it as such (unless the particular configurations of the Oracle instance), while in the tnsnames.ora file we will have to enter the parameters for the database connection. Open the file copied from the sample folder and add at the end of the file:

alias =
 (DESCRIPTION =
 (ADDRESS = (PROTOCOL = TCP)(HOST = myserver.mycompany.com)(PORT = 1521))
 (CONNECT_DATA =
 (SERVER = DEDICATED)
 (SERVICE_NAME = orcl)
 )
)

Substituting the parameters HOST, PORT, and SERVICE_NAME with those that the Oracle DBA you will provide. The configuration must be made for both Oracle Home.

Later we download and install Oracle Provider for OleDB from:

http://www.oracle.com/technetwork/database/windows/index-089115.html

The installation procedure will install the provider for Oracle Database that will be visible between the providers of SQL Server:

providers

Restart the SQL Server instance, return to the list of providers (from SSMS Server Objects -> Linked Servers -> Providers), right click on the Oracle provider and select the properties. To be sure of the operation of the provider, and avoid unpleasant crash dell’instanza SQL Server (trust me, it is!) you must select the option “Allow Inprocess”:

msdaora

This option will allow the provider to work in the memory space of SQL Server.

At this point we have to create our linked server. We can utillizzare the wizard of Management Studio, or acting directly through query:

EXEC master.dbo.sp_addlinkedserver @server = N'OracleDB', @srvproduct=N'Oracle', @provider=N'OraOLEDB.Oracle', @datasrc=N'NameTNS', @provstr=N'OraOLEDB.Oracle'
EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'OracleDB',@useself=N'False',@locallogin=NULL,@rmtuser=N'USERNAME',@rmtpassword='PASSWORD'

You’ll just replace NameTNS with TNS Oracle database and a USERNAME and PASSWORD with the user credentials Oracle DB.

At this point we are ready to run our first remote query:

SELECT * FROM OracleDB.CatalogName.SchemaName.TableName

If you want to send to the SQL to Oracle database and its engine, you can write it in PL / SQL with the OPENQUERY command:

SELECT * 
FROM OPENQUERY(OracleDB, 'select * from CatalogName.SchemaName.TableName')

This second solution is more effective when you want the DB Oracle returns only a subset of the necessary tables. To be clear the query:

SELECT * FROM OracleDB.HumanResources.Epmloyees WHERE Name = 'michele'

return the entire table and on it will be subsequently applied the filter. While with the query:

SELECT * FROM OPENQUERY(OracleDB, 'select * from HumanResources.Employees where Name = 'michele')

will be the Oracle DB to filter the results and SQL Server will send only the rows that meet the where clause.

For completeness, I point out that a driver OLEDB for Oracle Database has been released long ago even by Microsoft (Microsoft OLEDB Provider for Oracle) but its compatibility is certified up to version 9 of Oracle DB. Any configuration procedure remains unchanged: just do not download and use the Oracle provider as providers MSDAORA instead of OraOLEDB.Oracle.

Leave a Reply