Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
 Настройка отправки почты с сервера  [new]
beginSQL
Member

Откуда:
Сообщений: 4
Добрый день ребята. Только начинаю изучение SQL. помогите написать робота правильно.

Microsoft SQL Server 2017 (RTM-CU8) (KB4338363) - 14.0.3029.16 (X64) Jun 13 2018 13:35:56 Copyright (C) 2017 Microsoft Corporation Enterprise Edition (64-bit) on Windows Server 2016 Standard 10.0 <X64> (Build 14393: )

Настроил отправку писем с сервера пользователям, протестил EXEC msdb.dbo.sp_send_dbmail, процедура письма шлет.
У меня есть таблица где сидят зарегистрированные партнеры, но поле email не является обязательным, но в 90% случае это поле заполнено.
Нужно написать робота, который каждые 10 дней с момента регистрации партнера, будет отправлять на email письма с неким уведомлением для них, в течении 30 дней. И если на 30-ый день партнер не выслал документы, его учетка аннулируется, т.е. проставляется флаг удаления в этой же таблице. Если он выслал и его доки прошли верификацию, то наш пользователь ставит ему битовый флаг = доки высланы. И робот перестает ему слать письма. Но по каким-то причина может сломаться отправка почтового сервера и письмо может не уйти, то отправить письмо повторно, независимо прошел 10-ый день или нет.


Вот есть некая таблица зарегистрированных партнеров

IF OBJECT_ID('[TestDoc].[A]') IS NOT NULL
  DROP TABLE [TestDoc].[A]

CREATE TABLE [TestDoc].[A]
  (
    [Id]        Int           NOT NULL  IDENTITY(1,1),
    [Partner] varchar(128)    not null,
    [email]    varchar(128)   NULL,
    [DateReg]  Date       NULL, 
    [DELETED] bit NOT NULL,
    [HaveDog] bit NOT NULL, 
   PRIMARY KEY CLUSTERED([Id])
  )
GO

INSERT INTO [TestDoc].[A]
select 1,'Partner1','Partner1@mail.ru','2018-07-01',0,0
UNION
select 2,'Partner2','Partner2@mail.ru','2018-07-11',0,0
UNION
select 3,'Partner3','Partner3@mail.ru','2018-07-22',0,1
UNION
select 4,'Partner4','','2018-07-09',0,0


EXEC msdb.dbo.sp_send_dbmail
	@profile_name = 'aaa.ru',
	@recipients = [TestDoc].[A].email, --Сюда подставлять email партнеров
	@body = N'Уважаемые партнеры. Просьба предоставить оригинал документов до '+dateadd(d,30,dateReg),
	@subject = N'Предоставьте оригинал заявления'


Я так понял логи отправилось письмо или нет, пишутся сюда
SELECT * FROM msdb.dbo.sysmail_mailitems


И также есть системная вьюшка
 
SELECT * FROM msdb.dbo.sysmail_allitems

, где есть sent_status, успешно ли отправлено письмо или нет, в случае не успеха (failed) отправить повторно.

Но какая то тяжелая таблица, не пойму как осуществлять поиск, если по email, я так понял она не индексирована по email И поиск очень тяжелый.

Я понимаю что джоб должен запускаться каждый день на отправку, т.к. у каждого партнера своя дата регистрации, и что нужен некий цикл на проверку таблицы на флаги и проверка в логах отправилось ли письмо успешно.
24 июл 18, 12:13    [21597405]     Ответить | Цитировать Сообщить модератору
 Re: Настройка отправки почты с сервера  [new]
Guf
Member

Откуда: Новосибирск
Сообщений: 641
beginSQL,

У msdb.dbo.sp_send_dbmail есть исходящий параметр
автор
[ @mailitem_id = ] mailitem_id [ OUTPUT ]
Optional output parameter returns the mailitem_id of the message. The mailitem_id is of type int.

А у msdb.dbo.sysmail_allitems есть
автор
mailitem_id int Identifier of the mail item in the mail queue.
24 июл 18, 12:28    [21597520]     Ответить | Цитировать Сообщить модератору
 Re: Настройка отправки почты с сервера  [new]
beginSQL
Member

Откуда:
Сообщений: 4
Кто может помочь?
24 июл 18, 18:27    [21599240]     Ответить | Цитировать Сообщить модератору
 Re: Настройка отправки почты с сервера  [new]
Владислав Колосов
Member

Откуда:
Сообщений: 7754
beginSQL,

Вам же написали, что делать - сохраняйте ID отправленных писем, затем используйте их для проверки статуса.
24 июл 18, 20:56    [21599484]     Ответить | Цитировать Сообщить модератору
 Re: Настройка отправки почты с сервера  [new]
beginSQL
Member

Откуда:
Сообщений: 4
вопрос по вьюшке
SELECT * FROM msdb.dbo.sysmail_allitems

в случае sent_status - failed , я должен просто тут обновить статус на Retrying ? update msdb.dbo.sysmail_mailitems set sent_status = 1 ? этого будет достаточно?
26 июл 18, 11:29    [21603679]     Ответить | Цитировать Сообщить модератору
 Re: Настройка отправки почты с сервера  [new]
beginSQL
Member

Откуда:
Сообщений: 4
Владислав Колосов,
Я сохраняю @mailitem_id, во временную таблицу, потом идет сравнение с msdb.dbo.sysmail_allitems , где sent_status <> 'sent', если запись существует, можно ли отправить письмо с этим сохраненным @mailitem_id, повторно? или нужно снова вызывать процедуру sp_send_dbmail ? в случае ошибки отправить письмо на след. день можно ли указать ему дату отправки dateadd(d,1,getdate()) ? Просто джоб срабатывает каждый день, но там есть определенные условия срабатывания, если в этот день некоторые @mailitem_id отвалились с ошибкой, на след день он их не отправит, т.к. они уже не будут удовлетворять условиям.
26 июл 18, 11:40    [21603740]     Ответить | Цитировать Сообщить модератору
 Re: Настройка отправки почты с сервера  [new]
Владислав Колосов
Member

Откуда:
Сообщений: 7754
beginSQL,

для ответственной рассылки я написал собственную систему, для которой Reporting Services формирует и сохраняет письма в файлы. Система читает файлы из папки и отсылает так, как мне это нужно. Внутренняя система годится лишь для отправки разного рода оповещений и алёртов, т.к. почти не контролируема. Кроме того, необходимо периодически чистить файлы журналов отправки, а их несколько.
26 июл 18, 16:34    [21605246]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить