sql server 如何用命令判断已存在的链接服务器是否测试成功

jiangjubo 2019-07-27 09:54:53

用命令！用命令！用命令！
谢谢！

...全文

543 3 打赏收藏转发到动态举报

写回复

用AI写文章

3 条回复

切换为时间正序

请发表友善的回复…

发表回复

唐诗三百首 2019-07-27

打赏
举报


-- 查询所有链接服务器
if object_id('tempdb..#linkserver') is not null
   drop table #linkserver

select srvname,
       providername,
       checkresult=cast('未测试' as varchar(100))
 into #linkserver
 from sys.sysservers
 where srvname<>@@servername
 and srvname<>N'repl_distributor'


-- 测试
declare @srvname nvarchar(200),@providername nvarchar(200),@tsql nvarchar(1000),@checkresult varchar(100)
declare ap cursor static for select srvname,providername from #linkserver

open ap
fetch next from ap into @srvname,@providername
while(@@fetch_status<>-1)
begin
  -- 此处仅针对SQL Server和Oracle类型的链接服务器进行测试,其他数据库类型请自行添加测试SQL.
  select @tsql=case when @providername=N'SQLOLEDB' then  N'select * from openquery(['+@srvname+N'],N''select 1 '') t '
                    when @providername=N'OraOLEDB.Oracle' then  N'select * from openquery(['+@srvname+N'],N''select 1 from dual '') t '
                    else N'' end
  
  if (@tsql<>N'')
  begin
    begin try
      exec(@tsql)
      select @checkresult='测试成功.'
    end try
    begin catch
      select @checkresult='测试失败.'
    end catch
    
    update #linkserver set checkresult=@checkresult where srvname=@srvname
  end
 
  fetch next from ap into @srvname,@providername
end

close ap
deallocate ap


-- 结果
select * from #linkserver