How can you cancel a SQL Server execution process programmatically

sql, sql-server, t-sql

Solution

use `KILL` with the process id:

KILL 53;

Just be aware that you can't kill your own spid, you need to create another connection and then kill the spid from the

if you try to kill your own SPID you will get the following error

Server: Msg 6104, Level 16, State 1, Line 1
Cannot use KILL to kill your own process.

Problem

Let's say you have execute the following (long running) process from your code: ``` int processID = DB.Execute(SQL); //some long running sql statement ``` Is there a way to programatically call SQL Server to cancel the process if it is taking too long (kind of like hitting the "Stop" button in QueryAnalyzer)? ``` //cancel the process if it is taking too long DB.Execute("sp_CancelProcess @ProcessID=" + processID); ```

Original source