How do I test if a linked server works using T-SQL?

I get that linked servers are almost as despised as the dreaded cursor or (lord save us) NOLOCK, but they do have their places. In this particular case I was looping through a series of servers (using a cursor), creating a temporary linked server, grabbing some information, and logging it. Unfortunately not all of the servers were valid and of those that are valid, I don’t have access to all of them. The first thing I tried was to just put the create statement in a try catch block.

BEGIN TRY  
EXEC master.dbo.sp_addlinkedserver @server = N'MyLinkedServer', @srvproduct=N'', @provider=N'SQLNCLI', @Datasrc = @ServerName  
EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'MyLinkedServer',@useself=N'True',@locallogin=NULL,@rmtuser=NULL,@rmtpassword=NULL  
END TRY
BEGIN CATCH
INSERT INTO dbo.[LinkedServerLog] VALUES (
@ServerName       
,ERROR_NUMBER()  
,ERROR_SEVERITY()  
,ERROR_STATE()  
,ERROR_PROCEDURE()  
,ERROR_LINE()  
,ERROR_MESSAGE());    
END CATCH

Unfortunately, if there is a problem with the server / server name the sp_addlinkedserver doesn’t seem to throw an error and sp_addlinkedsrvlogin throws an error that breaks me out of the try block. So after my Google-fu failed me I did what I frequently do and asked on #sqlhelp. And I was given an answer that led me to the solution.

It turns out there is a function sp_testlinkedserver, and that was the answer. Once the linked server was created I could test it and if it failed it jumped into the catch block and I could log what happened. I would still have a problem if sp_addlinkedsrvlogin failed, but in all of the cases where I tried it it ran just fine.

BEGIN TRY  
EXEC master.dbo.sp_addlinkedserver @server = N'MyLinkedServer', @srvproduct=N'', @provider=N'SQLNCLI', @Datasrc = @ServerName  
-- Test the linked server.
EXEC sp_testlinkedserver @server = N'MyLinkedServer'  
EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'MyLinkedServer',@useself=N'True',@locallogin=NULL,@rmtuser=NULL,@rmtpassword=NULL  
END TRY
BEGIN CATCH
INSERT INTO dbo.[LinkedServerLog] VALUES (
@ServerName       
,ERROR_NUMBER()  
,ERROR_SEVERITY()  
,ERROR_STATE()  
,ERROR_PROCEDURE()  
,ERROR_LINE()  
,ERROR_MESSAGE());    
END CATCH

What exactly was I doing? Well, you’ll have to wait a bit to find out.