Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
smita
Starting Member
4 Posts |
 Posted - 2002-03-06 : 05:50:38
|
| I am writing a Vbscript file which detaches 2 existing databases...copies one to other and reattaches them.I am using the DetachDB and attachDB functions to do so...in the SQL-DMO object...But I have a problem...If the database is being used it just throws off error saying database cannot be detached and does not complete the whole script..I wanted to know if there is any function in the SQL-DMO that I can use to clear the present users connected to the database forcrfully and detach the database..I did have a look at the books online...But I could not get any solutionThis is a bit urgent...Can anybody give me some solution...It would be great..smita |
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2002-03-06 : 07:18:43
|
| There is a KillProcess method that applies to a SQLServer DMO object, you can have it run through each SPID and kill them. I can't find anything in the DMO object model that lists all the SPIDs, but you can get them by running sp_who. |
 |
|
|
smita
Starting Member
4 Posts |
Posted - 2002-03-06 : 08:44:54
|
quote:
There is a KillProcess method that applies to a SQLServer DMO object, you can have it run through each SPID and kill them. I can't find anything in the DMO object model that lists all the SPIDs, but you can get them by running sp_who.
Can you suggest how I can call the sp_who procedure in my vbscript file.I am a bit new to this.smita |
|
|
|
jhunt
Starting Member
21 Posts |
Posted - 2002-04-10 : 00:54:44
|
| I was having trouble with this issue too. I found in another post some sql that someone was using to do something similar. With the clues I got from that I was able to use SQL-DMO to kick out other users/processes before I restore the databases. Very short code.=================================================================== Set objQueryResults = m_SQLServer.ExecuteWithResults("Select spid from sysprocesses where db_name(dbid) = '" & strDatabaseName & "'")' SQL Server object previously defined Dim i as integerDim objQueryResults as QueryResults Dim strDatabaseName as StringstrDatabaseName = "Northwind" Set objQueryResults = m_SQLServer.ExecuteWithResults _ ("Select spid from sysprocesses where db_name(dbid) = '" & strDatabaseName & "'") For i = 1 To objQueryResults.Rows Call m_SQLServer.KillProcess(objQueryResults.GetColumnLong(i, 1)) Next i====================================================================== |
|
|
|
AjarnMark
SQL Slashing Gunting Master
3246 Posts |
Posted - 2002-04-10 : 01:51:12
|
| You might want to execute a sp_dboption 'DatabaseName', 'dbo use only', 'TRUE' and sp_dboption 'DatabaseName', 'single user', 'TRUE' to prevent any new connections from being made while you're killing off old ones.These are the Transact-SQL statements, I don't know the related SQL-DMO, but I'm sure it's in there somewhere...Remember to reset these to FALSE when you're done.------------------------GENERAL-ly speaking...Edited by - AjarnMark on 04/10/2002 01:51:51 |
|
|
|
|
|
|
|
|
Detaching a database using SQL-DMO object