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

471182

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

534997

Were you able to find a solution to this? I am having the same problem.

Thanks.

471182

Well sort of.

We uninstalled and re-installed the oracle clients and ODAC and it now works.

But I can't tell you exactly what as wrong.

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

Edited by: peetmoore on 16-Jun-2009 23:29

Johnsung-Oracle

It could have been the setup of the linked server.

Ensure you check the following in the Linked server setup for Oracle Provider for OLE DB
"Dynamic Parameters"
"Allow InProcess" .

Hope this helps

471182

Yes we did have allow in process checked.

Interestingly, we had another working linked server pointing to another database and where using the "right click> script> create to" method to generate a script for adding a new linked server. We would then edit the script changing the database name, uid and password etc and found it wouldn't work.

So instead we used the wizard and created a linked server step by step and it worked.

We created a script from the new Linked server and compared it with the first and they where effectively identical. (apart from the obvious db:uid:pwd)