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

4

October 27, 2020 by Kenneth Fisher

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.

4 thoughts on “How do I test if a linked server works using T-SQL?

  1. Chad Estes says:

    “What exactly was I doing? Well, you’ll have to wait a bit to find out.”
    How long will you keep us in suspense?
    Inquiring minds want to know.

  2. […] recently did a post on testing a linked server where I said I would explain why I wanted to make the test. Basically I needed to scan a few […]

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Enter your email address to follow this blog and receive notifications of new posts by email.

Join 3,154 other followers

Follow me on Twitter

ToadWorld Pro of the Month November 2013
%d bloggers like this: