USE [master]
GO
/****** Object:  StoredProcedure [dbo].[usp_ReplicationTokenCheck]    Script Date: 11/07/2013 13:33:53 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[usp_ReplicationTokenCheck]
AS
BEGIN
 SET NOCOUNT ON;
--=======================================================================
--检查记录表是否存在，不存在则创建
IF(NOT EXISTS(
 SELECT 1 FROM master.sys.tables 
 WHERE name=‘PublicationTokenResult‘ 
 AND type=‘U‘))
BEGIN
CREATE TABLE master.dbo.PublicationTokenResult
(
 ID INT IDENTITY(1,1) PRIMARY KEY,
 Publication NVARCHAR(200),
 Subscriber NVARCHAR(200),
 SubscriberDB NVARCHAR(200),
 DistributorLatency INT,
 SubscriberLatency INT,
 OverallLatency INT,
 CreatedTime DATETIME
)
END
--=======================================================================
--获取发布列表
CREATE TABLE #Rep 
(
 DBName NVARCHAR(200),
 RepName NVARCHAR(200)
)
INSERT INTO #Rep(DBName,RepName)
EXEC sp_MSforeachdb ‘ 
IF(OBJECT_ID(‘‘[?].[dbo].[syspublications]‘‘) IS NOT NULL)
BEGIN
SELECT ‘‘?‘‘ AS DBName,name as RepName FROM [?].[dbo].[syspublications]
END
‘
SELECT * FROM #Rep
DECLARE @publication AS NVARCHAR(200);
DECLARE @DBName AS NVARCHAR(200);
 
DECLARE curRep CURSOR
FOR SELECT DBName,RepName FROM #Rep;
OPEN curRep;
FETCH NEXT FROM curRep INTO @DBName,@publication;
WHILE(@@FETCH_STATUS=0)
BEGIN
DECLARE @sql NVARCHAR(MAX)
SET @sql=‘
USE [‘+@DBName+‘]
DECLARE @publication AS NVARCHAR(200);
DECLARE @delaySeconds INT;
DECLARE @tokenKeepDays INT;
DECLARE @recordKeepDays INT;
SET @tokenKeepDays=3;
SET @recordKeepDays=180;
SET @publication=‘‘‘+@publication+‘‘‘
SET @delaySeconds=30;
--=======================================================================
--创建token
DECLARE @tokenID AS INT;
EXEC sys.sp_posttracertoken 
  @publication = @publication,
  @tracer_token_id = @tokenID OUTPUT;
--=======================================================================
--等待指定秒数
DECLARE @SQL NVARCHAR(200);
SET @SQL=‘‘WAITFOR DELAY ‘‘‘‘‘‘+CONVERT(NVARCHAR(20),DATEADD(SECOND,@delaySeconds,‘‘2001-1-1‘‘),108)+‘‘‘‘‘‘‘‘
EXEC(@SQL)
--=======================================================================
--为每个已插入发布以确定滞后时间的跟踪标记分别返回一行
CREATE TABLE #tokens (tracer_id int, publisher_commit datetime);
INSERT #tokens (tracer_id, publisher_commit)
EXEC sys.sp_helptracertokens @publication = @publication;
SET @tokenID = (SELECT TOP 1 tracer_id FROM #tokens
ORDER BY publisher_commit DESC);
DROP TABLE #tokens;
--=======================================================================
--获取令牌执行结果
-- Get history for the tracer token.
CREATE TABLE #result(
 DistributorLatency int, 
 Subscriber nvarchar(200),
 SubscriberDB nvarchar(200),
 SubscriberLatency int,
 OverallLatency int
 );
INSERT #result(DistributorLatency,Subscriber,SubscriberDB,SubscriberLatency,OverallLatency)
EXEC sys.sp_helptracertokenhistory 
  @publication = @publication, 
  @tracer_id = @tokenID;
  
--=======================================================================
--保存处理执行结果
INSERT INTO master.dbo.PublicationTokenResult
(
 Publication,
 Subscriber,
 SubscriberDB,
 DistributorLatency,
 SubscriberLatency,
 OverallLatency,
 CreatedTime
)
SELECT @publication,
 Subscriber,
 SubscriberDB,
 DistributorLatency,
 SubscriberLatency,
 OverallLatency,
 GETDATE()
 FROM #result;
--=======================================================================
--清理执行结果和删除过期记录
DROP TABLE #result
DECLARE @date DATETIME;
SET @date=DATEADD(DAY,0-@tokenKeepDays,GETDATE())
EXEC sp_deletetracertokenhistory 
@publication = @publication,
@cutoff_date = @date
DELETE FROM master.dbo.PublicationTokenResult
WHERE CreatedTime<DATEADD(DAY,0-@recordKeepDays,GETDATE())
‘
BEGIN TRY
EXEC(@SQL)
END TRY
BEGIN CATCH
PRINT ‘HAS ERROR‘
END CATCH
PRINT @SQL
FETCH NEXT FROM curRep INTO @DBName,@publication;
END
CLOSE curRep;
DEALLOCATE curRep;
DROP TABLE #Rep;
--===============================================================
END

GO