quote:

---

Originally posted by Lumbago

Yes, you can use the system stored procedure sp_helptracertokens to obtain the tracer_id which in my script is the TracerTokenID...that's basically what you need.

- Lumbago
http://xkcd.com/327/

---

CREATE PROCEDURE [dbo].[proc_replication_latency_monitor]
AS

-- Procedure created by Henning Frettem (Lumbago), www.sqlteam.com, 12th of June 2008

BEGIN

SET NOCOUNT ON

DECLARE 
  @PublishedDBName varchar(200),
  @SQL nvarchar(2000),
  @PublicationPK int, 
  @PublicationName varchar(200),
  @PublicationDatabase varchar(200),
  @TracerTokenID int,
  @Parmameters nvarchar(500),
  @tokenID INT;

DECLARE @Publications table (
  PublicationPK int IDENTITY(1, 1) PRIMARY KEY CLUSTERED,
  PublicationDatabase varchar(200),
  PublicationName varchar(200),
  TracerTokenID int,
  TracerTokenPostTime datetime
)

DECLARE @Latency table (
  PublicationPK int,
  DistributorLatency bigint,
  Subscriber sysname,
  SubscriberDB sysname,
  SubscriberLatency bigint,
  OverallLatency bigint
)

--> Cursor for fetching all publications in all databases
DECLARE curPublishedDatabases CURSOR LOCAL FAST_FORWARD FOR 
  SELECT [name] FROM sys.databases WHERE is_published > 0

OPEN curPublishedDatabases
FETCH curPublishedDatabases INTO @PublishedDBName
WHILE @@FETCH_STATUS = 0
  BEGIN 
    SET @SQL = N'SELECT ''' + @PublishedDBName + ''', [name] FROM ' + @PublishedDBName + '.dbo.syspublications'
    INSERT INTO @Publications (PublicationDatabase, PublicationName)
    EXEC sp_executesql @SQL

    FETCH NEXT FROM curPublishedDatabases INTO @PublishedDBName
  END

CLOSE curPublishedDatabases
DEALLOCATE curPublishedDatabases

CREATE TABLE #tokens (tracer_id int, publisher_commit datetime)

--> Cursor for posting tracer tokens
DECLARE curPublications CURSOR LOCAL FAST_FORWARD FOR 
SELECT PublicationPK, PublicationDatabase, PublicationName FROM @Publications

OPEN curPublications
FETCH curPublications INTO @PublicationPK, @PublicationDatabase, @PublicationName
WHILE @@FETCH_STATUS = 0
  BEGIN 
    --SET @Parmameters = N'@PublicationName varchar(200), @TracerTokenID_OUT int OUTPUT';
    -- SET @SQL = N'EXEC ' + @PublicationDatabase + '.sys.sp_posttracertoken @publication = @PublicationName, @tracer_token_id = @TracerTokenID_OUT OUTPUT'
    

    -- Return only last inserted tracer token information to a temp table.
    INSERT #tokens (tracer_id, publisher_commit)
    EXEC sys.sp_helptracertokens @publication = @PublicationName;
    SET @tokenID = (SELECT TOP 1 tracer_id FROM #tokens ORDER BY publisher_commit DESC)

    SET @SQL = N'EXEC ' + @PublicationDatabase + '.sys.sp_helptracertokens @publication = ''' + @PublicationName + ''', @tracer_id = ' + @tokenID

    EXEC sp_executesql @SQL, @Parmameters, @PublicationName = @PublicationName, @tracer_id = @TracerTokenID OUTPUT

    UPDATE @Publications SET 
    TracerTokenID = @TracerTokenID,
    TracerTokenPostTime = GETDATE()
    WHERE PublicationPK = @PublicationPK

    FETCH NEXT FROM curPublications INTO @PublicationPK, @PublicationDatabase, @PublicationName
  END
CLOSE curPublications
DEALLOCATE curPublications

--> Wait two minutes for all tokens to be commited at all subscribers
--WAITFOR DELAY '000:02:00.000'

--> Then check the results for each posted token 
DECLARE curTokens CURSOR LOCAL FAST_FORWARD FOR 
  SELECT PublicationPK, PublicationDatabase, PublicationName, TracerTokenID FROM @Publications

OPEN curTokens
FETCH curTokens INTO @PublicationPK, @PublicationDatabase, @PublicationName, @TracerTokenID
WHILE @@FETCH_STATUS = 0
  BEGIN 
    --> Insert token history for each token 
    [SET @SQL = N'EXEC ' + @PublicationDatabase + '.dbo.sp_helptracertokenhistory @publication = ''' + @PublicationName + ''', @tracer_id = ' + CAST(@TracerTokenID AS VARCHAR(50)) + ''
    INSERT INTO @Latency
    (DistributorLatency, Subscriber, SubscriberDB, SubscriberLatency, OverallLatency)
    EXEC sp_executesql @SQL

    --> Make sure that the PublicationPK is added to the token history
    UPDATE @Latency SET PublicationPK = @PublicationPK WHERE PublicationPK IS NULL
    /*
    --> Clean up the tracer token
    SET @SQL = N'EXEC ' + @PublicationDatabase + '.sys.sp_deletetracertokenhistory @publication = ''' + @PublicationName + ''', @tracer_id = ' + CAST(@TracerTokenID AS VARCHAR(50)) + ''
    EXEC sp_executesql @SQL
    */ 
  FETCH NEXT FROM curTokens INTO @PublicationPK, @PublicationDatabase, @PublicationName, @TracerTokenID
END
CLOSE curTokens
DEALLOCATE curTokens

DROP TABLE #tokens

SELECT 
b.PublicationDatabase,
b.PublicationName,
a.Subscriber,
a.SubscriberDB,
a.OverallLatency,
b.TracerTokenPostTime 
FROM @Latency a
INNER JOIN @Publications b
ON a.PublicationPK = b.PublicationPK

END