How do I rollback a SQL CLR stored procedure when a user cancels the query?

Unfortunately SQLCLR does not handle query cancellation very well. However, given the error message, that seems to imply that the cancellation does its own ROLLBACK. Have you tried not using a Transaction within the SQLCLR code but instead handling it from outside? Such as:

1. BEGIN TRAN;
2. EXEC SQLCLR_Stored_Procedure;
3. IF (@@TRANCOUNT > 0) ROLLBACK TRAN;

The workflow noted above would need to be enforced. This can be done rather easily by creating a wrapper T-SQL Stored Procedure that executes those 3 steps and only give EXECUTE permission to the wrapper Stored Procedure. If permissions are then needed for the SQLCLR Stored Procedure, that can be accomplished rather easily using module signing:

1. Create an Asymmetric Key in the same DB as the SQLCLR Stored Procedure
2. Create a User from that Asymmetric Key
3. GRANT that Key-based User EXECUTE permission on the SQLCLR Stored Procedure
4. Sign the wrapper T-SQL Stored Procedure, using ADD SIGNATURE, with that Asymmetric Key

Those 4 steps allow the T-SQL wrapper proc to execute the SQLCLR proc, while the actual application Login can only execute the T-SQL wrapper proc :-). And, in, the event that the cancellation aborts the execution prior to executing ROLLBACK, the Transaction should be automatically rolled-back when the connection closes.

Also, do you have XACT_ABORT set to ON or OFF? UPDATE: O.P. states that it is set to OFF, and setting to ON did not seem to behave any differently.

Have you tried checking the connection state in the finally block? I am pretty sure that the SqlConnection is Closed upon the cancellation. You could try the following approaches, both in the finally block:

1. Test for the connection state and if Closed, re-open the SqlConnection and then execute the non-query command.

   UPDATE: O.P. states that the connection is still open. Ok, how about closing it and re-opening it?

   UPDATE 2: O.P. tested and found that the connection could not be re-opened.

2. Since the context is still available, as proven by your print commands working, use something like SqlContext.Pipe.ExecuteAndSend(new SqlCommand("DROP TABLE..;"));

   UPDATE: O.P. states that this did not work.

OR, since you create a guaranteed unique table name in the code, you can try creating the table as a global temporary table (i.e. prefixed with two pound-signs: ##TableName) which will a) be available to the bulk import process, and b) clean itself up when the connection fully closes. In this approach, you technically wouldn't need to perform any manual clean up.

Of course, when Connection Pooling is enabled, the automatic cleanup happens only after the connection is re-opened and the first command is executed. In order to force an immediate cleanup, you would have to connect to SQL Server with Connection Pooling disabled. Is it possible to use a different Connection String just when this Stored Procedure is to be executed that includes Pooling=false;? Given how this Stored Procedure is being used, it does not seem like you would suffer any noticeable performance degradation from disabling Connection Pooling on just this one specific call. To better understand how Connection Pooling – enabled or disabled – affects the automatic cleanup of temporary objects, please see the blog post I just published that details this very behavior:

Sessions, Temporary Objects, and the Afterlife

This approach is probably the best overall since you probably cannot guarantee either that ROLLBACK would be executed (first approach mentioned) or that a finally clause would be executed (assuming you ever got that to work). In the end, uncommitted Transactions will be rolled-back, but if someone executes this via SSMS and it aborts without the ROLLBACK, then they are still in an open Transaction and might not be aware of it. Also, think about the connection being forcibly closed, or the Session being killed, or the server being shutdown / restarted. In those cases tempdb is your friend, whether by using a global temporary table, or at the very least creating the permanent Table in tempdb so that it is automatically removed the next time that the SQL Server starts (due to tempdb being created new, as a copy of model, upon each start of the SQL Server service).

Stepping back there are probably much better ways to pass the data out of your CLR procedure.

1) you can simply use the SqlContext Pipe to return a resultset without creating a table.

2) you can create a temp table (#) in the calling code and access it from inside the CLR procedure. You might want to introduce a TSQL wrapper procedure to make this convenient.

Anyway using BEGIN TRAN/COMMIT | ROLLBACK worked for me:

using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
using System.Threading;

static class SqlConnectionExtensions
{
    public static DataTable ExecuteDataTable(this SqlConnection con, string sql, params SqlParameter[] parameters)
    {
        var cmd = new SqlCommand(sql, con);
        foreach (var p in parameters)
        {
            cmd.Parameters.Add(p);
        }
        using (var dr = cmd.ExecuteReader())
        {
            var dt = new DataTable();
            dt.Load(dr);
            return dt;
        }
    }

    public static int ExecuteNonQuery(this SqlConnection con, string sql, params SqlParameter[] parameters)
    {
        var cmd = new SqlCommand(sql, con);
        foreach (var p in parameters)
        {
            cmd.Parameters.Add(p);
        }
        return cmd.ExecuteNonQuery();
    }

}

public partial class StoredProcedures
{
    [Microsoft.SqlServer.Server.SqlProcedure]
    public static void GetData(SqlString code)
    {
        Guid guid = Guid.NewGuid();
        using (var connection = new SqlConnection("context connection=true"))
        {
            connection.Open();

            try
            {
                connection.ExecuteNonQuery("begin transaction;");
                SqlContext.Pipe?.Send("Constrain");

                connection.ExecuteNonQuery($"CREATE TABLE qb.{code}_{guid:N} (Id INT)");

                SqlContext.Pipe?.Send($"Create: qb.{code}_{guid:N}");

                //emulate service call
                Thread.Sleep(TimeSpan.FromSeconds(10));

                SqlContext.Pipe?.Send($"Done: qb.{code}_{guid:N}");
                connection.ExecuteNonQuery("commit transaction");

            }
            catch (Exception ex)
            {
                connection.ExecuteNonQuery("rollback;");
                throw;
            }

        }

    }
}

While it is not something advised for use, this would be an ideal match for sp_bindsession. With sp_bindsession, you would call sp_getbindtoken from the context session, pass the token to the server and call sp_bindsession from the service connection.

Afterwards, the two connections behave "as one", with temporaries and transactions being transparently propagated.