通过

Creating a Linked Server for Oracle in 64bit SQL Server

  • 项目

This post is a walkthrough of creating a Linked Server to Oracle from 64bit SQL Server.  There's lots of information on doing this on the web, but much of it is out-of-date.

First, install the correct Oracle drivers.  You want the latest version of the Oracle Data Access Components (ODAC), and you want the XCopy deployment.  They are available here:

64-bit Oracle Data Access Components (ODAC) Downloads

https://www.oracle.com/technetwork/database/windows/downloads/index-090165.html

One you download and unzip this into a folder run the following command from that folder:

C:\Users\dbrowne\Downloads\ODAC121010Xcopy_x64>.\install.bat oledb c:\oracle\odac64 odac64 true

Then you need to add two folders to your system path: c:\oracle\odac64 and c:\oracle\odac64\bin

Then you must reboot for the system path change to be visible by services like SQL Server.

After reboot you're ready to create and test the linked server.

First configure the Oracle OleDB provider to run inside the SQL Server process, and configure it to accept parameters.

 exec master.dbo.sp_MSset_oledb_prop 'ORAOLEDB.Oracle', N'AllowInProcess', 1 

 exec master.dbo.sp_MSset_oledb_prop 'ORAOLEDB.Oracle', N'DynamicParameters', 1

Then create the linked server definition.  Instead of a TNSNames alias, use an EZConnect identifier.  Here I'm specifying an IP address and a SID to connecto to an Oracle Express instance running on a VM:

 

 exec sp_addlinkedserver N'MyOracle', 'Oracle', 'ORAOLEDB.Oracle', N'//172.16.8.119/xe', N'FetchSize=2000', ''

 

 exec master.dbo.sp_serveroption @server=N'MyOracle', @optname=N'rpc out', @optvalue=N'true'

 exec sp_addlinkedsrvlogin @rmtsrvname='MyOracle', @useself=N'FALSE', @rmtuser=N'system', @rmtpassword='xxxxxx'     

Now you're ready to test.  We configured the linked server for 'rpc out' so we can send a simple passthrough query to test connectivity:

 exec ('select 1 a from dual') at MyOracle

That's it.

Comments

  • Anonymous
    October 30, 2013
    I have a Windows 2012 R2 with SQL Server 2012 R2 installed on it.  I followed the instruction above.  And test the connection and I get this error. TITLE: Microsoft SQL Server Management Studio

The test connection to the linked server failed.

ADDITIONAL INFORMATION: An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)

