Conneciton must be open for this operation

[=?Utf-8?B?U3VzYW4=?=]

Our application is written in C# with Visual Studio 2005 using Oracle Data
Access Component (ODAC) 10.2.0.2.21 and Oracle Client 10.2.0.1.0 for
connection to the Oracle database. Specifically, the window's application is
using Oracle.DataAccess.dll 2.102.2.20. The windows application is served to
the user through Citrix. Since we pushed out a new version of the application
two weeks ago, I have seen a lot of "Connection must be open for this
operation" messages in the application log (I implemented log4Net rolling
file appender). The result to the user is that their user profile is getting
corrupted and the updates they are making to data are not persisting. An
Oracle error is not being thrown except for the one above. The code and
stored procedures for the user profile have not changed in well over a year.
The last change that would affect it was when we upgraded the ODAC and Oracle
Client back in January. Till this deployment two weeks ago, the users were
not having any problem. During testing, with the new application, we did not
have any problems.The machines hosting the windows application were brand new
out of the box when we installed the ODAC and Oracle Client so there are no
previous versions of Oracle on the machines. The tnsnames.ora and sqlnet.ora
files have not been altered. No changes to the host machines were made,
except for the updated version of our application since January. No settings
in Citrix have been altered. In searching different forums,
• I have checked the system32 folder and there are no Oracle files in there.
• I tried removing the Oracle.DataAccess.dll file from the installation
directory, but that just resulted in our application not working.

The only other alteration is that we are allowing another application to
"share" two of our tables. They are using a trigger from their side to update
the two tables using a DBLink. The table with the product updates is one of
the two tables that we are sharing with them, but I don't believe that they
can even see the table where we store the user profiles. A coworker of mine
has suggested altering some of the connection string settings as the other
application sharing tables with us may be hogging resources. For instance, we
are not setting the Min Pool Size, Connection Lifetime, Connection Timeout,
Incr Pool Size, or Decr Pool Size when we open a connection to Oracle from
the application, but have the ability to set them from the application config
file so that they will be used. Any suggstions on what numbers to use for
these? We have less than 500 users on at a time and the other application is
a lot less popular so has less than 100 users on at once, but I don't know
how they handle connections.

Any ideas? I'm really at the point where I'm pulling my hair.

Thank you,
Susan

[=?UTF-8?B?R8O2cmFuIEFuZGVyc3Nvbg==?=]

Susan wrote:

Our application is written in C# with Visual Studio 2005 using Oracle Data
Access Component (ODAC) 10.2.0.2.21 and Oracle Client 10.2.0.1.0 for
connection to the Oracle database. Specifically, the window's application is
using Oracle.DataAccess.dll 2.102.2.20. The windows application is served to
the user through Citrix. Since we pushed out a new version of the application
two weeks ago, I have seen a lot of "Connection must be open for this
operation" messages in the application log (I implemented log4Net rolling
file appender). The result to the user is that their user profile is getting
corrupted and the updates they are making to data are not persisting. An
Oracle error is not being thrown except for the one above. The code and
stored procedures for the user profile have not changed in well over a year.
The last change that would affect it was when we upgraded the ODAC and Oracle
Client back in January. Till this deployment two weeks ago, the users were
not having any problem. During testing, with the new application, we did not
have any problems.The machines hosting the windows application were brand new
out of the box when we installed the ODAC and Oracle Client so there are no
previous versions of Oracle on the machines. The tnsnames.ora and sqlnet.ora
files have not been altered. No changes to the host machines were made,
except for the updated version of our application since January. No settings
in Citrix have been altered. In searching different forums,
• I have checked the system32 folder and there are no Oracle files in there.
• I tried removing the Oracle.DataAccess.dll file from the installation
directory, but that just resulted in our application not working.

The only other alteration is that we are allowing another application to
"share" two of our tables. They are using a trigger from their side to update
the two tables using a DBLink. The table with the product updates is one of
the two tables that we are sharing with them, but I don't believe that they
can even see the table where we store the user profiles. A coworker of mine
has suggested altering some of the connection string settings as the other
application sharing tables with us may be hogging resources. For instance, we
are not setting the Min Pool Size, Connection Lifetime, Connection Timeout,
Incr Pool Size, or Decr Pool Size when we open a connection to Oracle from
the application, but have the ability to set them from the application config
file so that they will be used. Any suggstions on what numbers to use for
these? We have less than 500 users on at a time and the other application is
a lot less popular so has less than 100 users on at once, but I don't know
how they handle connections.

Any ideas? I'm really at the point where I'm pulling my hair.

Thank you,
Susan

How are you reading the data from the database?

The error message is the same or similar to what you get if you try to
use a DataReader after you have closed the database connection. If you
have a small result set so that it all fits in the buffer, it might be
able to use a DataReader after the connection has been closed, but this
can stop working at any time if the data grows by a few bytes.

--
Göran Andersson
_____
http://www.guffa.com