SQL Server Linked Server to Oracle

Posted on

As a database administrator by trade, I have had a very perplexing task to have my SQL Server 2005 Enterprise servers communicate with my Oracle 10g box and gather data in real-time in fast, efficient ways. This article will go over the steps to setup the the linked server and some of the gotchas so others have a successful scenario to glean from.

  1. Install the Oracle Client from Oracle (10.2.x client version) on the SQL Server box
  2. Setup the TNS to the Oracle box through the Oracle Net Manager application
    1. Click the ADD (+) icon under Local | Service Naming
    2. Type in the Service Name, which is used to find in the Service Naming list and the reference to the Service Name in SQL Developer, for example
    3. Choose the Protocol, which is usually TCP/IP
    4. Type in the Host Name, which is the Name or IP Address of the Oracle server.
    5. Type in the Service Name, which is the name of the service or the Global Database Name. It can get confusing between this one and the Service Name above (#1). The difference is that this one (#5) is used to identify the Oracle Name. TNS is a go between. Use (#1) in your app, which then goes through the TNS, which then points to the Service Name (#5) at the database. Another way to look at it is, from your Webserver or SQL Server, directly to Oracle, you go through the TNS using (#1) as the hard coded reference
    6. Specify the Connection Type, which is either Default or Dedicated Server
  3. First, turn on the Allow InProcess option
    1. In SQL Server enterprise manager, expand Server Objects node, and then expand the Providers node.
    2. Right-click on the OraOLEDB.Oracle provider and click Properties
    3. Check the Allow Inprocess option (at least this has to be done on my machines)
  4. Second, create the Oracle linked server
    1. Right-click on Linked Servers node and click New Linked Server
    2. Type in the name of the linked server as it would appear in the Linked Server list and as it would be referenced by SQL scripts
    3. Choose “Other data source” and select the Oracle Provider for Ole DB. If this is not an option, then follow instructions to install the Oracle Client.
    4. Type in the Product Name, which is the Service Name in #2 of #2 above.
    5. Type in the Data Source, which is the Service Name in #2 of #2 above.
    6. In the Security tab, type in the Remote Login and Password under the last option “Be made using this security context:”
    7. Click OK to commit the changes
  5. Write the sql query using the following example
    1. select * from openquery(oraclels, ‘select * from <dbname>.<tablename> where <clause>’)

This doesn’t give the fastest results that we have been able to achieve. There is NOT an easy way to build the query on the fly with variable parameters and have it work quickly. You could create an EXECUTE @SQL statement that has been built around the same query above, but not one that is then in the same batch. The query has to be static and then supply any variable clauses. For example…

select field1, field2
from (select * from openquery (oraclels, ‘select * from db.table where <static clause>’)
where <variable clause>

Hope this helps.

Leave a Reply