Sql-server – Graceful Test for Linked Server Availability Inside a TVF or View

linked-serverset-returning-functionssql servert-sql

I’ve got a mission critical table valued function that needs to return results from several linked servers simultaneously. If any of those servers are down, it still needs to return results from the other servers. Because it’s function a Try/Catch block is not allowed. Dynamic SQL is not permitted inside functions either.

sys.sp_testlinkedserver seems like the obvious solution. It returns an integer when the linked server is online. However, it throws an error when there’s a problem with he linked server, so it’s useless.

This TVF will replace a similarly unreliable view based on the following query:

SELECT Col_1, Col_2, Col_3, Col_4 FROM Server_A.Some_DB.TheTable 
UNION 
SELECT Col_1, Col_2, Col_3, Col_4 FROM Server_B.Some_DB.TheTable 
UNION
SELECT Col_1, Col_2, Col_3, Col_4 FROM Server_C.Some_DB.TheTable

Again, if any of the servers are down, the query throws an error. This is why I thought that a TVF would be useful so that I can test a server before doing anything that could cause an error.

Are there any graceful ways to test the availability of a linked server that don’t throw errors when the linked server is down?

Best Answer

The solution is to create a stored procedure on the local server that includes error handling for queries to remote servers and returns a single dataset. Then to create a view that calls the stored procedure using OPENROWSET.

The stored procedure has to use a table variable for holding the data it gets from each server. It can’t use a UNION between them because that would defeat the purpose of error trapping.

Here is an example of the stored procedure. In this example, I’m querying sys.Databases because that’s a view that everyone has and can easily test:

USE MyDatabase
GO
CREATE PROCEDURE TEST_PROC 
AS
    SET FMTONLY OFF
    SET NOCOUNT ON

    DECLARE @OUTPUT TABLE (
        Server_Name NVARCHAR(100),
        Name        NVARCHAR(128),
        Database_ID INTEGER,
        State       INTEGER,
        State_Desc  NVARCHAR(60) )

    BEGIN TRY
        INSERT INTO @OUTPUT (Server_Name, Name, Database_ID, State, State_Desc) 
        SELECT 'SERVER_A',  Name, Database_ID, State, State_Desc 
        FROM SERVER_A.Master.sys.Databases 
    END TRY
    BEGIN CATCH
        INSERT INTO @OUTPUT (Server_Name, Name, Database_ID, State, State_Desc) 
        SELECT 'SERVER_A',  'Server is Unavailable', Null, NULL, ''  
    END CATCH

    BEGIN TRY
        INSERT INTO @OUTPUT (Server_Name, Name, Database_ID, State, State_Desc) 
        SELECT 'SERVER_B',  Name, Database_ID, State, State_Desc 
        FROM SERVER_B.Master.sys.Databases 
    END TRY
    BEGIN CATCH
        INSERT INTO @OUTPUT (Server_Name, Name, Database_ID, State, State_Desc) 
        SELECT 'SERVER_B',  'Server is Unavailable', Null, NULL, ''  
    END CATCH

    BEGIN TRY
        INSERT INTO @OUTPUT (Server_Name, Name, Database_ID, State, State_Desc) 
        SELECT 'SERVER_C',  Name, Database_ID, State, State_Desc 
        FROM SERVER_C.Master.sys.Databases 
    END TRY
    BEGIN CATCH
        INSERT INTO @OUTPUT (Server_Name, Name, Database_ID, State, State_Desc) 
        SELECT 'SERVER_C',  'Server is Unavailable', Null, NULL, ''  
    END CATCH
GO

You will have to create a TRY/CATCH block for every server you want to query. With a little creativity, I’m guessing that it’s possible to do the above using a cursor loop based on sys.servers to generate and execute the above TRY/CATCH blocks for all linked servers.

Here is the associated view:

CREATE VIEW TEST_VIEW 
AS
    SELECT SELECT Server_Name, Name, Database_ID, State, State_Desc 
    FROM OPENROWSET('SQLNCLI11', 'Server=.;Trusted_Connection=yes;', 'EXEC MyDatabase.dbo.Test_Proc')   
GO

Note that “Server=.;” in the connection string points to the local server. My understanding of SQL data sources is a little shaky, so this may not work for everyone. Also, you may have to update the rest of the connection string to match the needs of your environment.

The only downside I’ve encountered in testing is that if one of the servers is offline, it’s got about a 21 second timeout. So it takes at least that long to return. When all servers are up, it runs in a fraction of a second. I’ve tried changing the timeout parameters in the linked servers definitions, but they don’t appear to make a difference. I’ll experiment more and update this solution if I find better results.

Thanks J.D. I wouldn’t have known to go this route without your suggestions.

-Robbie

Related Question