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 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


------------------------------------- 
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 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


===================================
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

Reply via email to