Seeking assistance with linked server

  • Greetings, I am attempting to create a linked server to an oracle 10gR2 database. I have installed the Oracle 10gR2 client and it is properly configured, I am able to connect to the remote database using sqlplus. I have configured the linked server using the enterpries manager but when I try to view the tables in the linked database I see the error;

    Error 7399 OLE DB provider 'MSDAORA' reported an error. OLE DB error trace [OLE/DBprovider 'MSDAORA' IDBInitialize:: initialize returned 0x80004005: ].

    I have two questions, first, is SQL Server compatible with the Oracle 10gR2 client? I am able to do this successfully on a second box using the Oracle 9i client.

    Second question, I configured the linked server using the enterprise manager rather than the stored procedure sp_addlinkedserver, could that have any bearing on the problem?

    I'd greatly appreciate any suggestions. Thanks.

  • What version of SQL are you using?

    Thanks

  • What does your configuration look like?

    I'm assuming you're running MSSQL2000 with the oracle Client loaded on that server.

    We've had the same problem before, have you tried looking at the security context of the linked server?

  • Thanks for the replies. Yes, I am running SQL Server 2000 and the Oracle client is loaded on that server. Can you elaborate on what you mean by the security context of the linked server please. I am able to connect to the remote database using the Oracle client on this server so I know that Oracle is configured correctly. Thanks.

  • In the Enterprise Manager in the linked server tab you can go to the properties of the linked server and on the security tab you can change how you want the security to work.  Check off the Be made using this security contex: at the bottom and type in the user id and password of the user that has access to the linked server.  Apply those changes.  If your connecting to a Oracle box then you will need to set the Server Options as well.  We have had to stop and start the agent upon occasions to get the settings to take effect.

  • Hello, I have rechecked the setup of the Linked Server in the Enterprise Manager and it is correct. I also restarted the sqlserver agent but to no avail. I'm stumped. Is sqlserver 2000 compatible with Oracle 10gR2 client? Thanks.

  • In Enterprise Manager when you view the linked server can you see the tables? Probably not. Try the following:-

    Read books online look for sp_addlinkedserver, sp_dropserver and sp_addlinkedsrvlogin.

    Drop what you've already set up with sp_dropserver first then apply the sp_addlinkedserver command for Oracle as described. Once the linked server is there set up the connection using sp_addlinkedsrvlogin. To see if this has worked go back to Enterprise Manager and hopefully under Linked Servers you will be able see the Oracle tables

     

     

    Facts are stubborn things, but statistics are more pliable - Mark Twain
    Carolyn
    SQLServerSpecialists[/url]

  • Carolyn, I tried that but am still encountering the same error. I am beginning to wonder if this version od SQL Server is compatible with the Oracle 10gR2 client. Do you know where I can find this information? Thanks.

  • Hello, it turns out it is necessary to set AllowInProcess for the Oracle OLE DB drivers. DOing that resolved the problem. THanks.

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply