declare @i int
set @i=1
while @i<(select max(spid) from sysprocesses )
begin
if exists(select 1 from sysprocesses where spid=@i and dbid=db_id('dbname'))
exec('kill '+@i)
set @i=@i+1
end
set @i=1
while @i<(select max(spid) from sysprocesses )
begin
if exists(select 1 from sysprocesses where spid=@i and dbid=db_id('dbname'))
exec('kill '+@i)
set @i=@i+1
end
(
SPID int
)Declare @vcSQLText varchar(200),
@iSPID int--Get the currently connected users
Insert into @tblConnectedUsers
Select p.spid
from master.dbo.sysprocesses p (nolock)
join master..sysdatabases d (nolock) on p.dbid = d.dbid
Where d.[name] = 'EBN_QA' --> database name here--Loop though the connected users and kill their connections
While 1 = 1
Begin Select top 1 @iSPID = SPID
From @tblConnectedUsers
Where SPID > IsNull(@iSPID, 0)
order by SPID asc-- break when there are no more SPIDs
If @@RowCount = 0
Break--Build the SQL string
Set @vcSQLText = 'Kill ' + Convert(varchar(10), @iSPID) Exec( @vcSQLText ) End
Dim srv1 'As New SQLDMO.SQLServer
Dim usr1 'As SQLDMO.User
Dim qres 'As SQLDMO.QueryResults
Dim rs 'As New ADODB.Recordset
Dim sQueryResult 'As String
Dim rsQryResults 'As New ADODB.Recordset
Dim sRows() 'As String
Dim sCols() 'As String
Dim idxRow 'As Long
Dim idxCol 'As Long
Dim wshShell ' As wscript Object
Dim strSQLServer 'As String
Dim strDBKill 'As String
set wshShell = createObject("wscript.shell")
set srv1 = CreateObject("SQLDMO.SQLServer")
set usr1 = CreateObject("SQLDMO.User")
set rs = CreateObject("ADODB.RecordSet")
Set rsQryResults = CreateObject("ADODB.RecordSet") WshShell.LogEvent 0, "Kill Process Script Started" strSQLServer = "ENTER SERVER NAME HERE"
strDBKill = "ENTER DATABASE NAME HERE" 'Instantiate SQLServer object and
'point it at the server.
srv1.LoginSecure = True
srv1.Connect strSQLServer
Set oQueryResults = srv1.EnumProcesses For idxCol = 1 To oQueryResults.Columns
rsQryResults.Fields.Append oQueryResults.ColumnName(idxCol), 200, oQueryResults.ColumnMaxLength(idxCol) + 2
Next 'idxCol next column
''Create the recordset rows
rsQryResults.Open
For idxRow = 1 To oQueryResults.Rows
'Add a new record
rsQryResults.AddNew
'Add values to each field in the row
For idxCol = 1 To oQueryResults.Columns
rsQryResults.Fields(idxCol - 1) = oQueryResults.GetColumnString(idxRow, idxCol)
Next
rsQryResults.Update
Next 'idxRow
' return to caller
Set rs = rsQryResults
rs.MoveFirst
Do While Not rs.EOF
For Each fd In rs.Fields
strfieldlist = strfieldlist & fd.Name & " " & fd.Value & vbCrLf
Next
If UCase(rs("dbname").Value) = Ucase(strDBKill) Then
WshShell.LogEvent 0, "The following process was killed programatically " & rs("Spid").Value & " " & rs("dbname").Value & " " & rs("status").Value
srv1.KillProcess (CInt(rs("Spid").Value))
End If
rs.MoveNext
Loop
WshShell.LogEvent 0, "Kill Process Script Complete"
Set oQueryResults = nothing
Set rs = nothing
if err.number <> 0 then
WshShell.LogEvent 1,"Error " & err.number & " " & err.description
err.clear
end if