saída sp_testlinkedserver detalhada

Tal como acontece com a resposta vinculada , você deve ser capaz de fazer isso usando TRY/CATCH; o problema (sem trocadilhos) com a outra resposta é que, como o nome do servidor vinculado é validado em tempo de compilação, caso o teste falhe, o lote não é executado. Em vez disso, você pode colocar o teste do servidor vinculado dentro do SQL dinâmico:

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

Isso deve permitir que você capture uma variedade de erros, incluindo:

• Se @srvnão for preenchido:

  214 | O procedimento espera o parâmetro '@servername' do tipo 'sysname'.

• Se @srvnão for um servidor vinculado definido:

  7202 | Não foi possível encontrar o servidor 'whatever' em sys.servers. Verifique se o nome do servidor correto foi especificado. Se necessário, execute o procedimento armazenado sp_addlinkedserver para adicionar o servidor a sys.servers.

• Se uma conexão @srvfalhou no momento da criação do servidor vinculado:

  7303 | Não é possível inicializar o objeto de fonte de dados do provedor OLE DB "SQLNCLI11" para o servidor vinculado "qualquer coisa".

• Se uma conexão @srv1expirar (por exemplo, o servidor está offline) - isso estava usando um servidor vinculado local, portanto, o texto pode ser um pouco diferente ao usar pipes nomeados ou TCP/IP:

  2 | Provedor de memória compartilhada: não foi possível abrir uma conexão com o SQL Server [2].

  (Com TCP/IP, Kin diz que a mensagem de erro é:

  Provedor TCP: uma tentativa de conexão falhou porque a parte conectada não respondeu adequadamente após um período de tempo ou a conexão estabelecida falhou porque o host conectado não respondeu.

• Se @srvnão estiver configurado para acesso a dados:

  7411 | O servidor 'whatever' não está configurado para DATA ACCESS.

• Se o servidor vinculado foi configurado com o nome de usuário/senha errado (ou passa as credenciais atuais e elas falham):

  18456 | Falha de login para o usuário 'foo'.

Você também pode iniciar o lote EXECUTE ASse estiver tentando testar o servidor vinculado com credenciais específicas.

Se o teste do servidor vinculado for bem-sucedido, você poderá continuar dentro do TRYbloco para fazer outras coisas, como OPENQUERYconsultas usando nomes de 4 partes etc., e deve haver uma série de outros erros que você pode capturar e relatar detalhadamente. ..

Aaron respondeu sua pergunta perfeitamente. Estou apenas adicionando o script completo que escrevi para testar em meu ambiente.

Abaixo segue o roteiro:

/************************************************************************************
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

Abaixo está a saída: