Post reply

Seeking assistance with linked server

  • March 13, 2007 at 3:36 pm

    #369810

    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.

  • March 13, 2007 at 9:53 pm

    #694853

    What version of SQL are you using?

    Thanks

  • March 13, 2007 at 11:44 pm

    #694855

    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?

  • March 14, 2007 at 10:32 am

    #695015

    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.

  • March 14, 2007 at 11:19 am

    #695025

    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.

  • March 14, 2007 at 2:45 pm

    #695081

    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.

  • March 14, 2007 at 4:47 pm

    #695102

    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]

  • March 15, 2007 at 1:39 pm

    #695338

    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.

  • March 16, 2007 at 1:12 pm

    #695533

    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