使用SQL Server 2008 T-SQL自动发送电子邮件，具有自定义消息标题和正文 [英] Automates sending email using SQL Server 2008 T-SQL, with custom message title and body

问题描述

此问题是此线程

鉴于我创建了两个简单的表用户和 / code>：

Given that I've created two simple tables User and Item:

• 用户（Id，姓名，电子邮件）


• Id，CreatedDate，EmailSent，UserId）

我可以创建一个定期运行以下脚本的SQL Server代理作业： p>

I am able to create an SQL Server Agent job that periodically runs the following script:

USE test
DECLARE @emails varchar(1000)
SET @emails = STUFF((SELECT ';' + u.Email FROM [user] u JOIN [item] i 
ON u.ID = i.UserId 
WHERE i.EmailSent = 0 AND DATEDIFF(day, i.CreatedDate, GETDATE()) >= 30 
FOR XML PATH('')),1,1,'')

/** send emails **/
EXEC msdb.dbo.sp_send_dbmail 
@profile_name='test',
@recipients=@emails,
@subject='Test message',
@body='This is the body of the test message.'

目的是获取任何已创建的项目> = 30天，然后向其用户发送提醒电子邮件。首先检查 EmailSent 以排除已经提醒的项目。

The purpose is to get any item that has been created for >= 30 days and then send a reminder email to its user. The EmailSent is checked first to exclude items already reminded.

我想改进此脚本，如下所示： p>

I want to improve this script as follows:



1. 有一个单独的查询，执行 SELECT 部分，并将结果存储到变量中它可以在电子邮件发送查询中重复使用，并在一个更新查询中，将所选项目 EmailSent code> True （发送电子邮件后完成）


2. 使用用户的名称自定义邮件的标题和正文， code>和项目的 Id 。如下所示：亲爱的名称，项目 Id 已创建30天以上。



1. There's a separate query that does the SELECT part and stores the result into a variable so that it can be reused in the email sending query, and in an UPDATE query that sets selected items' EmailSent to True (done after emails sent).
2. Customise the title and body of the message with user's Name and item's Id. Something like: Dear Name, the item Id has been created for 30 days or more.

任何人都知道如何完成这些改进？

Anybody has any idea of how these improvement could be done?

推荐答案

我已经设法达到目前为止

I've managed to have this so far

USE test
-- 1) Update selected items to state 1
UPDATE [PickingList]
SET ReminderState = 1
WHERE ReminderState = 0 AND DATEDIFF(day, CreatedDate, GETDATE()) >= 30

DECLARE @userId INT
DECLARE @email NVARCHAR(100)
DECLARE @plId INT
DECLARE @getId CURSOR

-- 2) Process those having state 1
SET @getId = CURSOR FOR
SELECT u.ID, u.Email, pl.ID
FROM [User] u JOIN [PickingList] pl
ON u.ID = pl.UserId
WHERE pl.ReminderState = 1

OPEN @getId
FETCH NEXT
FROM @getId INTO @userId, @email, @plId
WHILE @@FETCH_STATUS = 0
BEGIN
    /** send emails **/
    DECLARE @pId VARCHAR(1000)
    DECLARE @title VARCHAR(1000)
    DECLARE @body VARCHAR(8000)
    SET @pId = CAST(@plId AS VARCHAR(16))
    SET @title = @pId + ' was created more than 30 days ago'
    SET @body = 'The following picking list ' + @pId + ' blah blah'

    DECLARE @code INT
    SET @code = -1
    EXEC @code = msdb.dbo.sp_send_dbmail 
    @profile_name='test',
    @recipients=@email,
    @subject=@title,
    @body=@body

    -- 3) Log the email sent and update state to 2
    -- Below is what I want to do, but ONLY when the it can be sure that 
    -- the email has been delivered
    INSERT INTO [PickingListEmail] (UserId, PickingListId, SentOn) 
    VALUES (@userId, @plId, GETDATE())
    UPDATE [PickingList] SET ReminderState = 2 WHERE ReminderState = 1

    FETCH NEXT
    FROM @getId INTO @userId, @email, @plId
END
CLOSE @getId
DEALLOCATE @getId

<根据 sysmail_log 提取的数据，确定已发送电子邮件，因为 sp_send_dbmail 只会关心它是否可以将邮件发送到SMTP服务器，所以即使发送失败，也会返回成功代码 0 。

In step (3), before saving the email sent and update the item to state 2 (processed), I would want to make sure that the email has been sent, based on the data fetched from sysmail_log, as sp_send_dbmail would only care about whether it can send the mail to the SMTP server, so will return the success code 0 even when the sending fails.

这样的一个例子：

msdb.dbo.sysmail_mailitems上sent_status的值的含义

或

检查sp_send_dbmail是否成功

这篇关于使用SQL Server 2008 T-SQL自动发送电子邮件，具有自定义消息标题和正文的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！