Detach database from SQL Server

[Guest]

Hello,

When I have closed my appliciation in VB.NET, and I try to detach my
database from SQL SERVER, I often get a message that the database cannot be
detachted, because it is still in use.

So I think there is some code missing in my application (in the
MDI_FormClosing?)
to make my database is no longer in use.

I already put a Connection.Close in the FormClosing also in a Finally-block
of

that FormClosing, but that does not seem to be enough.

Can someone help me with that?

Many thanks and greetings,

Michel

 

[William Vaughn]

Remember, if you open a connection to the database, the connection remains
open even AFTER you close the connection--until the Connection pool is
flushed. This can be done in code in ADO.NET 2.0. If you're using the SSMS
tools, be sure to check the "close connections" option first.

--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant, Dad, Grandpa
Microsoft MVP
INETA Speaker
www.betav.com
www.betav.com/blog/billva
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________
Visit www.hitchhikerguides.net to get more information on my latest book:
Hitchhiker's Guide to Visual Studio and SQL Server (7th Edition)
and Hitchhiker's Guide to SQL Server 2005 Compact Edition (EBook)

 

[Guest]

Hello,

Remember, if you open a connection to the database, the connection remains
open even AFTER you close the connection--until the Connection pool is
flushed. This can be done in code in ADO.NET 2.0. If you're using the SSMS
tools, be sure to check the "close connections" option first.

I am new to all this, so can you tell me how to flush the connection pool?
What code should I use to do this?
Can you also enlighten me on SSMS tools. What are they for?

The only thing I want for now is to be able to detach my database, once my
program ends.

Many thanks for your help.

Michel

 

[William Vaughn]

You have kind of a catch 22. In order to send commands like sp_detach to a
database server instance, you have to have a connection. If you flush the
pool to free the connection, you can't detach the database if the connection
references it. The trick is to open a connection to another database
(usually master). This assumes that you have rights to access the master
database. Another approach is to change the current (default) database by
using the USE <db> statement.

SqlConnection.ClearAllPools() ' Clears all connection pools (and closes
all connections)

SqlConnection.ClearPool(cn) ' Clear the pool for the selected connection

SQL Server Management Studio (SSMS) is the interactive management suite of
tools that can be used to perform any and all kinds of maintenance on the
database.

SMO (System Maintenance Objects) is the object interface used by SSMS. It
can also detach databases programmatically. You might have better luck with
this as its designed to perform these tasks where ADO.NET is not.

You might also benefit from my book as it discusses many of these issues.

hth--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant, Dad, Grandpa
Microsoft MVP
INETA Speaker
www.betav.com
www.betav.com/blog/billva
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________
Visit www.hitchhikerguides.net to get more information on my latest book:
Hitchhiker's Guide to Visual Studio and SQL Server (7th Edition)
and Hitchhiker's Guide to SQL Server 2005 Compact Edition (EBook)

 

[Patrice]

Also in 2.0 you have a new connection string option that allows to attach
the DB and to have it detached automatically :
http://msdn.microsoft.com/vstudio/tour/vs2005_guided_tour/VS2005pro/Smart_Client/AttachDatabase.htm