Re: Detach database from SQL Server

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)
-----------------------------------------------------------------------------------------------------------------------


"Michel" <Michel@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message news:9358F28F-014E-406D-8B50-54FC8500AD9F@xxxxxxxxxxxxxxxx
Hello,

"William Vaughn" wrote:

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


.

Relevant Pages

  • Complicated Connection Problems bewteen ADP and SQL Server
    ... This database ... expertise for getting the user workstations talking to the SQL Server. ... connection would fail and the adp wouldn't be able to talk to the server. ... might be in my ADO connection string. ...
    (microsoft.public.access.adp.sqlserver)
  • Complicated Connection Problem between ADP and SQL Server
    ... This database ... expertise for getting the user workstations talking to the SQL Server. ... connection would fail and the adp wouldn't be able to talk to the server. ... might be in my ADO connection string. ...
    (microsoft.public.sqlserver.connect)
  • Re: ADO Connection Timeout
    ... so what happens when a connection failure forces one station to revert ... to a local database? ... Further, you *will* have contention issues, Jet does not support record ... to the central server, but you are willing to live with periods where it ...
    (microsoft.public.data.ado)
  • Re: ADO Connection Timeout
    ... When the first test is run, the results are stored in the central database. ... to the central server, but you are willing to live with periods where it ... i.e. a local database or even a text file. ... to function until the connection can be restored to the server. ...
    (microsoft.public.data.ado)
  • Re: ADO Connection Timeout
    ... to the central server, but you are willing to live with periods where it ... i.e. a local database or even a text file. ... to function until the connection can be restored to the server. ...
    (microsoft.public.data.ado)