469,294 Members | 1,940 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,294 developers. It's quick & easy.

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

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
Jul 20 '05 #1
5 5468
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
Jul 20 '05 #2
"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

Jul 20 '05 #3
Daniel A. Thomas (dathomas@--spam--istar.ca) 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
Jul 20 '05 #4
"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

Jul 20 '05 #5
Thanks guys for having a look at this anyway!
M
Jul 20 '05 #6

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

reply views Thread by Silvia | last post: by
1 post views Thread by Jake K | last post: by
7 posts views Thread by =?Utf-8?B?Sm9obiBTdGFnZ3M=?= | last post: by
1 post views Thread by CARIGAR | last post: by
reply views Thread by zhoujie | last post: by
reply views Thread by harlem98 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.