As most of you are aware, when you close a VB.NET connectrion to a SQL
server, the connection doesn't actually drop right then and there.
From my tests, using VB.NET 2003 and SQL2K, the connection doesn't
timeout and drop off for 6 1/2 minutes. How can one force this
connection to immediately drop off with code?

This is called connection pooling. It is a good thing, why would you want to
get rid of it?

"Joe Cool" <joecool@home.net> wrote in message
news:qjqr911o1pun4psdtkn7j76ncs0a3n5j4b@4ax.com...
> As most of you are aware, when you close a VB.NET connectrion to a SQL
> server, the connection doesn't actually drop right then and there.
> From my tests, using VB.NET 2003 and SQL2K, the connection doesn't
> timeout and drop off for 6 1/2 minutes. How can one force this
> connection to immediately drop off with code?
>

I don't believe there is a way within .NET's CLR to clear the connection
pool in ADO.NET 1.x (which would close the actual SQL Server connection);
I've read that this should be available in 2.0, but I don't know if that is
the case.

Is there a reason you need to force the full SQL Server disconnect?


"Joe Cool" <joecool@home.net> wrote in message
news:qjqr911o1pun4psdtkn7j76ncs0a3n5j4b@4ax.com...
> As most of you are aware, when you close a VB.NET connectrion to a SQL
> server, the connection doesn't actually drop right then and there.
> From my tests, using VB.NET 2003 and SQL2K, the connection doesn't
> timeout and drop off for 6 1/2 minutes. How can one force this
> connection to immediately drop off with code?
>

On Wed, 01 Jun 2005 17:07:39 GMT, Joe Cool <joecool@home.net> wrote:

¤ As most of you are aware, when you close a VB.NET connectrion to a SQL
¤ server, the connection doesn't actually drop right then and there.
¤ From my tests, using VB.NET 2003 and SQL2K, the connection doesn't
¤ timeout and drop off for 6 1/2 minutes. How can one force this
¤ connection to immediately drop off with code?

Not sure which provider or driver you are using with SQL Server but you could probably disable
connection pooling, although I wouldn't recommend it.


Paul
~~~~
Microsoft MVP (Visual Basic)

On Wed, 1 Jun 2005 13:49:16 -0400, "Marina" <someone@nospam.com>
wrote:

>This is called connection pooling. It is a good thing, why would you want to
>get rid of it?
>
>"Joe Cool" <joecool@home.net> wrote in message
>news:qjqr911o1pun4psdtkn7j76ncs0a3n5j4b@4ax.com.. .
>> As most of you are aware, when you close a VB.NET connectrion to a SQL
>> server, the connection doesn't actually drop right then and there.
>> From my tests, using VB.NET 2003 and SQL2K, the connection doesn't
>> timeout and drop off for 6 1/2 minutes. How can one force this
>> connection to immediately drop off with code?
>>
>

As you and others have asked "Why?" here's the answer. I am developing
a VB.NET application that uses SQL as the database backend and I check
the existance of all required tables when the application starts. If
any tables are missing, I assume the database is corrupt and give the
user the option of deleting the current corrupt database and
recreating it. I open a connection to scan the database for the
requied tables and iof the user wants to start over, I would like to
close the connection and immediately open a connection to the master
database and drop the application database. But with connection
pooling, it appears that I will have to wait for 6 1/2 minutes (in my
case) before I can do that. I would prefer to not have to wait.

Yes, connection pooling is a good thing, but I would like to
temporarily disable it in this case.

Joe Cool (joecool@home.net) writes:
> As you and others have asked "Why?" here's the answer. I am developing
> a VB.NET application that uses SQL as the database backend and I check
> the existance of all required tables when the application starts. If
> any tables are missing, I assume the database is corrupt and give the
> user the option of deleting the current corrupt database and
> recreating it. I open a connection to scan the database for the
> requied tables and iof the user wants to start over, I would like to
> close the connection and immediately open a connection to the master
> database and drop the application database. But with connection
> pooling, it appears that I will have to wait for 6 1/2 minutes (in my
> case) before I can do that. I would prefer to not have to wait.

No, you don't have to wait. If the credentials are the same you don't
even have to change connection, just issue "USE master". If you have
different credentials, or want a new connection anyway, issue a
"USE tempdb" on the old connection before you close it, so that the
"DROP DATABASE" command is not blocked. Yet another possibility is
to issue "ALTER DATABASE db SET SINGLE_USER WITH ROLLBACK IMMEDIATE"
before you drop the database. That will kick out the connections
that lingers around.



--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

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

