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