OLE DB Provider Oracle

english:

Hello,

I have got a problem with the a linked server in SQL server 2005 (64 bit) to Oracle 8.0.5. (32 bit).

Further details:

The old existing SQL server 2000 (32 bit) has a linked server to Oracle 8.0.5. The works with the OLE DB provider from Microsoft. The new SQL server 2005 (64 bit) should also have a linked server to this Oralce database. I have installed the Oracle 8.0 clients on the new server. The connection to oracle works with the client tools from Oracle.

But the SQL server 2005 couldn't connect to the oracle database.

The error message from the SQL server sounds (translated form a german error message):

A instance of the OLE DB provider "OraOLEDB.Oracle" for the linked server "xxxxxx" can't create.

Is the Oracle client software / OLE DB provider the problem?

Exist an other OLE DB provider for Oracle, may be from Microsoft, which works?

Thanks for your help

german:

Hallo Zusammen,

ích habe ein Problem bei der Migration von SQL Server 2005 auf ein x64 Windows System.

Problem ist folgendes:
Auf dem alten Server Windows x32 lief SQL Server 2000 mit einem Verbindungsserver zum Oracle Server 8.05. Das funktionierte mit dem OLE DB treiber von Microsoft hervoragend. Nun haben wir aber eine neue Windows x64 Maschine und ich m?chte die Verbindung zum Oracle Server wieder nutzen. Hatte die Oracle Client Software installiert und bekomme eine Fehlermeldung:

Eine Instanz des OLE DB Anbieters "ORAOLEDB.ORACLE" für den Verbindungsserver "xxxxx" kann nicht erstellt werden.

Ist das ein Problem mit der Oracle Client Software bzw. dem zugrunde liegenden OLE DB Treiber?
Gibt es einen anderen OLE DB Treiber für Oracle, z.B. von Microsoft, der funktionieren k?nnte?

Bin schon l?nger damit besch?ftigt, das zum Laufen zu bekommen. Die Oracle Version ist nicht die neueste, ich wei?.
Was kann ich tun, damit das endlich l?uft.

Für Hilfen w?re ich sehr dankbar.

If you are running 64-bit SQL, you need to use a 64-bit OLEDB provider with linked servers.

So you need to get Oracle's 64-bit OLEDB Provider for Oracle, Microsoft does not provide a 64-bit OLEDB Provider for Oracle.

|||

I got it mostly working with the latest oracle client (10.2.0.3) which you can download from Oracle if you have support with them. If you don't have the .3 version, you'll encounter a bug with their client if you make connections in DTS / intergration services packages to the Oracle DB server. I'm still running into an issue with a remote link server (see my other post).

Here are the steps I did to make the client work. Hope this helps you.

1) To get it working you need to do a custom installation and select at minimum:

Oracle Windows Interfaces (all)

Oracle Database Utilities

SQL*Plus

Oracle Net

2) You then need to copy the files: tnsnames.ora AND sqlnet.ora from another DB server to where ever the Oracle client is installed. In my case it's located at: D:\oracle\product\10.2.0\client\NETWORK\ADMIN

3) You then need to modify the registry to change the dll's that MDAC uses to connect to Oracle:

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSDTC\MTxOCI

OracleXaLib=oraclient10.dll

OracleSqlLib=orasql10.dll

OracleOciLib=oci.dll

4) You then need to reboot to have the changes take effect.

5a) Browse to: /Server Objects/Linked Servers/Providers and right click on OraOLEDB.Oracle and select properties. Then check the following boxes: Dynamic parameter, Allow inprocess

5b) Then you need to create the link server in the Microsoft SQL Server Management Studio by doing the following:

Browse to: Server Objects/Linked Servers, right click and select "New Linked Server"

Enter the linked server name

Under provider select "Oracle Provider for OLE DB"

Enter a product name IE Oracle...

Enter the data source name (the same name in the tnsnames.ora file)

Select "Security" on the left side and select the last radio button "Be made using this security context" and enter the user name and password to connect to the remote db.

6) Then click OK :)

|||

Hello Arron,

thanks for the information.

I have installed the oracle version 10.2.0.1.0. That was the latest that I found on http://www.oracle.com/technology/software/products/database/oracle10g/index.html for Windows with 64-bit.

We haven't got a support contract with oracle, because it is an old version.

We wouldn't use DTS or integration services (would be nice if it's working also).
We need this only in stored procedures with SQL statements (SELECT, INSERT and UPDATE).

Stefan and MediaK