SQL How to union selects from linked servers with connectivity check

Question

I need to create view or procedure, that selects some data from 50 linked servers, but some of them can be offline(bad internet).

I have this piece of code

declare @srvr nvarchar(128), @retval int;
set @srvr = 'SERVER103';
begin try
    exec @retval = sys.sp_testlinkedserver @srvr;
end try
begin catch
    set @retval = sign(@@error);
end catch;
if @retval = 0
    select sel1.sum1, sel1.sum2, sel2.sum1, sel2.sum2 from
        (select sum(column1) as sum1, sum(column2) as sum2 from [SERVER103].[dbo].[table] where date like '201702%' group by column1, column2) as sel1
        (select sum(column1) as sum1, sum(column2) as sum2 from [SERVER103].[dbo].[table2] where date like '201702%' group by column1, column2) as sel2

But it still raises error about connectivity and breaks whole script. Next thing I need it must go through all linked servers and union results into one big result.

Any ideas? Thanks

Answer 1

The following code uses a cursor to go through each linked server. If the connection is good, dynamic sql is added, otherwise it prints a message. The end dynamic sql is just for the good links:

declare @loop as int=1
declare @srvname as nvarchar(100)
declare @sql as nvarchar(max)=N''
declare @Date as varchar(10) = '201702%'
declare srvcursor cursor for select srvname from sysservers where srvname <> @@SERVERNAME
open srvcursor
fetch next from srvcursor into @srvname
WHILE @@FETCH_STATUS = 0
    begin
    begin try
    exec sys.sp_testlinkedserver @srvname
    set @sql=@sql+N'select sel1.sum1, sel1.sum2, sel2.sum1, sel2.sum2 from
        (select sum(column1) as sum1, sum(column2) as sum2 from [' + @srvname + N'].[dbo].[table] where date like ''' + @Date + N''' group by column1, column2) as sel1
        (select sum(column1) as sum1, sum(column2) as sum2 from [' + @srvname + N'].[dbo].[table2] where date like ''' + @Date + N''' group by column1, column2) as sel2
        UNION '
    end try
    begin catch
    print @srvname + 'is broken.'
    end catch
    fetch next from srvcursor into @srvname
    end
if @sql <> N''
    begin
    set @sql = left(@sql, len(@sql)-6)
    print @sql
    --exec(@sql)
    end
close srvcursor
deallocate srvcursor