SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Linked Server with Oracle Database


Linked Server with Oracle Database

Author
Message
pbyrum
pbyrum
SSC Veteran
SSC Veteran (239 reputation)SSC Veteran (239 reputation)SSC Veteran (239 reputation)SSC Veteran (239 reputation)SSC Veteran (239 reputation)SSC Veteran (239 reputation)SSC Veteran (239 reputation)SSC Veteran (239 reputation)

Group: General Forum Members
Points: 239 Visits: 227
I have a 64-bit SQL Server 2005 on Windows 2003 Server Standard Edition server. I have the Oracle 11g client installed, the 64 bit version. I also have an ODBC connection to the Oracle DB and that works. I have a Data Link (UDL) to access the Oracle table and that works. I also have the correct user name and password. The TNSNames.ora is good. The linked server is created successfully, and I can see where you can select Tables and Views in the Catalogs. So, everything looks good. However, when I select to view the tables, I get the following error:

The OLE DB Provider "OraOLEDB.Oracle" for linked server "PBLA" reported an error. Access denied. Cannot obtain the required interface ("IID_IDBSchemaRowset") from OLE DB provider "OraOLEDB.Oracle" for linked server "PBLA". (MS SQL Server Error 7399)

Any suggestions?

Thank you!
PaulB-TheOneAndOnly
PaulB-TheOneAndOnly
SSCertifiable
SSCertifiable (5.5K reputation)SSCertifiable (5.5K reputation)SSCertifiable (5.5K reputation)SSCertifiable (5.5K reputation)SSCertifiable (5.5K reputation)SSCertifiable (5.5K reputation)SSCertifiable (5.5K reputation)SSCertifiable (5.5K reputation)

Group: General Forum Members
Points: 5529 Visits: 4639
Some people has fixed the issue by unchecking the "Allow inprocess" option

On SQL Server Management Studio
Server Objects --> Linked Servers --> Providers --> MSDAORA, right click select properties

It wouldn't hurt to try.

_____________________________________
Pablo (Paul) Berzukov

Author of Understanding Database Administration available at Amazon and other bookstores.

Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.
pbyrum
pbyrum
SSC Veteran
SSC Veteran (239 reputation)SSC Veteran (239 reputation)SSC Veteran (239 reputation)SSC Veteran (239 reputation)SSC Veteran (239 reputation)SSC Veteran (239 reputation)SSC Veteran (239 reputation)SSC Veteran (239 reputation)

Group: General Forum Members
Points: 239 Visits: 227
Thanks that fixed it.
PaulB-TheOneAndOnly
PaulB-TheOneAndOnly
SSCertifiable
SSCertifiable (5.5K reputation)SSCertifiable (5.5K reputation)SSCertifiable (5.5K reputation)SSCertifiable (5.5K reputation)SSCertifiable (5.5K reputation)SSCertifiable (5.5K reputation)SSCertifiable (5.5K reputation)SSCertifiable (5.5K reputation)

Group: General Forum Members
Points: 5529 Visits: 4639
Glad it worked out Wink

_____________________________________
Pablo (Paul) Berzukov

Author of Understanding Database Administration available at Amazon and other bookstores.

Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.
Konrad Willmert
Konrad Willmert
SSC Rookie
SSC Rookie (31 reputation)SSC Rookie (31 reputation)SSC Rookie (31 reputation)SSC Rookie (31 reputation)SSC Rookie (31 reputation)SSC Rookie (31 reputation)SSC Rookie (31 reputation)SSC Rookie (31 reputation)

Group: General Forum Members
Points: 31 Visits: 41
Just in case someone else needs help: I had this same problem but was using the Oracle OLE DB Provider "OraOLEDB.Oracle". I resolved the issue by turning "Allow inprocess" on.
83851263
83851263
Grasshopper
Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)

Group: General Forum Members
Points: 13 Visits: 167
Hi guys

This site is first link then you trying to resolve error
General access denied error The OLE DB provider "OraOLEDB.Oracle" for linked server reported an error. Access denied



If You can't set ''Allow in process" option on provider for some reason try this


test code

begin tran
exec ('select 1 from dual') at ORACLELINK
commit

with domain account you get error (but with sql account thee is no error!!!!)


Msg 7399, Level 16, State 1, Line 2
The OLE DB provider "OraOLEDB.Oracle" for linked server "ORACLELINK" reported an error. Access denied.
Msg 7391, Level 16, State 2, Line 2
The operation could not be performed because OLE DB provider "OraOLEDB.Oracle" for linked server "ORACLELINK" was unable to begin a distributed transaction.




take trace on your distrebuted transaction (or test code above) with

events OLE DB *

if you find error

<hresult>-2147024891</hresult>
<inputs>
<punkTransactionCoord>0x0000000029C11760</punkTransactionCoord>
<isoLevel>4096</isoLevel>
<isoFlags>0</isoFlags>
<pOtherOptions>0x0000000000000000</pOtherOptions>
</inputs>

OLE DB eroor codes

0x80070005 -2147024891 General access denied error

0x80004002 -2147467262 No such interface supported


The solution is here

http://blogs.msdn.com/b/dataaccesstechnologies/archive/2010/08/19/permissions-needed-to-set-up-linked-server-with-out-of-process-provider.aspx
aranda_sergio
aranda_sergio
Grasshopper
Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)

Group: General Forum Members
Points: 11 Visits: 0
Thank you Issue resolve Allow In Process:

Issue:

The OLE DB provider "OraOLEDB.Oracle" for linked server "TEST" reported an error. Access denied.
Cannot obtain the required interface ("IID_IDBSchemaRowset") from OLE DB provider "OraOLEDB.Oracle" for linked server "xxx". (Microsoft SQL Server, Error: 7399)
lup328
lup328
SSC Rookie
SSC Rookie (43 reputation)SSC Rookie (43 reputation)SSC Rookie (43 reputation)SSC Rookie (43 reputation)SSC Rookie (43 reputation)SSC Rookie (43 reputation)SSC Rookie (43 reputation)SSC Rookie (43 reputation)

Group: General Forum Members
Points: 43 Visits: 207
I am having similar issues with my SQL 2014 with WinServer 2012.

When I do as suggested, this is the error I get:

Msg 7399, Level 16, State 1, Line 3
The OLE DB provider "OraOLEDB.Oracle" for linked server "xxxxx" reported an error. The provider reported an unexpected catastrophic failure.
Msg 7330, Level 16, State 2, Line 3
Cannot fetch a row from OLE DB provider "OraOLEDB.Oracle" for linked server "xxxxx".

Any suggestion on what to do next?
Hany Helmy
Hany Helmy
Hall of Fame
Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)

Group: General Forum Members
Points: 3079 Visits: 1117
@lup328; did you resolve your issue? as I have the same, please share the solution.

Thanx
Hany
paultormey
paultormey
SSC Journeyman
SSC Journeyman (78 reputation)SSC Journeyman (78 reputation)SSC Journeyman (78 reputation)SSC Journeyman (78 reputation)SSC Journeyman (78 reputation)SSC Journeyman (78 reputation)SSC Journeyman (78 reputation)SSC Journeyman (78 reputation)

Group: General Forum Members
Points: 78 Visits: 651
Hi Hany,

Did you try the suggested solution:
Enable "Allow inprocess" option in the Oracle Provider for OLE DB.

That resolves the issue.:-)
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search