ADO Interop - SQL Server 2000 connections not released
From: scotthibbs (scotthibbs_at_discussions.microsoft.com)
Date: 08/15/04
- Next message: donotspam: "Advanced SQL generation options are disabled"
- Previous message: Lubomir: "How to initialize datagrid with a row filter?"
- Next in thread: Jerry Pisk: "Re: ADO Interop - SQL Server 2000 connections not released"
- Reply: Jerry Pisk: "Re: ADO Interop - SQL Server 2000 connections not released"
- Messages sorted by: [ date ] [ thread ]
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.
- Next message: donotspam: "Advanced SQL generation options are disabled"
- Previous message: Lubomir: "How to initialize datagrid with a row filter?"
- Next in thread: Jerry Pisk: "Re: ADO Interop - SQL Server 2000 connections not released"
- Reply: Jerry Pisk: "Re: ADO Interop - SQL Server 2000 connections not released"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
