TL:DR, there is a good reason, you are not finding any good solutions for what you want.  It does not seem possible, within the constraints you have. 

At first glance getting details from the existing stored procedures in [Programmatically Monitor Replication](https://docs.microsoft.com/en-us/sql/relational-databases/replication/monitor/programmatically-monitor-replication?view=sql-server-ver15) putting them together in a table (*temp or permanent*) to use for tracking and reporting seems like a great idea.  But when you start googling around you don't find anything as simple as you imagined it would be.  

The two top solutions google finds involve `INSERT-EXEC` or `OPENQUERY` & `OPENROWSET`, but they have issues and ultimately most googling leads to [How to Share Data between Stored Procedures, by Erland Sommarskog](http://www.sommarskog.se/share_data.html) 

> INSERT-EXEC

> It's a method that is seemingly very appealing, because it's very simple to use and understand... *It Can't Nest*

> Msg 8164, Level 16, State 1, Procedure BigSalesByStore, Line 8

> An INSERT EXEC statement cannot be nested.

and

> OPENQUERY and its cousin OPENROWSET

> Moreover, it is not aimed at improving performance. It may save you from rewriting your stored procedure, but most likely you will have to put in more work overall – and in the end you get a poorer solution. While I'm not enthusiastic over INSERT-EXEC, it is still a far better choice than OPENQUERY.

The first thing I tried that should have been a really good start.

	IF OBJECT_ID('tempdb..#SP_RMHP') IS NOT NULL DROP TABLE #SP_RMHP
	GO

	Create Table #SP_RMHP(
		publisher SYSNAME	
		, distribution_db SYSNAME	
		, status INT
		, warning INT
		, publicationcount INT
		, returnstamp CHAR (16)
		)
	INSERT #SP_RMHP 
	  EXEC distribution.dbo.sp_replmonitorhelppublisher

	Select * from #SP_RMHP

	drop table #SP_RMHP

The problem is that, sometimes you get the error `An INSERT EXEC statement cannot be nested.` and sometimes you don't.  In SQL 2017 I almost always get it.  In SQL 2014 sometimes I don't, sometimes I do.  Very frustrating and not a path to solution.

So now what? 

## Focus on pulling data directly from the distribution database tables. 

Data supporting the replication process is kept in tables on 4 different database. [MSN details are here](https://docs.microsoft.com/en-us/sql/relational-databases/system-tables/replication-tables-transact-sql?view=sql-server-ver15)

* msdb Database
* Distribution Database
* Publication Database
* Subscription Database

In one of my replication instances I do not have access to the Subscription servers, so I focused on just the first 3.

The table msdb.sysreplicationalerts has some errors, but everyone I saw was also displayed in the SQL logs, I am looking for evidence of broken replication when there are no errors reported in the SQL logs. So not what I need, but might be helpful to others. 

    select top (100) * from msdb..sysreplicationalerts

I looked through all of the tables, examined the column descriptions and looked at the data durring uptime.  I found a few potential source, so I set the subscription database offline, in theory this would duplicate an issue with the subscriber that did not directly impact replication or the publication. 

With the subscriber database offline (*for hours during this testing*)

* The replication monitor showed green, no Latency, and the Last Synchronization time was updated every couple of minutes to be current. 

 * Clearly Replication is not "working" as the subscriber is offline :(

* A possible candidate is 'MSreplication_monitordata' (distribution database), but essentially shows the same thing as the replication monitor, I did not dig deeper, but it is probably the source for Last Synchronization time in the replication monitor

.

	Select last_distsync
	, DATEDIFF ( MINUTE , last_distsync , GETDATE() )  as 'MinDifflast_distsync'
	, agentstoptime
	, time_stamp
	, DATEDIFF ( MINUTE , time_stamp, GETDATE() )  as 'MinDifftime_stamp'
	, GETDATE() as 'GETDATE'
	, agent_id --may want this for reporting
	, agent_name --may want this for reporting
	--, * 
	from MSreplication_monitordata
	where agent_type = 3 --3=Distribution Agent

. 

* The only other thing that looked promising is the table MSsubscriptions on the publication database.  There are some sequence numbers there that differ when the subscriber is offline, **unfortunately** they still differ when you bring the subscriber database back online.  Looking at one of live publication replications, there are 100's that differ at any given time. 

.

	select subscription_seqno
	, publisher_seqno
	, * 
	from MSsubscriptions
	where subscription_seqno <> publisher_seqno

Now I am going to look into [Canary tables as discussed by Kendra Little in this article](https://www.brentozar.com/archive/2014/07/monitoring-sql-server-transactional-replication/) This is problematic, because it requires modification of databases (add the table) with can be problematic if you are responsible for monitoring, but prohibited from altering the user database.