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

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)

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
Cool. The thread object itself is "waitable", with Thread.Join learn.microsoft.com/en-us/dotnet/api/…
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

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Related