Clearing connection before detachDB
-
I wrote the following code:
oSQLServer = New SQLDMO.SQLServer
oSQLServer.Connect("(local)\abcdef", "xxx", "xxxx")
oSQLServer.DetachDB(dbName, True)
oSQLServer.DisConnect()
oSQLServer = Nothing
My problem is that sometimes they are still connections to that db.
I read that I could do something like "ALTER DATABASE dbname SET SINGLE_USER WITH ROLLBACK IMMEDIATE". Will it do the job ?
Jean-Luc
www.corobori.com -
I would set the database offline. Even a single user connected would stop the DETACH working.
Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.
When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara
-
EdVassie wrote "I would set the database offline."
I don't think that this would help much since sp_dbOption will not allow you to take a database offline if there are users connected to that database, which is what the thread initiator is trying to address in the first place as it prevents him from detaching the db.
To achieve the desired result, I have written a stored procedure which identifies the SPIDs connected to the database I wish to restore by walking master..sysprocesses.
...
SELECT spid
FROM master..sysprocesses
WHERE dbid = @intDBID
...
I then issue KILL statements to rid myself of the connections and immediately proceed to restore (or detach) the database.
...
SET @strExec = 'KILL ' + CAST(@intSPID AS VARCHAR)
EXEC (@strExec)
...
Note that these KILLs are effective for user SPIDs only...
HTH
-
Chris,
Is there any chance of getting a copy of your script?
- Patrick
-
Don't know if it's what you're looking for but since I originally posted this message I solved this problem by running the following code prior of detaching the db
oSQLServer = New SQLDMO.SQLServer
oSQLServer.Connect("(local)\" & mdCommon.GetSQLServerName, mdCommon.Getuid, mdCommon.Getpwd)
Dim i As Integer
Dim objQueryResults As QueryResults
objQueryResults = oSQLServer.ExecuteWithResults _
("Select spid from sysprocesses where db_name(dbid) = '" & Me.cboDatabase.SelectedText & "'")
For i = 1 To objQueryResults.Rows
Call oSQLServer.KillProcess(objQueryResults.GetColumnLong(i, 1))
Next
oSQLServer.DetachDB(Me.cboDatabase.SelectedText, True)
oSQLServer.DisConnect()
oSQLServer = Nothing
Hope it helps
Jean-Luc
www.corobori.com -
Jean-Luc,
I will be doing this using T-SQL in a DTS job, but your code and Chris's snippets will give me a start.
Thanks.
Patrick
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply