sp_testlinkedserver output verbose

Question

I've built a script/Agent job that goes and polls our linked servers every 10 minutes to make sure they are online. Now, when one goes offline, It generates an email and notifies me. See below:

Now, right now it just uses a pass/fail. What I want to do is, when communication between servers goes offline, to generate not only that it's offline, but also the reason why. Similar to the read out you get from SSMS when you test a linked server and it's offline:

Is this possible? I've searched for any additional verbose parameters that may help me accomplish this but haven't been able to find any. The information gets generated in SSMS so that leads me to believe that there is some way to get it, I'm just not sure how.

Thanks as usual guys!

UPDATED Had to add some system specific functionality to this as well as my own SQL that I had already built but error reporting is online. Works with our SQL servers and our iSeries/400 environments. Here's what it spits out now:

Thanks again for all the help on this one. Might have to post this out to SSC so others can use it.

Answer 1

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...

Answer 2

Aaron has answered your question perfectly. I am just adding the full script that I wrote to test in my environment.

Below is the script :

/************************************************************************************
Author      :   Kin Shah

Purpose     :   Written for dba.stackexchange.com
                http://dba.stackexchange.com/q/81505/8783
                Find linked servers that are not able to connect with error message

Date        :   10/30/2014              

************************************************************************************/

if object_id ('tempdb..#LinkedServerTest') is not null
begin
   drop table #LinkedServerTest 
end;
create table #LinkedServerTest (Server_name sysname,
                                [STATUS] char(7),
                                [Time_Server_Polled] datetime,
                                [ERROR_NUMBER] int,
                                [ERROR_MESSAGE] varchar(max)
                                )
if exists (
        select 1
        from master.sys.servers
        where is_linked = 1
        )
begin
    declare @servername sysname

    select @servername = min(name)  from master.sys.servers where is_linked = 1

        while @servername is not null
        begin

        begin try
            declare @cmd nvarchar(max);

            set @cmd = 'SELECT * FROM OPENQUERY(' + quotename(@servername) + ', ''select 1 from tempdb..sysobjects'')'

                --print @cmd
                EXEC master.sys.sp_executesql @cmd
        end try

        begin catch
            insert into #LinkedServerTest
            select @servername as Server_name
                    ,'OFFLINE' as [STATUS]
                    , getdate() as [Time_Server_Polled]
                    ,ERROR_NUMBER() as [Error_Number]
                ,ERROR_MESSAGE() as [ERROR_MESSAGE]
        end catch
        select @servername = min(name)  from master.sys.servers where is_linked = 1 and name>@servername

    end
end
else
    select 'There are no linked servers on this server instance !!'

select * from #LinkedServerTest

Below is the output: