0

I'm using SQL Server 2017 and am trying to set up a linked server to an Oracle database. I installed ODAC and updated my PATH to include the Oracle root from ODAC installation.

Everything looks good and I am able to choose the Oracle Provider for OLE DB when setting up the server, but when I save my configuration, I get the following error:

===================================

The linked server has been created but failed a connection test. Do you want to keep the linked server?

===================================

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

Program Location:

at Microsoft.SqlServer.Management.Common.ServerConnection.ExecuteNonQuery(String sqlCommand, ExecutionTypes executionType, Boolean retry) at Microsoft.SqlServer.Management.Smo.ExecutionManager.ExecuteNonQuery(String cmd, Boolean retry) at Microsoft.SqlServer.Management.Smo.LinkedServer.TestConnection() at Microsoft.SqlServer.Management.SqlManagerUI.LinkedServerProperties.DoPreProcessExecution(RunType runType, ExecutionMode& executionResult)

===================================

Cannot create an instance of OLE DB provider "OraOLEDB.Oracle" for linked server "UDW". (.Net SqlClient Data Provider)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft%20SQL%20Server&ProdVer=13.00.5081&EvtSrc=MSSQLServer&EvtID=7302&LinkId=20476

Server Name: localhost Error Number: 7302 Severity: 16 State: 1 Procedure: sp_testlinkedserver Line Number: 1

Program Location:

at Microsoft.SqlServer.Management.Common.ConnectionManager.ExecuteTSql(ExecuteTSqlAction action, Object execObject, DataSet fillDataSet, Boolean catchException) at Microsoft.SqlServer.Management.Common.ServerConnection.ExecuteNonQuery(String sqlCommand, ExecutionTypes executionType, Boolean retry)

This error message is not exactly helpful, and if you follow the Microsoft link, it redirects you to the Microsoft homepage (thanks Microsoft!)

Is there a way to test if this is a problem of not finding TNSNAMES vs some sort of firewall issue on the Oracle server?

I am able to set up a LinkedServer on another database in the same network so I can confirm that the TNSNAMES is exactly the same and the credentials I am using are valid.

Improve this question

2 Answers 2

Reset to default
3

The key to this issue is this line:

Cannot create an instance of OLE DB provider "OraOLEDB.Oracle" for linked server "UDW". (.Net SqlClient Data Provider)

The solution is to go to the provider options and check allow inprocess. Shown below:

enter image description here I can't explain exactly what it does, but this guy does a pretty good job.

Improve this answer
Sign up to request clarification or add additional context in comments.

1 Comment

Make sure to restart the database services in order for it to work.
0

3 more possible reasons for the next guy:

  1. Make sure you have Environmental Variable Path set for oracle. In my case first objects are C:\oracle and C:\oracle\bin
  2. Make sure you have TNS file in C:\oracle\network\admin
  3. Make sure to reboot the machine after these

NOTE: If using the ODAC XCopy make sure you thoroughly read the readme.htm file within the zip download (Instructions vary). Also run the install.bat command using a Command Prompt running as Administrator.

enter image description here

When updating system PATH variables, try doing it to both User and Sytem.

enter image description here

Improve this answer

1 Comment

Your answer could be improved with additional supporting information. Please edit to add further details, such as citations or documentation, so that others can confirm that your answer is correct. You can find more information on how to write good answers in the help center.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.