Re: ADO Interop - SQL Server 2000 connections not released

Tech-Archive recommends: Fix windows errors by optimizing your registry

From: Jerry Pisk (jerryiii_at_hotmail.com)
Date: 08/15/04 

Date: Sun, 15 Aug 2004 09:45:43 -0700

Honestly - what exactly did you expect? You're not closing your connections
so they will just keep growing until the garbage collector kicks in.

Jerry

"scotthibbs" <scotthibbs@discussions.microsoft.com> wrote in message
news:3A86FE73-3FEB-43D9-ADF0-CC08EC8CA6E8@microsoft.com...
> 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

  • ADO Interop - SQL Server 2000 connections not released
    ... - DB is SQL Server 2000 ... When the ASP page is executed...no matter how many calls to the COM+ ... objects, connections, recordsets, etc. are properly set to nothing upon exit. ... We have tried OLE DB Services=-4 to disable connection pooling and the ...
    (microsoft.public.data.ado)
  • ASP 145 Error-- New Application Failed
    ... I am getting HTTP1.1/ New Application Failed error. ... Old environment which worked OK: ... ODBC connection from ASP to SQL Server: ... Inefficient use of database connections ...
    (microsoft.public.inetserver.asp.general)
  • ASP 145- HTTP/1.1: New Application Failed
    ... I am getting HTTP1.1/ New Application Failed error. ... Old environment which worked OK: ... ODBC connection from ASP to SQL Server: ... Inefficient use of database connections ...
    (microsoft.public.de.inetserver.iis.asp)
  • ASP 145- HTTP/1.1: New Application Failed
    ... I am getting HTTP1.1/ New Application Failed error. ... Old environment which worked OK: ... ODBC connection from ASP to SQL Server: ... Inefficient use of database connections ...
    (microsoft.public.inetserver.iis)
  • Re: db in Single User mode yet can be accessed via odbc
    ... See my previous post on how to set the single_user at database level. ... > you r right i am referring the the single user on ... >>mode of the whole SQL Server. ... Maybe other connections are to other ...
    (microsoft.public.sqlserver.server)