How to detect query cancellation signal inside a SQLCLR function

Question

I have a CLR scalar function that does some calculations, these can take a long time.

When the user cancels the execution of the query, it doesn't seem like the function execution is affected, so the query continues to execute forever.

Is there a way to get a signal inside C# that says query is cancelled and cancel the processing inside the CLR function? Like a CancellationToken or something like that?

SQLCLR was written long before CancellationToken or async were created. It's not a good idea to create expensive CLR functions in the first place. They're using the same RAM and threads as the database engine but the query optimizer has on idea what their cost is. SQLCLR isn't the equivalent of sp_execute_external_script — Panagiotis Kanavos
– Panagiotis Kanavos, Commented Sep 11 at 13:35
What are you trying to do, and why with SQLCLR? At some point a C# extension was created on top of .NET Core. The compiled code runs outside the database and data gets transferred by sp_execute_external_script to and from the code as a DataFrame. — Panagiotis Kanavos
– Panagiotis Kanavos, Commented Sep 11 at 13:40
@PanagiotisKanavos this is for internal use. I have some data in cold storage which resides somewhere far away and not in a database, which can take a lot of time to filter. I want to access using a function so i can do a easy FROM clrFunction(...) query. I can add a timeout but i'd rather do something more fancy like cancellation detection if possible — siggemannen
– siggemannen, Commented Sep 11 at 13:45
All the more reason to not do this in a SQLCLR function because you're freezing database engine threads, slowing other queries as well. Cancellation wasn't even a concept when SQLCLR was created. In fact, MS always stressed the performance implications. And the SQL Server team always grumbled about .NET's memory usage — Panagiotis Kanavos
– Panagiotis Kanavos, Commented Sep 11 at 13:47
The function itself just gives the job to someone else and waits for an answer, i don't think it uses anything other than a thread. But anyways, the question is mostly about if it's possible to detect cancellation, since surely Microsoft knows that people cancel queries, if not, then i can look into other solutions — siggemannen
– siggemannen, Commented Sep 11 at 13:50

David Browne - Microsoft · Accepted Answer · 2025-09-12 23:18:59Z

4

You should get a ThreadAbortException, and unless you are using threading or tasks, that will cancel the running code. If you are using threading or tasks, you'd have to catch the exception and cancel them.

I tested with this code

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

public partial class UserDefinedFunctions
{
    [Microsoft.SqlServer.Server.SqlFunction]
    public static int ClrDelay(int durationMs, bool spin = false)
    {
        var sw = new Stopwatch();
        sw.Start();
        try
        {
            while (sw.ElapsedMilliseconds < durationMs)
            {
                if (spin)
                {
                    Thread.SpinWait(10);
                }
                else
                {
                    Thread.Sleep(10);
                }
            }

                return durationMs;
        }
        catch (Exception ex)
        {
            System.IO.File.WriteAllText("c:\\temp\\out.txt", $"Catch at {DateTime.Now} {ex}");
            throw;
        }
        catch
        {
            System.IO.File.WriteAllText("c:\\temp\\out.txt", "Catch");
            throw;
        }
    }
}

And the output after running

select dbo.clrdelay(10000,0)

and canceling in SSMS is

PS C:\temp> cat .\out.txt
Catch at 9/11/2025 10:29:43 AM System.Threading.ThreadAbortException: Thread was being aborted.
   at Microsoft.Win32.SafeNativeMethods.QueryPerformanceCounter(Int64& value)
   at System.Diagnostics.Stopwatch.GetTimestamp()
   at System.Diagnostics.Stopwatch.GetRawElapsedTicks()
   at System.Diagnostics.Stopwatch.GetElapsedDateTimeTicks()
   at System.Diagnostics.Stopwatch.get_ElapsedMilliseconds()
   at UserDefinedFunctions.ClrDelay(Int32 durationMs, Boolean spin)

edited Sep 12 at 23:18

answered Sep 11 at 15:32

David Browne - Microsoft

90.7k7 gold badges52 silver badges87 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

siggemannen Sep 12 at 7:29

Yes, this worked nicely! I put the "slow" code in a thread and created a manual reset event which i wait for until the thread is done. The wait is wrapped in a try catch, and on error i cancel the "slow code". So, the trick is to have an "alertable" wait so CLR has a chance to call into the thread safely

David Browne - Microsoft Sep 12 at 15:11

Cool. The thread object itself is "waitable", with Thread.Join learn.microsoft.com/en-us/dotnet/api/…

siggemannen Sep 16 at 12:18

Yeah, i just tried it and it worked with just Thread.Join! Thanks a bunch

Collectives™ on Stack Overflow

How to detect query cancellation signal inside a SQLCLR function

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related