none
Microsoft OLE DB Provider for Oracle 64-Bit, SQL Server 2008: Does it exist?

    Question

  •  

    Does anyone know if the "Microsoft OLE DB Provider for Oracle" in 64-Bit Windows exists in SQL Server 2008 64-bit?  It only existed in 32-bit SQL 2005,  which was a big problem.    Needed for Linked Server creation.  We do not want to use OPENQUERY calls with the native Oracle 10G driver,  too many coding changes.

     

    Thank You

    Friday, April 18, 2008 6:23 PM

Answers

All replies

  • Please refer this post:

    http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1181796&SiteID=1

    Matt mentioned that:

    .... Microsoft does not provide a 64-bit OLEDB Provider for Oracle.

     

    Monday, April 21, 2008 9:26 PM
    Moderator
  • Hello,
    Unforntunally you really must:
    1- install the Oracle OLEDB,
    2- create the linked server
    3- run the openquery at the server (it worked!!)
    4- run the openquery at a client Management Studio (it failed!!)
    In our case it failed and returned the generic error:
    Msg 7302, Level 16, State 1, Line 1 Cannot create an instance of OLE DB provider "OraOLEDB.Oracle" for linked server "ORACLE".Msg

    5- so we had to do this:
    USE [master]
    GO
    EXEC master.dbo.sp_MSset_oledb_prop N'OraOLEDB.Oracle', N'AllowInProcess', 1
    GO
    6- run the openquery at a client Management Studio (IT WORKS!!)

    Regards!


    (versions: Windows server 2008 r2 64bit sql server 2008 sp1 64 bits oracle 10g)
    Tuesday, March 09, 2010 5:19 PM
  • Hello,

    Has anyone managed to go a step further and run a distributed transaction across a Linked server from 64-bit SQL2008 to 64-bit Oracle 10g?
    If so I would be very interested in knowing the steps required to achieve this.

    We are trying to upgrade from a SQL2005 + Oracle 8i environment to a SQL 2008 Oracle 10g platform and distributed transactions are a requirement.

    Regards

     

     

    Thursday, May 06, 2010 1:22 PM
  • I had run into an issue running SQL 2008 ent ed  SP2 64-bit HA on a windows 2008 ent ed 64-bit ms cluster, trying to connect a linked server to an Oracle 10.2.0.3 64-bit ent edition database on a hp-ux host. 

    I was just able to get the linked server to work with the following:

    1. I downloaded and installed the Oracle 10.2.0.4 64-bit client for Windows 2008 64-bit http://www.oracle.com/technetwork/database/10204-winx64-vista-win2k8-082253.html (need OTN license).  Installed the runtime client as well as the windows interfaces (2 installs, same oracle home).  I used the same administrative user for both my Oracle client and SQL Server installations, this user also runs my SQL database service.

    2. Added the tnsnames.ora entry on the server, sucessfully tested a tnsping <tns entry name> and was able to create and sucessfully test and ODBC connection, and connected via sqlplus remotely to the Oracle database (sqlplus username/pw@<tns entry name>

    3. Granted my install account full permissions on my oracle installation folder (F:\Oracle in my case) and c:\windows\temp.  This was due to an error with OraOLEDBpus10.dll when I initially tried to setup my linked server.

    4. Ran the suggested SQL above when I still was able to test the connection successfully but still got a Microsoft SQL Sevrer Error: 7399, cannot obtain the schema rowset "DBSCHEMA_TABLES" for OLE DB Provider "OraOLEBB.Oracle" for Linked Server.

    USE [master]
    GO
    EXEC master.dbo.sp_MSset_oledb_prop N'OraOLEDB.Oracle', N'AllowInProcess', 1
    GO

    5. After the above steps, my Linked Server now appears to work.

    Hope that helps.

    Thursday, December 02, 2010 3:10 PM
  • This really worked for me.

    What exactly I did.

    1. In Environment Variable System Variable set the path of the Oracle client.

    2. Check TNS Ora

    3. Execute below command

    USE [master]
    GO
    EXEC master.dbo.sp_MSset_oledb_prop N'OraOLEDB.Oracle', N'AllowInProcess', 1
    GO

    4. Create linked server to Oracle Server

    5. All the problem is gone!

    Tuesday, July 02, 2013 1:15 PM