2

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?

7
  • 1
    Are you absolutely constrained to a TVF? A Stored Procedure would make this a lot easier to achieve.
    – J.D.
    Dec 1, 2020 at 23:24
  • Not necessarily. I'd prefer an object that I can query directly like a TVF or a view. With a Stored Procedure, I'd have to populate a temp table and query that. That's doable, but adds complexity. Unless there's a method of using a Stored Procedure in this context that I'm not thinking of?
    – Robbie
    Dec 2, 2020 at 15:08
  • You can theoretically query and SELECT the results from a stored procedure by leveraging OPENROWSET or OPENQUERY but those are not the most elegant solutions in my opinion, and have their own limitations. I'll post an answer if I think of something better.
    – J.D.
    Dec 2, 2020 at 15:35
  • 1
    I was saying you could leverage OPENQUERY / OPENROWSET to query a stored procedure as if it was any other dataset. Inside the stored procedure itself you can handle your exception logic (e.g try / catch, if / else, etc) since it's a stored procedure.
    – J.D.
    Dec 2, 2020 at 17:58
  • 1
    OK, I didn’t understand the suggestion at first, J.D. After some testing and tweaking, your suggestion does indeed work. See below for the complete solution. Thank You!
    – Robbie
    Dec 3, 2020 at 17:43

1 Answer 1

1

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

4
  • Btw a temp table might be better / easier to use than a table variable. In this specific case it might not make any difference (other than you wouldn't have to pre-declare it) but for future reference temp tables are more efficient when you need to join to them as opposed to a table variable because of the way the cardinality estimator works in the SQL Server Engine.
    – J.D.
    Dec 3, 2020 at 18:29
  • When I use a temp table SQL Server appears unable to catch the error if a server is down. Querying the view generates the message"Msg 3930, Level 16, State 1, Procedure MyDatabase.dbo.Test_Proc, Line 29 [Batch Start Line 26] The current transaction cannot be committed and cannot support operations that write to the log file. Roll back the transaction. OLE DB provider "SQLNCLI11" for linked server "SERVER_B" returned message "A network-related or instance-specific error has occurred while establishing a connection to SQL Server. Server is not found or not accessible."
    – Robbie
    Dec 4, 2020 at 15:08
  • Hmm I'm not entirely sure why you get that error just from switching to a TempTable from a TableVariable. I'd have to look closer into it. But in any case, from your example code, it doesn't seem like you do a lot of joining or manipulating of the data so you're probably fine with a TableVariable in this particular case.
    – J.D.
    Dec 4, 2020 at 16:21
  • 1
    I'm not sure either. It's odd. I was hoping that I could use a temp table. That would make it easier to use a cursor loop based on sys.servers to generate and run dSQL for the Try/Catch blocks. The number of servers we have is small and doesn't change that often, so making TEST_PROC more dynamic isn't that high a priority for us.
    – Robbie
    Dec 4, 2020 at 16:32

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.