Before I can drop an mdf file form the server, all connections needs to be closed. how can I force to close this connection. The solution explained on this blog don't seems to work in my case http://sqlserver2005.de/SQLServer2005/MyBlog/tabid/56/EntryID/9/Default.aspx
I'm using SQL express, with visual studio pro 2005.
Thx for you quick responses
best regards
Luc N
please verify the code I've used
Dim svr As Server
svr = Nothing
svr = New Server(".\SQLEXPRESS")
'attach database
Dim con As New SqlConnection(svr.ConnectionContext.ConnectionString)
Dim com As New SqlCommand("sp_detach_db", con)
Dim d As Database
con.Open()
For Each d In svr.Databases
If Not d.IsSystemObject Then
'com.CommandType = Data.CommandType.StoredProcedure
'com.Parameters.Add(New SqlParameter("@.dbname", d.Name))
'com.ExecuteNonQuery()
'MsgBox(d.UserName.ToString())
d.DatabaseOptions.UserAccess = DatabaseUserAccess.Restricted
SqlConnection.ClearAllPools()
d.Drop()
'com.CommandText = "DROP DATABASE " & d.Name.ToString
'com.CommandType = CommandType.Text
'com.ExecuteNonQuery()
'End If
End If
Next
com.Connection.Close()
Why do you think that the information form the blog is not working for you ?Jens K. Suessmeyer
http://www.sqlserver2005.de
|||
I've still got the message 'cannot drop the database because.......................'
I've modified my code , which seems to be working,
please reply your comments on this code
thx Luc
Try
Dim svr As Server
svr = Nothing
svr = New Server(".\SQLEXPRESS")
'attach database
Dim con As New SqlConnection(svr.ConnectionContext.ConnectionString)
Dim d As Database
con.Open()
For Each d In svr.Databases
If Not d.IsSystemObject Then
Exit For
End If
Next
If InStr(d.Name, "exp", CompareMethod.Text) <> 0 Then
svr.KillAllProcesses(d.Name)
d.DatabaseOptions.UserAccess = DatabaseUserAccess.Restricted
SqlConnection.ClearAllPools()
d.Drop()
con.Close()
End If
Catch ex As Exception
' MsgBox(ex.Message)
End Try
|||As I pointed out in the comment, they fixed the behaviour in the Service Pack, so KillDatabase should work for you.
