Post reply

Linked server against Oracle

  • January 21, 2004 at 7:24 am

    #76111

    Hi all! I´m having a trouble with a linked server to Oracle 8.1.6. My test box was running SQL2K without SPs, in this situation the linked (using Microsoft OLE DB Provider for Oracle) was running fine. Yesterday i installed SP3a, since that moment the linked give me this error:

    Error 7399: OLE DB Provider 'MSDAORA' reported an error. The provider did not give any information about the error.

    OLE DB error trace [OLE/DB Provider 'MSDAORA' IDBInitialize::Initialize returned 0x80004005: The provider did not give any information about the error.].

     

    I tried to fix the situation installing MDAC 2.8, but the error continues...any ideas ???

  • January 22, 2004 at 5:34 am

    #491088

    Up!

  • January 22, 2004 at 5:40 am

    #491090

    Error Code 0x80004005 is unfortunately an unspecified error.

    You aren't really very specific about the error?  Does the OPENQUERY command get this error? Does the OPENROWSET command get this error?

    the following article may help you, its got some new stuff with sp3.

    http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_oa-oz_78z8.asp

     

  • January 22, 2004 at 5:52 am

    #491096

    I get this error when i try to see the Oracle tables shareds (through EM, for example). Of course, if i run a distributed query i get the same error...

  • January 22, 2004 at 6:12 am

    #491103

    What provider are you using?  MSDAORA or the Oracle oledb provider or a third party provider?

    Does the OPENROWSET and OPENQUERY command executed through QA give any more info?

     

  • January 22, 2004 at 6:33 am

    #491110

    MSDAORA. Also using Oracle provider for OLEDB.

    Runnig the commands, the information is the same:

     

    Server: Msg 7399, Level 16, State 1, Line 1

    OLE DB provider 'MSDAORA' reported an error. The provider did not give any information about the error.

    OLE DB error trace [OLE/DB Provider 'MSDAORA' IDBInitialize::Initialize returned 0x80004005:  The provider did not

    give any information about the error.].

  • January 22, 2004 at 8:39 am

    #491140

    We upgraded from sp2 to sp3 a few months back without any issues.

     

    I would work back and eliminate possiblities.

    See if from the sql server box you can tnsping the box.

    then see if you can connect native via sqlplus. 

    Then validate a simple connection via the msdaora driver through vb or vbscript.

    Also validate that the configuration as specified in the following article didn't get changed.

    http://support.microsoft.com/default.aspx?scid=kb;en-us;280106

     

     

  • January 22, 2004 at 10:09 am

    #491149

    even before u run a query, check if you are able to connect to the oracle server and try connecting thro' oracle client from the sql box.

  • January 22, 2004 at 10:16 am

    #491153

    -See if from the sql server box you can tnsping the box.

    Works Ok

    -then see if you can connect native via sqlplus. 

    Works Ok

    -Then validate a simple connection via the msdaora driver through vb or vbscript.

    I don´t know how to do that

  • January 22, 2004 at 11:23 am

    #491184

    put the following into a file of extension .vbs and run

    it after changing the tnsname user and password.

     

    dim cn

    SET cn = createobject("ADODB.cONNECTION")

    cn.open  "Provider=MSDAORA;Data Source=<tns name>","<User>","<pass>"

    msgbox "connection successful"

  • January 23, 2004 at 6:42 am

    #491327

    hi!

    you don't even need VBS to test an ADO connection. just create a file with arbitrary name and ".udl" extension ... double-click it to bring up the standard ADO connection string editor, where you've got the possibility to try around ...

    best regards, chris.

  • October 24, 2007 at 4:53 am

    #744860

    Hello,

    Hope you guys have come across this.

    Iam trying to connect to the Linked server via Query Analyser and its throwing Error 7399 :OLE DB provider 'MSDAORA' reported an error.

    Via Enterprise manager, it happend the same, but when i reinstalled oracle client 9, it became ok.

    Can anyone please help, how to resolve via Q.A ?

    Thank you

    Raman

  • October 26, 2007 at 11:32 am

    #745846

    Tmp,

    This is a long post, but as the old saying goes, I am teaching people to fish ...

    Errors such as "Error 7399 :OLE DB provider 'MSDAORA' reported an error." can be frustrating to troubleshoot.

    I would determine where in the communication that connectivity is not working.

    From the SQL box:

    DOS Prompt:

    1) Use Ping.exe to check TCP/IP communication with the the Oracle server.

    2) Verify the TNSNAMES.ORA file for the entry for your target database.

    WARNING: Create a backup your TNSNAMES.ORA file before manipulating it, Oracle is quite sensitive with the formatting of this file.

    The default location for an Oracle 9.2.0.X.X client install is at: C:\oracle\ora92etwork\admin

    My Oracle database "PROD" entry looks like:

    PROD =

    (DESCRIPTION =

    (ADDRESS_LIST =

    (ADDRESS = (PROTOCOL = TCP)(HOST = ORACLE_SERVER_NAME)(PORT = 1521))

    )

    (CONNECT_DATA =

    (SERVICE_NAME = PROD)

    )

    )

    NOTE: You may need to match the corresponding entry in the LISTENER.ORA file on the Oracle Server.

    3) Use TNSPING.EXE to verify that the listener is up on the target server, and listening for your database.

    C:\>TNSPING PROD

    TNS Ping Utility for 32-bit Windows: Version 9.2.0.7.0 - Production on 26-OCT-20

    07 13:20:15

    Copyright (c) 1997 Oracle Corporation. All rights reserved.

    Used parameter files:

    C:\oracle\ora92etwork\admin\sqlnet.ora

    Used TNSNAMES adapter to resolve the alias

    Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)

    (HOST = ORACLE_SERVER_NAME)(PORT = 1521))) (CONNECT_DATA = (SERVICE_NAME = PROD)))

    (OK) 40 ms

    C:\>

    SQL*PLUS:

    4) Use SQL*Plus to connect, and run a sample query, if possible use the scott/tiger@PROD (login/password@database)

    SELECT sysdate from dual;

    Once all of those stages are verified, move onto the linked server portion.

    5) Connect to Query Analyzer or SQL Server Management Studio (SSMS):

    6) Verify your current Linked Server and Linked Server Login information:

    EXEC sp_linkedservers

    EXEC sp_helpserver

    7) If needed make the Linked Server and Linked Server Login

    7a)-- Create the Linked Server to Oracle database.

    EXEC sp_addlinkedserver 'Ora_PROD', 'Oracle 9.2.0', 'MSDAORA', 'PROD'

    7b)-- Create the Linked Server login.

    EXEC sp_addlinkedsrvlogin 'Ora_PROD', 'FALSE',NULL, 'scott', 'tiger'

    Before trying to connect to "live" tables, I would suggest using the Oracle "dummy" table DUAL for testing connectivity with OpenQuery.

    8a) Check connectivity and access the DUAL "dummy" table in the Oracle database.

    SELECT sysdate

    FROM Ora_DTRAQ_PROD...DUAL

    8b) Now, using OPENQUERY, check connectivity and access the DUAL "dummy" table in the Oracle database.

    SELECT * FROM OPENQUERY

    (Ora_PROD,'SELECT sysdate FROM DUAL')

    GO

    My SQL Server Linked Server to Oracle Reference Notes:

    -- Adding linked server (from SQL Server 2000 Books Online):

    /* sp_addlinkedserver [@server =] 'server'

    [, [@srvproduct =] 'product_name']

    [, [@provider =] 'provider_name']

    [, [@datasrc =] 'data_source']

    [, [@location =] 'location']

    [, [@provstr =] 'provider_string']

    [, [@catalog =] 'catalog']

    */

    -- Add a Linked Server 'Ora817Link', connecting to an Oracle 8.1.7.x.x database.

    EXEC sp_addlinkedserver 'Ora817Link', 'Oracle', 'MSDAORA', 'oracle817'

    -- Adding linked server login:

    /* sp_addlinkedsrvlogin [@rmtsrvname =] 'rmtsrvname' -- ORACLE IP or Network Name

    [,[@useself =] 'useself']

    [,[@locallogin =] 'locallogin'] -- local SQL Server 2005 login

    [,[@rmtuser =] 'scott'] -- ORACLE Login

    [,[@rmtpassword =] 'tiger'] -- ORACLE Password

    */

    -- Another Linked Server Login Example:

    -- After you setup a linked server to a remote ORACLE database,

    -- you have to set the remote login and password for the connection

    -- to work:

    /*

    EXEC sp_addlinkedsrvlogin

    @rmtsrvname = '255.55.230.99', -- ORACLE IP or Network Name

    @useself = false,

    @locallogin = 'sa', -- local SQL Server login

    @rmtuser = 'msmith', -- ORACLE Login

    @rmtpassword = 'secret' -- ORACLE Password

    GO

    */

    -- Add Linked Server Login for 'Ora817Link', using the Scott Oracle User.

    EXEC sp_addlinkedsrvlogin 'Ora817Link', 'FALSE',NULL, 'scott', 'tiger'

    -- Use Admin Database on .

    -- Connect to PROD instance.

    -- Create the Linked Server to Oracle database.

    USE Admin EXEC sp_addlinkedserver 'Ora_PROD', 'Oracle 9.2.0', 'MSDAORA', 'PROD'

    -- Create the Linked Server login.

    EXEC sp_addlinkedsrvlogin 'Ora_PROD', 'FALSE',NULL, 'scott', 'tiger'

    SELECT sysdate

    FROM Ora_DTRAQ_PROD...DUAL

    SELECT * FROM OPENQUERY

    (Ora_PROD,'SELECT * FROM DUAL')

    GO

    SELECT * FROM OPENQUERY

    (Ora_PROD,'SELECT sysdate FROM DUAL')

    GO

    Best of luck, and please update us as to your progress or how you were able to solve your issue.

    Regards,

    "Key"
    MCITP: DBA, MCSE, MCTS: SQL 2005, OCP

Viewing 13 posts - 1 through 12 (of 12 total)

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