---第一個儲存過程只是先做測試，保證[dba].[ProdServerList]欄位LogicalName對應的linked server都能被訪問，不能訪問的會列印出來Could not get server: serverName

DECLARE @HostName varchar(200) --宣告變數,使用者接收迴圈時的變數
DECLARE @ServerName varchar(200) --宣告變數,使用者接收迴圈時的變數

--定義遊標
DECLARE RunPerServer CURSOR FOR
SELECT hostname,LogicalName FROM [dba].[ProdServerList]

--開啟遊標 
OPEN RunPerServer
FETCH NEXT FROM RunPerServer INTO @HostName,@ServerName --從遊標裡取出資料賦值到宣告的變數中

IF OBJECT_ID('tempdb.dbo.#DBAserversname') IS NOT NULL
DROP TABLE #DBAserversname    
CREATE TABLE #DBAserversname (server_name NVARCHAR(128) NULL)

WHILE @@FETCH_STATUS = 0 --返回被FETCH語句執行的最後遊標的狀態，0表示fetch語句成功，1表示fetch語句失敗，2表示被提取的行不存在
BEGIN
BEGIN TRY
        declare @sql1 varchar(8000) = 'EXECUTE (''select top 1 name from master.sys.databases where name not in (''''distribution'''',''''model'''',''''DBA'''',''''tempdb'''') ORDER BY NEWID()'') AT ' + QUOTENAME(@ServerName)
        print  @sql1 
	exec(@sql1)     
        insert into #DBAserversname select @ServerName
        print 'The cursor successfully fetched a servername'        
    END TRY
    BEGIN CATCH
        PRINT 'Could not get server: ' + @ServerName + ' Error: ' + ERROR_MESSAGE()
        print @ServerName
    END CATCH
    FETCH NEXT FROM RunPerServer INTO @HostName,@ServerName 
END
CLOSE RunPerServer --關閉遊標
DEALLOCATE RunPerServer --撤銷遊標

select * from #DBAserversname

第二個儲存過程
隨意取linked server中的一臺伺服器，再隨機取該linked server中的一個資料庫

--先建立一張物理表，存放歷史記錄
CREATE TABLE [dba].[ProdDBTestLog](
	[LogID] [int] IDENTITY(1,1) NOT NULL,
	[HostName] [sysname] NOT NULL,
	[LogicalName] [sysname] NOT NULL,
	[DatabaseName] [sysname] NOT NULL,
	[DBAName] [varchar](50) NULL,
	[Log_Date] [datetime] NOT NULL,
 CONSTRAINT [PK_ProdDBTestLog] PRIMARY KEY CLUSTERED 
([LogID] ASC)) ON [PRIMARY]
GO

--建立一個儲存過程去取linked server、資料庫、DBA資訊
CREATE PROCEDURE [dba].[usp_test_backups_for_products]
as begin

DECLARE @ServerName varchar(200) 
DECLARE @WK int

IF OBJECT_ID('tempdb.dbo.#DBserversname') IS NOT NULL
BEGIN
DROP TABLE #DBserversname    
END
CREATE TABLE #DBserversname (Host_Name NVARCHAR(100) NULL,Server_Name NVARCHAR(100) NULL,Logdate datetime NULL)

insert into #DBserversname SELECT top 1 hostname,LogicalName,getdate() FROM [dba].[ProdServerList] ORDER BY NEWID()

SELECT @ServerName=Server_Name FROM #DBserversname

declare @sql1 varchar(8000) = 'EXECUTE (''select top 1 name ,getdate() from master.sys.databases where name not in (''''distribution'''',''''model'''',''''DBA'''',''''master'''',''''tempdb'''') ORDER BY NEWID()'') AT ' + QUOTENAME(@ServerName)

IF OBJECT_ID('tempdb.dbo.#Databasename') IS NOT NULL
BEGIN
DROP TABLE #Databasename    
END

CREATE TABLE #Databasename (Database_name NVARCHAR(100) NULL,Logdate datetime NULL) 
insert into #Databasename exec(@sql1)

select @WK=datepart(WK, getdate())%4

If (@WK=1)
begin
insert into [dba].[ProdDBTestLog] select a.Host_Name,a.Server_Name,b.Database_name,'Vinay BM',getdate() from #DBserversname a ,#Databasename b
end

If (@WK=2)
begin
insert into [dba].[ProdDBTestLog] select a.Host_Name,a.Server_Name,b.Database_name,'Spencer Wen',getdate() from #DBserversname a ,#Databasename b
end

If (@WK=3)
begin
insert into [dba].[ProdDBTestLog] select a.Host_Name,a.Server_Name,b.Database_name,'Lukes Liao',getdate() from #DBserversname a ,#Databasename b
end

If (@WK=0)
begin
insert into [dba].[ProdDBTestLog] select a.Host_Name,a.Server_Name,b.Database_name,'Ethan Zhang',getdate() from #DBserversname a ,#Databasename b
end

DECLARE @Body NVARCHAR(MAX)
SET @Body =
'

<tr>
        <b>Hi DBA, Please handle this task at this week, Please reply this email adding the test result screenshot and record the details in confulence page https://confluence1.daicompanies.com/pages/viewpage.action?pageId=144785980 </b>
    </tr>
    <br> 
    <br> 
<table style="font-family:Arial; border-collapse: collapse">
    <thead style="font-size:14px; color:#FFF">
        <tr>
            <th style="background:#0074D9; border:1px solid #ddd; padding:6px 10px">HostName</th>
            <th style="background:#0074D9; border:1px solid #ddd; padding:6px 10px">ServerName</th>
            <th style="background:#0074D9; border:1px solid #ddd; padding:6px 10px">DBNAME</th>
			<th style="background:#0074D9; border:1px solid #ddd; padding:6px 10px">DBA</th>
            <th style="background:#0074D9; border:1px solid #ddd; padding:6px 10px">Date</th>
        </tr>
    </thead>
    <tbody style="font-size:12px; color:#666">
'

SELECT
    @Body +=
'    <tr>
        <td style="border:1px solid #ddd; padding:6px 10px">' + HostName + '</td>
        <td style="border:1px solid #ddd; padding:6px 10px">' + LogicalName + '</td>
        <td style="border:1px solid #ddd; padding:6px 10px">' + DatabaseName + '</td>
		<td style="border:1px solid #ddd; padding:6px 10px">' + DBAName + '</td>	
        <td style="border:1px solid #ddd; padding:6px 10px">' + CONVERT(varchar(50),Log_Date) + '</td>
    </tr>
'
from [dba].[ProdDBTestLog] where Log_Date>getdate()-1

SET @Body +=
'    </tbody>
</table>

'

PRINT @Body

EXECUTE msdb.dbo.sp_send_dbmail
    @subject = N'Test backups for products, pick a random database server test, who, when and what backup was tested',
    @recipients = N'lukes.liao@will.com',
    @body = @Body,
    @body_format = N'HTML';

END

T-SQL隨機取任意一臺例項上任意一個資料庫並按周輪詢通知相關任意進行操作

相關文章