Skip to Main Content
Forums

DevOps, CI/CD and Automation

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.

Linked server using MSDAORA works, OraOLEDB doesn't. Why?

471182May 17 2009 — edited Jun 23 2009
Hi

I am trying to connect SQL Server 2008 via a linked server to Oracle 9.2.0.7.

I'm currently working on a PC running Windows XP Pro with SQL Server 2008, and have the 9.2.0.7 client and ODAC installed.

I have created 2 linked servers, one using the MSDAORA data provider and the other with OraOLEDB provider.

I can select data through the MS Link using four part names no problem.
select * from LNK_NEXREFMG..SAPR3.A516
But the same query using the Oracle provider fails.
select * from LNK_NEXREFMG_OLE..SAPR3.A516
The OLE DB provider "OraOLEDB.Oracle" for linked server "LNK_NEXREFMG_OLE" reported an error. Access denied.
Cannot obtain the required interface ("IID_IDBSchemaRowset") from OLE DB provider "OraOLEDB.Oracle" for linked
server "LNK_NEXREFMG_OLE". (Microsoft SQL Server, Error: 7399)
Both Linked servers are created using the same user/passwd and same parameters. From SQL Server when I click test connection, they both say "ok". But when I try and use them, the MS link works, the oracle one doesn't...

I can connect using SQL*Plus, TNSNAMES is ok, The UID/PWD is the same. The query syntax is ok, and work with the MS driver. So I suspect the OraOLEDB driver/installation/parameters.

Any suggestions?

Are any different parameters required when creating a Linked server using OraOLEDB?

Does anyone have a link or reference of the Registry settings I should check?

Any help or assistance would be greatly appreciated.

Regards

Peter

Edited by: user468179 on 17-May-2009 20:37

Edited by: peetmoore on 16-Jun-2009 16:50

Comments

anishjp

Anybody?

Answer

No...if you need more than that, might be time to look at something like APEX. Or build a rest api to pull the data and use your favorite js library to chart the data.
We're building this feature into SQL Developer Web - stay tuned.

Marked as Answer by anishjp · Apr 23 2021
1 person found this helpful
anishjp

Okay, thank you. I will look at APEX then.

Regards,
Anish

1 - 3
Locked Post
New comments cannot be posted to this locked post.

Post Details

Locked on Jul 21 2009
Added on May 17 2009
#oracle-provider-for-ole-db, #windows-and-net
4 comments
11,808 views