sql-server - sp_replmonitorsubscriptionpendingcmds 到表中 [How2]

Question

我正在寻找一份工作，告诉我复制中待处理命令的数量，我正在使用 sp_replmonitorsubscriptionpendingcmds 并尽力将其放入表中。

但是我一直报错INSERT EXEC 语句不能嵌套。任何想法如何去做？我愿意接受建议

谢谢

我试过了:

DECLARE @tab AS TABLE (pendingcmds int, estimatedprocesstime int) 

INSERT into @tab EXECUTE  sp_executesql N'exec sp_replmonitorsubscriptionpendingcmds   @publisher=[AUTOSQL1\COMPLOT],  
@publisher_db=PTK_M,  @publication=PTK_M_1way,  
@subscriber=[COMPLOTSQL2008\REPL],  @subscriber_db=
PTK_M,  @subscription_type=0'
  SELECT * FROM @tab

insert pendingcmds   (pendingcmdcount ,estimatedprocesstime) 
execute sp_replmonitorsubscriptionpendingcmds   
@publisher=[AUTOSQL1\COMPLOT],  @publisher_db=PTK_M,  
@publication=PTK_M_1way,  @subscriber=[COMPLOTSQL2008\REPL],  
@subscriber_db=PTK_M,  @subscription_type=0
into pendingcmds   (pendingcmdcount ,estimatedprocesstime)

declare @v nvarchar (max)
set @v=N'exec sp_replmonitorsubscriptionpendingcmds   @publisher=[AUTOSQL1\COMPLOT],  
@publisher_db=PTK_M,  @publication=PTK_M_1way,  
@subscriber=[COMPLOTSQL2008\REPL],  @subscriber_db=
PTK_M,  @subscription_type=0'
print @v
INSERT INTO pendingcmds (pendingcmdcount,estimatedprocesstime)
exec sp_executesql @v

Answer 1

sp_replmonitorsubscriptionpendingcmds 在内部调用以下内容，SQL Server 将其视为嵌套插入。

insert into @countab (pendingcmdcount)
exec @retcode = sys.sp_MSget_repl_commands 
                @agent_id = @agent_id,
                @last_xact_seqno = @xact_seqno,
                @get_count = 2,
                @compatibility_level = 9000000

这是一篇关于解决方法的好博客:SimpleTalk Monitoring Transactional Replication in SQL Server by Francis Hanlon