Рассылка с SQL сервера без Outlook'а

Начну с предыстории.
Есть в нашей компании такая роль/должность - помощник руководителя. Это человек, который выполняет огромные объемы работы, работая в системе Террасофт 3.Х. Периодически помощнику назначаются задачи, сам помощник создает контрагентов, сделки.
Руководством была поставлена задача по контролю помощника, а именно "чтобы каждый день на почту в 16:00 приходило письмо, в котором будут таблицы с задачами, контрагентами и сделками, которые висят на помощнике (кроме задач в работе)". То есть те вопросы, который помощник не распределил по менеджерам или не решил.

Перейдем к технической реализации, это гораздо интереснее :smile:
Так как на реализацию задач по внутреннему проекту по определению не выделяется много времени, то я решил действовать быстро, надежно и наверняка (пусть и несколько некрасиво с точки зрения многоуровневой архитектуры), а именно работать с HTML таблицами и отправкой почты прямо из кода SQL. И да, это можно сделать.
Итак, для настройки возможности отправки email из sql-кода нам надо:

  1. Создать профиль и учетку для отправки писем для авторизации на сервере почты. Делается это в разделе "Management\Database Mail":
  2. Включить возможность отправки email скриптом:
    sp_CONFIGURE 'show advanced', 1
    GO
    RECONFIGURE
    GO
    sp_CONFIGURE 'Database Mail XPs', 1
    GO
    RECONFIGURE
    GO

После такой настройки мы уже можем отправлять email кодом вроде этого:

EXEC msdb.dbo.sp_send_dbmail
    @profile_name = 'Svistunov',
    @recipients = 'svistunov@samarasoft.ru;galanin@samarasoft.ru;lazareva@samarasoft.ru',
    @body = @tableHTML,
    @body_format = 'HTML',
    @subject = 'Задачи Лазаревой';

Остается только сформировать переменную @tableHTML на основе SQL-запросов к БД Террасофт. Я сделал это так (для краткости в примере только задачи, контрагенты и сделки формируются аналогично):

DECLARE @tableTasks nvarchar(max);
SET @tableTasks =
    N'

Задачи

'
+
    N''
+
    N'ЗадачаКонтрагент' +
    N'Сделка' +
    CAST (
                (SELECT td = ISNULL(t.Title, ' '),'',
                        td = ISNULL(a.Name, ' '),'',
                        td = ISNULL(o.Title, ' '),''
                        FROM tbl_Task t
                        LEFT JOIN tbl_Account a ON (t.AccountID=a.ID)
                        LEFT JOIN tbl_Opportunity o ON (t.OpportunityID=o.ID)
                        LEFT JOIN tbl_TaskStatus s ON (t.StatusID=s.ID)
                        WHERE t.OwnerID IN
                        (SELECT c.ID FROM tbl_Contact c WHERE c.Name LIKE '%Лазарева%')
                        AND s.STATUS='В работе'
                FOR XML PATH('tr'), TYPE
                )
                AS NVARCHAR(MAX)
        ) +
    N'' ;

--@tableAccounts
--@tableOpportunities

DECLARE @tableHTML nvarchar(max);
SET @tableHTML =
    N'

Задачи, Контрагенты и Сделки, где ответственный - Лазарева

'
+
    ISNULL(@tableTasks, '

Задач нет

'
) +
    ISNULL(@tableAccounts, '

Контрагентов нет

'
) +
    ISNULL(@tableOpportunities, '

Сделок нет

'
) +
    N'

Информация сформирована автоматически

'
;

Собственно это все. Остается только создать job, в нем всего один step, а в нем весь необходимый код. Прописываете в job'е расписание и каждый день можете лицезреть в почте вот такое письмо:

Нравится

Поделиться

1 комментарий

Очень полезный материал!

Спасибо!

Показать все комментарии