So, a few weeks back I posted a code snippet for how to quickly find replication errors in MSSQL.  At the time, I didn’t have the opportunity to address an issue in the script to properly call the system stored procedure sp_browsereplcmds. This resulted in the user having to manually copy and past a value from the first result to get the final answer.

Well the issue cropped up again; this time I was able to spend a little extra time to refine the code snippet and fix that script issue.

Here’s the updated version:

USE distribution
GO

DECLARE @comandID INT
DECLARE @publisherID INT
DECLARE @articleID INT
DECLARE @error_seqno VARBINARY(16)
DECLARE @command_seqno VARBINARY(16)
DECLARE @seqno NCHAR(22)

SELECT TOP 1  @error_seqno = xact_seqno
 , @comandID = command_id
FROM msrepl_errors
WHERE error_code > ''
ORDER BY id DESC

SELECT TOP 1
 @publisherID = publisher_database_id
 , @articleID = article_id
 , @command_seqno = xact_seqno
FROM msrepl_commands (NOLOCK)
WHERE command_id = @comandID
 AND xact_seqno = @error_seqno

SET @seqno = CONVERT(NVARCHAR(MAX), @command_seqno, 1)

EXEC sp_browsereplcmds @article_id = @articleID
 , @command_id = @comandID
 , @xact_seqno_start = @seqno
 , @xact_seqno_end = @seqno
 , @publisher_database_id = @publisherID

GO

As an added bonus, I also stumbled across this fine script on StackOverflow for checking the overall health of your replications (my script will report the most recent error, even if your replication is not currently experiencing an error).  https://stackoverflow.com/questions/220340/how-do-i-check-sql-replication-status-via-t-sql