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';
BEGIN TRY
EXEC master.sys.sp_executesql
N'EXEC master.sys.sp_testlinkedserver @srv;',
N'@srv SYSNAME', @srv;
END TRY
BEGIN CATCH
SELECT ERROR_NUMBER(), '|', ERROR_MESSAGE();
END CATCH
This should allow you to capture a variety of errors, including:
If @srv
is not populated:
214 | Procedure expects parameter '@servername' of type 'sysname'.
If @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 [2].
(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.
If @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...