Results 1 to 4 of 4
  1. #1
    Join Date
    Feb 2004
    Posts
    2

    Unanswered: ODBC / Oracle Linked Server Problem

    I have a SQL server that I am trying to link to a number of Oracle environments. After much tuning, we managed to achieve this although the four-part naming was not possible and we had to use Openquery and run pass throughs.

    Nothing in our configuration has changed and SQL Server is no longer able connect to the linked databases. The Oracle client on the PC is fine and is able tnsping any of the remote databases. I am also able to create ODBC connections to the remote databases on the SQL box that are fine.

    Using a datalink in DTS, I can connect to the remote databases. This suggests to me that there is something wrong within the actual database links. I have set them up using the working ODBC DSN's on the SQL box.

    If I try and run a query against them in Query Analyser, I get the following error message :

    Server: Msg 7399, Level 16, State 1, Line 1
    OLE DB provider 'MSDAORA' reported an error.
    [OLE/DB provider returned message: ORA-12154: TNS:could not resolve service name
    ]
    OLE DB error trace [OLE/DB Provider 'MSDAORA' IDBInitialize::Initialize returned 0x80004005: ].

    If I click on the tables icon in EM to view the remote catalogues I get the following error :

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


    Any help that could be give on this would be greatly appreciated.

  2. #2
    Join Date
    Dec 2002
    Posts
    1,245
    Can you post the script(s) that you used to create the linked servers on your SQL Server?

    Also, I think it would be helpful if you could post the content of the tnsname.ora file.

    regards,

    hmscott

  3. #3
    Join Date
    Feb 2004
    Posts
    2
    Hi, it appears the problem lies somewhere in the Oracle client on the server or the NT build. We have successfully managed to get the links up and alive by bouncing the server once a day, stopping and restrating the SQL Services and refreshing the login details.

    We are currently investigating as to whether it not it could be related to the connections created by Terminal Services.







    Originally posted by hmscott
    Can you post the script(s) that you used to create the linked servers on your SQL Server?

    Also, I think it would be helpful if you could post the content of the tnsname.ora file.

    regards,

    hmscott

  4. #4
    Join Date
    Jan 2004
    Location
    Shanghai,China
    Posts
    76
    I'm ware of the errmsg "Server: Msg 7399, Level 16, State 1, Line 1
    OLE DB provider 'MSDAORA' reported an error.
    [OLE/DB provider returned message: ORA-12154: TNS:could not resolve service name
    ] "
    I think the problem is that u haven't configure sql*net correctly.
    See the tnsnames.ora in %ORACLE_HOME%/network/admin.

Posting Permissions

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