OraOLEDB.Oracle Linked Server

Do you want to use Oracle’s driver to directly connect to an Oracle database instead of using ODBC (DSN)? Is it any faster? You be the judge!

Step 1

Make sure you have Oracle 12g drivers installed on the server. This could be any version of Oracle

Step 2

Make a note as to where your Oracle installation path is. You’ll need a few information from the TNSADMIN.ora file. For example, my path is C:\Oracle\Ora12\Network\Admin\tnsadmin.ora

Inside tnsadmin.ora, you’ll need the HOST, PORT, and SERVICE_NAME

Step 3

In SSMS, navigate to the OraOLEDB.Oracle providers and open up the properties

Enable Allow inprocess

Step 4

Create a Linked Server

  1. Linked Server: Call this Linked Server whatever you want
  2. Provider: Select Oracle Provider for OLEDB
  3. Product name: Call this whatever you want
  4. Data source: This information comes from Step 2 in this format HOST:PORT/SERVICE_NAME i.e.(My_Host.afterowl.com:1571/My_Service_Name.afterowl.com)
  5.  Click on the Security tab on the left and use Be made using the security context. Then click OK to create the Linked Server
Step 5

This is how you would use your new shiny Oracle Linked Server

SELECT * FROM OPENQUERY(“ORACLE”, ‘SELECT * FROM MyTable’) AS MyTable

Advertisements

OPENQUERY Vs LinkedServer

What’s the differences between OPENQUERY vs. Linked Server?

  1. OPENQUERY uses the source server’s processing power to query the data
  2. OPENQUERY returns the result set specific to the WHERE clause making it faster
  3. LinkedServer uses the local server processing power to query all of the source’s data and then reprocesses the data to return the result set
  4. LinkedServer is not a direct call to the source server

How to use OPENQUERY and LinkedServer

ORACLE represents the Providers name under the Linked Servers, Server Objects in SQL MSSMS. (MSSMS -> Object Explorer -> Database -> Server Objects -> Linked Servers – > Providers)

OPENQUERY

SELECT * FROM OPENQUERY(ORACLE,’SELECT * FROM PAYMASTR WHERE CHECK_ID = 1′)

LinkedServer

SELECT * FROM ORACLE.DATABASENAME.dbo.PAYMASTR WHERE CHECK_ID = 1