Hi Team,

Could you please let me know do you have any TSQL script to monitor replication(Transactional, Snapshot) with current status ? I have tried below script but it giving error. could you please have a look at the below script or do you have any other new TSQL script to monitor the replication status ?

"Msg 8164, Level 16, State 1, Procedure sp_MSload_tmp_replication_status, Line 80
An INSERT EXEC statement cannot be nested."

DECLARE @srvname VARCHAR(100)

DECLARE @pub_db VARCHAR(100)

DECLARE @pubname VARCHAR(100) CREATE TABLE #replmonitor(status    INT NULL,warning    INT NULL,subscriber    sysname NULL,subscriber_db    sysname NULL,publisher_db    sysname NULL, publication    sysname NULL,publication_type    INT NULL,subtype    INT NULL,latency    INT NULL,latencythreshold    INT NULL,agentnotrunning    INT NULL, agentnotrunningthreshold    INT NULL,timetoexpiration    INT NULL,expirationthreshold    INT NULL,last_distsync    DATETIME, distribution_agentname    sysname NULL,mergeagentname    sysname NULL,mergesubscriptionfriendlyname    sysname NULL,mergeagentlocation    sysname NULL, mergeconnectiontype    INT NULL,mergePerformance    INT NULL,mergerunspeed    FLOAT,mergerunduration    INT NULL,monitorranking    INT NULL, distributionagentjobid    BINARY(16),mergeagentjobid    BINARY(16),distributionagentid    INT NULL,distributionagentprofileid    INT NULL, mergeagentid    INT NULL,mergeagentprofileid    INT NULL,logreaderagentname VARCHAR(100),publisher varchar(100)) DECLARE replmonitor CURSOR FOR SELECT b.srvname,a.publisher_db,a.publication FROM distribution.dbo.MSpublications a,  master.dbo.sysservers b WHERE a.publisher_id=b.srvid OPEN replmonitor  FETCH NEXT FROM replmonitor INTO @srvname,@pub_db,@pubname WHILE @@FETCH_STATUS = 0 BEGIN INSERT INTO #replmonitor EXEC distribution.dbo.sp_replmonitorhelpsubscription  @publisher = @srvname      , @publisher_db = @pub_db      ,  @publication = @pubname      , @publication_type = 0 FETCH NEXT FROM replmonitor INTO @srvname,@pub_db,@pubname END CLOSE replmonitor DEALLOCATE replmonitor   SELECT publication,publisher_db,subscriber,subscriber_db,         CASE publication_type WHEN 0 THEN 'Transactional publication'             WHEN 1 THEN 'Snapshot publication'             WHEN 2 THEN 'Merge publication'             ELSE 'Not Known' END,         CASE subtype WHEN 0 THEN 'Push'             WHEN 1 THEN 'Pull'             WHEN 2 THEN 'Anonymous'             ELSE 'Not Known' END,         CASE status WHEN 1 THEN 'Started'             WHEN 2 THEN 'Succeeded'             WHEN 3 THEN 'In progress'             WHEN 4 THEN 'Idle'             WHEN 5 THEN 'Retrying'             WHEN 6 THEN 'Failed'             ELSE 'Not Known' END,         CASE warning WHEN 0 THEN 'No Issues in Replication' ELSE 'Check Replication' END,         latency, latencythreshold,          'LatencyStatus'= CASE WHEN (latency > latencythreshold) THEN 'High Latency'         ELSE 'No Latency' END,         distribution_agentname,'DistributorStatus'= CASE WHEN (DATEDIFF(hh,last_distsync,GETDATE())>1) THEN 'Distributor has not executed more than n hour'         ELSE 'Distributor running fine' END         FROM #replmonitor --DROP TABLE #replmon

One alternative is to capture the data with a CLR stored procedure. I describe this for a different scenario in this article on my web site:
http://www.sommarskog.se/share_data.html#CLR

You could also try setting up a linked server which loops back to your own server, and set the server option "remote proc transaction promotion" to false. Then you could use INSERT-EXEC.

INSERT INTO #replmonitor

Hi Rajeev,

Could you please use the following query and check if it is successful?

INSERT INTO #replmonitor

SELECT a.*

FROM OPENROWSET

('SQLNCLI', 'Server=DBServer;Trusted_Connection=yes;',

'SET FMTONLY OFF; exec distribution..sp_replmonitorhelpsubscription

@publisher = DBServer,

@publication_type = 0,

@publication=MyPublication') AS a;

There is a similar thread for your reference.
https://social.msdn.microsoft.com/Forums/sqlserver/en-US/634090bf-915e-4d97-b71a-58cf47d62a8a/msg-8164-level-16-state-1-procedure-spmsloadtmpreplicationstatus-line-80?forum=sqlreplication

Thanks,
Lydia Zhang

Thanks Lydia for your solution.  It worked for me..

'SET FMTONLY OFF; exec distribution..sp_replmonitorhelpsubscription

Beware that the effect of SET FMTONLY OFF is that the stored procedure is executed twice. If SET FMTONLY OFF is intended to be a workaround for temp tables, beware that this workaround does not work on SQL 2012.