Я хотел поделиться тем, как я работал над этой проблемой, прежде чем закрыть этот вопрос. В итоге я использовал другой метод для решения этой проблемы вместо того, который я искал. В любом случае критика моего сценария Гордоном Линоффом была полезной, и я думаю, что у Раджеша тоже была правильная идея, но я нашел способ, который был для меня проще.
Цель моей проблемы была проста: я хотел, чтобы моя команда получала электронное письмо каждый раз, когда запрос возвращал какие-то результаты. В моем случае всякий раз, когда были какие-либо объекты, ожидающие в очереди более 30 минут. Прошу прощения за отсутствие подробностей, не хочу нарушать политику компании. Я думаю, что это можно сделать через систему оповещений, но мне это никогда не удавалось. Вместо этого я написал задание, которое включает в себя в качестве шага сценарий, который проверяет все объекты, соответствующие моим критериям, а затем отправляет электронное письмо моей команде, если они есть. Это, вероятно, очевидно для администраторов баз данных, но я супер новичок в этом деле, так что извините за мое невежество.
Для тех, кто застрял, вот что я сделал. Это руководство относится к выпуску 2014 года, и я приношу свои извинения, но я не могу включать изображения, так как у меня еще недостаточно репутации. Я рекомендую сначала выполнить эти шаги на тестовой базе данных.
1.) Напишите свой запрос
Создайте «Новый запрос» и начните с написания собственного запроса, который проверяет базу данных на наличие условия, на основе которого вы хотите, чтобы ваши автоматические электронные письма уведомлялись. Просто простой оператор выбора. Мы собираемся немного изменить наш запрос, чтобы записать количество записей, соответствующих критериям в предложении «где» в переменной @recordCount. Мы еще ничего не делаем с RecordCount, но выполняем скрипт, чтобы убедиться, что синтаксис правильный. Идите вперед и создайте свою собственную переменную recordCount, назначьте ее также для количества результатов вашего запроса и выполните ее, чтобы проверить синтаксис. На данный момент нам придется отложить наш запрос, чтобы мы могли настроить электронную почту в базе данных, но мы вернемся к нему на шаге 4, поэтому обязательно сохраните его или оставьте открытым.
Пример:
declare @recordCount int
Select @recordCount = isnull(count(*), 0)
From (table)
where (conditions)
Достаточно просто.
Шаг 2. Создайте профиль электронной почты
Нам нужно создать профиль для MSSQL, чтобы отправлять электронные письма от. Это не тот адрес электронной почты, на который вы хотите отправлять уведомления, однако они могут быть одним и тем же адресом электронной почты. В обозревателе объектов разверните узел сервера, затем разверните узел «Управление». Вы увидите что-то под названием «Почта базы данных». Дважды щелкните его, чтобы запустить мастер настройки почты базы данных. Нажмите «Далее» на вводной странице. На второй странице мастера базы данных вам нужно выбрать первый вариант (он должен что-то говорить о настройке нового профиля).
•Нажмите "Далее."
• Дайте вашему профилю имя. Помните, что это имя отправителя электронной почты, а не получателя.
• Заполните соответствующее описание.
• Нажмите «Добавить».
• В появившемся всплывающем окне выберите «Новая учетная запись».
• В появившемся окне «Новая учетная запись электронной почты базы данных» укажите имя и описание своей учетной записи.
• В разделе «Сервер исходящей почты» укажите адрес электронной почты отправителя и дайте ему отображаемое имя.
• Вам необходимо знать SMTP-сервер используемой учетной записи электронной почты. Если это учетная запись @intel.com, напишите smtp.intel.com, как показано ниже. Почта Google использует smtp.google.com, а yahoo использует smtp.mail.yahoo.com.
• Список имен smtp-серверов самых популярных почтовых провайдеров можно найти по этой ссылке: http://www.serversmtp.com/en/what-is-my-smtp
• Вам также необходимо знать номер порта.
• В разделе «Аутентификация» вам, скорее всего, понадобится первый вариант, хотя, если вы знаете адрес электронной почты и пароль для входа в эту учетную запись, вы можете выбрать второй вариант и указать их.
•Нажмите ОК.
• На следующем экране выберите сделать профиль общедоступным, если это целесообразно, и в крайнем правом столбце выберите этот профиль по умолчанию.
•Нажмите "Далее.
• На следующей странице оставьте параметры по умолчанию и нажмите «Далее».
• На последней странице проверьте изменения и нажмите «Готово».
Шаг 3. Включите электронную почту на агенте сервера
• Если вы развернете сервер и прокрутите вниз, вы увидите «Агент SQL Server». Щелкните правой кнопкой мыши и выберите «свойства».
• На боковой панели слева выберите «Система оповещений».
• В разделе «Почтовый сеанс» убедитесь, что установлен флажок «Включить почтовый профиль». «Почтовая система:» должна быть установлена на «Почта базы данных», и вы захотите выбрать профиль, созданный на шаге 2, в качестве почтового профиля. • В нижней части раздела «Пейджинговые сообщения электронной почты» установите флажок «Включить текст сообщения электронной почты в уведомление».
• Выберите ОК.
• Вам потребуется перезапустить агент. Вернувшись в проводник объектов, снова щелкните правой кнопкой мыши «Агент SQL Server» и выберите «Пуск» или «Перезапустить».
• Даже несмотря на то, что на агенте включена электронная почта, у вас может быть отключена почта базы данных. Создайте «Новый запрос» и скопируйте и вставьте следующий короткий скрипт. Это установит для переменной «Database Mail XPs» значение 1, что означает, что она включена: sp_configure 'показать дополнительные параметры', 1;
GO
RECONFIGURE;
GO
sp_configure 'Database Mail XPs', 1;
GO
RECONFIGURE
GO
• Вернитесь к опции «Почта базы данных» в обозревателе объектов в разделе «Имя сервера» > «Управление». Щелкните правой кнопкой мыши и выберите «Отправить тестовое письмо». В появившемся окне убедитесь, что в качестве профиля выбрана ваша новая учетная запись профиля, а в строке «Кому:» укажите собственный адрес электронной почты. • Вы должны получить тестовое электронное письмо в течение нескольких минут.
Шаг 4. Написание сценария
• Мы собираемся расширить наш запрос с шага 1.
• Предположительно, если запрос возвращает некоторые записи, мы хотим получить уведомление. Итак, мы расширим наш запрос до следующего скрипта:
declare @recordCount int
Select @recordCount = isnull(count(*), 0)
From (your table)
where (your criteria)
IF (@recordCount > 0)
Begin
EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'the name of the profile you created in step 2',
@recipients = 'your email address',
@query = 'select * from (your table - same as above) where (your criteria - same as above)' ,
@subject = 'your email subject',
@Body = 'your e-mail message'
End
• В верхней части этого нового скрипта мы видим наш точный запрос из предыдущего. Под ним находится условное утверждение. В нем говорится, что если есть какие-либо записи, соответствующие критериям нашего запроса, выполните команду send_dbmail. Идите вперед и скопируйте и вставьте этот скрипт в новый запрос, заполнив необходимую информацию. Поскольку это тест, вы, вероятно, захотите указать свой собственный адрес электронной почты для получателей. Кроме того, убедитесь, что вы дали ему точное имя, которое вы дали созданному вами профилю.
• Примечание. Если вы уже забыли имя созданного вами профиля или не уверены в правильности написания или регистра, щелкните правой кнопкой мыши агент SQL Server в проводнике объектов и выберите «Свойства». На левой боковой панели выберите «Система оповещений». В верхней части страницы будет указано имя профиля. Как только вы это сделаете, отмените из этого окна.
• После того, как вы заменили части, выделенные жирным шрифтом, вашим собственным материалом, выделите весь сценарий и выполните его. Если не было найдено ни одной записи, соответствующей вашим критериям, вы получите сообщение «Команда (ы) успешно выполнена». Если хотя бы одна запись будет найдена, вы получите сообщение «Почта в очереди».
• Если вы получили сообщение «Команда(ы) выполнена успешно», продолжайте и создайте фиктивную запись, которая будет соответствовать вашим критериям и вызовет отправку электронной почты, а затем снова запустите сценарий.
• Вы должны получить электронное письмо через несколько минут. Обратите внимание, что электронная почта уродлива и неправильно отформатирована. Мы исправим это сейчас.
• Чтобы отформатировать нашу электронную почту, нам нужно снова настроить наш скрипт. В приведенном ниже примере я отформатировал электронное письмо с помощью HTML, чтобы результаты запроса отображались в простой таблице. То, что нужно изменить, выделено жирным шрифтом.
declare @recordCount int
Select @recordCount = isnull(count(*), 0)
From **(table)**
where **(conditions)**
IF (@recordCount > 0)
Begin
DECLARE @xml NVARCHAR(MAX)
DECLARE @body NVARCHAR(MAX)
SET @xml = CAST(( SELECT **[(column)]** AS 'td','',**[(column)]** AS 'td','',
**[(column)]** AS 'td','', **(column)** AS 'td'
FROM **(table)**
where **(conditions)**
FOR XML PATH('tr'), ELEMENTS ) AS NVARCHAR(MAX))
SET @body ='**(Message at beginning of email)**' <br> <br> <br>
<html><body><H3 style="color:#3333FF">**(Label for your table)**</H3>
<table border = 1>
<tr>
<th> **(Column header text)**</th> <th> **(Column header text)** </th> <th> **(Column header text)** </th> <th> **(Column header text)** </th></tr>'
SET @body = @body + @xml +'</table></body></html>'
EXEC msdb.dbo.sp_send_dbmail
@profile_name = '**(profile name from last step)'**,
@recipients = '**(recipient e-mail)**',
@subject = '**(Email subject)**',
@Body = @body,
@Body_format='HTML'
End
• Самое главное, что вы добавляете переменную @Body_format и устанавливаете ее в HTML. Затем вы можете добавить переменную @Body в свой сценарий и установить для нее HTML-содержимое, которое вы хотите отправить по электронной почте. Не забудьте установить @Body равным @body внутри блока EXEC внизу. Все остальное — простая разметка. В результате получается простая таблица, но вы можете изменить разметку на любую, какую захотите.
• Идите вперед и сохраните свой сценарий. Мы будем использовать его на последнем шаге.
Шаг 5. Настройка задания для запуска нашего скрипта
• Мы почти закончили! Теперь, когда наш скрипт работает, все, что нам нужно сделать, это настроить задание для запуска этого скрипта по расписанию. В обозревателе объектов под сервером разверните агент SQL Server. Щелкните правой кнопкой мыши «Работа» и выберите «Новая работа».
• Дайте вашей работе имя и описание, оставьте в качестве владельца логин по умолчанию, а для параметра «Категория» можно оставить значение «[Без категории: (местный)]». Убедитесь, что установлен флажок «Включено».
• На левой боковой панели выберите «Шаги».
• Внизу выберите «Новый».
• В появившемся окне дайте шагу имя. Установите для параметра «Тип» значение «Сценарий Transact-SQL (T-SQL)» и выберите соответствующую базу данных. «Запуск от имени» можно оставить пустым.
• Нажмите кнопку «Открыть» и выберите сценарий, который вы сохранили на шаге 4. Инструмент автоматически заполнит ваш сценарий. Вы можете нажать «Разобрать», чтобы в последний раз проверить синтаксис вашего скрипта.
•Нажмите ОК.
• На левой боковой панели окна «Новая работа» выберите «Расписания». Внизу нажмите «Новый».
• Убедитесь, что установлен флажок «Включено» и что вы дали своему расписанию имя.
• То, как вы устанавливаете свой график, зависит от вас. Вы можете запланировать однократное выполнение задания или настроить его на повторяющееся выполнение каждый месяц, неделю, день, минуту или секунду, либо на неопределенный срок, либо до определенной даты окончания, только в течение рабочего дня или круглосуточно.
• Просмотрите описание внизу в разделе «Сводка», чтобы убедиться, что это именно то, что вы хотели. Нажмите «ОК».
• Вернувшись в окно «Новая работа», нажмите «ОК».
• Убедитесь, что агент SQL Server запущен, и разверните задания. Теперь вы должны увидеть свою работу в списке, если нет, вы можете щелкнуть правой кнопкой мыши «Работа» и выбрать «Обновить».
• Найдите свою работу в списке, щелкните ее правой кнопкой мыши и выберите «Начать работу на шаге...».
• Ваша работа запущена и будет выполняться так, как вы ее запланировали! Если вам когда-нибудь понадобится отредактировать его, вы можете просто дважды щелкнуть его.
Остались вопросы? •Это руководство было создано из следующих двух ресурсов:
1.) Почтовая документация базы данных Microsoft: http://msdn.microsoft.com/en-us/library/ms187605.aspx
2.) Извините, я не могу опубликовать вторую ссылку на ресурс, потому что у меня еще нет репутации. Просто Google Microsoft SQL Server устранение неполадок.
Всем спасибо.
person
Village
schedule
01.12.2014
