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-ый · Comments Closed
Метки: , , , ,  · Рубрики: 13-ый, MS SQL

Комментариев нет

  1. Irina Pironova - 14.07.2018

    Здравствуйте! Users can interact with the Report Server web service directly, or instead use Report Manager, a Web-based application that interfaces with the Report Server web service. With Report Manager, users can view, subscribe to, and manage reports as well as manage and maintain data sources and security settings. Report Manager can also deliver SQL reports by e-mail, or place them on a file share.