Results 1 to 2 of 2
  1. #1
    Join Date
    Oct 2002
    Posts
    4

    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?

  2. #2
    Join Date
    Dec 2001
    Location
    Toronto, Canada
    Posts
    335
    Here is the code I used to create a linked server and then select data
    Code:
    set nocount on
    go
    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'
    
    EXEC sp_addlinkedserver
       @server = @server,
       @srvproduct = 'Oracle',
       @provider = 'MSDAORA',
       @datasrc = 'DBS_Athena'
    
    exec sp_addlinkedsrvlogin @server, 'FALSE', NULL, @userid, @pswd
    
    exec sp_linkedservers
    GO
    
    
    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.
    MCDBA

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •