[Comments] THROW command is non-terminating across linked servers

[chadbaldwin]

https://chadbaldwin.net/2021/04/02/throw-non-terminating-with-linked-server.html

[EitanBlumin]

Have you tried enabling the arithabort and xact_abort settings?
Those two can help when using nested execution contexts.

[chadbaldwin]

@EitanBlumin Nice! Good call on XACT_ABORT that works too. Before testing it, I didn't think it would make a difference because I wasn't using transactions, but it worked!

I just read the documentation for XACT_ABORT and I see this:

The THROW statement honors SET XACT_ABORT. RAISERROR does not. New applications should use THROW instead of RAISERROR.

That explains why this works. That's really interesting. To test this even further, instead of using THROW, I used RAISERROR and tested severity levels 0-19 (20+ kills the connection), and to my surprise (exactly as the documentation said), it did not terminate execution.

Which brings up another really important thing to know... If you're calling a stored procedure, and it uses RAISERROR('Error Message',16,1) (which is the same severity level used by THROW), not only will it not stop execution, but it will even ignore XACT_ABORT ON.

I was hoping XACT_ABORT ON would be a nice clean solution for all cases. But it seems a TRY/CATCH/THROW block is still the best way to go.

---

I tested ARITHABORT, though I didn't expect this to do anything since I wasn't throwing an overflow or div/0 error, and the test confirmed my expectations. It didn't make a difference in this case when using THROW to raise the exception.