我目前正在运行以下程序来监视复制并在超过 10000 个 pendingcmds 的阈值时发送邮件，但是无论阈值如何，警报邮件都会每 5 分钟生成一次。我需要帮助来重新编码阈值部分，因为这没有提供预期的结果。
DECLARE @RESULT nvarchar(100)
DECLARE @cmd varchar(100)
SET @RESULT = ('SELECT pendingcmdcount FROM #pendingcmds')
EXEC sys.sp_executesql @RESULT
PRINT @RESULT
IF @RESULT > '10000'
用于生成警报的代码
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 > '10000'
--
--BEGIN
/*************************************************************/
/****************** HTML Preparation *************************/
/*************************************************************/
DECLARE @HTML VARCHAR(MAX),
@table VARCHAR(MAX)
SET @HTML = --HTML layout--
'<html><head>' +
'<H1 style="color: #000000">Publisher to Subscriber has exceeded threshold</H1>' +
'<style>' +
'td {border: solid black 1px;padding-left:5px;padding-right:5px;padding-top:1px;padding-bottom:1px;font-size:9pt;color:Black;} ' +
'</style>' +
'</head>' +
'<body><table cellpadding=0 cellspacing=0 border=0>' +
'<tr bgcolor=#FF0000>'+
'<td align=center>Pending CMD Count</b></td>' +
'<td align=center>Estimated Process Time</b></td></tr>';
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'</table></body></html>';
SET @HTML = @HTML + '<TR></TR><B>End of Report</B></TABLE></BODY></HTML>';
PRINT @HTML
Declare @MailSubject varchar(100)
SET @MailSubject = 'Alert - Publisher to Subscriber has exceeded threshold!!! - ' + DATENAME(weekday, getdate())
IF LEN(@HTML) > 10
EXEC msdb.dbo.sp_send_dbmail
@recipients = '[email protected]',
@body_format = 'HTML',
@body= @HTML,
@subject = @MailSubject,
@profile_name = 'MailRelay'
DROP TABLE #pendingcmds
--END
--ELSE
--DROP TABLE #pendingcmds
--
--END
以下建议未经过全面测试。
之后立马
我会删除任何未达到阈值的行。
然后，我会改变你是否发送电子邮件的测试
从
至：