Вам когда-нибудь приходилось запускать определенный запрос в SQL Server и отправлять результаты по электронной почте определенной группе людей каждый день? А что, если вы хотите отправлять электронное письмо только в том случае, если запрос возвращает результаты? В этой статье мы рассмотрим, как автоматизировать этот процесс с помощью Database Mail и SQL Agent Job в SQL Server.
Настройка Database Mail
Прежде чем мы сможем начать автоматизацию процесса отправки электронной почты, нам необходимо настроить Database Mail, если это еще не сделано. Для этого выполните следующие шаги:
- Откройте SQL Server Management Studio (SSMS) и разверните папку Management.
- Щелкните правой кнопкой мыши на Database Mail и выберите “Configure Database Mail”.
- Дайте профилю имя и добавьте учетную запись SMTP. Вам потребуется получить имя сервера и информацию об аутентификации SMTP у системного администратора или администратора почты.
- Завершите настройку мастера, следуя инструкциям на экране.
Запомните имя профиля, так как мы будем использовать его в нашем скрипте.
Написание запроса и отправка электронной почты
Теперь, когда у нас настроена Database Mail, давайте напишем простой запрос и отправим результаты по электронной почте. Например, предположим, что нам нужен список сотрудников из таблицы employee, у которых текущий статус занятости равен false (0 для столбца данных типа bit).
SELECT Name, EmploymentStatus, StartDate
FROM dbo.Employee
WHERE EmploymentStatus = 0
Чтобы отправить этот список соответствующим получателям или списку рассылки, мы можем использовать хранимую процедуру msdb.dbo.sp_send_dbmail и предоставить необходимые параметры:
EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'QA_Notification',
@recipients = 'human_resources@company.com',
@subject = 'Employment Status',
@query = 'SELECT Name, EmploymentStatus, StartDate
FROM dbo.Employee
WHERE EmploymentStatus = 0',
@attach_query_result_as_file = 1,
@query_attachment_filename = 'EmploymentStatus.csv'
Вы можете протестировать этот код в окне запроса, но нашей целью является его автоматическая отправка. Для достижения этой цели мы можем создать SQL Agent Job и запланировать его запуск с нужной периодичностью.
Обработка нулевых результатов
Но что, если запрос не возвращает ни одной записи? В таком случае нет необходимости отправлять электронное письмо. Чтобы справиться с этим, мы можем добавить простой код для проверки наличия строк для возврата:
IF (SELECT COUNT(*) FROM dbo.Employee WHERE EmploymentStatus = 0) > 0
BEGIN
EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'QA_Notification',
@recipients = 'human_resources@company.com',
@subject = 'Employment Status',
@query = 'SELECT Name, EmploymentStatus, StartDate
FROM dbo.Employee
WHERE EmploymentStatus = 0',
@attach_query_result_as_file = 1,
@query_attachment_filename = 'EmploymentStatus.csv'
END
Добавляя этот код в начало, мы гарантируем, что электронное письмо будет отправлено только в случае наличия результатов для отчета.
Владение заданиями SQL Server
Прежде чем закончить этот пост, давайте кратко обсудим владение заданиями SQL Server. Важно определить, кто должен “владеть” заданием SQL Agent и почему это важно.
Когда задание SQL Server Agent выполняется, оно использует учетные данные самого сервера для входа в сеанс, а затем переключает вход в сеанс на учетную запись, которая владеет заданием SQL Server Agent. Если задание выполняется с неправильным контекстом безопасности, это может привести к сбоям и странным записям в журнале SQL Server.
Рекомендуется использовать отдельную учетную запись службы, которая является членом роли сервера sysadmin в SQL Server, для владения всеми заданиями SQL. Это можно установить при создании нового задания, указав владельца в свойствах задания.
Чтобы проверить, кто владеет всеми заданиями SQL на ваших серверах, вы можете использовать запрос, который извлекает данные из таблицы msdb.dbo.sysjobs и объединяет их с таблицей master.sys.syslogins. Например:
SELECT j.name AS [JobName], l.name AS [LOGIN]
FROM msdb.dbo.sysjobs AS j
JOIN master.sys.syslogins AS l ON j.owner_sid = l.sid
Владение заданиями SQL и обеспечение их выполнения в правильном контексте безопасности позволяет избежать возможных сбоев и поддерживать более организованную систему управления заданиями.
В заключение, автоматизация отправки результатов запросов SQL Server по электронной почте может сэкономить время и усилия для администраторов баз данных. Используя Database Mail, SQL Agent Jobs и обработку нулевых результатов, вы можете создавать настраиваемые решения для себя и вашей организации. Не стесняйтесь делиться своими творческими решениями в комментариях или написать мне напрямую!