I was working on designing a distributed system using SQL Server 2008 Linked Servers. I wanted to engineer in fault tolerance for Linked Server being offline. I wanted to find the least expensive call to determine if a Linked Server is online and available. I searched BOL and I found sp_testlinkedserver, and it looked just like what I need. So I tried it out with a linked server with this script:
» declare @w int;
» exec @w = sys.sp_testlinkedserver N'DATASVR'
» print case @w when 0 then 'Online' else 'Offline' end;
When I ran it I got this result:
I thought "Wow, that's cool. I could really have used that in SQL 2000."
Then, being a thorough experimenter, I wanted to see it in action when the Linked Server is offline. So I stopped the SQL Server 2000 service on the Linked Server. This is the result I got:
» OLE DB provider "SQLNCLI10" for linked server "DATASVR" returned message "Login timeout expired".
» OLE DB provider "SQLNCLI10" for linked server "DATASVR" 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. Check if instance name is correct and if SQL Server is configured to allow remote connections. For more information see SQL Server Books Online.".
» Msg 2, Level 16, State 1, Line 0
» Named Pipes Provider: Could not open a connection to SQL Server .
Notice that "Offline" was never printed. The call to sp_testlinkedserver fails so catastrophically that the next statement is never executed! What is the point of having something to test a connection if subsequent code cannot recover from the test?
We evaluated the concept of putting the call to sp_testlinkedserver in a try-catch block, but we could just put our code that uses the Linked Server in the try-catch block as well. It seems the code inside of sp_testlinkedserver should have a try-catch block so that the [OLE DB provider "SQLNCLI10" for linked server "XXXXX"] errors don't crash sp_testlinkedserver.