問題描述
以下存儲過程由我們的代理每 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.
這篇關于為什么這個程序會生成兩封電子郵件?的文章就介紹到這了，希望我們推薦的答案對大家有所幫助，也希望大家多多支持html5模板網！