ADO Interop - SQL Server 2000 connections not released

From: scotthibbs (scotthibbs_at_discussions.microsoft.com)
Date: 08/15/04 

Date: Sun, 15 Aug 2004 07:01:03 -0700

We have an application that uses the following:
   - ASP
   - COM+ components to make data calls via ADODB
   - DB is SQL Server 2000

Here is the common connection VB code:
        cn = New ADODB.Connection
        With cn
            .Provider = "SQLOLEDB.1"
            strConnect = "User ID=xxxxxx;Password=xxxx;Persist Security
Info=False;Initial Catalog=" & Database & ";Data Source=" & SQLServer & ";OLE
DB Services=-1"
            .ConnectionString = strConnect
            .CursorLocation = ADODB.CursorLocationEnum.adUseServer
            .ConnectionTimeout = 10
            .Open()
        End With

When the ASP page is executed...no matter how many calls to the COM+
component..the number of connections to the database stays less than 10 for a
single user. I would expect this behavior since we are specifying ADO
connection pooling (OLE DB Services=-1). We also have confirmend that all
objects, connections, recordsets, etc. are properly set to nothing upon exit.

We now have converted the ASP pages to ASP.NET. The COM+ component has been
converted to .NET...using Interop to continue calling ADODB. ADO.NET does
not have some of the needed functionality..particularly the GetRows() method
we depend on heavily. The ASP.NET page is structured the same as ASP...i.e.
no codebehind.

The problem is that the ASP.NET pages drive the number of connections
with the SQL server to an extraordinary number.....hundreds or thousands for
a single user. I constructed a test page where I repeatedly call the
component 100 times. The connections to SQL Server 2000 continually increase
every time I hit the page. The connections seem to reach some sort of
maximum, but it is not consistent....usually around 500 connections. The
connections will drop off very slowly after 10 or 20 minutes...sometimes
longer.

We have tried OLE DB Services=-4 to disable connection pooling and the
result is very "spiky" connection behavior at the SQL Server...as one would
expect. This, however, would not be an option if we had a production
workload of thousands of users. The server would be too busy without pooling
(-4)....and run out of memory if we turn pooling (-1) back on.

Relevant Pages

  • Re: SQL server connection problem
    ... I've tried to change the user connections to 0, ... "SQL server connection time out. ... The following message appears when restart the server and before login ... I receive Error message: “A connection could not be established to ...
    (microsoft.public.sqlserver.clients)
  • Re: SQL Server 2005
    ... procedure to increase the maximum number of concurrent connections. ... When you successfully connect to the instance of SQL Server 2005, ... I have an application that requires an database to be installed on the ...
    (microsoft.public.sqlserver.setup)
  • Re: TCPv4 Counters "Connetions Active" and "Connections Passive" on W2K3 IIS 6 S
    ... Active is the number of connections that are initiated by the system. ... For the SQL server some indications might be: ... values indicates that active queries are waiting for other queries. ... Also database size, queries and indexes can make huge differences. ...
    (microsoft.public.inetserver.iis)
  • Re: Not able to login in Sharepoint 3.0
    ... In Microsoft SQL Server 2000 SP4 or SQL 2005 you must allow remote connections to SQL for form based authentication to work. ... useFailoverPartner, Boolean& failoverDemandDone, String host, String ...
    (microsoft.public.sharepoint.windowsservices)
  • Re: Linked Table Password
    ... Prompt When Opening Linked Table" - ... recreating the links to the SQL Server ... > relationships again after the automated process. ... These connections can be DSN or DSN-less connections, ...
    (microsoft.public.access.security)