Unable to catch connectivity errors between linked server

Question

For some reason I cannot catch connectivity related errors only in a specific server.

There is a communication between 2 SQL-Servers through linked server. Sometimes the connection is bad and the the result is the following error:

TCP Provider: The specified network name is no longer available.

I Have try-catch to handle this issue and everything works fine since the severity is not above 20.

BEGIN TRY
DECLARE @return_value int

EXEC    @return_value = [dbo].[sp_someSP]

SELECT  'Return Value' = @return_value

END TRY
BEGIN CATCH
SELECT ERROR_MESSAGE(),ERROR_NUMBER(),ERROR_SEVERITY(),ERROR_PROCEDURE()
--NEVER GOES HERE!!!
END CATCH

the procedure will cause the following error:

OLE DB provider "STREAM" for linked server "(null)" returned message "Protocol error in TDS stream". OLE DB provider "STREAM" for linked server "(null)" returned message "Communication link failure". Msg 64, Level 16, State 1, Line 0 TCP Provider: The specified network name is no longer available. OLE DB provider "STREAM" for linked server "(null)" returned message "Query timeout expired".

I tested the linked Server and works fine (I even recreated the Linked Server)

Linked Server Configuration:

@optname=N'collation compatible', @optvalue=N'false'

@optname=N'data access', @optvalue=N'true'

@optname=N'dist', @optvalue=N'false'

@optname=N'pub', @optvalue=N'false'

@optname=N'rpc', @optvalue=N'true'

@optname=N'rpc out', @optvalue=N'true'

@optname=N'sub', @optvalue=N'false'

@optname=N'connect timeout', @optvalue=N'100'

@optname=N'collation name', @optvalue=null

@optname=N'lazy schema validation', @optvalue=N'false'

@optname=N'query timeout', @optvalue=N'100'

@optname=N'use remote collation', @optvalue=N'true'

@optname=N'remote proc transaction promotion', @optvalue=N'false'

SQL Server: 2008 R2 Standard Edition (64-bit)

I cannot understand why I cannot catch the following error.

EDIT: The problem lies beyond the execution of the Stored Procedure. Even if I execute something like this:

EXEC ('Select * from SomeTable') at LinkedServer

Sometimes (Less than 0.1% of the cases) I will get the mentioned error. I am trying to find some kind of pattern but so far no luck. Also, I searched if there is some kind of network configuration that might be killing the queries but the result was negative.

Answer 1

Try to use dynamic SQL like this:

BEGIN TRY
    DECLARE @sql VARCHAR(1000) = 'EXEC [YourDatabase].[dbo].[sp_someSP]';   
    EXEC (@sql) AT [YourServer];
END TRY
BEGIN CATCH
    SELECT ERROR_NUMBER(), ERROR_MESSAGE();
END CATCH;

Or one level up like that:

BEGIN TRY
    DECLARE @sql VARCHAR(1000) = 
       'EXEC (''EXEC [YourDatabase].[dbo].[sp_SomeSP] 
       ''''Parameter1'''', ''''Parameter2'''''') AT [YourLinkedServer]';
    PRINT @sql
    EXEC (@sql); 

END TRY
BEGIN CATCH
    SELECT ERROR_NUMBER(), ERROR_MESSAGE();
END CATCH;

See this answer, it seems that the cause is a compile time error.