E-mail от SQL сервера. Часть 2.1 — Пишем письма

Итак, прошлый раз, мы остановились на том, что настроили отправку писем средствами  SQL Server-а (используя компонент database mail). Так же мы выяснили, что теперь можно отправить письмо вызовом функции sp_send_dbmail из любой процедуры.

А теперь предлагаю обсудить, почему же мы не будем её использовать для отправки писем из хранимых процедур :)

Ну во первых, использование этой функции требует наличия у того, кто её запускает роли DatabaseMailUserRole. Давать такую роль без контрольно всем подрят не стоит.

Во вторых мы получим не контролируемый поток писем, сложно будет выяснить, кто и когда отправляет письма, если надо будет как-то изменить н-р получателей.

Поэтому мы сделаем чуток иначе, мы создадим табличку, куда будут складываться задания по отправке писем, а потом централизованно, используя Job, работающий под пользователем имеющим соответствующие права, и будем их отправлять из этой таблицы. Табличка:


CREATE TABLE [dbo].[SendMail](
[id] [int] IDENTITY(1,1) NOT NULL,
[email] [nvarchar](400) NOT NULL,
[subject] [nvarchar](255) NOT NULL,
[text] [nvarchar](max) NOT NULL,
[source] [nvarchar](255) NOT NULL,
[counter] [int] NOT NULL CONSTRAINT [DF_SendMail_counter]  DEFAULT ((0))

)

поле id — это просто идентификатор письма
email — почтовые адреса
subject — тема письма
text — содержимое письма
source — псевдоним отправителя, позволяющий сортировать письма и опозновать отправителя
counter — число попыток отправить

Для отправки письма, напишем такую процедуру, вызов которой вставим в Job и будем его выполнять с некой переодичностью :


create procedure MailProcess
as
begin
declare @id int;
declare @email varchar(255);
declare @subject varchar(255);
declare @text varchar(max);
declare @source varchar(255);
declare @res int;
declare @podpis varchar(999);

set @podpis='----------------------------------------------------------------------------------------'+char(13)+char(13)+
'Письмо создано автоматически, Ваш ответ никто не прочтет.'+char(13)+char(13)+'Идентификатор отправителя : '; -- некая уникальная подпись, которую мы будем вставлять во все письма

declare cur_mail cursor  for
select id,email,subject,text,source from SendMail where isnull(counter,0)<6; -- отбираем только те письма, число попыток отправить которые не более 5
open  cur_mail;

fetch next from cur_mail into @id,@email,@subject,@text,@source;

while @@fetch_status=0
begin
set @text=@text+char(10)+char(13)+char(10)+char(13)+@podpis+@source+', сервер - ' +@@SERVERNAME; -- т.к. может быть несколько серверов отправителей, то указываем кто отправил
exec @res= msdb.dbo.sp_send_dbmail @profile_name ='test', @recipients=@email,@subject=@subject,@body=@text; -- собственно вызов отправки письма, тут надо или указать имя профиля - test, в нашем случае или указать в настройках профиль по умолчанию, тогда тут его можно пропустить

-- если письмо успешно поставлено в очередь на отправку, то удаляем его, иначе увеличиваем счетчик попыток

if @res=0 delete from SendMail where id=@id
else update  SendMail set counter=isnull(counter,0)+1 where id=@id

fetch next from cur_mail into @id,@email,@subject,@text,@source;
end;

close  cur_mail;
deallocate cur_mail;

end;

Теперь отправить письмо можно выполнив insert в табличку SendMail. Причем сделать это можно из любой программы, не обязательно из SQL Server-а.

Для удобства можно оформить insert через хранимую процедуру.

В следующей части мы напишем табличку, позволяющию управлять отправкой писем, создавая для них правила и перепишем процедуру отправки для работы с этой табличкой.

Продолжение следует…

26.04.2010 · 13-ый · Комментариев нет
Метки: , , , ,  · Рубрики: 13-ый, MS SQL

Написать комментарий