BUTTONS: OK

  • Anonymous
    October 30, 2013
    I checked the registry and none of the files listed in HKEY_LOCAL_MACHINESOFTWAREMicrosoftMSDTCMTxOCI are in the folder indicated.

  • Anonymous
    December 19, 2013
    BEWARE: tested on several Systems, just doesnt work somethings missing in this HowTo - it cannot create an instance Error 7302

  • Anonymous
    January 08, 2014
    Note that the SQL Server instance must be 64bit too.  A 32bit SQL Server would need the 32bit OleDB provider.

  • Anonymous
    January 10, 2014
    Very Nice Explanation Thanks

  • Anonymous
    January 30, 2014
    The comment has been removed

  • Anonymous
    February 13, 2014
    Thanks for the post! I was using a Windows 7 64 bit system and connecting to an Oracle 11g on CentOS on a VM in the same system. When using this guidelines for 64bit, it did not work. However, the same steps used with 32 bit ODAC and Oraclie Client 11g worked like a charm! Thanks a lot! Regards Dev

  • Anonymous
    March 10, 2014
    The comment has been removed

  • Anonymous
    April 13, 2014
    you must copy your tnsnames.ora file to c:oracleodac64network

  • Anonymous
    April 27, 2014
    After two days of struggle with creating linked server on Windows Server 2012 R2 and Sql server 2012 - found solution from Troy 7's comment (Thanks Troy) The Data Source I was providing was the SID as defined in the tnsnames - which worked fine on Windows Server 2008/Sql Server 2012 linked server In Windows server 2012 - it's expecting Data Source:  //IPAddressOrHostnameOfOracleServer:PORT/SERVICE_NAME In both cases I was creating the linked server to the same Oracle db on the same server. No idea why the detailed definition was needed ...

  • Anonymous
    April 27, 2014
    Thanks to Okan Tekeli too... I had to copy the tnsnams.ora into the c:oracleodac as well. I had it in the Oracle client networkadmin. This was fine for Windows Server 2008 Windows server 2012 R2 expects a copy of the tnsnames.ora in c:oracleodac

  • Anonymous
    June 09, 2014
    Thanks Dave, this worked great!

  • Anonymous
    July 28, 2014
    Thanks for a great writeup! For those who have not been able to make this work, you can try to doublecheck if you have launched CMD as admin before running the install.bat command. Without this you might still get entries in the registry and files in the right places (if you accept regedit prompts during install) - but the provider will not be registrered with SQL

  • Anonymous
    August 06, 2014
    Can someone please explain me the second parameter "Oracle" in the "sp_addlinkedserver " query? I am little bit confuse over it.

  • Anonymous
    August 19, 2014
    Great topic. Straightforward. To the pont. You don't have to install Oracle Client. Only Oracle Driver will do. Everything works for first time.  I wish only such instruction to be on internet. Thank You  very much.

  • Anonymous
    September 23, 2014
    SQL Server 2012 R2 on Windows Server 2012 R2 Datacenter Followed your instructions and installed from ODAC121012Xcopy_x64.zip.  When expanding all the way down to Tables in newly created linked server, error 'The OLE DB provider "ORAOLEDB.Oracle" has not been registered.(Microsoft SQL Server, Error:7403)' occurred. On examination,the new folders and installed files properly show up in Windows Explorer, but data provider ORAOLEDB.Oracle is not in the Provider folder under Linked Servers in SSMS. Checked and rebooted again and again, but still the same.  What could be wrong?  Your help would be greatly appreciated - Like you said, there were lots of postings on this, but most are out-of-date. Thanks in advance! David

  • Anonymous
    October 27, 2014
    Best set of instructions for this. thanks.

  • Anonymous
    November 09, 2014
    Thanks for the help, it works.

  • Anonymous
    November 17, 2014
    the Error with 7403 can solved. You must aktivate the checkbox for "in Process allowed". in the linked server can you config the driver. there you must open OraOleDB.Oracle.

  • Anonymous
    November 19, 2014
    OLE DB provider "ORAOLEDB.Oracle" for linked server "MyOracle" returned message "ORA-03134: Connections to this server version are no longer supported.". Trying to set up a linked server from 2014 sql express to a server running oracle 9.2.0.8.0

  • Anonymous
    December 15, 2014
    i doing follow instruction and complete all but when i tried to test connection my sql server auto stop service. Could you please help me to solve this problem?

  • Anonymous
    January 10, 2015
    The comment has been removed

  • Anonymous
    January 10, 2015
    @daniesh. Check the NTFS permissions of the SQL Server service account on the Oracle client folders.

  • Anonymous
    February 02, 2015
    thanks!  been trying to get this working all day.  your post did the trick.

  • Anonymous
    March 20, 2015

  1. This procedure does not register ORAOLEDB.Oracle provider with SQL Server.
  2. The solution below failed under the standard user command prompt for me. I had to do it as an admin.
  3. The fourth parameter (install_dependents) is true by default. So, what worked for me is this: Admin Command Prompt>instal all c:OracleODAC odac64 This was tested with MS SQL Server 2008 R2 SP2 and ODAC 12c R3 (12.1.0.2.1).

  • Anonymous
    May 15, 2015
    Finally a simple guide, all steps worked fine. I got ORA-12514 becasue I used a wrong oracle service name. these commands (from oracle server) could be useful to get info: lsnrctl status lsnrctl service ciao

  • Anonymous
    May 29, 2015
    The comment has been removed

  • Anonymous
    June 15, 2015
    Very detail to the point. First class

  • Anonymous
    June 20, 2015
    Thanks exec master.dbo.sp_MSset_oledb_prop 'ORAOLEDB.Oracle', N'AllowInProcess', 1 exec master.dbo.sp_MSset_oledb_prop 'ORAOLEDB.Oracle', N'DynamicParameters', 1 exec sp_addlinkedserver N'OrawsvtestLinkedSVR', 'Oracle', 'ORAOLEDB.Oracle', N'OraTnsEntryName', N'FetchSize=8000', '' exec master.dbo.sp_serveroption @server=N' OrawsvtestLinkedSVR ', @optname=N'rpc out', @optvalue=N'true' exec sp_addlinkedsrvlogin @rmtsrvname=' OrawsvtestLinkedSVR', @useself=N'FALSE', @rmtuser=N'Oraid', @rmtpassword='Pwd'    

  • Anonymous
    June 30, 2015
    Windows Server 2012 x64, SQL Server Ent x64. Completely removed any sign of oracle from the server, then downloaded the ODAC112040Xcopy_64bit.zip (as the remote oracle DB is v11.2.0.4.0) created a linked server with your instructions (but I used TNS instead of ezconnect) and everything works like a charm. Thanks a lot

  • Anonymous
    June 30, 2015
    Sorry my SQL version is 2012 as well (11.0 SP1)

  • Anonymous
    July 14, 2015
    The comment has been removed

  • Anonymous
    August 17, 2015
    when i want to linked server test connection, I receive below error: 32-bit OLE DB provider "ORAOLEDB.Oracle" cannot be loaded in-process on 64-bit SQL Server (7438)

  • Anonymous
    August 30, 2015
    It worked only after put a path(variables) with TNS_ADMIN = path of where tnsnames are

  • Anonymous
    September 04, 2015
    Great great topic. Things are so easy, if you know how... It works fine for me on MSSQL Server 2014 SP1.

  • Anonymous
    September 30, 2015
    I agree with Radek together with www.orafaq.com/.../EZCONNECT I was able to solve my problem

  • Anonymous
    October 12, 2015
    Hello, This worked for me on MSSQL 2014, and I've tried before on 2008, so the problem I was getting was the Oracle driver is missing, but in 2014 it's the driver is available. Botan from Somalia

  • Anonymous
    October 12, 2015
    The comment has been removed

  • Anonymous
    October 14, 2015
    Installing SqlSERVER 2008 R2 on Windows Server 2012. The ORAoleDB.oracle providers would not show up under providers in SSMS. This was solved by registering the oraoledb12.dll  by running the cmd.exe as an admin(otherwise error 0080070005).  Command: regsvr32.exe c:oracleodac64binOraOLEdb12.dll.

  • Anonymous
    November 24, 2015
    Thank you so much, it's work for me on sql server 2014 64bit

  • Anonymous
    March 04, 2016
    Works great, thanks.

  • Anonymous
    March 09, 2016
    The comment has been removed

    • Anonymous
      April 04, 2016
      Looks like an update to the blog engine broke it. I reformatted the snippets. Thanks.

  • Anonymous
    March 09, 2016
    This also worked for me. Thank you.One thing to note, the EZCONNECT string seems to only work with the Service Name of the database, not the SID.E.g. //server-name.domain:1521/service_name

    • Anonymous
      June 07, 2016
      Fyi, about the "cannot create an instance of ole db provider oraoledb.oracle for linked server msg 7302", this was happening in our new production server but not in our development server. Development server that worked did NOT have "in process" selected, so Oracle was safely running fine outside of SQL Server's process. However, strangely, only way to get it to work in new production server was to select "in process", but this is risky. Anyways, after about a hundred different security configuration changes, MSDAINITILIZE stuff, etc., was about to re-image with Windows Server 2012 R2 again when we decided to run Windows Updates (yeah I know...). Anyways, that worked! Can't really say why, but after installing latest windows updates running Oracle out of process (de-selecting "in process) in our new production server works now! Thought that might help someone out there...

  • Anonymous
    May 05, 2016
    If you get the following error because your are running a distributed transaction:ole db provider for linked server was unable to begin a distributed transactionrun the following command to install the OracleMTS.\install.bat oramts c:\oracle\odac64 odac64 falserun this after the other install command.