8 Replies Latest reply: Nov 15, 2011 1:08 PM by Mark D Powell RSS

    unable to connect SQLState=08004 Oracle ODBC ORA-12154:TNS..

    773278
      hi all

      i am using
      windows xp.
      Client 11g R2
      -----------------------
      Oracle Linux
      Database 11g R2
      i am able to connect to Database using SQLPLUS but when i try to use "ORACLE ODBC Driver Configuration"
      and try to test connection then i am getting the following error.

      unable to connect SQLState=08004 Oracle ODBC ORA-12154:TNS..

      plz suggest thanks in advance.
        • 1. Re: unable to connect SQLState=08004 Oracle ODBC ORA-12154:TNS..
          sb92075
          nisha wrote:
          hi all

          i am using
          windows xp.
          Client 11g R2
          -----------------------
          Oracle Linux
          Database 11g R2
          i am able to connect to Database using SQLPLUS but when i try to use "ORACLE ODBC Driver Configuration"
          and try to test connection then i am getting the following error.

          unable to connect SQLState=08004 Oracle ODBC ORA-12154:TNS..

          plz suggest thanks in advance.
          ORA-12154 ALWAYS only occurs on SQL Client & no SQL*Net packets ever leave client system
          ORA-12154 occurs when client requests a connection to some DB server system using some connection string.
          The lookup operation fails because the name provided can NOT be resolved to any remote DB.
          The analogous operation would be when you wanted to call somebody, but could not find their name in any phonebook.
          The most frequent cause for the ORA-12154 error is when the connection alias can not be found in tnsnames.ora.
          The lookup operation of the alias can be impacted by the contents of the sqlnet.ora file; specifically DOMAIN entry.
          TROUBLESHOOTING GUIDE: ORA-12154 & TNS-12154 TNS:could not resolve service name [ID 114085.1]
          http://edstevensdba.wordpress.com/2011/02/26/ora-12154tns-03505/
          • 2. Re: unable to connect SQLState=08004 Oracle ODBC ORA-12154:TNS..
            Mark D Powell
            In other words did you configurethe client tnsnames.ora file? Does tnsping work?

            HTH-- Mark D Powell --
            • 3. Re: unable to connect SQLState=08004 Oracle ODBC ORA-12154:TNS..
              EdStevens
              nisha wrote:
              hi all

              i am using
              windows xp.
              Client 11g R2
              -----------------------
              Oracle Linux
              Database 11g R2
              i am able to connect to Database using SQLPLUS but when i try to use "ORACLE ODBC Driver Configuration"
              and try to test connection then i am getting the following error.

              unable to connect SQLState=08004 Oracle ODBC ORA-12154:TNS..

              plz suggest thanks in advance.
              If you can connect with sqlplus, then your basic tns config is correct. Problem has to be in your configuration of the ODBC DNS you defined for the connection. Please describe the DNS in as much detail as possible.
              What ODBC driver did yo specify?
              What are the names and values of all the fields you specify in the DNS for this connection?
              • 4. Re: unable to connect SQLState=08004 Oracle ODBC ORA-12154:TNS..
                773278
                hi

                thanks for reply.

                System DSN is something like this in ODBC Data Source Administrator.
                Name=DBQ         Driver= Oracle in OraClient11g_home1
                
                how to use the above DSN for connectivity?
                maybe i am wrong if i am wrong plz correct me and if possible plz
                i am using the following code which is working fine for text fields instead of blob.
                and if anyone knows regarding image that how to store in database using ASP code so plz
                help me thanks..
                here is table structure and ASP code.
                Roll number
                Name varchar2(20)
                Subject varchar2(20)
                Marks number
                Attach Blob
                <%
                Dim conn,recset 
                 
                 
                 
                set conn=server.CreateObject("ADODB.connection")
                set recset=server.CreateObject("ADODB.recordset")
                'set conn=Server.CreateObject("ADODB.Connection")
                 conn.Provider="MSDAORA;Data Source=orcl;User ID=XXXX;Password=XXX"
                 
                 
                conn.Open(conn) 
                 
                Dim r,n,s,m,att
                 
                r=upload.Form("roll")
                n=upload.Form("nam")
                s=upload.Form("sub")
                m=upload.Form("mark")
                att=upload.Form("FILE1")
                 
                Response.Write "Succssfully Inserted"
                conn.execute "insert into table1 values('"&r&"','"&n&"','"&s&"','"&m&"','"&file1&"')"
                conn.close
                 
                %>
                • 5. Re: unable to connect SQLState=08004 Oracle ODBC ORA-12154:TNS..
                  EdStevens
                  nisha wrote:
                  hi

                  thanks for reply.

                  System DSN is something like this in ODBC Data Source Administrator.
                  Name=DBQ         Driver= Oracle in OraClient11g_home1
                  That's not all of it. What do you have for 'TNS Service Name"?
                  how to use the above DSN for connectivity?
                  {code}
                  You reference the dsn name in your odbc enabled connection.
                  maybe i am wrong if i am wrong plz correct me and if possible plz
                  i am using the following code which is working fine for text fields instead of blob.
                  That's not the problem you reported when you opened this thread.
                  and if anyone knows regarding image that how to store in database using ASP code so plz
                  help me thanks..
                  here is table structure and ASP code.
                  {code}
                  Roll number
                  Name varchar2(20)
                  Subject varchar2(20)
                  Marks number
                  Attach Blob
                  {code}

                  {code}
                  <%
                  Dim conn,recset



                  set conn=server.CreateObject("ADODB.connection")
                  set recset=server.CreateObject("ADODB.recordset")
                  'set conn=Server.CreateObject("ADODB.Connection")
                  conn.Provider="MSDAORA;Data Source=orcl;User ID=XXXX;Password=XXX"


                  conn.Open(conn)

                  Dim r,n,s,m,att
                  Sorry, I don't know much about asp coding. I do see that you are stating that your provider is MSDAORA, which suggests you are really using the MS driver, not oracle's. In fact, I'd guess that this is bypassing the ODBC DSN definitions completely, and you are telling it to use the MS driver, and reference 'orcl', which would be looked up in the tnsnames.ora (which Mark referred to and to which you have not yet responded).
                  >
                  r=upload.Form("roll")
                  n=upload.Form("nam")
                  s=upload.Form("sub")
                  m=upload.Form("mark")
                  att=upload.Form("FILE1")

                  Response.Write "Succssfully Inserted"
                  conn.execute "insert into table1 values('"&r&"','"&n&"','"&s&"','"&m&"','"&file1&"')"
                  conn.close

                  %>


                  {code}
                  • 6. Re: unable to connect SQLState=08004 Oracle ODBC ORA-12154:TNS..
                    773278
                    hi

                    tnsnames.ora is something like this.
                    orcl =
                      (DESCRIPTION =
                        (ADDRESS_LIST =
                          (ADDRESS = (PROTOCOL = TCP)(HOST = 192.173.1.1)(PORT = 1521))
                        )
                        (CONNECT_DATA =
                          (SERVICE_NAME = orcl)
                        )
                      )
                    plz guide me how to use Oracle Driver for ASP.
                    • 7. Re: unable to connect SQLState=08004 Oracle ODBC ORA-12154:TNS..
                      sb92075
                      nisha wrote:
                      plz guide me how to use Oracle Driver for ASP.
                      This a WINDOWS problem & solution!

                      Oracle RDBMS accepts every connection request it receives.
                      • 8. Re: unable to connect SQLState=08004 Oracle ODBC ORA-12154:TNS..
                        Mark D Powell
                        You should have posted this information to begin with. The posted example is using the MS driver rather than the Oracle driver which in itself should not matter. I do not think the posted code is using the DSN. It appears to directly provide the reference to the required Oracle client tnsnames.ora entry along with the target database username and password.

                        I suggest pulling your code documentation and look at how you can code the ADODB.connection. I would also switch to the Oracle provider.

                        If you can connect from the client to the target Oracle using SQLPlus then the cliient is fine and the problem is in your code/connection.

                        HTH -- Mark D Powell --