C# Web Service in IIS Stops Connecting to Oracle (via ADO.NET) Over Time

I have a Web Service, written in C#, published on
a Windows XT Web Server under IIS, which
repeatedly connects to an Oracle 9x database via
ADO, processes, disconnects from the database, and
exits.

The Web Service is triggered by our Tibco
messaging system; in our test environment, it is
invoked every ten minutes. The trouble is, it will
run for hours -- Connect, Process, Disconnect,
Exit -- then, eventually it will fail on the
Connect: it will throw an exception while trying
to execute the OracleConnection.Open method (if I
remember correctly, I don't have the output with
me at the moment), and I am not yet getting any
specific Oracle error message or number. All
connection attempts after that point fail
immediately.

If we restart IIS, it returns to normal running
(for a while, that is.)

Also, another Web service, which runs serially after
the failing one and has been running successfully all
this time, will also start failing in its connections
to Oracle (once the other Web Service has failed.)

There have been instances where the 2nd Web Service
has been the one to initiate the failures, seeming
to eliminate one specific Service as the problem.

My question: assuming that the Web Services'
Connects (Opens) are all paired with associated
Disconnect (Closes), is there something (some
resource, perhaps) in IIS or Oracle which can be
consumed over time, or iterations? It seems as if
the Web Service, although it exits, is running out
of something or corrupting something, which can
only be repaired by restarting IIS. And the effect
appears to be IIS-wide -- at least within the default
application space -- since the other process
fails (database connects) after the one failure.

Would anyone have any ideas?

Thanks,

GHuston

On Wed, 07 Nov 2007, Temporary (AT) yahoo (DOT) com wrote:

I would isolate and prove this out. Not just by looking at the code but
by having the app first connect then you find its session in the
database, then have the app close and see if that session actually does
go away.

Here is a query you can use (I have this in my SQLPATH and cut-n-pasted it here):

select t3.SPID, chr(39) || t1.sid || ',' || t1.serial# || chr(39) || ';' kill,
t1.username || ':' || t1.machine || ':' || t1.program userurl
,to_char(logon_time,'MM/DD/YYYY:HH:MI:SS') || ':' || status, sql_text
from v$session t1
,v$sqlarea t2
,v$process t3
where t1.SQL_ADDRESS = t2.address (+)
and upper(t1.program) not like '%ORACLE%'
and t3.ADDR = t1.PADDR
order by t1.machine,t1.username
;

So, do the following:

1) Run the query and keep the output for comparison.
2) Have the app connect and start processing.
3) While the app is executing, run the query and find the connection
and make sure this is the connection the app is using.
4) Wait until the app signals it has disconnected.
5) Run the query and see if the previously identified connection is
actually gone from the database.

This will prove/disprove your above assumption.

If there are different connection paths, you should do the above test
until all ways to connect and disconnect have been tested.

--
Galen Boyer

Thanks, Galen, for the reply. I will try your suggestion (of course, I
probably only have access to these views on our Development system, but
that will do as well as any.)

Thanks again,

GHuston

Galen Boyer <galen_boyer (AT) yahoo (DOT) com> wrote in news:umytkbq2o.fsf (AT) rcn (DOT) com: