Forum Stats

  • 3,724,389 Users
  • 2,244,744 Discussions
  • 7,850,988 Comments

Discussions

Howdy, Stranger!

It looks like you're new here. If you want to get involved, click one of these buttons!

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

471182
471182 Member Posts: 34
edited June 2009 in Oracle Provider for OLE DB
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

Answers

  • 534997
    534997 Member Posts: 11
    Were you able to find a solution to this? I am having the same problem.

    Thanks.
  • 471182
    471182 Member Posts: 34
    edited June 2009
    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
    Johnsung-Oracle Member Posts: 59
    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
    Johnsung-Oracle
  • 471182
    471182 Member Posts: 34
    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)
This discussion has been closed.