Posted by Erland Sommarskog on 06/02/05 00:47

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/techinfo/productdoc/2000/books.asp

[Back to original message]