为什么这个程序会生成两封电子邮件?

2021-09-10 00:00:00 sql tsql sql-server

以下存储过程由我们的代理每 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.

相关文章