通过脚本获取发生错误的同步链接

use distribution 
go
declare @xact_seqno	varbinary(16)
declare @xact_seqno_varchar varchar(30)
declare @error_text  varchar(1024)
declare @command_id	int
declare @command nvarchar(max) 
declare @error_time datetime
declare @article_id	int
declare @UndelivCmdsInDistDB int
declare @i int
declare  @nEndCommandID int

declare @ErrorCode varchar(15)
set @ErrorCode=''

--从MSerpl_error获得错误清单，仅获得1小时内，且非if @@trancount > 0 rollback tran,并且command_id大于0的最后1行错误信息
select top 1 @xact_seqno=xact_seqno,@i=datalength(xact_seqno),
@command_id=command_id,@error_text=error_text,@error_time=[time]   
from dbo.MSrepl_errors with (nolock)
where error_text not like 'if @@trancount > 0 rollback tran' and 
	time >dateadd(hour,-1,GETDATE()) and 
	command_id>0
order by time desc

--格式化varchar
set @xact_seqno_varchar= master.dbo.fn_varbintohexstr(@xact_seqno)
--当@xact_seqno_varchar不为0时
if not (@xact_seqno_varchar='0x0000000000000000000000000000' or @xact_seqno_varchar is null)
begin
DECLARE @temp TABLE
( 
	 xact_seqno varbinary(16) NULL
	,originator_srvname char(40) NULL
	,originator_db char(40) NULL
	,article_id int NULL
	,type int NULL
	,partial_command int NULL
	,hashkey int NULL
	,originator_publication_id int NULL
	,originator_db_version int NULL
	,originator_lsn varbinary(16) NULL
	,command nvarchar(1024) NULL
	,command_id int NULL 
)
	INSERT INTO @temp
	( 
		 xact_seqno
		,originator_srvname
		,originator_db
		,article_id
		,type
		,partial_command
		,hashkey
		,originator_publication_id
		,originator_db_version
		,originator_lsn
		,command
		,command_id 
	) 
	--通过系统存储过程sp_browsereplcmds获得出错语句的事务id,以及错误语句的起始command_id
	EXEC sp_browsereplcmds @xact_seqno_varchar,@xact_seqno_varchar
	
	--获得结束的command_id(超过1000字节体现在输出内容中将为多行,我们使用查询下一个ESCAPE判定语句起始行) 
	set @nEndCommandID=(select MIN (command_id) from @temp where command_id>@command_id and  command like '{CALL \[%' ESCAPE '\')
	--获得完整的错处语句
	set @command=''
	if (@nEndCommandID>0)
		SELECT   @article_id=article_id,@command=@command+command
		FROM @temp 
		where command_id>=@command_id and command_id<@nEndCommandID
	else
		SELECT  @article_id=article_id,@command=@command+command
		FROM @temp 
		where command_id>=@command_id 

	--输出出错同步链的明细信息
	SELECT  
			source_Server=@@SERVERNAME ,source_db =A.publisher_db,
			source_Object_schema =A.Source_owner,source_Object_name=A.source_object,
			destination_Server=srv.name,destination_db=S.Subscriber_db,
			destination_object_schema=A.Source_owner,destination_object_name=A.destination_object,
			@command as command ,@error_time as error_time,@error_text as error_text,now_time=getdate(),
			Job_name=tb.name,Job_ID=tb.job_id,tb.publication ,@xact_seqno_varchar  as xact_seqno_varchar,@command_id   as command_id,
			TB.UndelivCmdsInDistDB,TB.profile_id
	FROM dbo.MSpublications AS P WITH (NOLOCK)
	inner join dbo.MSarticles AS A WITH (NOLOCK)
		on p.Publication_id = A.Publication_id
	INNER JOIN dbo.MSsubscriptions AS S WITH (NOLOCK)
		ON S.Publication_id = P.Publication_id	and S.article_id = a.article_id
	INNER JOIN sys.servers as srv WITH (NOLOCK)
		ON S.subscriber_id = srv.Server_id
	inner join (
		select distinct h.article_id,a.profile_id ,dh.agent_id,s.publication_id ,s.publisher_database_id,h.UndelivCmdsInDistDB,a.name,a.job_id,a.publication,s.subscriber_id
		from     dbo.MSdistribution_agents as a with(nolock)  
		inner join    dbo.MSsubscriptions as s with(nolock)   on  s.agent_id = a.id
		inner join (select agent_id, max(timestamp) as timestamp
						  from dbo.MSdistribution_history with(nolock) 
						  group by agent_id) cte 
			on cte.agent_id=a.id
		inner join dbo.MSdistribution_history dh with(nolock)  
			on dh.agent_id=cte.agent_id and dh.timestamp=cte.TIMESTAMP
		inner join dbo.MSdistribution_status  as h  with (nolock)
			on h.agent_id=cte.agent_id
		where  (s.status in (0,1) or dh.runstatus >=5 or dh.comments like '%Error %')
	) as TB on S.agent_id = TB.agent_id and a.article_id=TB.article_id
	where a.article_id=@article_id  
end
else
begin
--当然@xact_seqno_varchar为0时，表示不是语句造成的同步错误，直接返回该同步相关信息
	SELECT  
			source_Server=@@SERVERNAME ,source_db =A.publisher_db,
			source_Object_schema ='',source_Object_name='',
			destination_Server=d.srvnetname,destination_db=S.Subscriber_db,
			destination_object_schema='',destination_object_name='',
			@command as command ,@error_time as error_time,@error_text as error_text,now_time=getdate(),
			Job_name=a.name ,Job_ID=a.job_id,a.publication ,@xact_seqno_varchar  as xact_seqno_varchar,@command_id   as command_id,
			H.UndelivCmdsInDistDB as UndelivCmdsInDistDB,a.profile_id
	from dbo.MSdistribution_agents  a with(nolock) 
	inner join master.dbo.sysservers d  with(nolock) 
		on a.subscriber_id=d.srvid 
	inner join dbo.MSpublications c with(nolock) 
		on 	a.publisher_db=c.publisher_db and a.publication=c.publication
	inner join dbo.MSsubscriptions s WITH(NOLOCK)
		 on s.agent_id = a.id
	inner join (select agent_id, max(timestamp) as timestamp
				from dbo.MSdistribution_history with(nolock) 
				group by agent_id) cte 
		on cte.agent_id=a.id
	inner join dbo.MSdistribution_history dh with(nolock)  
		on dh.agent_id=cte.agent_id and dh.timestamp=cte.TIMESTAMP
	inner join dbo.MSdistribution_status  as h  with (nolock)
			on h.agent_id=cte.agent_id
	where  (s.status in (0,1) 	or dh.runstatus >=5 or dh.comments like '%Error %') and @command >0
end