While working on a project today, I ran into an interesting issue I’d never encountered before.
THROW command is non-terminating if it is used in a stored procedure over a linked-server.
I don’t know the details to why it works this way. The
THROW command returns an error message with a severity level of 16, which, according to my RAISERROR Cheatsheet, does not stop execution.
There’s something special about the
THROW command beyond raising an error message. Behind the scenes, there is likely some extra information being passed to tell SQL Server that execution needs to stop in that moment, and that extra bit of information does not appear to be passed between linked servers.
Setting up the demo instance
This was fairly easy to set up a demo script for. I personally tested this using SQL Server 2019 on Docker. Normally I wouldn’t mention using Docker, but since this requires setting up a linked server, some of you may not have access to do so.
I won’t walk through setting up docker or anything like that (I’d like to in a future blog post), but if you are already familiar with it, you can go here to see what images are available. This is the command I used:
docker run -e 'ACCEPT_EULA=Y' -e 'MSSQL_SA_PASSWORD=yourStrong(!)Password' -p 1433:1433 -d mcr.microsoft.com/mssql/server:2019-latest
After that’s all done and running, connect to the instance, and create a database:
CREATE DATABASE SandBox; GO USE SandBox; GO
Recreating the issue
First, you need to add a linked server, if you already have one, then you can skip this step:
EXEC sys.sp_addlinkedserver @server = 'LOCALHOST';
Thankfully I didn’t need to set up an additional server to recreate this issue, it worked just fine using a linked server pointing at itself.
Next, let’s create some sample stored procedures:
This one simply raises an exception using
CREATE OR ALTER PROCEDURE dbo.Fail AS THROW 51000, 'Test', 1; GO
The following two procs execute
dbo.Fail, one locally, the other over a linked server. Then tries to output a message as test to see if the
THROW caused our proc to terminate execution.
CREATE OR ALTER PROCEDURE dbo.FailLocal AS EXEC SandBox.dbo.Fail; RAISERROR('If you can see this, it didnt terminate',0,1) WITH NOWAIT; GO
CREATE OR ALTER PROCEDURE dbo.FailLinkServer AS EXEC LOCALHOST.SandBox.dbo.Fail; RAISERROR('If you can see this, it didnt terminate',0,1) WITH NOWAIT; GO
Run the procs
Running the first proc should do exactly what I would expect it to do:
dbo.Fail is run, it raises an exception, and as expected, all execution stops and the output message isn’t returned.
Now run the second proc:
This time, we ran
dbo.Fail via our linked sever connection. But, rather than raising an exception and terminating execution, it continued on running.
I don’t know if there are better ways to handle this, but in my case, my plan to get around this problem is to call the stored procedure from a try/catch block, and then rethrow the error from there.
CREATE OR ALTER PROCEDURE dbo.FailLinkServer AS BEGIN TRY EXEC LOCALHOST.SandBox.dbo.Fail; END TRY BEGIN CATCH THROW; END CATCH; RAISERROR('If you can see this, it didnt terminate',0,1) WITH NOWAIT; GO
This way, any errors thrown by the stored procedure remotely, are re-thrown locally. After applying this fix, and running a test…
Woohoo! Now it’s working how I would expect it to work.