ASP.NET 2.0, Enterprise Library 2006 and SQL 7 connection problems

[galico]

Hi All,

We are having a very strange problem with the above. We have designed an
application in ASP.NET 2.0 that uses the enterprise library data application
blocks amongst others. We seem to be maxing out the number of connections
allowed to the sql server 7 server after running a few large queries through
the application? There are less than 1000 rows of data in the database and we
are at a loss as to how to fix this, does anyone have any ideas?

Thanks in advance

Andy

[Steven Cheng[MSFT]]

Hi Andy,

Welcome to the ASPNET newsgroup.

From your description, I understand you're developing an ASP.NET 2.0 web
application which use the enterprise library 2.0 (the data application
block) to connect a sqlserver 7.0 database. However, you found that the sql
server is suffering a connection booming issue when the ASP.NET application
running, correct? If anything I missed , please feel free to correct me.

As for the ADO.NET data accessing, the underlying database connections are
generally managed by the specific data access provider. For your scenario,
I think you're using the sqlserver provider(.net managed provider)
,correct? Also, since you're using data application block in the enterprise
library, all the ADO.NET connection management pattern are done by the
application block, it should not suffer any obvious connection leak issue.
Anyway, for basic troubleshooting, we can first isolate the problem
scenario and here are something we can check first:

1. what's the connection string (use sql or windows authentication), for
sqlserver provider, when the connection pool is based on connectionstring,
when using sql quthentication(embeded username/password in
connectionstring) the connection pool's has best effiency. When using
windows authentication, it will use subpools (according to different login
user). You can check your one and verify whether the issue is specific to a
certain authentication type.

2. whether the issue is data applcationblock (entlib). for testing, you can
try locate the code period which will result the connection booming and
them use pure ADO.NET code(without using enterprise library) to see whether
the problem is specific to entlib.

3. use sql profiler to trace teh connection states at sqlserver side.
For monitoring the connection behavior at sqlserver side(if you have the
sufficient privillege), sql profiler is a good tool to trace the connection
or execution operations.

Hope this helps some. If there is any other findings, please feel free to
post here.

Regards,

Steven Cheng
Microsoft MSDN Online Support Lead
==================================================

When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.

==================================================
This posting is provided "AS IS" with no warranties, and confers no rights.

Get Secure! www.microsoft.com/security
(This posting is provided "AS IS", with no warranties, and confers no
rights.)

[galico]

Hi Steven,

Thanks for your help. Here is a sample of the code we use to call the
enterprise library, followed by our connection string. I'll try your
suggestions, please advise of anything else you think of.

Code
------
//Use the Enterprise Library DatabaseFactory to fetch the BCP
Database db = DatabaseFactory.CreateDatabase();

//Scope the DataReader so it will be disposed after the read
is complete
using (IDataReader reader = db.ExecuteReader("FetchSetBCP",
this.BCPID, this.CCMID,

this.ChangeTypeID,this.DateCompleted,this.Title,th is.RequiredDate,this.AreaID,

this.ChangeDescription,this.BenefitDescription,thi s.TotalAnualBenefit,
this.CategoryID, this.AlreadyDiscussed,
this.HeatRef, this.DateSubmitted, this.BCPReference,
this.BenefitOwner,this.Confidential,this.RejectNot es,
this.NonFinancialBenefits,this.CompletedBy))
{
List<BCPlist = new List<BCP>();

while (reader.Read())
{
BCP bcp = new BCP();

//fill the BCP properties
fillProperties(reader, bcp);

list.Add(bcp);
}
return list;
}

end code
----------

connection string
-------------------
<add name="CCM" connectionString="Data Source=SQLCAR002;Initial
Catalog=CCM;User Id=CCMDBUser;password=password; pooling=false;"
providerName="System.Data.SqlClient"/>

Thanks,

Andy

"Steven Cheng[MSFT]" wrote:

Hi Andy,

Welcome to the ASPNET newsgroup.

From your description, I understand you're developing an ASP.NET 2.0 web
application which use the enterprise library 2.0 (the data application
block) to connect a sqlserver 7.0 database. However, you found that the sql
server is suffering a connection booming issue when the ASP.NET application
running, correct? If anything I missed , please feel free to correct me.

As for the ADO.NET data accessing, the underlying database connections are
generally managed by the specific data access provider. For your scenario,
I think you're using the sqlserver provider(.net managed provider)
,correct? Also, since you're using data application block in the enterprise
library, all the ADO.NET connection management pattern are done by the
application block, it should not suffer any obvious connection leak issue.
Anyway, for basic troubleshooting, we can first isolate the problem
scenario and here are something we can check first:

1. what's the connection string (use sql or windows authentication), for
sqlserver provider, when the connection pool is based on connectionstring,
when using sql quthentication(embeded username/password in
connectionstring) the connection pool's has best effiency. When using
windows authentication, it will use subpools (according to different login
user). You can check your one and verify whether the issue is specific to a
certain authentication type.

2. whether the issue is data applcationblock (entlib). for testing, you can
try locate the code period which will result the connection booming and
them use pure ADO.NET code(without using enterprise library) to see whether
the problem is specific to entlib.

3. use sql profiler to trace teh connection states at sqlserver side.
For monitoring the connection behavior at sqlserver side(if you have the
sufficient privillege), sql profiler is a good tool to trace the connection
or execution operations.

Hope this helps some. If there is any other findings, please feel free to
post here.

Regards,

Steven Cheng
Microsoft MSDN Online Support Lead
==================================================

When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.

==================================================
This posting is provided "AS IS" with no warranties, and confers no rights.

Get Secure! www.microsoft.com/security
(This posting is provided "AS IS", with no warranties, and confers no
rights.)

[Steven Cheng[MSFT]]

Thanks for your response Andy,

From the code snippet you provided, the code logic is correct and there is
no particular code problem here. And you've put datareader in using ()
block which may ensure the datareader be disposed. Also, the Enterprise
library/Data App block will also ensure that connection be closed after
reader be closed(in the ExecuteReader method). So I don't think this is a
entlib specific issue, but to verify this you can still use some standard
ADO.NET code(without using entlib) to perform the query from the sqlserver
database to see whether it still suffer the issue.

Also, make sure to perform some sql profiler trace at the sqlserver
server-side to see the actual connection open/close behavior when it
suffers the problem.

In addition, from the connectionstring you provided, the connection pooling
ins turned off, have you tried turn it on to see whether the behavior has
any difference?

=========
<add name="CCM" connectionString="Data Source=SQLCAR002;Initial
Catalog=CCM;User Id=CCMDBUser;password=password; pooling=false;"
providerName="System.Data.SqlClient"/>
=========

Thanks & Regards,

Steven Cheng
Microsoft MSDN Online Support Lead
==================================================

When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.

==================================================
This posting is provided "AS IS" with no warranties, and confers no rights.

Get Secure! www.microsoft.com/security
(This posting is provided "AS IS", with no warranties, and confers no
rights.)

[galico]

Hi Steven,

Thanks for the peer check! i appreciate it, and for your suggestion, we 'll
have a look at that.

I thought i should bring you up to speed on some of our own testing that we
have done. We have connected the application to a SQL 2000 version of the
database and can't seem to get the error, could it be a SQL 7 and .NET 2.0
issue?

We have also raised a Premier Support incident (SRQ060704601731) here in the
UK, i don't know if you can liase with the people helping us or not, we have
not had anything really useful back yet though.

Thanks,

Andy
"Steven Cheng[MSFT]" wrote:

Thanks for your response Andy,

From the code snippet you provided, the code logic is correct and there is
no particular code problem here. And you've put datareader in using ()
block which may ensure the datareader be disposed. Also, the Enterprise
library/Data App block will also ensure that connection be closed after
reader be closed(in the ExecuteReader method). So I don't think this is a
entlib specific issue, but to verify this you can still use some standard
ADO.NET code(without using entlib) to perform the query from the sqlserver
database to see whether it still suffer the issue.

Also, make sure to perform some sql profiler trace at the sqlserver
server-side to see the actual connection open/close behavior when it
suffers the problem.

In addition, from the connectionstring you provided, the connection pooling
ins turned off, have you tried turn it on to see whether the behavior has
any difference?

=========
<add name="CCM" connectionString="Data Source=SQLCAR002;Initial
Catalog=CCM;User Id=CCMDBUser;password=password; pooling=false;"
providerName="System.Data.SqlClient"/>
=========

Thanks & Regards,

Steven Cheng
Microsoft MSDN Online Support Lead
==================================================

When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.

==================================================
This posting is provided "AS IS" with no warranties, and confers no rights.

Get Secure! www.microsoft.com/security
(This posting is provided "AS IS", with no warranties, and confers no
rights.)

[Steven Cheng[MSFT]]

Thanks for your followup Andy,
We have connected the application to a SQL 2000 version of the
database and can't seem to get the error, could it be a SQL 7 and .NET 2.0
issue?
=======================
Yes, actually this is the first thing I've tried checking in our internal
case library and I didn't find any known issue of connecting SQL Server 7.0
with .net framework.
We have also raised a Premier Support incident (SRQ060704601731) here in
the
UK, i don't know if you can liase with the people helping us or not, we
have
not had anything really useful back yet though.
===============================
I think this the most efficient way since the product support engineer will
help you perform some thorough throubleshooting. And I think they'll also
ask you to capture some trace log since this will be important.

So far I haven't any further information about this issue since this is
likely a project specific one. Anyway, if you got any new update from the
product team, please feel free to post here.

Regards,

Steven Cheng
Microsoft MSDN Online Support Lead
==================================================

When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.

==================================================
This posting is provided "AS IS" with no warranties, and confers no rights.

Get Secure! www.microsoft.com/security
(This posting is provided "AS IS", with no warranties, and confers no
rights.)

[galico]

Hi Steven,

Just thought i'd give you an update. This may be beneficial to other users
here too. Since we moved the application database from SQL Server 7.0 to SQL
Server 2000 the problem has not occured. We believe that there are issues
with ASP.NET 2.0/ADO.NET 2.0/Enterprise Library 2006 and SQL Server 7.0.

The application also runs much quicker. Just thought i should let you know.

Andy

"Steven Cheng[MSFT]" wrote:

Thanks for your followup Andy,
We have connected the application to a SQL 2000 version of the
database and can't seem to get the error, could it be a SQL 7 and .NET 2.0
issue?
=======================
Yes, actually this is the first thing I've tried checking in our internal
case library and I didn't find any known issue of connecting SQL Server 7.0
with .net framework.
We have also raised a Premier Support incident (SRQ060704601731) here in
the
UK, i don't know if you can liase with the people helping us or not, we
have
not had anything really useful back yet though.
===============================
I think this the most efficient way since the product support engineer will
help you perform some thorough throubleshooting. And I think they'll also
ask you to capture some trace log since this will be important.

So far I haven't any further information about this issue since this is
likely a project specific one. Anyway, if you got any new update from the
product team, please feel free to post here.

Regards,

Steven Cheng
Microsoft MSDN Online Support Lead
==================================================

When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.

==================================================
This posting is provided "AS IS" with no warranties, and confers no rights.

Get Secure! www.microsoft.com/security
(This posting is provided "AS IS", with no warranties, and confers no
rights.)

[Steven Cheng[MSFT]]

Hi Andy,

Thanks for your followup and the updated information. It is really
appreciated and will benifit other community member who encountering the
similar problem.

Have a good day!

Sincerely,

Steven Cheng

Microsoft MSDN Online Support Lead