Kill its own session in SQL Query Analyzer

We all know that using KILL can terminate the connection of a  client to SQL server. But how to terminate its own connection in SQL Query Analyzer? KILL does not work because it cannot kill its own process.

However, we can make another connection to the server and run the KILL command with current process ID. E.g. in SQL Query Analyzer, you can use xp_shellcmd and isql to make the connection and run KILL with the current process ID to it. The sample code as following:

 DECLARE @Command nvarchar(255)
 DECLARE @SPID int
 DECLARE @ServerName nvarchar(255)
 SELECT @ServerName=CAST(SERVERPROPERTY(‘servername’) as nvarchar(255)),@SPID=@@SPID

 SET @Command=N’isql -S ‘+@ServerName+’ -d master -E -Q "KILL ‘+CAST(@SPID as nvarchar(20))+’"’
 EXEC master..xp_cmdshell @command
GO

Why we need to kill its own process? We need it when we run multiple batches in QA if some condition is met. The reasons are:

  • We cannot check the condition in the beginning of each batch, e.g. you cannot create procedure etc DML in a "IF" brantch;
  • Raierror error in one batch does not prevent QA to submit the following batch.

E.g. The following batches will be executed if the current db is "Northwind"

SET NOCOUNT ON
GO
IF DB_NAME()<>’Northwind’
BEGIN
— Print message if the current db is not Northwind
 PRINT ‘Wrong database. The process is to be terminated. You will see multi Communication link failure errors’
END
GO
IF DB_NAME()<>’Northwind’
BEGIN

— Terminate the connection if the current db is not Northwind.
— The following bacthes will NOT be executed
DECLARE @Command nvarchar(255)
 DECLARE @SPID int
 DECLARE @ServerName nvarchar(255)
 SELECT @ServerName=CAST(SERVERPROPERTY(‘servername’) as nvarchar(255)),@SPID=@@SPID

 SET @Command=N’isql -S ‘+@ServerName+’ -d master -E -Q "KILL ‘+CAST(@SPID as nvarchar(20))+’"’
 EXEC master..xp_cmdshell @command
END
GO
SELECT * FROM dbo.Categories
GO
SELECT * FROM dbo.Employees
GO
IF EXISTS(SELECT 1 FROM sysobjects WHERE ID=OBJECT_ID(‘dbo.usp_Test’))
DROP PROCEDURE dbo.usp_Test
GO
CREATE PROCEDURE dbo.usp_Test
AS
SELECT * FROM dbo.Region
GO
IF EXISTS(SELECT 1 FROM sysobjects WHERE ID=OBJECT_ID(‘dbo.usp_Test’))
DROP PROCEDURE dbo.usp_Test
GO

If you select Northwind database, the batches will have no errors give u rresults. If you select other database, it will print the message that you selected wrong database and no other batches are executed. As you can see, one side effect is you will receive lots of "Communication link failure" error. The number of the error is the same as the number of following batches you have.

 

Advertisements
This entry was posted in SQL server. Bookmark the permalink.

One Response to Kill its own session in SQL Query Analyzer

  1. Youjin says:

    Peter, very helpful tip! Thanks!!! 🙂

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s