Unanswered: Linked Sever with Oracle Oledb provider query failed!
I'v setuped a Oracle8.16 linked server througth oracle oledb provider:
OraOLEDB.Oracle. When I use this linked server to get data, SqlServer
gave the following error message:
"OLE DB provider 'OraOLEDB.Oracle' reported an error. The provider did not give any information about the error."
But when I change the OraOLEDB.Oracle provider to MSDAORA,
none error occured!
Can somebody pls. guide me to resolve the issue?
many thanks in advance?
Here is the code I used to create a linked server and then select data
set nocount on
declare @server sysname
declare @userid varchar(10)
declare @pswd varchar(10)
set @server = 'ATHENA_ORA'
set @userid = 'system'
set @pswd= 'manager'
exec sp_dropserver @server = @server , @droplogins ='droplogins'
@server = @server,
@srvproduct = 'Oracle',
@provider = 'MSDAORA',
@datasrc = 'DBS_Athena'
exec sp_addlinkedsrvlogin @server, 'FALSE', NULL, @userid, @pswd
SELECT * FROM ATHENA_ORA..SCOTT.DEPT
A couple of points, @datasrc = 'DBS_Athena', 'DBS_Athena' is defined in my TNSNAMES.ORA, which was done by using Net8 or SQL*Net. Also I had to put the schema.tablename in uppercase. SELECT * FROM ATHENA_ORA..SCOTT.DEPT worked, but SELECT * FROM ATHENA_ORA..SCOTT.dept or SELECT * FROM ATHENA_ORA..scott.DEPT did not work.