Hi Micheal & Stevie,
I have been using the dbWarden Monitoring tool for about an year now and seems to work brilliantly as this is currently running on SQL Server 2008 R2.
It does work fine even in SQL 2016 as well, but when I recently added the scripts to one of our SQL server 2017 development instance the HealthReport jobs keeps failing with the below error,
Warning: Null value is eliminated by an aggregate or other SET operation. [SQLSTATE 01003] (Message 8153) Column name or number of supplied values does not match table definition. [SQLSTATE 21S01] (Error 213). The step failed.
And when executed the rpt_HealthReport stored procedure explicitly we get the following error,
Warning: Null value is eliminated by an aggregate or other SET operation.
Msg 213, Level 16, State 7, Procedure sp_helpdistributor, Line 434 [Batch Start Line 2]
Column name or number of supplied values does not match table definition.
Initially I was looking for the sp_helpdistributor stored procedure, but later on realised that it was a system SP and when compared the parameters of the stored procedure between SQL 2008 R2 & SQL 2017, noticed that there has been inclusion of new parameters in SQL 2017 (delete batch size xact, delete batch size cmd, distribution_listener), but delete batch size xact, delete batch size cmd parameters were also part of SQL 2016 which worked fine and I think it is the distribution_listener parameter which was causing the failure of the job & the SP.
So after modifying the create table script as below for #REPLINFO temporary table the procedure and the job worked perfectly fine,
/* Replication Distributor */
CREATE TABLE #REPLINFO (
distributor NVARCHAR(128) NULL,
[distribution database] NVARCHAR(128) NULL,
[min distrib retention] INT,
[max distrib retention] INT,
[history retention] INT,
[history cleanup agent] NVARCHAR(500),
[distribution cleanup agent] NVARCHAR(500),
[rpc server name] NVARCHAR(200),
[rpc login name] NVARCHAR(200),
[delete batch size xact] INT,
[delete batch size cmd] INT,
Thanks again for your scripts Micheal & Stevie Rounds.