问题描述
以下存储过程由我们的代理每 5 分钟运行一次 - 它使用此过程扫描表 Control_EmailQueue 以查看是否有任何新电子邮件要发送.
The following stored procedure is run by our agent every 5 mins - it scans the table Control_EmailQueue by using this proc to see if there are any new e-mails to send out.
我想测试当在表 Control_EmailQueue 中输入不正确的电子邮件信息时 proc 的行为.两个测试及其结果详述如下.
I wanted to test how the proc behaves when incorrect email information is entered into the table Control_EmailQueue. Two tests and their results are detailed below.
TEST1
我向 Control_EmailQueue 添加了一条记录，该记录在所有 3 个字段 EmailTO/EmailCC 和 EmailBCC 中都有 NULL 条目.这工作正常，即错误被捕获并且 CATCH 中的代码被执行，所以我收到一封电子邮件，标题为 'ERROR OCCURED DURING EMAIL CREATION'
TEST1
I add a record to Control_EmailQueue which has NULL entries in all 3 fields EmailTO/EmailCC and EmailBCC. This works fine i.e and error is trapped and the code within CATCH is executed so I receive an email titled 'ERROR OCCURED DURING EMAIL CREATION'
TEST2
我将记录添加到 Control_EmailQueue.在字段 EmailTO 中，我输入这个字符串 'me@me.co.uk;xxxxxxx@xxxxx' 即第一个电子邮件地址有效但第二个电子邮件地址无效.当代理运行该程序时，me@me.co.uk 会收到一封电子邮件，但半秒后，me@me.co.uk<会收到另一封相同的电子邮件./代码>.CATCH 代码未在此测试中执行，因为未收到标题为 'ERROR OCCURED DURING EMAIL CREATION' 的电子邮件.
TEST2
I add a record to Control_EmailQueue. In the field EmailTO I enter this string 'me@me.co.uk; xxxxxxx@xxxxx' i.e. the first email address is valid but the second email address is not valid. When the procedure is run by the agent an email is received by me@me.co.uk but then half a second later another identical email is received by me@me.co.uk. The CATCH code is not executed in this test as the email titled 'ERROR OCCURED DURING EMAIL CREATION' is not received.
BEGIN TRY
DECLARE @Exit TINYINT = 0
WHILE @Exit = 0
BEGIN
BEGIN TRANSACTION
DECLARE @MailIdFound INT =
(
SELECT
CASE
WHEN MIN([EmailId]) IS NULL THEN 0
ELSE MIN([EmailId])
END
FROM [xxx].[console].[Control_EmailQueue]
WHERE
[DateInsertKey] IS NOT NULL
AND
( --the following gives option to re-run past mails by updating DateEmailKey to NULL
[DateEmailKey] IS NULL
OR
[DateEmailKey] < [DateInsertKey]
)
AND
ErrorOccured = 0
AND
EmailActive = 1
)
IF @MailIdFound = 0
BEGIN SET @Exit = 1 END --exit here as
ELSE
BEGIN --send the mail here
--DECLARE @EmailId INT
DECLARE @DateInsertKey INT
DECLARE @DateEmailKey INT
DECLARE @CallingReportName NVARCHAR(1000)
DECLARE @EmailBCC NVARCHAR(1000)
DECLARE @EmailTO NVARCHAR(1000)
DECLARE @EmailCC NVARCHAR(1000)
DECLARE @EmailBody NVARCHAR(MAX)
DECLARE @EmailAttachmentPath NVARCHAR(1000)
DECLARE @EmailImportance VARCHAR(6)
DECLARE @EmailSubject NVARCHAR(1000)
;WITH myMostUrgentMail_cte
AS
(
SELECT
TOP 1
--[EmailId],
[DateInsertKey],
[DateEmailKey],
[CallingReportName],
[EmailBCC],
[EmailTO],
[EmailCC],
[EmailBody],
[EmailAttachmentPath],
[EmailImportance],
[EmailSubject]
FROM [xxx].[console].[Control_EmailQueue]
WHERE [EmailId] = @MailIdFound
)
SELECT
@DateInsertKey = [DateInsertKey],
@DateEmailKey = [DateEmailKey],
@CallingReportName = [CallingReportName],
@EmailTO = [EmailTO],
@EmailCC = [EmailCC],
@EmailBCC = [EmailBCC],
@EmailBody = [EmailBody],
@EmailAttachmentPath = [EmailAttachmentPath],
@EmailImportance = CASE
WHEN [EmailImportance] = 0 THEN 'Low'
WHEN [EmailImportance] = 1 THEN 'Normal'
WHEN [EmailImportance] = 2 THEN 'High'
END,
@EmailSubject = [EmailSubject]
FROM myMostUrgentMail_cte
SET @EmailBody = @EmailBody + '<b>Please contact us with any questions</b></p></span></html>'
EXEC msdb..sp_send_dbmail
@recipients = @EmailTO,
@copy_recipients = @EmailCC,
@blind_copy_recipients = @EmailBCC,
@subject = @EmailSubject,
@file_attachments = @EmailAttachmentPath,
@Importance = @EmailImportance,
@body_format = 'html',
@body = @EmailBody
UPDATE x
SET
x.[DateEmailKey] = (CONVERT(CHAR(8),GETDATE(),(112))),
x.[DateEmailTime] = (CONVERT([time](7),left(CONVERT([char](12),GETDATE(),(114)),(8)),(0)))
FROM [xxx].[console].[Control_EmailQueue] x
WHERE x.[EmailId] = @MailIdFound
END
COMMIT TRANSACTION
END
END TRY
BEGIN CATCH
IF @@trancount>0
BEGIN
ROLLBACK TRANSACTION
END
-- handle error here
DECLARE @ErrorMessage VARCHAR(100) = '<html><p>Error occured during creation of EmailId: ' + CONVERT(VARCHAR(10),@MailIdFound) + '</p><p>xxx.console.Control_EmailQueue</p></html>'
EXEC msdb..sp_send_dbmail
@recipients = 'me@me.co.uk;'
, @subject = 'ERROR OCCURED DURING EMAIL CREATION'
, @body_format = 'html'
, @body = @ErrorMessage
UPDATE x
SET x.ErrorOccured = 1
FROM [xxx].[console].[Control_EmailQueue] x
WHERE x.[EmailId] = @MailIdFound
END CATCH;
END
推荐答案
该问题似乎与事务的计时有关.通过在提交后添加延迟，事务能够在执行下一个循环之前完成并提交.
The problem appears to be related to timing with the transaction. By adding a delay after the commit, the transaction is able to complete and commit prior to the next loop being executed.
您可能应该做的一件事是从 sp_send_dbmail 获取 mailitem_id.也许你是对的，它失败了，但没有出错，但这不应该影响交易.我唯一能想到的是，由于事务尚未实际提交，因此您正在执行脏读或幻读，因此小延迟允许实际提交数据.
One thing you should probably do is get the mailitem_id from sp_send_dbmail. Perhaps you are correct and it is failing, but not erroring, but that shouldn't impact the transaction. The only thing I can think is that you are getting dirty or phantom reads because the transaction isn't actually committed yet, so the small delay allows the data to actually be committed.
这篇关于为什么这个程序会生成两封电子邮件?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持跟版网！