As with the linked answer, you should be able to do this by using
TRY/CATCH; the catch (no pun intended) with the other answer is that, because the name of the linked server is validated at compile time, in the event the test fails, the batch doesn't execute. So instead, you can put the linked server test inside dynamic SQL:
DECLARE @srv SYSNAME = N'linked_server_name';
N'EXEC master.sys.sp_testlinkedserver @srv;',
N'@srv SYSNAME', @srv;
SELECT ERROR_NUMBER(), '|', ERROR_MESSAGE();
This should allow you to capture a variety of errors, including:
@srv is not populated:
214 | Procedure expects parameter '@servername' of type 'sysname'.
@srv is not a defined linked server:
7202 | Could not find server 'whatever' in sys.servers. Verify that the correct server name was specified. If necessary, execute the stored procedure sp_addlinkedserver to add the server to sys.servers.
If a connection to
@srv failed at the time of linked server creation:
7303 | Cannot initialize the data source object of OLE DB provider "SQLNCLI11" for linked server "whatever".
If a connection to
@srv1 times out (e.g. the server is offline) - this was using a local linked server, so the text may be a bit different when using named pipes or TCP/IP:
2 | Shared Memory Provider: Could not open a connection to SQL Server .
(With TCP/IP, Kin says the error message is:
TCP Provider: A connection attempt failed because the connected party did not properly respond after a period of time, or established connection failed because connected host has failed to respond.
@srv is not configured for data access:
7411 | Server 'whatever' is not configured for DATA ACCESS.
If the linked server was configured with the wrong username/password (or passes the current credentials and they fail):
18456 | Login failed for user 'foo'.
You can also start the batch with
EXECUTE AS if you are trying to test the linked server under specific credentials.
If the test of the linked server succeeds, you could continue inside the
TRY block to do other things, like
OPENQUERY, queries using 4-part names, etc., and there should be a whole slew of other errors you could capture and report on verbosely...