Gavin Draper's Blog

Father, Developer, Tech Geek, Extreme Sports Fanatic

@gavdraper | +gavdraper | github | StackOverflow Careers
 

Creating An Oracle Linked Server For SQL Server 2008 x64

This was a lot harder than I would have expected and took nearly 2 days of bashing my head against a wall to get it working, anyway here are the steps I ended up taking.

  1. Download the Oracle 11g R2 Client installers (Both the 32bit and 64bit versions).
  2. Run the 32bit installer
    1. For my installation path I specified c:\oracle
    2. For the software location I specified c:\oracle\product\11.2.0\client_32
    3. For the available product components I ticked the following
      • SQL Plus
      • Oracle Call Interface
      • Oracle Net
      • Oracle Connection Manager
      • Oracle Services For Microsoft Transaction Server
      • Oracle Administration Assistant For Windows
      • Oracle Provider for OLE DB
      • Oracle Data Provider for .Net
      • Oracle Providers for ASP.Net
    4. When Net configuration runs click "Default configuration" and next through it
    <li class="PostListItemOl">Repeat the above steps for the 64bit installer with the following changes</li>
    
    • Instead of _32 in your software location path put 64 e.g. c:\oracle\product\11.2.0\client_64
    • An error will most likely come up mid way through install saying "OracleMTSRecovaryService already exists", just click continue
    <li class="PostListItemOl">Modify the following registry keys</li>
    
    • HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\Microsoft\MSDTC\MTxOCI\OracleSqlLib to orasql11.dll
    • HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\Microsoft\MSDTC\MTxOCI\OracleXaLib to oraclient11.dll
    • HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSDTC\MTxOCI\OracleSqlLib to orasql11.dll
    • HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSDTC\MTxOCI\OracleXaLib to oraclient11.dll
    <li class="PostListItemOl">In management studio expend the Linked Servers node, then expend the Providers node. Right click OraOledb.oracle and go in to the properties. Tick "Allow In Process" then click OK.</li>
    <li class="PostListItemOl">Create or edit the following file C:\oracle\product\11.2.0\client_64\Network\Admin\tnsnames.ora with your database settings, it should look somthing like this...</li>
    
      
    # tnsnames.ora Network Configuration File: C:\oracle\product\10.2.0\db_1\NETWORK\ADMIN\tnsnames.ora
    # Generated by Oracle configuration tools. 
    
    GavsOracleDB =  
       (DESCRIPTION =
          (ADDRESS_LIST =
             (ADDRESS =
                (PROTOCOL = TCP)
                (HOST = gavindraper.co.uk)
                (PORT = 1521)
             )
          )
          (CONNECT_DATA = (SERVICE_NAME = mytest.gavdraper))
       )

  3. Reboot the server

  4. You should then be able to create a linked server to Oracle using the Add Linked Server Wizard in SQL Server


 
comments powered by Disqus