0

Aware that this topic has been raised many times here (I have gone through the posts), but none of the suggestion on the posts has resolved my issue.

When I attempt to create a linked server to the oracle database from SQL Server Management Studio, I get the error:

Cannot create an instance of OLE DB Provider 'OraOLEDB.Oracle' for linker server ''. (Microsoft SQL Server, Error: 7302)

My understanding of this error is that the Oracle provider is not installed correctly or not at all.

To check this I ran 'ODBC Data Source Administrator (64-bit) and attempted to add the provider (which was not available).

To resolve this I fully installed Oracle 10g Client as an admin and restarted the server. I added the new oracle bin folder to the environment %PATH% as well as %TNS_ADMIN% to point to the tnsnames.ora file.

I have run 'tnsping' using the value specified in the tnsname.ora file and it is able to connect.

I have exhausted my ideas to get the oracle provider recognised by the ODBC 64 bit connector.

Does anyone have any idea that could resolve this issue?

Thank you for any help.

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
ricky89
  • 1,326
  • 6
  • 24
  • 37
  • OLE DB and ODBC are different interfaces. To make an OLE DB connection, try follow instructions from this article step by step: http://blogs.msdn.com/b/dbrowne/archive/2013/10/02/creating-a-linked-server-for-oracle-in-64bit-sql-server.aspx – Dzmitry Paliakou Sep 24 '15 at 11:18
  • Hi Dmitry, I had already carried out the steps for XCopy but went through all the steps and am getting the exact same error as written above. – ricky89 Sep 24 '15 at 12:01
  • Ok I did it again but ran the XCopy batch as an admin and am getting a more promising error now 'OLE DB provider "OraOLDDB.Oracle" for linked server returned message "ORA-01017: invaliid username/password; logon denied"'. Will try and get tnsping on the server to test this aspect. – ricky89 Sep 24 '15 at 13:04
  • And i believe I put the wrong password in. So the issue was I didn't carry out the XCopy install as Admin....'sigh'. – ricky89 Sep 24 '15 at 13:12

1 Answers1

0

The solution was to follow the steps suggested by Dmitry. However, make sure you carry out the following step:

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

as an admin!

ricky89
  • 1,326
  • 6
  • 24
  • 37