sql server connect: Hi Guys,

I developed a web application in asp.net. This application has been giving
the users problems as detailed below. Can anyone suggest a solution or why
this is being caused? This error occurs at random and there are approx.
10-15 users at a time. We are using the MSDE version of MSSQL.

Any help will be greatly appreciated.


Kind Regards
Warren

Details of error:


You have encountered an error,
following are the details:



Error message: Exception of type System.Web.HttpUnhandledException was
thrown.

Error source: System.Web

Target site: Boolean HandleError(System.Exception)

Stack trace:
at System.Web.UI.Page.HandleError(Exception e)
at System.Web.UI.Page.ProcessRequestMain()
at System.Web.UI.Page.ProcessRequest()
at System.Web.UI.Page.ProcessRequest(HttpContext context)
at
System.Web.CallHandlerExecutionStep.System.Web.HttpApplication+IExecutionSte
p.Execute()
at System.Web.HttpApplication.ExecuteStep(IExecutionStep step,
Boolean& completedSynchronously)

Error message: There is already an open DataReader associated with
this Connection which must be closed first.

Error source: TGPS_Business_Component

Target site: System.Data.DataTable execute_sp(System.String,
System.Data.SqlClient.SqlParameter[], System.Data.SqlClient.SqlTransaction)

Stack trace:
at TGPS_Business_Component.C_DAL.execute_sp(String SP, SqlParameter[]
spParams, SqlTransaction tran)
at TGPS_Business_Component.C_DAL_Grower.Search_By_Surname(String
criteria)
at TGPS_Business_Component.C_Grower.Search_Surname(String criteria)
at TGPS_Web_Interface.grower_search.btnsearch_Click(Object sender,
EventArgs e)
at System.Web.UI.WebControls.Button.OnClick(EventArgs e)
at
System.Web.UI.WebControls.Button.System.Web.UI.IPostBackEventHandler.RaisePo
stBackEvent(String eventArgument)
at System.Web.UI.Page.RaisePostBackEvent(IPostBackEventHandler
sourceControl, String eventArgument)
at System.Web.UI.Page.RaisePostBackEvent(NameValueCollection postData)
at System.Web.UI.Page.ProcessRequestMain()

Hi Warren,

From the callstacks and error infos you provided, especially the following
lines
==============================
Error message: There is already an open DataReader associated with
this Connection which must be closed first.
==============================

I think there are two possible causes(since your SQLSERVER is MSDE rather
than server version)
1. There is any code in your web app which doesn't close and release the
opened connection correctly after finish the db maniplulation. For example,
when you're not using the
try{...}catch(){..}finally{release db conn}
or
using(conn = new connection)
{

}

to ensure the release of connection resource, when there is exception occur
during the code, the connection may be locked. this is a potential cause.
So please check this in your code first.

2. Since the MSDE version is a desktop engine, it has its limited
concurrent connections , not sure whether the problem is caused when there
is too many concurrent request on the db manipulate page, you can also have
a check. And here is a tech article discussing on using MSDE in web
application:

#Using MSDE 2000 in a Web Application
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnmsde2kwrk
/html/msde2000webapp.asp

You can have a look to see whether it helps. Also, if you have any new
findings, please feel free to post here. Thanks.

Regards,

Steven Cheng
Microsoft Online Support

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

Get Preview at ASP.NET whidbey
http://msdn.microsoft.com/asp.net/whidbey/default.aspx

Hi Warren,

Based on your descriptions, I understood that you encounter that error
randomly when there are 10-15 users that the same time. Have I understood
you? If there is anything I misunderstood, please feel free to let me know:)

This issue may be ASP.NET relatively so that I will co-working with some
specialists in ASP.NET. The following is my views on MSSQL side.

I am not sure whether those 10-15 users will interact with MSDE instance at
the same time. Performance throttling will occur when there are more than
five concurrent workload batches in progress. (MSDE 2000a allow 32,767
connections to an instance of the database engine, but only 5 concurrent
ones.)

The SQL Server 2000 Workload Governor
http://msdn.microsoft.com/library/?url=/library/en-us/architec/8_ar_sa2_0ciq
.asp

What are the limitations of MSDE?
http://www.aspfaq.com/show.asp?id=2343
(Thanks MVP Aaron for his great site :-)

In all, I am worried about too many concurrents may lead into this randomly
error.

Thank you for your patience and cooperation. If you have any questions or
concerns, don't hesitate to let me know. We are here to be of assistance!


Sincerely yours,

Mingqing Cheng
Microsoft Developer Community Support
---------------------------------------------------------------
Introduction to Yukon! - http://www.microsoft.com/sql/yukon
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only, many thanks!

Hi,

Thanks very much for your reply.

I will look into this. Do you or anyone else know how I can check to see how
many connections to a particular database I have? I will then play around
with the system and see the connection status to the database, to make sure
the connections are getting closed.

Kind Regards
Warren Patterson
[quoted text, click to view]

Thanks for your reply Mingqing

Is there a way to check how many connections I have to a database at any
time?

Kind Regards
Warren Patterson



[quoted text, click to view]

Hi Warren,

Thanks for you update!

You could use sp_who to find who is using SQL Server and what kind of
operation he is doing now. You could also use sp_who2, which is not an
offically documented stored procedures, to get more detailed information:)

Thank you for your patience and cooperation. If you have any questions or
concerns, don't hesitate to let me know. We are here to be of assistance!


Sincerely yours,

Mingqing Cheng
Microsoft Developer Community Support
---------------------------------------------------------------
Introduction to Yukon! - http://www.microsoft.com/sql/yukon
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only, many thanks!

Thanks Mingqing,

I will try this.

Many Thanks
Warren Patterson

[quoted text, click to view]

Hi Warren,

When you find you need more information or help on this topic with the
result of sp_who or sp_who2, you could reply here or create a new thread,
which is more appreciated, to continue discussion with me. Whenever you
meet any questions or difficuties, we are always here to be of assitance!

Thank you for your patience and cooperation. If you have any questions or
concerns, don't hesitate to let me know. We are here to be of assistance!


Sincerely yours,

Mingqing Cheng
Microsoft Developer Community Support
---------------------------------------------------------------
Introduction to Yukon! - http://www.microsoft.com/sql/yukon
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only, many thanks!