Handle "Query was cancelled by user." in CLR StoredProc

Question

Hi everyone

How can one elegantly handle the situation where the user cancels the CLR procedure (Alt+Break or job cancellation etc)?

There doesn't appear to be any event or way to handle it. I've spent the past few hours trying to find a way to determine if the procedure had been cancelled by the user.  What seems to be happening is that the CLR runtime for the procedure seems to get cleaned up immediately from when it is cancelled - it's like the runtime for the procedure has already been disposed. If I set a breakpoint and cancel the SP then I find my code context is immediately invalid.

I was hoping to use a global Mutex but I whilst I can create a global mutex if the proc gets cancelled then the mutex still remains because it hasn't been released, due to the abrupt termination of the CLR procedure.

This may be by-design, but is there any way this can be handled?

Thanks

Answer 1

I don't think there is a difference between a CLR and T-SQL stored procedure in this regard.  Cancel mean cancel; no user code executes after the attention signal.

---

Dan Guzman, SQL Server MVP, http://weblogs.sqlteam.com/dang/