14 Replies Latest reply on Sep 19, 2013 8:25 PM by jgarry

    Connecting with Oracle 10g database from .Net 3.5

    Arun Prasath

      Hi all,

      In my windows application I am trying to connect to an Oracle 10g database using the Microsoft ODBC for Oracle driver; I used following connection string

      "Driver={Microsoft ODBC for Oracle};Server=myservername;Uid=myuserid;Pwd=mypassword;"


      Below error occurred when I trying to connect database.

      ERROR [08001] [Microsoft][ODBC driver for Oracle][Oracle]ORA-12154: TNS: Could notresolve the connect identifier specified

      ERROR [IM006] [Microsoft][ODBC Driver Manager] Driver's SQLSetConnectAttr failed

      ERROR [01000] [Microsoft][ODBC Driver Manager] The driver doesn't support the version of ODBC behavior that theapplication requested (see SQLSetEnvAttr).”

      When i run the application from VisualStudio 2008 itself, i m not getting any error.I m getting this error in another computer where i m executing the .exe of that project.

      I have installed .Net FrameWork 3.5 and CrystalReports redistribution on the deploying machine.

        • 1. Re: Connecting with Oracle 10g database from .Net 3.5

          What did you do to resolve the error yourself? Are you aware this is a FAQ?

           

          -------------

          Sybrand Bakker

          Senior Oracle DBA

          • 2. Re: Connecting with Oracle 10g database from .Net 3.5
            EdStevens

            ArunPrasath wrote:

             

            Hi all,

            In my windows application I am trying to connect to an Oracle 10g database using the Microsoft ODBC for Oracle driver; I used following connection string

            "Driver={Microsoft ODBC for Oracle};Server=myservername;Uid=myuserid;Pwd=mypassword;"


            Below error occurred when I trying to connect database.

            ERROR [08001] [Microsoft][ODBC driver for Oracle][Oracle]ORA-12154: TNS: Could notresolve the connect identifier specified

            ERROR [IM006] [Microsoft][ODBC Driver Manager] Driver's SQLSetConnectAttr failed

            ERROR [01000] [Microsoft][ODBC Driver Manager] The driver doesn't support the version of ODBC behavior that theapplication requested (see SQLSetEnvAttr).”

            When i run the application from VisualStudio 2008 itself, i m not getting any error.I m getting this error in another computer where i m executing the .exe of that project.

            I have installed .Net FrameWork 3.5 and CrystalReports redistribution on the deploying machine.

            Unfrotunately, Microsoft driver for oracle uses bad (in oracle's terms) terminlogoy.  When the setup app asks for 'server' it really means the tns net service name, which is resolved in the tnsnames.ora file.  Also, please note if you haven't already, that you need to have the oracle client installed as well.  The ODBC drivers sit on top of the oracle client.  They do not replace it.

             

            For a discussion of what lies behind your error message see http://edstevensdba.wordpress.com/2011/02/26/ora-12154tns-03505/

             

            Also please note that oracle supplies their own ODBC driver ...

             

            Is it possible for you to use the oracle client directly and not have to use the ODBC driver at all?  If so, you'd be well advised to do so.

            • 3. Re: Connecting with Oracle 10g database from .Net 3.5
              Arun Prasath

              Hi Sybrand, I m asking how to resolve it. I haven't resolved it yet.

              • 4. Re: Connecting with Oracle 10g database from .Net 3.5
                Arun Prasath

                Hi Ed,

                I have installed Oracle Client already and accessing the database via TOAD in the machine on which i'm deploying my .Net application.

                It looks strange that when i run the project from the VisualStudio2008 itself, i m not getting any error. These errors coming when i run it on another machine.

                • 5. Re: Connecting with Oracle 10g database from .Net 3.5
                  EdStevens

                  ArunPrasath wrote:

                   

                  Hi Ed,

                  I have installed Oracle Client already and accessing the database via TOAD in the machine on which i'm deploying my .Net application.

                  It looks strange that when i run the project from the VisualStudio2008 itself, i m not getting any error. These errors coming when i run it on another machine.

                   

                  Did you read the link I gave you, regarding the reported error message?

                  These might help as well.

                   

                  read: http://edstevensdba.wordpress.com/2011/02/09/sqlnet_overview/ Help! I can’t connect to my database 

                  read: http://edstevensdba.wordpress.com/2011/02/16/sqlnet_client_cfg/ Help! I can’t connect to my database (part duex)

                  • 6. Re: Connecting with Oracle 10g database from .Net 3.5
                    Arun Prasath

                    Ed, I've read your first link.

                    It seems, i did everything right.

                    I'm having TNS entry in the machine where i develop the application. And also having TNS entry in the machine where i'm deploying my application.

                    Yet i'm getting the error in the deploying machine alone..

                    • 7. Re: Connecting with Oracle 10g database from .Net 3.5
                      jgarry

                      Look at the [01000] error in your original post.  The microsoft driver doesn't support the request.  Use the Oracle driver.

                       

                      Edit:  To be a little more clear, I'm guessing that the Toad works because it sets the environment appropriately to pick up the correct tns entry it needs.  When your executable tries to set the environment with the MS driver, it fails, hence it can't get the correct entry.

                      • 8. Re: Connecting with Oracle 10g database from .Net 3.5
                        Aman....

                        We can't see what you have on your machines. Show us your tns files.

                         

                        Aman....

                        • 9. Re: Connecting with Oracle 10g database from .Net 3.5
                          EdStevens

                          ArunPrasath wrote:

                           

                          Ed, I've read your first link.

                          It seems, i did everything right.

                          I'm having TNS entry in the machine where i develop the application. And also having TNS entry in the machine where i'm deploying my application.

                          Yet i'm getting the error in the deploying machine alone..

                          Then you have a good base to compare and contrast between the system that works and the one that doesn't.

                          Compare the tnsnames.ora files

                          Compare the sqlnet.ora files

                          Compare the odbc DSN definitions

                          Compare the relevant registry keys.  For Oracle that will be HKLM\software\oracle, and some entries in HKLM\software\odbc

                          • 10. Re: Connecting with Oracle 10g database from .Net 3.5
                            Arun Prasath

                            In both machine i found tnsnames.ora file on $ORACLE_HOME/network/admin.

                            In addition to that I found listener.ora file in the machine which runs the application good.

                            I found sqlnet.ora file in the machine which throw errors.

                            • 11. Re: Connecting with Oracle 10g database from .Net 3.5
                              EdStevens

                              ArunPrasath wrote:

                               

                              In both machine i found tnsnames.ora file on $ORACLE_HOME/network/admin.

                               

                              And what did you discover when you compared them?

                               

                               

                              In addition to that I found listener.ora file in the machine which runs the application good.

                              Is this also the machine that runs the database?  The listener has no meaning on the client side of the connection.

                               

                              I found sqlnet.ora file in the machine which throw errors.

                              Did you not find one on the other?  I suggested that you compare these files between the two machines.    Simply telling me that you found them is pointless.

                              • 12. Re: Connecting with Oracle 10g database from .Net 3.5
                                Arun Prasath

                                Sorry for the improper data Ed..

                                 

                                From both the machines i'm trying to connect to a database which resides in a separate production server.

                                tnsnames.ora files of both machines are same in every letter of that file.


                                The machine which runs the application good having a folder named 'SAMPLE', listener.ora and tnsnames.ora

                                The machine which runs throw errors having a folder named 'SAMPLE', sqlnet.ora and tnsnames.ora

                                • 13. Re: Connecting with Oracle 10g database from .Net 3.5
                                  EdStevens

                                  ArunPrasath wrote:

                                   

                                  Sorry for the improper data Ed..

                                   

                                  From both the machines i'm trying to connect to a database which resides in a separate production server.

                                  tnsnames.ora files of both machines are same in every letter of that file.


                                  The machine which runs the application good having a folder named 'SAMPLE', listener.ora and tnsnames.ora

                                  The machine which runs throw errors having a folder named 'SAMPLE', sqlnet.ora and tnsnames.ora

                                  Obviously, the stuff in the 'samples' directory is just that .. samples.  We are concerned about the files in ORACLE_HOME/network/admin.

                                   

                                  Is the file <ORACLE_HOME>/network/admin/tnsnames.ora  the same from both the good and failing clients?

                                   

                                  Is the file <ORACLE_HOME>/network/admin/sqlnet.ora  the same from both the good and failing clients?

                                  • 14. Re: Connecting with Oracle 10g database from .Net 3.5
                                    jgarry

                                    I don't know about the modern versions, but in the past I've certainly seen invisible characters, spaces or otherwise, ruin a tnsnames.ora.  Also, it's easy to make the dumb mistake of not scrolling down far enough to notice additional entries.  Sometimes regenerating with netca magically fixes things.

                                     

                                    A tnsping should show what it is using.