2

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: enter image description here

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:

enter image description here

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:

enter image description here

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

  • Check this.. dba.stackexchange.com/questions/36178/… – TheGameiswar Oct 30 '14 at 17:13
  • 1
    I think that will work wonderfully. I'm integrating it right now. Thanks for the help! – Kris Gruttemeyer Oct 30 '14 at 17:27
  • If we have 50 link servers to check, it would be a pain to write all the scripts. Anyone has a better solution. Thanks. – user76232 Sep 17 '15 at 18:56
  • You don't need to write a script for each server. As you can see in Kin's answer, we utilize the system catalog sys.servers and iterate through them programmatically. I have a script for each type of sever (SQL, iSeries, Informix, etc). 3 SPs that cover my 31 linked servers. – Kris Gruttemeyer Sep 17 '15 at 19:53
5

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

  • Absolutely perfect. I'm smack in the middle of building a try/catch routine with some yummy dynamic sql sprinkled in it. This was definitely the information I was looking for. Thanks, as always, Aaron. – Kris Gruttemeyer Oct 30 '14 at 18:49
  • Just updated the post with the output after making all the code changes. It's exactly what I needed. Having the exact reason why the connection failed already baked into the notification email will definitely make my life easier. – Kris Gruttemeyer Oct 30 '14 at 20:24
3

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:

enter image description here

  • 1
    I was able to capture a connection timeout no problem. – Aaron Bertrand Oct 30 '14 at 19:00
  • @AaronBertrand actually it captures 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.. Removed that link. – Kin Shah Oct 30 '14 at 19:03

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.