Mail Alert when threshold of 10000 pendingcmds are breached I currently have the below running to monitor replication and send a mail when the threshold of 10000 pendingcmds are breached, however the alert mail is generated every 5 minutes regardless of the threshold. I need assistance to re-code the threshold part as this is not delivering the expected results. 

        DECLARE @RESULT nvarchar(100)
        DECLARE @cmd varchar(100)

        SET @RESULT = ('SELECT pendingcmdcount FROM #pendingcmds') 
        EXEC sys.sp_executesql @RESULT
        PRINT @RESULT
        IF @RESULT &gt; '10000'


Code used to generate alert

        USE [TestDB]

        create table #pendingcmds
        (pendingcmdcount int,
        estimatedprocesstime int)

        Insert INTO #pendingcmds (pendingcmdcount, estimatedprocesstime)
        select * FROM OPENROWSET('SQLOLEDB', 
        'Server=Server\Instance;Trusted_Connection=yes;', 
        'set fmtonly off; 
        exec distribution..sp_replmonitorsubscriptionpendingcmds 
        @publisher=''Server\Instance'', 
        @publisher_db=''testproduction'', 
        @publication=''Summary_Inventory_TestProduction'', 
        @subscriber=''Server'', 
        @subscriber_db=''TestReplAzure'', 
        @subscription_type=0')

        SELECT * FROM #pendingcmds

        --DECLARE @RESULT nvarchar(100)
        ----DECLARE @cmd varchar(100)
        --
        --SET @RESULT = ('SELECT pendingcmdcount FROM #pendingcmds') 
        --EXEC sys.sp_executesql @RESULT
        --PRINT @RESULT
        --IF @RESULT &gt; '10000'
        --
        --BEGIN

         /*************************************************************/  
         /****************** HTML Preparation *************************/  
         /*************************************************************/

        DECLARE @HTML  VARCHAR(MAX),
                @table VARCHAR(MAX)

        SET @HTML = --HTML layout--
                        '&lt;html&gt;&lt;head&gt;' +
                        '&lt;H1 style="color: #000000"&gt;Publisher to Subscriber has exceeded threshold&lt;/H1&gt;' +
                        '&lt;style&gt;' +
                        'td {border: solid black 1px;padding-left:5px;padding-right:5px;padding-top:1px;padding-bottom:1px;font-size:9pt;color:Black;} ' +
                        '&lt;/style&gt;' +
                        '&lt;/head&gt;' +
                        '&lt;body&gt;&lt;table cellpadding=0 cellspacing=0 border=0&gt;' +
                        '&lt;tr bgcolor=#FF0000&gt;'+  
                        '&lt;td align=center&gt;Pending CMD Count&lt;/b&gt;&lt;/td&gt;' + 
                        '&lt;td align=center&gt;Estimated Process Time&lt;/b&gt;&lt;/td&gt;&lt;/tr&gt;';  

        SELECT @table = CONVERT(nvarchar(max) ,
            (SELECT
             td = CAST(pendingcmdcount as varchar(30)), '',
             td = CAST(estimatedprocesstime as varchar(30)), '' 
             FROM #pendingcmds
             FOR XML PATH(N'tr'), TYPE));

        SET @HTML = @HTML + @table + CHAR(10) + 
                     N'&lt;/table&gt;&lt;/body&gt;&lt;/html&gt;';

        SET @HTML = @HTML + '&lt;TR&gt;&lt;/TR&gt;&lt;B&gt;End of Report&lt;/B&gt;&lt;/TABLE&gt;&lt;/BODY&gt;&lt;/HTML&gt;';

        PRINT @HTML

        Declare @MailSubject varchar(100)
        SET @MailSubject = 'Alert - Publisher to Subscriber has exceeded threshold!!! - ' + DATENAME(weekday, getdate())

        IF LEN(@HTML) &gt; 10 

            EXEC msdb.dbo.sp_send_dbmail
            @recipients = 'tester@test.com',
            @body_format =  'HTML',
            @body= @HTML,
            @subject = @MailSubject,
            @profile_name = 'MailRelay'

        DROP TABLE #pendingcmds
        --END

        --ELSE 
        --DROP TABLE #pendingcmds
        --
        --END
The following suggestions are not fully tested.

Immediately after

Insert INTO #pendingcmds (pendingcmdcount, estimatedprocesstime)
select * FROM OPENROWSET('SQLOLEDB', 
'Server=Server\Instance;Trusted_Connection=yes;', 
'set fmtonly off; 
exec distribution..sp_replmonitorsubscriptionpendingcmds 
@publisher=''Server\Instance'', 
@publisher_db=''testproduction'', 
@publication=''Summary_Inventory_TestProduction'', 
@subscriber=''Server'', 
@subscriber_db=''TestReplAzure'', 
@subscription_type=0')


I would delete any rows where your threshold was not met.

DELETE FROM #pendingcmds WHERE pendingcmdcount &lt;= 10000


Then, I would change your test for whether to send the email or not 

From 

IF LEN(@HTML) &gt; 10 

            EXEC msdb.dbo.sp_send_dbmail
            @recipients = 'tester@test.com',
            @body_format =  'HTML',
            @body= @HTML,
            @subject = @MailSubject,
            @profile_name = 'MailRelay'


TO:

IF (SELECT COUNT(*) FROM #pendingcmds) &gt; 0 
    EXEC msdb.dbo.sp_send_dbmail
    @recipients = 'tester@test.com',
    @body_format =  'HTML',
    @body= @HTML,
    @subject = @MailSubject,
    @profile_name = 'MailRelay'