How to Cancel a SQL Server Execution Process Programmatically

SQL Server - stop or break execution of a SQL script

The raiserror method

raiserror('Oh no a fatal error', 20, -1) with log

This will terminate the connection, thereby stopping the rest of the script from running.

Note that both severity level 20 or higher and the WITH LOG option are necessary for it to work this way.

This even works with GO statements, eg.

print 'hi'
go
raiserror('Oh no a fatal error', 20, -1) with log
go
print 'ho'

Will give you the output:

hi
Msg 2745, Level 16, State 2, Line 1
Process ID 51 has raised user error 50000, severity 20. SQL Server is terminating this process.
Msg 50000, Level 20, State 1, Line 1
Oh no a fatal error
Msg 0, Level 20, State 0, Line 0
A severe error occurred on the current command.  The results, if any, should be discarded.

Notice that 'ho' is not printed.

CAVEATS:

• This only works if you are logged in as admin ('sysadmin' role), and also leaves you with no database connection.
• If you are NOT logged in as admin, the RAISEERROR() call itself will fail and the script will continue executing.
• When invoked with sqlcmd.exe, exit code 2745 will be reported.

Reference: http://www.mydatabasesupport.com/forums/ms-sqlserver/174037-sql-server-2000-abort-whole-script.html#post761334

The noexec method

Another method that works with GO statements is set noexec on (docs). This causes the rest of the script to be skipped over. It does not terminate the connection, but you need to turn noexec off again before any commands will execute.

Example:

print 'hi'
go

print 'Fatal error, script will not continue!'
set noexec on

print 'ho'
go

-- last line of the script
set noexec off -- Turn execution back on; only needed in SSMS, so as to be able 
               -- to run this script again in the same session.

Stop SQL query execution from .net Code

Yes sqlcommand.cancel is your friend http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlcommand.cancel.aspx

Canceling SQL Server query with CancellationToken

After looking at what your stored procedure is doing, it appears that it is somehow blocking the cancellation.

If you change

RAISERROR (@msg,0,1) WITH NOWAIT;

to remove the WITH NOWAIT clause, then the cancellation works as expected. However, this prevents the InfoMessage events from firing in real time.

You could track progress of the long running stored procedure some other way or register for the token cancellation and call cmd.Cancel() since you know that works.

One other thing to note, with .NET 4.5, you can just use Task.Run instead of instantiating a TaskFactory.

So here's a working solution:

private CancellationTokenSource cts;
private async void TestSqlServerCancelSprocExecution()
{
    cts = new CancellationTokenSource();
    try
    {
        await Task.Run(() =>
        {
            using (SqlConnection conn = new SqlConnection("connStr"))
            {
                conn.InfoMessage += conn_InfoMessage;
                conn.FireInfoMessageEventOnUserErrors = true;
                conn.Open();

                var cmd = conn.CreateCommand();
                cts.Token.Register(() => cmd.Cancel());
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.CommandText = "dbo.[CancelSprocTest]";
                cmd.ExecuteNonQuery();
            }
       });
    }
    catch (SqlException)
    {
        // sproc was cancelled
    }
}

private void cancelButton_Click(object sender, EventArgs e)
{
    cts.Cancel();
}

In my testing of this, I had to wrap ExecuteNonQuery in a Task in order for cmd.Cancel() to work. If I used ExecuteNonQueryAsync, even without passing it a token, then the system would block on cmd.Cancel(). I'm not sure why that's the case, but wrapping the synchronous method in a Task provides a similar usage.

T-SQL STOP or ABORT command in SQL Server

An alternate solution could be to alter the flow of execution of your script by using the GOTO statement...

DECLARE  @RunScript bit;
SET @RunScript = 0;

IF @RunScript != 1
BEGIN
RAISERROR ('Raise Error does not stop processing, so we will call GOTO to skip over the script', 1, 1);
GOTO Skipper -- This will skip over the script and go to Skipper
END

PRINT 'This is where your working script can go';
PRINT 'This is where your working script can go';
PRINT 'This is where your working script can go';
PRINT 'This is where your working script can go';

Skipper: -- Don't do nuttin!

Warning! The above sample was derived from an example I got from Merrill Aldrich. Before you implement the GOTO statement blindly, I recommend you read his tutorial on Flow control in T-SQL Scripts.

How to cancel a running SQL query?

There are two different sessions that will help you:

If you want to exchange an object like your statement between two requests from the same user, independent if these requests run in parallel or one after another, you usually store them in the HttpSession of the HttpServletRequest.

And you can use the Session of Hibernate to cancel the current query:

public void startLongRunningStatement() {
  EntityManager entityManager = ...

  // Aquire session
  Session hibernateSession = ((HibernateEntityManager) em.getDelegate()).getSession();

  // Store the HibernateSession in the HttpSession
  HttpSession httpSession = servletRequest.getSession()
  httpSession.setAttribute("hibernateSession", hibernateSession);

  try {
    // Run your query  
    Query query = mEntityManager.createNativeQuery(globalQuery.toString());
    List<?> results = query.getResultList();

  } finally {
    // Clear the session object, if it is still ours
    if (httpSession.getAttribute("hibernateSession") == hibernateSession) {
      httpSession.removeAttribute("hibernateSession");
    }
  }
}

public void cancel() {
  // Get the Hibernate session from the HTTP session
  HttpSession httpSession = servletRequest.getSession()
  Session hibernateSession = (Session) httpSession.getAttribute("hibernateSession");
  if (hibernateSession != null) {
    // Cancel the previous query
    hibernateSession.cancelQuery();
  }

}

Gracefully terminate long running SQL Server query from ADO.NET

Calling Close is sufficient, just a few remarks:

First of all make sure you write your Close methods inside finally block to ensure correct exception handling.

Also you do not need to call both SqlConnection.Close and SqlConnection.Dispose methods as they both do the same. Dispose method is added to implement IDisposable pattern and this method internally calls SqlConnection.Close method, which has more natural name, because we close connections, but not dispose them :)

If you are using DataReader then you have to close it as well each time after you want to use connection for other purpose.

stop a DTS package programmatically (using C#)

Here are a few options:

1. If you are running a DTS package in a job, then you can stop the running job with sp_stop_job.

2. If you know the SPID of the process for the DTS package, you can issue a KILL command to the SPID and that will stop the DTS package execution. One possible way to identify the SPID is to find the SPID in the master.dbo.sysprocesses table by some identifying piece of information and then issue a KILL command for the SPID. Here's a set of code that I tested by setting the Connection Properties -> Advanced -> Application Name to I Want To Kill This SPID.

   DECLARE @TEST INT;

   SET @TEST =

   (SELECT MAX(SPID)

   FROM master.dbo.sysprocesses

   WHERE program_name IN ('I Want To Kill This SPID'));

   IF @TEST IS NOT NULL

   BEGIN

   DECLARE @KILL_COMMAND NVARCHAR(100);

   SET @KILL_COMMAND = 'KILL ' + CONVERT(NVARCHAR(100), @TEST);

   EXEC sp_executeSQL @KILL_COMMAND;

   END

   The downside of the above approach is that it only works on killing packages that have database connections. If you are using a DTS package that does not connect to the host server, then there won't be a killable SPID. If there are multiple SPIDs, this also won't kill them all. You may have to modify the code to build a list of SPIDs to kill. Then there is the fact that you have to set the Application Name. You may want to set unique values for each DTS Package so that you don't accidentally kill the wrong package. The default value appears to be set to DTS Designer which would be bad to kill if multiple packages are running on the server.

3. You might be able to start and kill DTS 2000 packages using extended stored procedures, but I am not 100% sure this is feasible.

---

Related Topics