Linked Server SQL 2008 64 bit Oracle

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Other SQL Server 2008 Topics

Linked Server SQL 2008 64 bit Oracle

Author

Topic

rjb
Starting Member

3 Posts

Posted - 2011-04-12 : 06:33:33

Hello there,

I'm trying to access my customers Oracle database with a SQL Linked Server. The error I get is:

Cannot create an instance of OLE DB provider "OraOLEDB.Oracle" for linked server...

I did the following steps:

1. Installed Oracle client 10g with a custom installation and selected everything.

2. Created a tnsnames.ora

dev =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = dijkora01)(PORT = 1521))
(CONNECT_DATA =
(SERVICE_NAME = dev)
)
)

3. Tested this with tnsping dev, which said that the connection is OK.

4. Activated 'Allow inprocess' for my OraOLEDB.Oracle provider for the linked servers in my SQL Server Management Studio.

5. Created a new linked server with:

Linked Server: IAP
Provider: Oracle Provider for OLE DB
Product name: Oracle
Data source: dev

In the security tab I activated 'Be made using this security context'. When I now press 'OK' I get the above error message.
Can someone help me with this?

RickD
Slow But Sure Yak Herding Master

3608 Posts

Posted - 2011-04-12 : 06:54:00

This may help you:

http://social.msdn.microsoft.com/forums/en-US/sqldataaccess/thread/da8338c8-308e-4046-bb47-1e9cedc451ee/

rjb
Starting Member

3 Posts

Posted - 2011-04-12 : 07:57:58

I've done that until this change:

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSDTC\MTxOCI
OracleXaLib=oraclient10.dll
OracleSqlLib=orasql10.dll
OracleOciLib=oci.dll

When I change this and try to add a new linked server SQL server keeps saying 'Not responding'. It looks like it's searching for the dll's, but can't find them.
I've also got a newer version (11) of Oracle Client, which I can't seem to remove. I can't find this in my software, but I try to make an ODBC connection, it gives Me Oracle Client 11 as an option and not the 10 version I installed.

Any idea if that is the problem and how I can solve it?
Thanks in advance!

rjb
Starting Member

3 Posts

Posted - 2011-04-12 : 10:54:42

I've removed it now, but I still got the same error is I got in my first post unfortunately.

I also can't make an ODBC connection to my Oracle database.

RickD
Slow But Sure Yak Herding Master

3608 Posts

Posted - 2011-04-12 : 10:58:58

Have you tried changing the .dlls to the version 11 instead?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources