SQL Server TRY...CATCH Is Not Catching An Error

Question

BEGIN TRY
    EXEC N'EXEC sp_testlinkedserver N''[MyLinkedServer]'';';
END TRY
BEGIN CATCH
    SELECT 'LinkedServerDown' AS Result
    RETURN
END CATCH
SELECT TOP(1) FirstName FROM [MyLinkedServer].TestDatabase.dbo.Customer

My first experience with using a TRY...CATCH in SQL Server does not have me impressed so far.

I've stopped the SQL Service on my linked server to attempt to test a situation where our linked server is down, inaccessible, etc.

Instead of catching any error, this code just throws the "Login timeout expired" and "network-related or instance-specific error has occurred..." error and ceases execution of the rest of the code.

Is my SQL TRY...CATCH block not set up correctly?

Answer 1

As per the MSDN, what sp_testlinkedserver do is

Tests the connection to a linked server. If the test is unsuccessful the procedure raises an exception with the reason of the failure.

So when you compile your code (SP), sp_testlinkedserver checks for connection. But you can defer this and capture it by using dynamic SQL.

Like this -

BEGIN TRY
    EXEC sp_executesql N'EXEC sp_testlinkedserver [192.168.51.81];';
END TRY
BEGIN CATCH
    SELECT 'LinkedServerDown' AS Result
END CATCH