聚沙成塔

實際執行發現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 |聚沙成塔，裡面包含文章中的案例實作，
期待能帶給您新的啟發或靈感。

未來文章將喬遷新址「一趟數據分析之旅」，歡迎追蹤繼續支持，您將不會錯過任何新知識。