Inactive connections to Oracle with ADO and OLE DB

[Mark Wager-Smith]

I am having a problem dropping connections to Oracle when using
ADO to connect to the database. I have COM objects connecting to the
database just long enough to do their work, then issuing the
Connection.Close() method. Everything works fine, but the connections in
Oracle remain around (although INACTIVE). Soon, my SESSIONS_PER_USER
limit is reached and my application stops working.

How can I get those connections to drop? The DBA has set the IDLE_TIME to
20 minutes, and the SESSIONS_PER_USER to 70.

I have the web site configured to run in its own memory space which is
causing MTS to create and manage the objects. I think that MTS is trying
to use connection pooling, but is not doing so correctly. On ocassion, I will
see some of the INACTIVE processes go ACTIVE, then back to INACTIVE. This
tells me that some kind of connection pooling is going on, doesn't it? More
often than not, however, a new connection is created rather than use an existing
INACTIVE one.

How can I get connection pooling to work the way its supposed to?

We used to use Microsoft's ODBC driver for Oracle, but the performance was
abismal on our network. ODBC required way too much overhead for us to use
in a production environment, but the connection pooling worked very well.
This problem only surfaced when we switched to OLE DB.

we are now using Oracle's OLE DB provider as we are getting much better
performance than with Microsoft's and some of our SQL statements break
with Microsoft's OLE DB Provider. Not sure yet why, but I think I must be
using some feature that is only available with Oracle's Provider.

The versions in our installation is as follows:

IIS 4
NT 4.0 SP6a
MDAC 2.5
Oracle 8.1.7 (8i server & client)

Any help would be greatly appreciated. Please respond also to
mws...@IntuitiveWebDesigns.com

Thank you,

Mark Wager-Smith

[Rob Kellington]

I don't have a solution to this - just that we just discovered the
same thing occuring today. In our situation, it appears that it
involves INSERT connections. Regular selects/updates/etc seem to be
freed up properly. But inserts hang around.

Does anyone have an idea on how to force the disconnect?

Rob Kellington
Edmonton

Mark Wager-Smith <mws...@IntuitiveWebDesigns.com-nospam> wrote in message news:<qsktltg1aki55le2q...@4ax.com>...

[Rob Kellington]

The latest version of Oracle Provider have solved this problem. We
tested it today and it seems fine. I got the latest from MetaLink:

ID:818737 Patchset::1862949 README
ORACLE PROVIDER FOR OLE DB PATCH VERSION 8.1.7.2.0
Product:Provider for OLE DB
Platform:MS Windows NT/2000 Server
Language:American English
Version:8.1.7.2
Last updated:02-JUL-2001
Size: 42M (44257263 bytes)

Hope that helps.