If you have to set the max pool to 1000, you know you're doing something wrong :)
It appears from the code snippet that you only want to have a single connection object, which is open as long as the calls coming in are using the same connection string. If so, wouldn't this object need to be Shared (static in c#)? I am a little confused by the .ConnectionString <> ConnectionString part. 1. As far as I know, if you set Persist Security Info = False (which is a good idea, but I can see you aren't doing) in the (SqlServer) connection string, this will always return false. 2. Connection string matching is how the underlying connection pool mechanism is implemented, isn't it?. So you should just try to acquire a connection based on the connection string, and let the underlying implementation handle whether or not this should be a new connection or recycle a pooled connection. Just make sure, as said previously in the thread, that you always call close/dispose on the connection. John -----Original Message----- From: Moderated discussion of advanced .NET topics. [mailto:[EMAIL PROTECTED] On Behalf Of Wallace B. McClure Sent: 26. september 2003 13:49 To: [EMAIL PROTECTED] Subject: Re: [ADVANCED-DOTNET] The timeout period elapsed prior to obtaini ng a connection from t he pool. I have seen this problem before. Heath is exactly right, the connection is not being closed and returned to the pool. The next problem is going to be that under a heavy load, you may still have a failure. To resolve this, you will want to call .Dispose() on all database objects and objects that have been created and used. I always perform my close and dispose within the finally section of a try-catch-finally block. As a last item, I also mark the objects for deletion by specifically setting the objects to null/nothing. Wally ----- Original Message ----- From: "Heath Ryan" <[EMAIL PROTECTED]> To: <[EMAIL PROTECTED]> Sent: Fri, 26 Sep 2003 13:21:04 +0200 Subject: Re: [ADVANCED-DOTNET] The timeout period elapsed prior to obtaini ng a connection from t he pool. Hi Stefan, Looks like your are not closing connections that were succesfully opened, but then again I do not have your full source code.... :) HTH, // Ryan -----Original Message----- From: Stefan Heunis To: [EMAIL PROTECTED] Sent: 9/26/2003 11:03 AM Subject: [ADVANCED-DOTNET] The timeout period elapsed prior to obtaining a connection from t he pool. Hi, I get the following error in a volume creation program, when the program try to open the SQL Connection. The program executes the same code successfully a number of times before it stops. I have changed the Max Pool Size to 1000 but it does not make a difference. Method : GetValidSQLConnection on thread : 2512 on Machine : WS-STEFAN2 from the base directory : N:\Dev\XLiveCreateVolume\bin\ : System.InvalidOperationException: Timeout expired. The timeout period elapsed prior to obtaining a connection from the pool. This may have occurred because all pooled connections were in use and max pool size was reached. at System.Data.SqlClient.SqlConnectionPoolManager.GetPooledConnection(SqlCo nnec tionString options, Boolean& isInTransaction) at System.Data.SqlClient.SqlConnection.Open() at SDTFunctions.clsSQLRead.GetValidSQLConnection(String pstrSQL) in N:\Dev\SDTFunctions\clsSQLRead.vb:line 191 , Query : Execute LIF_iProdValHIVOption_L_FK 2000 , ConnectionString: UID=sa;PWD=system;SERVER=svr-snowwhite;DATABASE=xlif_d_sdt ; Max Pool Size=1000 Can any body help me? Private mobjSQLConnection As Data.SqlClient.SqlConnection Public Sub GetValidSQLConnection(ByVal pstrSQL As String) Dim lstrError As String = "" Try If Sys.ConnectionString.Trim.Length = 0 Then Throw New Exception(Me.ToString & " - The property Sys.ConnectionString cannot be empty.") End If If Not mobjSQLConnection Is Nothing AndAlso (mobjSQLConnection.ConnectionString <> mobjSys.ConnectionString) Then mobjSQLConnection.Close() mobjSQLConnection.Dispose() mobjSQLConnection = Nothing End If If mobjSQLConnection Is Nothing Then mobjSQLConnection = New SqlClient.SqlConnection(mobjSys.ConnectionString) End If If mobjSQLConnection.State = ConnectionState.Closed Then mobjSQLConnection.ConnectionString = mobjSys.ConnectionString mobjSQLConnection.Open() End If Catch er As Exception lstrError = er.ToString & " , Query : " & pstrSQL Try If Not mobjSQLConnection Is Nothing Then lstrError &= " , ConnectionString: " & mobjSQLConnection.ConnectionString End If Catch ex As Exception End Try LogEvent(lstrError) If ContextUtil.IsInTransaction Then ContextUtil.SetAbort() Throw er End Try End Sub Stefan Heunis SDT Financial Software Solutions (Pty) Ltd Mobile +27(0)82 415 5131 Tel +27(0)12-347 4945 Fax +27(0)12-347 4946 Visit us at www.sdt.co.za The gateway to the financial services future! The views expressed in this e-mail are, unless otherwise stated, those of the author and not of SDT or its management. The information is confidential and is intended solely for the addressee. All reasonable steps are taken to ensure the accuracy, integrity and confidentiality of information. No liability or responsibility is accepted if information is corrupted or does not reach its intended destination. This email message has been scanned for viruses and cleared. =================================== This list is hosted by DevelopMentorR http://www.develop.com NEW! ASP.NET courses you may be interested in: 2 Days of ASP.NET, 29 Sept 2003, in Redmond http://www.develop.com/courses/2daspdotnet Guerrilla ASP.NET, 13 Oct 2003, in Boston http://www.develop.com/courses/gaspdotnet View archives and manage your subscription(s) at http://discuss.develop.com ------------------------------------- The information included in this message is personal and/or confidential and intended exclusively for the addressees as stated. This message and/or the accompanying documents may contain confidential information and should be handled accordingly. If you are not the intended reader of this message, we urgently request that you notify Centric immediately and that you delete this e-mail and any copies of it from your system and destroy any printouts immediately. It is forbidden to distribute, reproduce, use or disclose the information in this e-mail to third parties without obtaining prior permission from Centric. We expressly point out that there are risks associated with the use of e-mail. Centric and the companies within the group shall not accept any liability whatsoever for damage resulting from the use of e-mail. Legally binding obligations can only arise for Centric by means of a written instrument, signed by an authorized representative of Centric. ------------------------------------- =================================== This list is hosted by DevelopMentorR http://www.develop.com NEW! ASP.NET courses you may be interested in: 2 Days of ASP.NET, 29 Sept 2003, in Redmond http://www.develop.com/courses/2daspdotnet Guerrilla ASP.NET, 13 Oct 2003, in Boston http://www.develop.com/courses/gaspdotnet View archives and manage your subscription(s) at http://discuss.develop.com =================================== This list is hosted by DevelopMentorR http://www.develop.com NEW! ASP.NET courses you may be interested in: 2 Days of ASP.NET, 29 Sept 2003, in Redmond http://www.develop.com/courses/2daspdotnet Guerrilla ASP.NET, 13 Oct 2003, in Boston http://www.develop.com/courses/gaspdotnet View archives and manage your subscription(s) at http://discuss.develop.com =================================== This list is hosted by DevelopMentor� http://www.develop.com NEW! ASP.NET courses you may be interested in: 2 Days of ASP.NET, 29 Sept 2003, in Redmond http://www.develop.com/courses/2daspdotnet Guerrilla ASP.NET, 13 Oct 2003, in Boston http://www.develop.com/courses/gaspdotnet View archives and manage your subscription(s) at http://discuss.develop.com
