• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 3151
  • Last Modified:

TNS: Connection Closed

I searched this website, used google and ask.com but couldn't find a solution.

I wrote a console application that connects to Oracle 8i database another ASP.NET application connecting to the same database executing the same query. I got an error while running the console application TNS: connection closed and unhandled exception but the ASP.NET application worked fine!

Weeks ago, the database administrator downloaded Oracle 8.1.7 on my machine (Windows 2000) so now I have Oracle 8.0 and 8.1.7. Could that be the problem? But then why did it work with asp.net application?
0
uz3a
Asked:
uz3a
  • 4
  • 2
1 Solution
 
jacobhooverCommented:
More info needed:

1) What provider are you using?
2) What does your connection string look like?
3) What are you trying to do? (The ASP.Net page works, what's the purpose of the console app.?)
4) Can you show the section of code that's failing?
5) What is the exact error message? (Oracle errors are xxx-xxxxx, like TNS-00100)
0
 
uz3aAuthor Commented:
1) Microsoft OLE DB Provider for Oracle

2) Connection string is "Provider=MSDAORA.1;User ID=hanan;Data Source=hanan;Password=hanan"

3) The console application is to be scheduled to run monthly to retrieve a report from database and email it through MS exchange. I used the ASP.NET page to check wether it's only the console application or any other app carrying the same task.

4) The failing code is where I open the connection
 Private Function queryDB() As OleDbDataReader()
        Dim conn As OleDbConnection
        Dim comd As OleDbCommand
        Try
            conn = New OleDbConnection(connStr)
            comd = New OleDbCommand

            'It fails here because it calls conn.open
            If conn.State = ConnectionState.Closed Then conn.Open()
            comd.Connection = conn
            comd.CommandType = CommandType.Text

            Dim readers(1) As OleDbDataReader
            comd.CommandText = EXPIRED_INS
            readers(0) = comd.ExecuteReader(CommandBehavior.CloseConnection)

           Return readers
        Catch ex As Exception
            logAction(ex.Message, ex)
            Return Nothing
        End Try
    End Function

5) The error is "ORA-12537: TNS:connection closed"
{System.Data.OleDb.OleDbException}
    [System.Data.OleDb.OleDbException]: {System.Data.OleDb.OleDbException}
    HelpLink: Nothing
    InnerException: Nothing
    Message: "ORA-12537: TNS:connection closed"
    Source: "Microsoft OLE DB Provider for Oracle"
    StackTrace: "   at System.Data.OleDb.OleDbConnection.ProcessResults(Int32 hr)
   at System.Data.OleDb.OleDbConnection.InitializeProvider()
   at System.Data.OleDb.OleDbConnection.Open()
   at TRListener2.TRListener.queryDB() in C:\Documents and Settings\hmhassan\My Documents\Visual Studio Projects\TRListener2\TRListener.vb:line 77"
    TargetSite: {System.Reflection.RuntimeMethodInfo}
0
 
jacobhooverCommented:
Ok, a few more questions:

1) Is the console app and the web app running on the same machine?
2) Is the Database running on the same machine or a diferrent machine?
3) Is the server a *inux server or a Windows server?

The TNSNAMES.ORA file (client) and LISTENER.ORA file (server) would also help in diagnosing the issue.
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
uz3aAuthor Commented:
1) Yes they are.
2) another machine.
3) windows server IIS 5


TNSNAMES.ORA :
IHADB.WORLD =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = xx.xx.xx.xx)(PORT = xxxx))
    (CONNECT_DATA = (SID = db_name))
  )

LISTENER.ORA:
LISTENER =
  (ADDRESS_LIST =
    (ADDRESS = (PROTOCOL = IPC)(KEY = oracle.world))
    (ADDRESS = (PROTOCOL = IPC)(KEY = ORCL))
    (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC0))
    (ADDRESS = (COMMUNITY = NMP.world)(PROTOCOL = NMP)(SERVER = servername)(PIPE = ORAPIPE))
    (ADDRESS = (PROTOCOL = TCP)(Host = mymachine)(Port = 1521))
    (ADDRESS = (PROTOCOL = TCP)(Host = mymachine)(Port = 1526))
    (ADDRESS = (PROTOCOL = TCP)(Host = 127.0.0.1)(Port = 1521))
    (ADDRESS = (PROTOCOL=IPC)(KEY=TRAN))
  )

CONNECT_TIMEOUT_LISTENER = 10

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (GLOBAL_DBNAME = mymachine)
      (SID_NAME = ORCL)
    )
    (SID_DESC =
      (SID_NAME = extproc)
      (PROGRAM = extproc)
    )
    (SID_DESC = (SID_NAME=TRAN))
  )

TRACE_LEVEL_LISTENER = 0

0
 
jacobhooverCommented:
What port and SID is being used in the TNSNAMES.ORA:

IHADB.WORLD =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = xx.xx.xx.xx)(PORT = xxxx))
    (CONNECT_DATA = (SID = db_name))
  )

From my experience it should be something like:

IHADB.WORLD =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = xx.xx.xx.xx)(PORT = 1521))
    (CONNECT_DATA = (SID = ORCL))
  )

and the listener.ora (as a TEST case) like:
LISTENER =
  (ADDRESS_LIST =
    (ADDRESS = (PROTOCOL = TCP)(Host = mymachine*)(Port = 1521))
  )
CONNECT_TIMEOUT_LISTENER = 10

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (GLOBAL_DBNAME = mymachine*)
      (SID_NAME = ORCL)
    )
  )

TRACE_LEVEL_LISTENER = 0

* = Note, this should be the name of the PC that has the Oracle server installed on it.
** Also note I SEVERLY trimmed down the listener.ora file.  I personally only put enteries in my
listener.ora when they are REQUIRED.  We can add them back later on, but for this test case, it
will help us isolate the problem.

<Cheated and used MetaLink>
TCP connection fails with ORA-12537: TNS:connection closed.
Listener.log at server does not show an entry.

Solution Description
--------------------

Review client tnsnames.ora and server listener.ora to make sure that
the port requested by the client is using the correct port at the server,
and that the port has the correct protocol.

In this example, the client was attempting to make a TCP connection
to a port that was defined using the IIOP presentation at the server.
Of course, if no listener process were running on the incorrect port,
an ORA-12541 "no listener" error would have been reported.
0
 
jacobhooverCommented:
uz3a, Have you had a chance to test this?
0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 4
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now