July 24, 2012 at 3:05 pm
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!
July 24, 2012 at 3:37 pm
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 trybegin catch end catch
begin try
set @sql = 'insert into #t select ''b'''
exec sp_executesql @statement = @sql;
end trybegin catch end catch
select * from #t
drop table #t
**The typo is on purpose 🙂 **
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply