Stored Procedure Error

• Jerish

  SSC Veteran

  Points: 267

  More actions

  May 16, 2002 at 1:34 am

  #158241

  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

  dess@satyam.net.in

• sxnandwa

  Right there with Babe

  Points: 716

  More actions

  May 16, 2002 at 8:17 am

  #429269

  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.

• sxnandwa

  Right there with Babe

  Points: 716

  More actions

  May 16, 2002 at 8:27 am

  #429270

  A minor modification I forgot to add space in Kill so strCommand should be like

  Select @StrCommand = "Kill "+@Convert(Varchar(10),@Spid)