Oracle 8.1.7 client and Visual Studio 2005 connection problem ORA-12537

[henkka]

I have a problem connecting to an Oracle database. On my own desktop I run
Windows XP SP2, Visual Studio 2005 and Oracle client 8.1.7 with the
8.1.7.1.5 patch. I can connect to the database using SQLPlus (username
ownmgr, password mypwd, service upp.sotsa which corresponds to a
tnsnames.ora entry) without any problems but if I try it through VS 2005 I
get the error
ORA-12537
which description in English is something like "Connection broken".

I have tried to get connected through this code
string connectionString;

connectionString = "Data Source=upp.sotsa;User ID=ownmgr;Password=mypwd";

// Assumes connectionString is a valid connection string.

using (OracleConnection connection =

new OracleConnection(connectionString))

{

connection.Open();

// Do work here.

}

and through VS's Server explorer - Data connection, choosing Oracle
database, trying both the Data provider for OLE DB and Oracle. I get the
same ORA-12537 error. Also trying Visual Studio 2003 gives the same error.

I've gone through all the steps in Setting up the environment
http://msdn.microsoft.com/library/d...edb/htm/oledboracleconnectivity_checklist.asp
and especially changing the registry values which pointed to wrong DLL's
(changed them to the oci.dll, orasql18.dll, oraclient8.dll). I've also gave
the Windows group "Users" Read & Execute (or reallyEveryone full control)
for my c:\orant81 folder.

I'm running out of ideas, does anybody got a clue?

Henkka

 

[henkka]

I just figured out the problem. I had created my project in the default
Visual Studio location C:\Documents and Settings\MyName\My Documents\Visual
Studio 2005\Projects\ . This path is too long for the Oracle client. When I
re-created the project in C:\data\ everything worked. Apparently the Oracle
client uses the applicaton path as part of the connection string and the
connection string was too long.

See below (from Oracle support)

Doc ID: Note:263489.1
Subject: Connecting Through Crystal Reports - Fails 2 Out Of
Three Attempts ORA-12537
Type: PROBLEM
Status: PUBLISHED
Content Type: TEXT/X-HTML
Creation Date: 16-FEB-2004
Last Revision Date: 11-MAY-2004



The information in this article applies to:
Oracle Data Provider for .NET - Version: 9.2.0.1 to 9.2.0.4
Oracle Objects for OLE - Version: 8.1.7.3 to 9.2.0.4
Oracle Provider for OLE DB - Version: 8.1.7.0 to 9.2.0.4
Oracle ODBC Driver - Version: 9.2.0.1
Microsoft Windows 2000
Microsoft Windows XP
This error could possibly happen to any Oracle Client Application. The issue
resides in the Listener.
Errors
ORA-12537 TNS:connection closed

Symptoms
ORA-12537 from client side applications. The problem can be intermittant.

Enable sqlnet tracing with timestamping turned on and get traces from
applications that
recieve the error and applications that do not recieve the error. You will
see that the applications
that do not recieve the error have one connection packet sent to the
listener while those that
recieve the ORA-12537 have two connection packets.

Check the listener_log file. There will be no records of any connections
from the application
at the time of the error.

Copy SQLPlus.exe from the ORACLE_HOME\bin directory into the directory that
the client
application exe resides and see if the ORA-12537 can be reproduced there
after several attempts.
Cause
This issue is due to the connection string being large enough that the
connection packet is being
split into two packets and then sent to the listener.
Fix
Since the issue is caused by a large connection string there are several
workarounds
for the issue.

1. Install the exe in a shorter named directory.
2. Modify the TNS Names entry so that it is shorter.
3. Replace domain names with IP Addresses if that shortens and vice versa.
4. Remove any parameters from the entry that are not needed.
5. Patch the Database Listener to the most recent version.
References