Cannot create an instance of OLE DB provider “OraOLEDB.Oracle” for linked server “LinkedServerName”.

Today an incident was raised with me whereby a user wasnt able to remotely query an oracle database (10g) via a SQL Server instance.

SELECT * FROM OPENQUERY(LinkedServerName, ‘select * from oracleschema.tablename’)

Interestingly, the query worked perfectly when running it directly on the Server which hosted the SQL Server Instance. The problem was when the query was run remotely on the client machine – which gave the error below:

Msg 7302, Level 16, State 1, Line 1
Cannot create an instance of OLE DB provider "OraOLEDB.Oracle" for linked server "LinkedServerName

I did quite a lot of research on this and the issue can be resolved by editing the Provider Properties (right-click the provider in the providers folder) and ticking the "Allow inprocess" option corrects my problem.
Existing linked servers will not pick up the provider setting, so new linked server objects will have to be created. But the new ones work.

http://www.sqlservercentral.com/Forums/Topic491682-149-1.aspx

image

image

Similar errors about linked servers on SQL Server linking to Oracle

Msg 7399, Level 16, State 1, Line 1
The OLE DB provider "OraOLEDB.Oracle" for linked server "LinkedServerName" reported an error. Access denied.
Msg 7332, Level 16, State 2, Line 1
Cannot rescan the result set from OLE DB provider "OraOLEDB.Oracle" for linked server "LinkedServerName". Unknown provider error.

http://www.sqlservercentral.com/Forums/Topic482481-146-1.aspx

Advertisements

2 Responses to Cannot create an instance of OLE DB provider “OraOLEDB.Oracle” for linked server “LinkedServerName”.

  1. win says:

    It works!

    Thanks for taking the time to make this post.

  2. Carlos says:

    Master! Thank you so much!

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: