Velocity Reviews - Computer Hardware Reviews

Velocity Reviews > Newsgroups > Programming > ASP .Net > ASP.NET 2.0, Enterprise Library 2006 and SQL 7 connection problems

Reply
Thread Tools

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

 
 
=?Utf-8?B?Z2FsaWNv?=
Guest
Posts: n/a
 
      07-03-2006
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
 
Reply With Quote
 
 
 
 
Steven Cheng[MSFT]
Guest
Posts: n/a
 
      07-04-2006
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.)




 
Reply With Quote
 
 
 
 
=?Utf-8?B?Z2FsaWNv?=
Guest
Posts: n/a
 
      07-04-2006
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<BCP> list = 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.)
>
>
>
>
>

 
Reply With Quote
 
Steven Cheng[MSFT]
Guest
Posts: n/a
 
      07-05-2006
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.)



 
Reply With Quote
 
=?Utf-8?B?Z2FsaWNv?=
Guest
Posts: n/a
 
      07-05-2006
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.)
>
>
>
>

 
Reply With Quote
 
Steven Cheng[MSFT]
Guest
Posts: n/a
 
      07-06-2006
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.)




 
Reply With Quote
 
=?Utf-8?B?Z2FsaWNv?=
Guest
Posts: n/a
 
      08-01-2006
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.)
>
>
>
>
>

 
Reply With Quote
 
Steven Cheng[MSFT]
Guest
Posts: n/a
 
      08-02-2006
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

 
Reply With Quote
 
aashishgandhi aashishgandhi is offline
Junior Member
Join Date: Nov 2007
Posts: 1
 
      11-13-2007
We are facing a similar issue. When we were using .NET Framework v 1.1 and connecting to SQL Server 7, we didn't have any issues, but when we moved to v 2.0, we started getting this error -
An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server)
It's not that we are able to connect at all, but only at certain times we are getting this error probably due to increase in the load, but not sure.
 
Reply With Quote
 
 
 
Reply

Thread Tools

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
Enterprise Library 2006 -- Calling Oracle Procedure with Parameters TattedProgrammer ASP .Net 0 08-31-2006 03:22 PM
Enterprise Library for .NET Framework 2.0 - January 2006 rockdale ASP .Net 7 08-29-2006 02:15 PM
ASP.NET Exception Handling with the Enterprise Library (Jan 2006) Wade ASP .Net 3 07-25-2006 05:20 AM
What Happened to Sql Enterprise Manager and Sql Query Analyser in Visual Studio 2005 Edward ASP .Net 4 04-10-2006 04:15 PM
Enterprise Library Jan 2006 - custom exception formatter fails unless running debugger David Herbst ASP .Net 1 01-29-2006 04:02 PM



Advertisments