Catching Linked Server Errors

• dkschill

  Hall of Fame

  Points: 3375

  More actions

  July 24, 2012 at 3:05 pm

  #278284

  I am trying to write a stored procedure that uses multiple linked servers. The issue is occasionally one of the data sources will not be available when the stored procedure is called. I would like it to just ignore the missing linked server and return the results from the other sources.

  For example:

  select column_1 from linked1.db..table

  union all

  select column_1 from linked2.db..table

  union all

  select column_1 from linked3.db..table

  union all

  select column_1 from linked4.db..table

  I have tried to dump each statement into a temp table and wrapped each insert with a try catch, but it still exists the entire stored procedure when it errors.

  create table #t (column_1 varchar(10))

  begin try

  insert into #t

  select column_1 from linked1.db..table

  end try begin catch end catch

  begin try

  insert into #t

  select column_1 from linked2.db..table

  end try begin catch end catch

  begin try

  insert into #t

  select column_1 from linked3.db..table

  end try begin catch end catch

  begin try

  insert into #t

  select column_1 from linked4.db..table

  end try begin catch end catch

  select * from #t

  Any help on this would be awesome!

• dkschill

  Hall of Fame

  Points: 3375

  More actions

  July 24, 2012 at 3:37 pm

  #1517095

  These seems to work, but I am getting different results when linked servers are involved...

  create table #t (a varchar(10))

  declare @sql nvarchar(max) = '';

  begin try

  set @sql = 'insert into #t selct ''a'''

  exec sp_executesql @statement = @sql;

  end try begin catch end catch

  begin try

  set @sql = 'insert into #t select ''b'''

  exec sp_executesql @statement = @sql;

  end try begin catch end catch

  select * from #t

  drop table #t

  **The typo is on purpose 🙂 **