В этом руководстве вы узнаете, как настроить почту базы данных и отправлять электронные письма с SQL Server пользователям.

Введение в базу данных SQL Server Mail

Электронная почта базы данных позволяет отправлять сообщения электронной почты пользователям из SQL Server Database Engine. Сообщение электронной почты может быть простым текстом или HTML и может включать прикрепленные файлы.

Database Mail надежен, масштабируем, безопасен и поддерживает.

Надежность

• Database Mail использует протокол SMTP для отправки сообщений электронной почты.
• SQL Server использует отдельный процесс для доставки сообщений электронной почты, чтобы минимизировать влияние на производительность сервера. SQL Server будет ставить сообщения электронной почты в очередь, даже если сервер SMTP находится в автономном режиме. Сообщение электронной почты будет отправлено, когда сервер SMTP перейдет в режим онлайн.
• Database Mail может использовать несколько SMTP-серверов. Если SMTP-сервер недоступен, он будет использовать второй SMPT-сервер для отправки сообщений электронной почты.

Масштабируемый

• Database Mail отправляет сообщения электронной почты асинхронно в фоновом режиме. Чтобы отправить сообщение электронной почты, используйте хранимую процедуру sp_send_dbmail. Эта хранимая процедура добавляет запрос в очередь Service Broker и немедленно возвращает ответ. Внешний компонент электронной почты получает запрос и доставляет сообщение электронной почты.

Безопасность

• Database Mail по умолчанию выключен. Чтобы отправлять сообщения электронной почты, необходимо сначала включить его.
• Для отправки электронного письма пользователь должен быть членом роли базы данных DatabaseMailUserRole в базе данных msdb.
• Database Mail позволяет защитить почтовые профили.
• Database Mail позволяет вам настраивать ограничение размера и расширение прикрепленных файлов. Если вы хотите прикрепить файл из папки к электронному письму, учетная запись ядра SQL Server должна иметь разрешение на доступ к файлу.

Поддерживаемость

• Ведение журнала — Database Mail регистрирует активность электронной почты в таблицах системной базы данных msdb и в журнале событий приложений Microsoft Windows.
• Аудит – Database Mail хранит копии сообщений электронной почты и вложений в базе данных msdb.
• Несколько форматов электронной почты — Database Mail поддерживает как простой текст, так и HTML-форматы.

Настройка почты базы данных SQL Server

Сначала измените параметр «Показать расширенную конфигурацию» на 1:

sp_configure 'Show Advanced', 1;
reconfigure;

Сделав это, вы сможете просмотреть все глобальные параметры конфигурации текущего сервера с помощью хранимой процедуры sp_configure:

sp_configure

Во-вторых, включите Database Mail для текущего экземпляра SQL Server:

sp_configure 'Database Mail XPs',1 
reconfigure

В-третьих, создайте учетную запись Database Mail с помощью хранимой процедуры msdb.dbo.sysmail_add_account_sp:

EXECUTE msdb.dbo.sysmail_add_account_sp
@account_name = 'Primary Account',
@description = 'Account used by all mail profiles.',
@email_address = ' [email protected] ',
@replyto_address = ' [email protected] ',
@display_name = 'Database Mail',
@mailserver_name = 'smtp.sqlservertutorial.net';

В-четвертых, создайте профиль Database Mail:

EXECUTE msdb.dbo.sysmail_add_profile_sp
@profile_name = 'Public Profile',
@description = 'public profile for all users';

В-пятых, добавьте учетную запись в публичный профиль:

EXECUTE msdb.dbo.sysmail_add_profileaccount_sp
@profile_name = 'Public Profile',
@account_name = 'Primary Account',
@sequence_number = 1;

В-шестых, предоставьте доступ к профилю всем пользователям базы данных msdb:

EXECUTE msdb.dbo.sysmail_add_principalprofile_sp
@profile_name = 'public Profile',
@principal_name = 'public',
@is_default = 1; 

Отправка электронной почты с помощью Database Mail

Для отправки сообщения электронной почты используется хранимая процедура msdb.dbo.sp_send_dbmail.

1) Пример отправки сообщения по электронной почте

В следующем примере отправляется сообщение электронной почты на адрес [email protected] :

EXEC msdb.dbo.sp_send_dbmail  
    @recipients = ' [email protected] ',  
    @body = 'This is a test message',  
    @subject = 'Database Mail Test';

2) Отправка сообщения электронной почты с результатом запроса пример

Сначала выберите инвентарь для продуктов с идентификаторами 1 и 2:

SELECT
  store_name,
  product_name,
  SUM(quantity)
FROM sales.stores s
INNER JOIN production.stocks i
  ON i.store_id = s.store_id
INNER JOIN production.products p
  ON p.product_id = i.product_id
WHERE p.product_id IN(1, 2)
GROUP BY store_name,
         product_name;

Выход:

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

:

SELECT
  CAST((SELECT
    td = store_name,
    '',
    td = product_name,
    '',
    td = SUM(quantity),
    ''
  FROM sales.stores s
  INNER JOIN production.stocks i
    ON i.store_id = s.store_id
  INNER JOIN production.products p
    ON p.product_id = i.product_id
  WHERE p.product_id IN(1, 2)
  GROUP BY store_name,
           product_name
  FOR xml PATH('tr'), TYPE)
  AS nvarchar(max));

В-третьих, преобразуйте тело HTML-таблицы в текст:

DECLARE @tableHTML NVARCHAR(MAX);  
SET @tableHTML =  
    N'
Inventory Report
' +  
    N'

' + N'' + CAST(( SELECT td=store_name,'', td=product_name,'', td=SUM(quantity),'' FROM sales.stores s INNER JOIN production.stocks i ON i.store_id = s.store_id INNER JOIN production.products p ON p.product_id = i.product_id WHERE p.product_id IN(1, 2) GROUP BY store_name, product_name FOR XML PATH('tr'), TYPE ) AS NVARCHAR(MAX) ) + N'

Store Name	Product	Total Quantity

' ; SELECT @tableHTML;

Наконец, отправьте электронное сообщение на адрес электронной почты [email protected] :

EXEC msdb.dbo.sp_send_dbmail 
    @recipients=' [email protected] ',  
    @subject = 'Inventory List',  
    @body = @tableHTML,  
    @body_format = 'HTML';  

Краткое содержание

• Database Mail позволяет отправлять сообщения электронной почты с использованием SQL Server Database Engine.
• Используйте хранимую процедуру msdb.dbo.sp_send_dbmail для отправки сообщения электронной почты.