Use SYS. sp_testlinkedserver to test whether the linked server connection is normal.

In the forum, I saw a question asking if I could use a T-SQL to test whether the connection to the linked server is normal, so that when I access multiple linked servers, if one of the connections fails, I can skip and not handle it.

 

In sqlserver 2005 and later versions, a system stored procedure is provided"Sp_testlinkedserver"Can be used to test whether the Communications Server is connected. This stored procedure uses linked
The server name is used as the input parameter. The return value 0 indicates success, and 1 indicates failure.

 

The following is a test code:

 

Declare
@ Srvr nvarchar (128 ),
@ Retval int;

Set @ srvr
= 'Mydomainservername ';

Begin try

Exec
@ Retval = SYS. sp_test1_server @ srvr;

End try

Begin catch

Set
@ Retval = Sign (@ error );

End catch;

If @ retval
<> 0

Raiserror ('unable to connect toserver. This operation will be tried later! ', 16,
2 );

 

The following error occurs after running on my machine:

 

MSG 50000, level 16, state 2, line 11

Unable toconnect to server. This operation will be tried later!

 

For more information, see:

Testlinked server connection settings

Sp_testlinkedserver