EXEC sp_testlinkedserver over multiple Linked Servers

Question

I'm trying to get error messages over all my Linked Servers.

So I've three Linked Servers:

• IT_DATA
• 193.0.1
• Monitoring_Data

I know that executing the following query I will get the info that I need to one Linked Server:

BEGIN TRY
    EXEC sp_testlinkedserver N'193.0.1';
    EXEC sp_executesql N'SELECT * FROM OPENQUERY([193.0.1], ''SELECT 1 AS c;'');';
END TRY
BEGIN CATCH
    SELECT ERROR_NUMBER(), ERROR_MESSAGE();
END CATCH;
PRINT 'We got past the Catch block!';

But I'm trying to create a loop to exec the SP to all of my Linked Servers:

DECLARE @Linked_Server varchar(50)
SET @Linked_Server = '193.0.1'

BEGIN TRY
    EXEC sp_testlinkedserver N'193.0.1';
    EXEC sp_executesql N'SELECT * FROM OPENQUERY([193.0.1], ''SELECT 1 AS c;'');';
END TRY
BEGIN CATCH
    SELECT ERROR_NUMBER(), ERROR_MESSAGE();
END CATCH;
PRINT 'We got past the Catch block!';

But I don't know how can I create a loop to get the information of all the Linked Servers into one table.

Anyone have an closer example?

Thanks!

Answer 1

I don't like using cursors but in this case... Try this:

create table dbo.LinkedServer (
    LSName nvarchar(128)
    , ErrorNumber int
    , ErrorMessage nvarchar(4000)
);


DECLARE @Linked_Server nvarchar(128)
    , @SQLString nvarchar(max);

declare crsLinkedServers cursor
for
select
    s.name
from sys.servers s
where s.is_linked = 1;

open crsLinkedServers;

fetch next from crsLinkedServers
into @Linked_Server;

while @@fetch_status = 0
begin

    BEGIN try
        set @SQLString = N'SELECT * FROM OPENQUERY([' + @Linked_Server + '], ''SELECT 1 AS c;'');'
        EXEC sp_testlinkedserver @Linked_Server;
        EXEC sp_executesql @SQLString;
    END TRY
    BEGIN catch

        insert into dbo.LinkedServer (LSName, ErrorNumber, ErrorMessage)
        SELECT @Linked_Server, error_number(), error_message();

    END CATCH;

    fetch next from crsLinkedServers
    into @Linked_Server;

end

close crsLinkedServers;
deallocate crsLinkedServers;


select
*
from dbo.LinkedServer;

drop table dbo.LinkedServer;