SQL Script to kill process for restore - using temp table with sp_who2

[bmacbmac]

Hi,

I am trying to figure out a way to Kill a process through SQL Query Analyzer so my user doesn't have to get in and play around with Enterprise Manager.

Does anyone have a suggestion?

I was going to build an @temp table to insert the sp_who2 info so I could order or filter the results:

dECLARE @Temp TABLE (  
    spid smallint,  
    ecid smallint,  
    status nchar(30),  
    loginname nchar(128),  
    hostname nchar(128),  
    blk char(5),  
    dbname nchar(128),  
    cmd nchar(16),  
    request_id INT  
)  
  

INSERT INTO @Temp sp_who2  
  
SELECT * FROM @Temp WHERE dbname = 'kootbatch1a'

Then I was going to see if she could KILL based on the spid.

When trying this script I get an error Incorrect syntax near 'sp_who2'. If I change it to

INSERT INTO @Temp Exec sp_who2 - I get an error: EXECUTE cannot be used as a source when inserting into a table variable.

Does anyone have a suggestion how I can kill the process or insert the sp_who2 info into a temp table?

Using SQL 2000.

Thanks!

Brian

 

[gmmastros]

Create Table #Temp (  
    spid smallint,  
	Status VarChar(100),
	Login VarChar(100),
	HostName VarChar(100),
	BlkBy VarChar(100),
	DBName VarChar(100),
	Command VarChar(100),
	CPUTime Int,
	DiskIO Int,
	LastBatch VarChar(100),
	ProgramName VarChar(100),
	Spid1 Int
)  
  

INSERT INTO #Temp Exec sp_who2  
  
SELECT * FROM #Temp WHERE dbname = 'kootbatch1a'

With sql2000, you cannot Insert/Exec in to a table variable, you need to use a temp table instead. Also.... the columns in the temp table must match the columns returned from the stored procedure.


-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[markros]

You need to add one more field at the end

Spid1 Int, RequestID int

 

[gmmastros]

You need to add one more field at the end

Only if you are running this on SQL2005 (or above?). With SQL2000, sp_who2 returns 12 columns. According to the original post, bmacbmac is using SQL2000.



-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[gmmastros]

I also encourage you to read this:

http://wiki.lessthandot.com/index.php/Kill_All_Active_Connections_To_A_Database

It will show you how you can put the database in to single user mode (and then back to multi-user mode), effectively removing killing all active connections. Of course, you will need to have elevated privileges to run this command, so be aware of that.


-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[markros]

Thanks, George. Yes, I quickly tested it in SQL Server 2008 Express.

 

[bmacbmac]

Thanks guys. The #temp worked great. Will try the single user/multi user mode as well.