Timeout / Obtaining a connection from the pool - Please Help!

[Martin]

Dear Group

Sorry for posting this here. I'm desperate for a solution to this
problem and thought some of you might have come across it with .NET
and SQL Server.
Let's assume I've the following code:

Private Sub Button4_Click(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles Button4.Click

Dim sqlConnection As New System.Data.SqlClient.SqlConnection

sqlConnection.ConnectionString = "workstation id=THEINTREPIDFOX;packet
size=4096;user id=sa;data source=""(local)"";p" & _
"ersist security info=False;initial catalog=TestDB"

sqlConnection.Open()
sqlConnection.Close()
sqlConnection = Nothing

End Sub

When I click 100 times on the button the page finally times out with:

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.

Of course I could increase the max pool size but this isn't the
solution to this problem. I wonder what's wrong with my code?
According to MSDN, if the connection is closed it's released back into
the pool. Also tried to dispose but nothing helps. It looks like that
it keeps connections and doesn't release them. I'm grateful for any
hints, ideas, suggestions on this problem as I'm very desperate to
solve this.

Thanks very much for your time & efforts!

Martin

[Erland Sommarskog]

Martin (th************@hotmail.com) writes:

Sorry for posting this here. I'm desperate for a solution to this
problem and thought some of you might have come across it with .NET
and SQL Server.

I don't have an answer, but then again, this is an SQL Server
newsgroup and not an .Net group. There are specific groups for ADO
..Net, at least in microsoft.public.*. You might have better luck
there.
--
Erland Sommarskog, SQL Server MVP, so****@algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

[Daniel A. Thomas]

"Martin" <th************@hotmail.com> wrote in message
news:72**************************@posting.google.c om...
| Dear Group
|
| Sorry for posting this here. I'm desperate for a solution to this
| problem and thought some of you might have come across it with .NET
| and SQL Server.
| Let's assume I've the following code:
|
| Private Sub Button4_Click(ByVal sender As System.Object, ByVal e As
| System.EventArgs) Handles Button4.Click
|
| Dim sqlConnection As New System.Data.SqlClient.SqlConnection
|
| sqlConnection.ConnectionString = "workstation id=THEINTREPIDFOX;packet
| size=4096;user id=sa;data source=""(local)"";p" & _
| "ersist security info=False;initial catalog=TestDB"
|
| sqlConnection.Open()
| sqlConnection.Close()
| sqlConnection = Nothing
|
| End Sub
|
| When I click 100 times on the button the page finally times out with:
|
| 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.
|
| Of course I could increase the max pool size but this isn't the
| solution to this problem. I wonder what's wrong with my code?
| According to MSDN, if the connection is closed it's released back into
| the pool. Also tried to dispose but nothing helps. It looks like that
| it keeps connections and doesn't release them. I'm grateful for any
| hints, ideas, suggestions on this problem as I'm very desperate to
| solve this.
|
| Thanks very much for your time & efforts!
|
| Martin
Martin,

Hope this helps:

http://support.microsoft.com/default...b;en-us;308047

Dan

[Erland Sommarskog]

Daniel A. Thomas ([e-mail]) writes:

Hope this helps:

http://support.microsoft.com/default...b;en-us;308047

This article describes to open an ADO record set from .Net. It seems
to me that Martin was having problems with SqlClient ADO .Net. ADO
and ADO .Net may have similar-looking names, but they are very different.
:-)
--
Erland Sommarskog, SQL Server MVP, so****@algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

[Daniel A. Thomas]

"Martin" <th************@hotmail.com> wrote in message
news:72**************************@posting.google.c om...
| Dear Group
|
| Sorry for posting this here. I'm desperate for a solution to this
| problem and thought some of you might have come across it with .NET
| and SQL Server.
| Let's assume I've the following code:
|
| Private Sub Button4_Click(ByVal sender As System.Object, ByVal e As
| System.EventArgs) Handles Button4.Click
|
| Dim sqlConnection As New System.Data.SqlClient.SqlConnection
|
| sqlConnection.ConnectionString = "workstation id=THEINTREPIDFOX;packet
| size=4096;user id=sa;data source=""(local)"";p" & _
| "ersist security info=False;initial catalog=TestDB"
|
| sqlConnection.Open()
| sqlConnection.Close()
| sqlConnection = Nothing
|
| End Sub
|
| When I click 100 times on the button the page finally times out with:
|
| 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.
|
| Of course I could increase the max pool size but this isn't the
| solution to this problem. I wonder what's wrong with my code?
| According to MSDN, if the connection is closed it's released back into
| the pool. Also tried to dispose but nothing helps. It looks like that
| it keeps connections and doesn't release them. I'm grateful for any
| hints, ideas, suggestions on this problem as I'm very desperate to
| solve this.
|
| Thanks very much for your time & efforts!
|
| Martin

Hi Martin,

As Erland Sommarskog pointed out I've pointed you to ADO and not
ADO.NET Sorry 'bout that one. So first things first remove the
ADO coding "sqlConnection = Nothing".

Here is the working hypothesis ...
As your connect strings specifies "Persist Security Info=False;"(default=
false)security information is never returned. In addition, Integrated
Security is
false by default and when false, User ID and Password are specified in the
connection. The result is an exact match with an existing connection pool
object
is never possible and the connections in the pool are not being reused.

You might try adding "Integrated Security=SSPI;" to your connect string.
When set to "SSPI" Windows account credentials are used for
authentication.

If this unacceptable add "Pooling=False;" to your connect string. This
defeats
the performance enhancements of connection pooling but the Timeout error
will go.

Some of this information is contained in the following two articles at:

http://msdn.microsoft.com/library/de...tringTopic.asp

http://msdn.microsoft.com/library/de...taprovider.asp

Of course there's always plain old vanilla ADO.

:-)
Dan

[Martin]

Thanks guys for having a look at this anyway!
M