May 16, 2002 at 1:34 am
Hi Guys,
I am trying to create a Stored Procedure To Kill all the processes which are making the
lock in Emp Database . The stored procedure give syntax error at line no 11 . ie, at the kill
command. [If I am giving a spid manually for the kill command it will not give any syntax error.]
What is the problem in the SP and Is SP kill all the processes involved in Locking ?
Please confirm ASAP.
Also I want to know whether killing a process make any problem in SQL Server and what are the
things the Kill Command Do ?
ie, assume that the lock may occur at the middle of a transaction . If we kill the processes,
will it automatically rollback that transaction or only kill that particular process ?
=====================================
CREATE PROCEDURE sp_drop_locks AS
declare @blkid int
set @blkid = 0
select @blkid = max(blocked) from master.dbo.sysprocesses
where dbid=(select isnull(db_id('Emp'),-1))
WHILE (@blkid is not null and @blkid > 0 )
BEGIN
KILL @blkid
select @blkid = max(blocked) from master.dbo.sysprocesses
where dbid=(select isnull(db_id('Emp'),-1))
IF (@blkid is not null and @blkid > 0 )
CONTINUE
ELSE
BREAK
END
GO
====================================
Thanks
Jerish
May 16, 2002 at 8:17 am
The Kill command in its basic form is Pre Compiled so to have it working with variable SpID , You will need to use dynamic SQL.
For eg if you have ur SpID in variable @SpId then U Can use
Select @StrCommand = "Kill"+@Convert(Varchar(10),@Spid)
and the say Exec(@StrCommand).
Hope this resolves a part of your problem.
May 16, 2002 at 8:27 am
A minor modification I forgot to add space in Kill so strCommand should be like
Select @StrCommand = "Kill "+@Convert(Varchar(10),@Spid)
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply