as title
實際執行發現sys.sp_testlinkedserver 若成功會回傳0, 所以改用try catch 判斷連線是否成功。
SET NOCOUNT ON;
IF OBJECT_ID('tempdb..#Tmp_Link_Server_List') IS NOT NULL
DROP TABLE #Tmp_Link_Server_List
--=================================================
--step1:建立temp table
create table #Tmp_Link_Server_List
(
SRV_Name nvarchar(100),
SRV_ProviderName nvarchar(100),
SRV_Product nvarchar(100),
SRV_DataSource nvarchar(100),
SRV_ProviderString nvarchar(100),
SRV_Location nvarchar(100),
SRV_Cat nvarchar(100),
)
--=================================================
--step2:查詢link server 清單
insert into #Tmp_Link_Server_List
exec sp_linkedservers
alter table #Tmp_Link_Server_List add Connection_Result nvarchar(100) , Update_DateTime datetime , Data_Date date ;
--=================================================
--step3: 確認連線訊息
Declare @SRV_Name nvarchar(100)
DECLARE @Retval int
Declare MyCursor Cursor FOR
select SRV_Name from #Tmp_Link_Server_List
Open MyCursor
Fetch NEXT FROM MyCursor INTO @SRV_Name
While (@@FETCH_STATUS <> -1)
begin
Begin Try
Exec @retval = sys.sp_testlinkedserver @SRV_Name;
set @retval=1
End Try
Begin Catch
--Set @retval = sign(@@error);
set @retval=0
End Catch;
update #Tmp_Link_Server_List
set Connection_Result =
case
when (@Retval = 1 )
then 'Connection Success'
when (@Retval = 0 )
then 'Connection Fail'
else convert(nvarchar(100),@Retval )
end
,Update_DateTime = getdate()
,Data_Date = cast(getdate() as date)
where SRV_Name = @SRV_Name
Fetch Next From MyCursor Into @SRV_Name
end
Close MyCursor
Deallocate MyCursor
--=================================================
--step4: 更新Linked Server連線結果
delete Linked_Server_Connection_Monitor
where convert(varchar(10),Data_Date, 111)+ Srv_name in
(
select convert(varchar(10),Data_Date, 111)+ Srv_name from #Tmp_Link_Server_List
)
insert into Linked_Server_Connection_Monitor
select
[Data_Date]
,[SRV_Name]
,[SRV_ProviderName]
,[SRV_Product]
,[SRV_DataSource]
,[SRV_ProviderString]
,[SRV_Location]
,[SRV_Cat]
,[Connection_Result]
,[Update_DateTime]
from #Tmp_Link_Server_List
where [SRV_Name] <> @@SERVERNAME
--=================================================
--step3: 刪除舊資料
delete Linked_Server_Connection_Monitor
where Data_Date <= (DATEADD(month,-3, getdate()))
IF OBJECT_ID('tempdb..#Tmp_Link_Server_List') IS NOT NULL
DROP TABLE #Tmp_Link_Server_List
同時也歡迎追蹤Tableau Public Gallery- MR.360 |聚沙成塔,裡面包含文章中的案例實作,
期待能帶給您新的啟發或靈感。
未來文章將喬遷新址「一趟數據分析之旅」,歡迎追蹤繼續支持,您將不會錯過任何新知識。