SQL Server: Is that Linked Server really up?

Recently I needed to fix a SQL Server 2000 stored procedure that called various stored procedures on remote database servers (again using SQL Server 2000). In this case it was splitting a centralised queue of tasks across the database servers that needed to handle each request. For centralised management of remote sites this makes a lot of sense. To do this it relied upon declaring each of these servers as a Linked Server (an entry in the sysservers table).

The problem we had was that when one of those database servers was down it held up the rest of the process because the main stored procedure immediately died as soon as it could not see the Linked Server.

I found a nice explanation of why this happens on the SQL Server Engine Tips blog. They mentioned this was a problem with SQL Server 2000:

“Due to lack of exception handling and implementation of OPENQUERY/OPENROWSET/OPENDATASOURCE interfaces it is not possible to do it cleanly.”

However, there is a new stored procedure in SQL Server 2005 called sp_testlinkedserver that will solve the problem. Wonderful!

Unfortunately SQL Server 2005 is not an available option, so I was pleased to see someone had left another solution to this in the comments section. SQLDBATips.com has a sample stored procedure called usp_serverup that gets rid of the problem by using SQL-DMO to test the availability of the Linked Server. That gives us a tool we can use to test the connection prior to running the code that will down our SP. Now that is cool.

[UPDATE: The usp_serverup solution utilises OLE Automation (which is why it avoids the aforementioned problem), which means that the account running it must have sa privileges within SQL Server. That creates a major security issue, so we're falling back to making the Windows service that calls the stored procedure chunk its calls by site, thus insulating each site from the failure of other sites.]