0

I need to connect to a remote server to consume some tables in an Oracle db so I installed the Oracle provider, V1021277-01, ODAC 21c.

The way I am connecting is by creating a linked server

Provider: Oracle Provider for OLE DB

Product Name: Oracle

Data Source: [ip-adress:port]/[instance-name]

Then I get the following error:

The linked server has been updated but failed a connection test. Cannot create an instance of OLE DB provider "ORAOLEDB.Oracle" for linked server "ABC". (Microsoft SQL Server, Error: 7302)

However, when I create an UDL file the test connection successes and I completely sure the connection works since I tested with TELNET

Here my source: Windows Servers 2016, SSMS 2019.

Edit (8/17/2022) I enabled the below provider options but no luck.

exec master.dbo.sp_MSset_oledb_prop 'ORAOLEDB.Oracle', N'AllowInProcess', 1 

exec master.dbo.sp_MSset_oledb_prop 'ORAOLEDB.Oracle', N'DynamicParameters', 1

Any idea on why the connection fails on SSMS?

Improve this question
2
  • check if Allow inprocess is enabled on the Oracle Provider for OLE DB. in ssms, linked servers->providers->Oracle Provider for OLE DB->properties
    – Bob Klimes
    Aug 17, 2022 at 15:17
  • Thanks for the response. I have updated my question, and yeah, I already did it, any other idea would be really appreciated.
    – Jamo
    Aug 17, 2022 at 15:43

1 Answer 1

Reset to default
0

I finally made this work.

After trying different alternatives I found one (I didn't think about that before). This is only about to stop all the SQL Services, install the provider and then restart all the SQL services and that's all.

Here the link

https://stackoverflow.com/a/55422848/1762393

Improve this answer

Your Answer

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

Not the answer you're looking for? Browse other questions tagged or ask your own question.