We had to disable connectioning pooling on our ASP.Net 2002 and ASP.Net
framework 2003 applications becuase we got intermittent errors about
the connectionpool running out and there was no actual way to get rid
of it.

For the record we were closing and disposing of out connection objects
properly but despite this, we would get the dreaded erorr unexpectedly,
and have to reset the server to sort it out.

"Joe Cool" <joecool@home.net> wrote in message
news:a06s91lb492bkus82046v2u3u4s4fmko62@4ax.com...
> On Wed, 1 Jun 2005 13:49:16 -0400, "Marina" <someone@nospam.com>
> wrote:
>
>
> As you and others have asked "Why?" here's the answer. I am developing
> a VB.NET application that uses SQL as the database backend and I check
> the existance of all required tables when the application starts. If
> any tables are missing, I assume the database is corrupt and give the
> user the option of deleting the current corrupt database and
> recreating it. I open a connection to scan the database for the
> requied tables and iof the user wants to start over, I would like to
> close the connection and immediately open a connection to the master
> database and drop the application database. But with connection
> pooling, it appears that I will have to wait for 6 1/2 minutes (in my
> case) before I can do that. I would prefer to not have to wait.
>
> Yes, connection pooling is a good thing, but I would like to
> temporarily disable it in this case.

As Erland points out, you can do what you want w/o disabling connection
pooling.

But, my greater question is... while it may be "nice" you're checking for a
corrupt db, is this really worth it?

I mean SQL Server is fairly stable and if you're having this as a common
problem, you've got other issues to deal with.

Also, if the DB is corrupt, as a user, I'd probably want to RESTORE it from
a backup, not lose all my existing data.

I'll assume you have your reasons here, but I am curious.


>

On Wed, 1 Jun 2005 21:47:33 +0000 (UTC), Erland Sommarskog
<esquel@sommarskog.se> wrote:

>Joe Cool (joecool@home.net) writes:
>> As you and others have asked "Why?" here's the answer. I am developing
>> a VB.NET application that uses SQL as the database backend and I check
>> the existance of all required tables when the application starts. If
>> any tables are missing, I assume the database is corrupt and give the
>> user the option of deleting the current corrupt database and
>> recreating it. I open a connection to scan the database for the
>> requied tables and iof the user wants to start over, I would like to
>> close the connection and immediately open a connection to the master
>> database and drop the application database. But with connection
>> pooling, it appears that I will have to wait for 6 1/2 minutes (in my
>> case) before I can do that. I would prefer to not have to wait.
>
>No, you don't have to wait. If the credentials are the same you don't
>even have to change connection, just issue "USE master". If you have
>different credentials, or want a new connection anyway, issue a
>"USE tempdb" on the old connection before you close it, so that the
>"DROP DATABASE" command is not blocked. Yet another possibility is
>to issue "ALTER DATABASE db SET SINGLE_USER WITH ROLLBACK IMMEDIATE"
>before you drop the database. That will kick out the connections
>that lingers around.
>

Thanks. Very good info.

On Thu, 02 Jun 2005 01:25:56 GMT, "Greg D. Moore \(Strider\)"
<mooregr_deleteth1s@greenms.com> wrote:

>
>"Joe Cool" <joecool@home.net> wrote in message
>news:a06s91lb492bkus82046v2u3u4s4fmko62@4ax.com.. .
>> On Wed, 1 Jun 2005 13:49:16 -0400, "Marina" <someone@nospam.com>
>> wrote:
>>
>>
>> As you and others have asked "Why?" here's the answer. I am developing
>> a VB.NET application that uses SQL as the database backend and I check
>> the existance of all required tables when the application starts. If
>> any tables are missing, I assume the database is corrupt and give the
>> user the option of deleting the current corrupt database and
>> recreating it. I open a connection to scan the database for the
>> requied tables and iof the user wants to start over, I would like to
>> close the connection and immediately open a connection to the master
>> database and drop the application database. But with connection
>> pooling, it appears that I will have to wait for 6 1/2 minutes (in my
>> case) before I can do that. I would prefer to not have to wait.
>>
>> Yes, connection pooling is a good thing, but I would like to
>> temporarily disable it in this case.
>
>As Erland points out, you can do what you want w/o disabling connection
>pooling.
>
>But, my greater question is... while it may be "nice" you're checking for a
>corrupt db, is this really worth it?
>
>I mean SQL Server is fairly stable and if you're having this as a common
>problem, you've got other issues to deal with.
>
>Also, if the DB is corrupt, as a user, I'd probably want to RESTORE it from
>a backup, not lose all my existing data.
>
>I'll assume you have your reasons here, but I am curious.


I just don't trust the user not go go mucking around with the
Enterprise Manager and accidentally screwing something up.