0 Replies Latest reply on Dec 15, 2003 10:15 PM by 409805

    Problem 8.1.7.4 DB connection via OLEDB

    409805
      Hi,

      I am using the same VB code connecting to two oracle databases: one success, the other failed. I have oracle OLEDB provider 8.1.7.3 and MDAC2.7 on the PC.

      Here is some info I printed for both connection:
      =========================================
      Connect to oracle DB #1 via OLEDB � successful:
      �VB code:
      objPubConn(iDBID).Open "Provider=OraOLEDB.Oracle;Data Source=helios.milehi.lucent.com;User ID=userid;Password=password"

      objPubConn(iDBID).ConnectionString: Provider=OraOLEDB.Oracle.1;Persist Security Info=False;User ID=userid;Data Source=helios.milehi.lucent.com;Extended Properties=""
      objPubConn(iDBID).Version: 2.7
      objPubConn(iDBID).State: 1
      objPubConn(iDBID).Attributes: 0
      objPubConn(iDBID).Mode: 0

      Connect to oracle DB #2 via OLEDB - failed
      �VB code:
      objPubConn(iDBID).Open "Provider=OraOLEDB.Oracle;Data Source= hagrid.milehi.lucent.com;User ID=userid;Password=password"

      objPubConn(iDBID).ConnectionString: Provider=OraOLEDB.Oracle; Data Source=hagrid.milehi.lucent.com;User ID=userid ;Password=password
      objPubConn(iDBID).Version: 2.7
      objPubConn(iDBID).State: 0
      objPubConn(iDBID).Attributes: 0
      objPubConn(iDBID).Mode: 0

      Err.Description: ORA-12514: TNS:listener could not resolve SERVICE_NAME given in connect descriptor
      Err.Number: -2147467259
      Err.Source: OraOLEDB
      Err.HelpContext: 1000440

      The connection string on the DB#1 is Provider=OraOLEDB.Oracle.1 and Provider=OraOLEDB.Oracle on DB #2.

      I wonder if it is because DB #2 does not have the correct provider.

      The error code is pointing to the tnsnames.ora file. I have installed the oracle client on my PC and have the service_name in the file. But I got the same error. I wonder if there is something needs to be done on the server.

      ORA-12154 error description from oracle site:

      This is error indicates a failure to lookup the SQL*Net/NET8 Alias, used for the
      DSN configuration, by the underlying SQL*Net layer. This alias is typically
      defined in the TNSNAMES.ORA file. This is normally the
      ORACLE_HOME\NETWORK\ADMIN directory (or NET80\ADMIN).
      The SERVER name or SQL*NET Alias is the same information stored in this
      configuration file, so insure that you provided the correct information when
      configuring the ODBC DSN.
      Insure that this is the ONLY place on your system where a TNSNAMES.ORA
      file is located, due to a quirk in the network layer, it will load a
      TNSNAMES.ORA found in a current working directory in preference to one
      specified in this file.
      [Use of the Oracle SQL*NET Names server will change how this is done, contact
      your system administrator for details on that configuration.]

      source:
      http://www.oracle.com/ultrasearch/wwws/searchoc.jsp?p_Action=Search&p_Group=1&p_Group=4&p_Query=ORA-12154&Advanced.x=10&Advanced.y=3


      Thanks for your help,

      Julie Shi