DMO - detachDB problem

I have the following code:
Set oServer = New SQLDMO.SQLServer
oServer.Connect (Servername), ServerID, ServerPwd
Set oDatabase = oServer("Master")
Call oServer.DetachDB(DBName, True)
oServer.Close

however, I continue getting the following error:
"Cannot detach the database 'Northwind' because it is currently in use"

Doing a detach with Enterprise manager works just fine on the same
server/db - - it's just that, with coding it, I get the error - -

I can restart the computer and go straight to this code - (not running any
other db programs to access the database) and it still does it - -

what might I be doing wrong?
(is there a way to kill any and all connections, before connecting and
detaching?)

hi Elmo,
Elmo Watson wrote:
 > I have the following code:
 > Set oServer = New SQLDMO.SQLServer
 > oServer.Connect (Servername), ServerID, ServerPwd
 > Set oDatabase = oServer("Master")
 > Call oServer.DetachDB(DBName, True)
 > oServer.Close
 >
 > however, I continue getting the following error:
 > "Cannot detach the database 'Northwind' because it is currently in
 > use"
 >
 > Doing a detach with Enterprise manager works just fine on the same
 > server/db - - it's just that, with coding it, I get the error - -
 >
 > I can restart the computer and go straight to this code - (not
 > running any other db programs to access the database) and it still
 > does it - -
 >
 > what might I be doing wrong?
 > (is there a way to kill any and all connections, before connecting and
 > detaching?)

if you really want to kill all active connections to the database to be
detached you can execute the Transact-SQL code SQL Server uses, that's to
say
sSql = Replace("DECLARE @command NVARCHAR(500)" & vbCrLf _
& "DECLARE @spid INT" & vbCrLf _
& "DECLARE active_spids CURSOR LOCAL" & vbCrLf _
& " FOR SELECT s.spid FROM master.dbo.sysprocesses s" & vbCrLf _
& " WHERE (s.dbid = (SELECT dbid FROM master.dbo.sysdatabases
WHERE (name = '#1#')))" & vbCrLf _
& "OPEN active_spids" & vbCrLf _
& "FETCH NEXT FROM active_spids INTO @spid" & vbCrLf _
& "WHILE (@@fetch_status = 0) BEGIN" & vbCrLf _
& " SET @command = 'KILL ' + CONVERT(VARCHAR(10), @spid)" & vbCrLf _
& " EXECUTE ( @command )" & vbCrLf _
& " FETCH NEXT FROM active_spids INTO @spid" & vbCrLf _
& "END" & vbCrLf _
& "DEALLOCATE active_spids", _
"#1#", sDBname)

On Local Error Resume Next
Set oQry = objServer.ExecuteWithResults(sSql, Len(sSql))
On Local Error GoTo 0
Set oQry = Nothing

but I do think you still have some active connection to the database, even
if you do not reference the Northwind database in your code... have a look
at sp_who, sp_who2 results and/or Process Info in Enterprise Manager,
Management->Current Activity node...
--
Andrea Montanari (Microsoft MVP - SQL Server)
<a rel="nofollow" style='text-decoration: none;' href="http://www.asql.biz/DbaMgr.shtm" target="_blank">http://www.asql.biz/DbaMgr.shtm</a> <a rel="nofollow" style='text-decoration: none;' href="http://italy.mvps.org" target="_blank">http://italy.mvps.org</a>
DbaMgr2k ver 0.10.0 - DbaMgr ver 0.56.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
--------- remove DMO to reply