Для чего используются уведомления sql

Уведомления о событиях

Уведомления о событиях отправляют сведения о событиях в службу Service Broker. Уведомления о событиях выполняются в ответ на различные инструкции языка определения данных Transact-SQL (DDL) и события трассировки SQL, отправляя сведения об этих событиях в службу Service Broker.

Уведомления о событиях можно использовать следующим образом.

• Регистрировать и просматривать изменения или активность базы данных.
• Выполнять действия в ответ на события в асинхронном режиме, а не синхронном.

Уведомления о событиях предоставляют программную альтернативу триггерам DDL и использованию приложения трассировки SQL.

Преимущества уведомлений о событиях

Уведомления о событиях запускаются асинхронно, вне области транзакции. Поэтому, в отличие от триггеров DDL, уведомления о событиях можно использовать внутри приложений баз данных и реагировать на события, не требуя ресурсов, выделяемых для немедленной транзакции.

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

Данные событий можно использовать приложениями, работающими вместе с SQL Server, для отслеживания хода выполнения и принятия решений. Например, следующее уведомление о событии отправляет сообщение определенной службе каждый раз, когда инструкция ALTER TABLE выполняется в образце базы данных AdventureWorks2022 .

USE AdventureWorks2022; GO CREATE EVENT NOTIFICATION NotifyALTER_T1 ON DATABASE FOR ALTER_TABLE TO SERVICE '//Adventure-Works.com/ArchiveService' , '8140a771-3c4b-4479-8ac0-81008ab17984'; 

Основные понятия уведомлений о событиях

При создании уведомления о событии открывается одна или несколько бесед Service Broker между экземпляром SQL Server и указанной целевой службой. Эти диалоги обычно остаются открытыми, пока уведомление о событии существует в виде объекта на экземпляре сервера. В некоторых случаях ввиду ошибок диалоги могут закрываться до удаления уведомления о событии. Эти диалоги никогда не разделяются между уведомлениями о событиях. Каждое уведомление о событии владеет собственными диалогами. Явное завершение диалога не дает конечной службе далее получать сообщения, и при следующем появлении события диалог повторно открываться не будет.

Сведения о событии доставляются в службу Service Broker в виде переменной типа XML , которая предоставляет сведения о том, когда происходит событие, о затронутом объекте базы данных, инструкции пакетной службы Transact-SQL и других сведениях. Дополнительные сведения о схеме XML, созданной уведомлениями о событиях, см. в разделе EVENTDATA (Transact-SQL).

Сравнение уведомлений о событиях и Триггеры

В следующей таблице сравниваются триггеры и уведомления о событиях и выявляются различия между ними.

Сравнение уведомлений о событиях и Трассировка SQL

В следующей таблице сравниваются способы мониторинга серверных событий, основанные на уведомлениях о событиях и использовании инструмента трассировки SQL.

Задачи уведомлений о событиях

Работа с уведомлениями запросов

Собственный клиент SQL Server (часто сокращенный SNAC) был удален из SQL Server 2022 (16.x) и SQL Server Management Studio 19 (SSMS). Собственный клиент SQL Server (SQLNCLI или SQLNCLI11) и устаревший поставщик Microsoft OLE DB для SQL Server (SQLOLEDB) не рекомендуется для разработки новых приложений. Перейдите на новый драйвер Microsoft OLE DB (MSOLEDBSQL) для SQL Server или последний драйвер Microsoft ODBC для SQL Server . Сведения о SQLNCLI, которые поставляется в качестве компонента ядра СУБД SQL Server (версии 2012–2019), см. в этом исключении жизненного цикла поддержки.

Уведомления о запросах появились в SQL Server 2005 (9.x) и собственном клиенте SQL Server. На основе инфраструктуры Service Broker, представленной в SQL Server 2005 (9.x), уведомления запросов позволяют приложениям получать уведомления при изменении данных. Эта функция особенно полезна для приложений, которые предоставляют кэш данных из базы данных (например, для веб-приложений), и которым требуются уведомления об изменении исходных данных.

С помощью уведомлений о запросах можно запрашивать уведомления в течение указанного времени ожидания при изменении базовых данных запроса. В запросе на уведомление указываются параметры уведомления, среди которых имя службы, текст сообщения и значение времени ожидания для сервера. Доставка уведомлений осуществляется через очередь компонента SQL Service Broker, которую приложения могут опрашивать на предмет имеющихся уведомлений.

Строка параметров уведомлений запросов имеет следующий синтаксис.

Подписки на уведомления переживают процесс, который их инициирует, поскольку приложение может создать подписку на уведомление, а затем завершиться. Подписка остается действительной, а уведомление будет отправлено в случае изменения данных в течение времени ожидания, указанного при создании подписки. Уведомление определяется выполненным запросом, параметрами уведомления, а также текстом сообщения. Его можно отменить, установив нуль в качестве значения времени ожидания.

Уведомления отправляются только один раз. Чтобы получать постоянные уведомления об изменении данных, необходимо создавать новую подписку после обработки каждого уведомления путем повторного выполнения запроса.

Приложения SQL Server Native Client обычно получают уведомления с помощью команды Transact-SQL RECEIVE для чтения уведомлений из очереди, связанной с службой, указанной в параметрах уведомлений.

В запросах необходимо указывать имена таблиц, для которых требуется отправлять уведомления, например dbo.myTable . Имена таблиц должны состоять из двух частей. Подписка будет недействительной в случае использования трех- или четырехкомпонентных имен.

Инфраструктура уведомлений построена на основе функции очереди, представленной в SQL Server 2005 (9.x). Как правило, уведомления, формируемые на сервере, отправляются через эти запросы для последующей обработки.

Чтобы использовать уведомления о запросах, очередь и служба должны существовать на сервере. Их можно создать с помощью Transact-SQL, как показано ниже.

CREATE QUEUE myQueue CREATE SERVICE myService ON QUEUE myQueue ([http://schemas.microsoft.com/SQL/Notifications/PostQueryNotification]) 

Служба должна использовать стандартный контракт http://schemas.microsoft.com/SQL/Notifications/PostQueryNotification , показанный выше.

Поставщик OLE DB для собственного клиента SQL Server

Поставщик OLE DB собственного клиента SQL Server поддерживает уведомление потребителей об изменении набора строк. Потребитель получает уведомление на каждой стадии изменения набора строк, а также при каждой попытке внести изменение.

Передача запроса уведомлений серверу с помощью ICommand::Execute является единственным допустимым способом подписки на уведомления запросов с помощью поставщика OLE DB собственного клиента SQL Server.

Набор свойств DBPROPSET_SQLSERVERROWSET

Для поддержки уведомлений запросов с помощью OLE DB sql Server Native Client добавляет следующие новые свойства в набор свойств DBPROPSET_SQLSERVERROWSET.

Подписка на уведомления всегда фиксируется независимо от того, выполнялась ли инструкция в рамках пользовательской транзакции или в режиме AUTO COMMIT, а также была ли транзакция, в рамках которой выполнялась инструкция, зафиксирована либо был выполнен ее откат. Уведомление сервера срабатывает при возникновении любого из следующих недопустимых условий: изменение базовых данных или схемы либо по истечении времени ожидания, в зависимости от того, что произойдет первым. Регистрации уведомлений удаляются сразу же после их срабатывания. Поэтому, если приложению требуется получение уведомлений в дальнейшем, оно должно подписаться на них снова сразу после получения уведомления.

Другое соединение или поток может проверять целевую очередь на наличие уведомлений. Например:

WAITFOR (RECEIVE * FROM MyQueue); // Where MyQueue is the queue name. 

Обратите внимание, что SELECT * не удаляет запись из очереди, однако функция RECEIVE * FROM выполняется. Если очередь пуста, поток сервера будет остановлен. При наличии в очереди записей в момент вызова они возвращаются мгновенно; в противном случае вызов ожидает появления записи в очереди.

RECEIVE * FROM MyQueue 

Если очередь пуста, то эта инструкция немедленно возвращает пустой результирующий набор; в противном случае она возвращает все уведомления в очереди.

Если значение свойств SSPROP_QP_NOTIFICATION_MSGTEXT и SSPROP_QP_NOTIFICATION_OPTIONS не равно NULL и не является пустым, то при каждом выполнении этой команды на сервер отправляется заголовок потока табличных данных уведомлений о запросах, содержащий три свойства, указанные выше. Если значение любого из них равно NULL (или пустое), заголовок не отправляется, а формируется DB_E_ERRORSOCCURRED (или DB_S_ERRORSOCCURRED, если оба свойства обозначены, как необязательные), а значение состояния устанавливается в DBPROPSTATUS_BADVALUE. Поверка выполняется в случае команды Execute/Prepare. Аналогичным образом, DB_S_ERRORSOCCURED возникает, когда свойства уведомления запроса задаются для подключений к версиям SQL Server до SQL Server 2005 (9.x). В этом случае значение состояния — DBPROPSTATUS_NOTSUPPORTED.

Запуск подписки не гарантирует успешной доставки последующих сообщений. Кроме того, допустимость указанного имени службы также не проверяется.

При подготовке инструкций подписка никогда не запускается. Запуск подписки происходит только при выполнении инструкций, а использование базовых служб OLE DB не оказывает влияния на уведомления о запросах.

Дополнительные сведения о наборе свойств DBPROPSET_SQLSERVERROWSET см. в разделе «Свойства и поведение набора строк».

Драйвер ODBC для собственного клиента SQL Server

Драйвер ODBC собственного клиента SQL Server поддерживает уведомления о запросах с помощью добавления трех новых атрибутов в функции SQLGetStmtAttr и SQLSetStmtAttr :

• Атрибут SQL_SOPT_SS_QUERYNOTIFICATION_MSGTEXT
• SQL_SOPT_SS_QUERYNOTIFICATION_OPTIONS
• SQL_SOPT_SS_QUERYNOTIFICATION_TIMEOUT

Если значение свойств SQL_SOPT_SS_QUERYNOTIFICATION_MSGTEXT и SQL_SOPT_SS_QUERYNOTIFICATION_OPTIONS не равно NULL, то при каждом выполнении этой команды на сервер отправляется заголовок потока табличных данных уведомлений о запросах, содержащий три атрибута, указанных выше. Если значение любого из них равно NULL, заголовок не отправляется и возвращается SQL_SUCCESS_WITH_INFO. Проверка выполняется в функции SQLPrepare, SqlExecDirect и SqlExecute, все из которых завершаются ошибкой, если атрибуты недопустимы. Аналогичным образом, если эти атрибуты уведомления запроса задаются для версий SQL Server до SQL Server 2005 (9.x), выполнение завершается сбоем с SQL_SUCCESS_WITH_INFO.

Подготовка инструкций никогда не вызывает запуска подписки; подписка может быть запущена выполнением инструкции.

Особые случаи и ограничения

Следующие типы данных для уведомлений не поддерживаются:

Если сделан запрос на уведомление о запросе, который возвращает один из этих типов данных, уведомление срабатывает мгновенно с указанием, что выполнить подписку на уведомление невозможно.

Если запрос на подписку сделан для пакета или хранимой процедуры, для каждой инструкции, выполняемой в пакете или хранимой процедуре, будет сделан отдельный запрос на подписку. Инструкция EXECUTE не регистрирует уведомление, но направляет требование об уведомлении выполняемой команде. Если это пакет, к выполняемым инструкциям будет применен контекст и правила, описанные выше.

Отправка запроса на уведомление, отправленное тем же пользователем в том же контексте базы данных, и имеет тот же шаблон, те же значения параметров, один и тот же идентификатор уведомления и то же расположение доставки существующей активной подписки, продлевает существующую подписку, сбросив новое указанное время ожидания. Это означает, что если уведомление запрашивается для идентичных запросов, будет отправлено только одно уведомление. Данные условия применяются к запросам, повторяющимся в пакетах, или к запросам, которые вызываются несколько раз в хранимой процедуре.

Настройка оповещений о результатах выполнения заданий и задач MS SQL Server

Для оперативного реагирования необходимо своевременно получать информацию об ошибках выполнения заданий. Для этого в SQL Server существует специальный механизм оповещений. В этой статье мы рассмотрим как его настроить.

Механизм оповещений

Настройку оповещений можно разделить на 4 этапа:

1. Настройка профиля компонента Database Mail
2. Создание оператора оповещений и настройка почты агента SQL Server
3. Включение триггеров и задач оповещений
4. Проверка работоспособности

Настройка профиля компоненты Database Mail

Для того чтобы настроить учетную запись компонента Database Mail необходимо в дереве «Обозревателя Объектов» (Object Explorer) выбрать настраиваемый экземпляр SQL Server, перейти в «Управление» (Management), далее «Компонент Database Mail» (Database Mail). Щелкнув правой клавишей мыши на данном пункте, будет открыто контекстное меню, в котором необходимо выбрать «Настроить…» (Configure…), после чего будет открыто окно мастера настройки.

Первую страницу можно пропустить, поэтому перейдем сразу ко второй. Здесь нам необходимо выбрать первый пункт «Установить компонент Database Mail…» (Setup Database Mail…) и нажать «Далее» (Next). На третьей странице задаем имя и описание профиля, затем нажимаем кнопку «Добавить» для добавления учетной записи SMTP. В открывшемся окне необходимо заполнить данные учетной записи (e-mail адрес, сервер и порт SMTP, а так же параметры аутентификации).

На следующей странице необходимо назначить безопасность профиля: указать открытый это профиль или частный (и для каких пользователей), а также можно указать является ли профилем по умолчанию. Для наших целей достаточно сделать профиль открытым и профилем по умолчанию. На пятой странице оставляем все по умолчанию, на последней странице жмем «Готово» (Finish)

После того как профиль настроен, его надо проверить, для этого в контекстном меню пункта «Компонент Database Mail» (вызываемым щелчком правой мыши по данному пункту) надо выбрать «Отправить тестовое сообщение» (Send Test E-Mail). В открывшемся окне следует заполнить поле «Кому» (To) и нажать «Отправить …» (Send …)

Если все сделано правильно, тогда в ближайшее время на почту будет доставлено тестовое письмо.

Добавление оператора оповещений

Операторы — это псевдонимы людей или групп, которые могут получать электронные уведомления о завершении задач, заданий или предупреждения. Для добавления нового оператора оповещений необходимо в дереве «Обозревателя Объектов» (Object Explorer) выбрать настраиваемый экземпляр SQL Server, перейти в «Агент SQL Server» (SQL Server Agent), далее «Операторы» (Operators). Щелкнув правой клавишей мыши на данном пункте, выбрать «Создать оператора» (New Operator), после чего будет открыто окно свойств оператора оповещений.

Настройки оператора находятся на закладке «Общие» (General). Здесь необходимо заполнить «Имя» (Name), указать состояние «Включено» (Enabled), ввести адрес электронной почты. В целом, существуют альтернативные способы оповещения помимо электронной почты: с помощью команды net send или сообщением на пейджер.

На этом настройка оператора завершена, перейдем к следующему шагу.

Настройка почты агента SQL Server

Данный пункт необходим для рассылки уведомлений по электронной почты агентом SQL Server. Такая рассылка происходит, например, для уведомления о статусе выполнения задания. Для настройки почты агента необходимо в дереве «Обозревателя Объектов» (Object Explorer) выбрать настраиваемый экземпляр SQL Server, перейти в «Агент SQL Server» (SQL Server Agent), из контекстного меню выбрать «Свойства» (Properties).

В открывшемся окне перейдем на вкладку «Система предупреждений» (Alert System), установим флажок «Включить почтовый профиль» (Enable mail profile), в качестве почтовой системы оставим «Компонент Database Mail» (Database Mail) и выберем ранее созданный профиль в соответствующем поле.

Включение триггеров и задач оповещения

За основу для подключения оповещений возьмем план обслуживания из статьи Механизм «Планы обслуживания» и механизм заданий MS SQL Server.

Для удобства проверки настроим оповещения на успешное выполнение заданий, в реальной жизни больше имеет смысл подключать оповещения на случай ошибки. В целях демонстрации настроим оповещения на наше «Задание» (Job), а так же добавим оповещение в наш план обслуживания.

Итак, откроем наш план обслуживания, в нем выделим субплан «UpdateStatistics». На рабочую область субплана перетащим задачу «Уведомление оператора» (Notify Operator Task). Протянем стрелку от задачи «Обновление статистики». Двойным кликом по задаче «Уведомление оператора» откроем ее свойства, отметим созданного оператора, а так же введем «Тему» (Subject) и «Текст» (Message) письма. Для того чтобы изменить условие оповещения (по умолчанию стрелка вида «Успешное завершение») надо щелкнуть правой клавишей мыши на стрелке и выбрать ее вид: успешное выполнение/ошибка/выполнение.

Теперь перейдем к настройке оповещений в «Заданиях» (Jobs). Откроем свойства нашего задания и перейдем на вкладку «Уведомления» (Notifications). Установим галки рядом с видами уведомлений, которые мы хотим использовать (у меня это только электронная почта), далее выберем оператора и условие оповещения (успешное завершение/завершение с ошибкой/любое завершение).

Проверка работоспособности оповещений

Выполним ручной запуск нашего задания, для этого щелкнем правой клавишей мыши на нем и выберем «Запустить задание на шаге» (Start Job at Step). В результате должно прийти на почту 2 письма: одно (с установленными нами темой и текстом) соответствует задаче «Уведомления оператора» в плане обслуживания; второе — информирует о выполнении задания в целом.

Если не пришло ни одного письма тогда, возможно:

1. Ошибка в параметрах учетной записи SMTP. Попробуйте отправить тестовое сообщение, если оно не отправляется — перепроверьте параметры
2. Ошибка в правах безопасности профиля. Перепроверьте установлены ли права как описано ранее. Если все установлено верно, а результата нет — попробуйте указать все права: открытый и все частные, а так же сделать профилем по умолчанию для каждого права

Если не пришло только письмо с результатом выполнения задания: проверьте что почта SQL Server’а настроена в соответствии с вышеизложенным.

• Версия СУБД: SQL Server 2014

Настройка почтовых уведомлений в MS SQL Server

Часто возникает потребность каким-либо способом сообщать администраторам о проблемах, возникших на сервере. Причем уведомления в большинстве своем делятся на 2 типа:

1) реального времени, т. е. те, которые должны приходить сразу при возникновении проблемы
2) отложенного времени, т. е. те, которые приходят через достаточно продолжительное время (более 1 часа) после возникновения проблемы.

В моей работе было необходимо расширить функционал обычного Database Mail.

В данной статье будет рассмотрен пример того, как формировать уведомления в HTML-таблицы с последующей отправкой по почте администраторам.

Решение

1. Настроим Database Mail
2. Создадим таблицу для получателей:

USE [ИМЯ_БАЗЫ_ДАННЫХ] GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE TABLE [srv].[Recipient]( [Recipient_GUID] [uniqueidentifier] ROWGUIDCOL NOT NULL, [Recipient_Name] [nvarchar](255) NOT NULL, --основной почтовый адрес получателя [Recipient_Code] [nvarchar](10) NOT NULL, --код получателя [IsDeleted] [bit] NOT NULL, --признак удаления (используется получатель или нет) [InsertUTCDate] [datetime] NOT NULL, CONSTRAINT [PK_Recipient] PRIMARY KEY CLUSTERED ( [Recipient_GUID] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY], CONSTRAINT [AK_Recipient_Code] UNIQUE NONCLUSTERED ( [Recipient_Code] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY], CONSTRAINT [AK_Recipient_Name] UNIQUE NONCLUSTERED ( [Recipient_Name] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] GO ALTER TABLE [srv].[Recipient] ADD CONSTRAINT [DF_Recipient_Recipient_GUID] DEFAULT (newsequentialid()) FOR [Recipient_GUID] GO ALTER TABLE [srv].[Recipient] ADD CONSTRAINT [DF_Recipient_IsDeleted] DEFAULT ((0)) FOR [IsDeleted] GO ALTER TABLE [srv].[Recipient] ADD CONSTRAINT [DF_Recipient_InsertUTCDate] DEFAULT (getutcdate()) FOR [InsertUTCDate] GO

3. Создадим таблицу для адресов получателей:

USE [ИМЯ_БАЗЫ_ДАННЫХ] GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE TABLE [srv].[Address]( [Address_GUID] [uniqueidentifier] ROWGUIDCOL NOT NULL, [Recipient_GUID] [uniqueidentifier] NOT NULL, --получатель [Address] [nvarchar](255) NOT NULL, --почтовый адрес [IsDeleted] [bit] NOT NULL, --признак удаления (используется адрес или нет) [InsertUTCDate] [datetime] NOT NULL, CONSTRAINT [PK_Address] PRIMARY KEY CLUSTERED ( [Address_GUID] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY], CONSTRAINT [AK_Address] UNIQUE NONCLUSTERED ( [Recipient_GUID] ASC, [Address] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] GO ALTER TABLE [srv].[Address] ADD CONSTRAINT [DF_Address_Address_GUID] DEFAULT (newsequentialid()) FOR [Address_GUID] GO ALTER TABLE [srv].[Address] ADD CONSTRAINT [DF_Address_IsDeleted] DEFAULT ((0)) FOR [IsDeleted] GO ALTER TABLE [srv].[Address] ADD CONSTRAINT [DF_Address_InsertUTCDate] DEFAULT (getutcdate()) FOR [InsertUTCDate] GO

4. Создадим таблицу для очереди сообщений:

USE [ИМЯ_БАЗЫ_ДАННЫХ] GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE TABLE [srv].[ErrorInfo]( [ErrorInfo_GUID] [uniqueidentifier] NOT NULL, [ERROR_TITLE] [nvarchar](max) NULL, --заголовок [ERROR_PRED_MESSAGE] [nvarchar](max) NULL, --предварительная информация [ERROR_NUMBER] [nvarchar](max) NULL, --код сообщения (ошибки) [ERROR_MESSAGE] [nvarchar](max) NULL, --сообщение [ERROR_LINE] [nvarchar](max) NULL, --номер строки [ERROR_PROCEDURE] [nvarchar](max) NULL, --хранимая процедура [ERROR_POST_MESSAGE] [nvarchar](max) NULL, --пояснительная информация [RECIPIENTS] [nvarchar](max) NULL, --получатели через ';' [InsertDate] [datetime] NOT NULL, [StartDate] [datetime] NOT NULL, --дата и время начала [FinishDate] [datetime] NOT NULL, --дата и время окончания [Count] [int] NOT NULL, --кол-во раз [UpdateDate] [datetime] NOT NULL, [IsRealTime] [bit] NOT NULL, --признак реального времени [InsertUTCDate] [datetime] NULL, CONSTRAINT [PK_ErrorInfo] PRIMARY KEY CLUSTERED ( [ErrorInfo_GUID] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY] GO ALTER TABLE [srv].[ErrorInfo] ADD CONSTRAINT [DF_ErrorInfo_ErrorInfo_GUID] DEFAULT (newid()) FOR [ErrorInfo_GUID] GO ALTER TABLE [srv].[ErrorInfo] ADD CONSTRAINT [DF_ErrorInfo_InsertDate] DEFAULT (getdate()) FOR [InsertDate] GO ALTER TABLE [srv].[ErrorInfo] ADD CONSTRAINT [DF_ErrorInfo_StartDate] DEFAULT (getdate()) FOR [StartDate] GO ALTER TABLE [srv].[ErrorInfo] ADD CONSTRAINT [DF_ErrorInfo_FinishDate] DEFAULT (getdate()) FOR [FinishDate] GO ALTER TABLE [srv].[ErrorInfo] ADD CONSTRAINT [DF_ErrorInfo_Count] DEFAULT ((1)) FOR [Count] GO ALTER TABLE [srv].[ErrorInfo] ADD CONSTRAINT [DF__ErrorInfo__Updat__5FFEE747] DEFAULT (getdate()) FOR [UpdateDate] GO ALTER TABLE [srv].[ErrorInfo] ADD CONSTRAINT [DF_ErrorInfo_IsRealTime] DEFAULT ((0)) FOR [IsRealTime] GO ALTER TABLE [srv].[ErrorInfo] ADD CONSTRAINT [DF_ErrorInfo_InsertUTCDate] DEFAULT (getutcdate()) FOR [InsertUTCDate] GO

5. Создадим архивную таблицу для отправленных сообщений из очереди сообщений:

USE [ИМЯ_БАЗЫ_ДАННЫХ] GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE TABLE [srv].[ErrorInfoArchive]( [ErrorInfo_GUID] [uniqueidentifier] ROWGUIDCOL NOT NULL, [ERROR_TITLE] [nvarchar](max) NULL, [ERROR_PRED_MESSAGE] [nvarchar](max) NULL, [ERROR_NUMBER] [nvarchar](max) NULL, [ERROR_MESSAGE] [nvarchar](max) NULL, [ERROR_LINE] [nvarchar](max) NULL, [ERROR_PROCEDURE] [nvarchar](max) NULL, [ERROR_POST_MESSAGE] [nvarchar](max) NULL, [RECIPIENTS] [nvarchar](max) NULL, [InsertDate] [datetime] NOT NULL, [StartDate] [datetime] NOT NULL, [FinishDate] [datetime] NOT NULL, [Count] [int] NOT NULL, [UpdateDate] [datetime] NOT NULL, [IsRealTime] [bit] NOT NULL, [InsertUTCDate] [datetime] NULL, CONSTRAINT [PK_ArchiveErrorInfo] PRIMARY KEY CLUSTERED ( [ErrorInfo_GUID] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY] GO ALTER TABLE [srv].[ErrorInfoArchive] ADD CONSTRAINT [DF_ErrorInfoArchive_ErrorInfo_GUID] DEFAULT (newsequentialid()) FOR [ErrorInfo_GUID] GO ALTER TABLE [srv].[ErrorInfoArchive] ADD CONSTRAINT [DF_ArchiveErrorInfo_InsertDate] DEFAULT (getdate()) FOR [InsertDate] GO ALTER TABLE [srv].[ErrorInfoArchive] ADD CONSTRAINT [DF_ErrorInfoArchive_StartDate] DEFAULT (getdate()) FOR [StartDate] GO ALTER TABLE [srv].[ErrorInfoArchive] ADD CONSTRAINT [DF_ErrorInfoArchive_FinishDate] DEFAULT (getdate()) FOR [FinishDate] GO ALTER TABLE [srv].[ErrorInfoArchive] ADD CONSTRAINT [DF_ErrorInfoArchive_Count] DEFAULT ((1)) FOR [Count] GO ALTER TABLE [srv].[ErrorInfoArchive] ADD CONSTRAINT [DF_ErrorInfoArchive_UpdateDate] DEFAULT (getdate()) FOR [UpdateDate] GO ALTER TABLE [srv].[ErrorInfoArchive] ADD CONSTRAINT [DF_ErrorInfoArchive_IsRealTime] DEFAULT ((0)) FOR [IsRealTime] GO ALTER TABLE [srv].[ErrorInfoArchive] ADD CONSTRAINT [DF_ErrorInfoArchive_InsertUTCDate] DEFAULT (getutcdate()) FOR [InsertUTCDate] GO

Эта информация нужна для истории. Но также данную таблицу нужно чистить от очень старых данных (например, старее месяца).

6. Создадим хранимую процедуру, которая регистрирует новое сообщение в очередь сообщений:

USE [ИМЯ_БАЗЫ_ДАННЫХ] GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE PROCEDURE [srv].[ErrorInfoIncUpd] @ERROR_TITLE nvarchar(max), @ERROR_PRED_MESSAGE nvarchar(max), @ERROR_NUMBER nvarchar(max), @ERROR_MESSAGE nvarchar(max), @ERROR_LINE nvarchar(max), @ERROR_PROCEDURE nvarchar(max), @ERROR_POST_MESSAGE nvarchar(max), @RECIPIENTS nvarchar(max), @StartDate datetime=null, @FinishDate datetime=null, @IsRealTime bit = 0 AS BEGIN /* регистрация ошибки в таблицу ошибок на отправление по почте если уже в таблице есть запись с одинаковым заголовком, содержанием и отправителем , то изменится конечная дата ошибки, дата обновления записи, а также количество ошибок */ SET NOCOUNT ON; declare @ErrorInfo_GUID uniqueidentifier; select top 1 @ErrorInfo_GUID=ErrorInfo_GUID from srv.ErrorInfo where (ERROR_TITLE=@ERROR_TITLE or @ERROR_TITLE is null) and RECIPIENTS=@RECIPIENTS and (ERROR_MESSAGE=@ERROR_MESSAGE or @ERROR_MESSAGE is null) and (ERROR_PRED_MESSAGE=@ERROR_PRED_MESSAGE or @ERROR_PRED_MESSAGE is null) and (ERROR_POST_MESSAGE=@ERROR_POST_MESSAGE or @ERROR_POST_MESSAGE is null) and (IsRealTime=@IsRealTime or @IsRealTime is null); if(@ErrorInfo_GUID is null) begin insert into srv.ErrorInfo ( ERROR_TITLE ,ERROR_PRED_MESSAGE ,ERROR_NUMBER ,ERROR_MESSAGE ,ERROR_LINE ,ERROR_PROCEDURE ,ERROR_POST_MESSAGE ,RECIPIENTS ,IsRealTime ,StartDate ,FinishDate ) select @ERROR_TITLE ,@ERROR_PRED_MESSAGE ,@ERROR_NUMBER ,@ERROR_MESSAGE ,@ERROR_LINE ,@ERROR_PROCEDURE ,@ERROR_POST_MESSAGE ,@RECIPIENTS ,@IsRealTime ,isnull(@StartDate, getdate()) ,isnull(@FinishDate,getdate()) end else begin update srv.ErrorInfo set FinishDate=getdate(), [Count]=[Count]+1, UpdateDate=getdate() where ErrorInfo_GUID=@ErrorInfo_GUID; end END GO

7. Создадим хранимую процедуру, которая возвращает строку из адресов по коду или основному почтовому адресу получателя:

USE [ИМЯ_БАЗЫ_ДАННЫХ] GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE PROCEDURE [srv].[GetRecipients] @Recipient_Name nvarchar(255)=NULL, @Recipient_Code nvarchar(10)=NULL, @Recipients nvarchar(max) out /* Процедура составления почтовых адресов уведомлений */ AS BEGIN SET NOCOUNT ON; set @Recipients=''; select @Recipients=@Recipients+d.[Address]+';' from srv.Recipient as r inner join srv.[Address] as d on r.Recipient_GUID=d.Recipient_GUID where (r.Recipient_Name=@Recipient_Name or @Recipient_Name IS NULL) and (r.Recipient_Code=@Recipient_Code or @Recipient_Code IS NULL) and r.IsDeleted=0 and d.IsDeleted=0; --order by r.InsertUTCDate desc, d.InsertUTCDate desc; if(len(@Recipients)>0) set @Recipients=substring(@Recipients,1,len(@Recipients)-1); END GO

8. Создадим необходимые функции для работы с датой и временем:

USE [ИМЯ_БАЗЫ_ДАННЫХ] GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE FUNCTION [rep].[GetDateFormat] ( @dt datetime, -- входная дата @format int=0 -- заданный формат ) RETURNS nvarchar(255) AS /* Возвращает дату в виде строки по заданному формату и входной дате Проставляет необходимые нули: формат входная дата результат 0 17.4.2014 "17.04.2014" 1 17.4.2014 "04.2014" 1 8.11.2014 "11.2014" 2 17.04.2014 "2014" */ BEGIN DECLARE @res nvarchar(255); DECLARE @day int=DAY(@dt); DECLARE @month int=MONTH(@dt); DECLARE @year int=YEAR(@dt); if(@format=0) begin set @res=IIF(@day<10,'0'+cast(@day as nvarchar(1)), cast(@day as nvarchar(2)))+'.'; set @res=@res+IIF(@month<10,'0'+cast(@month as nvarchar(1)), cast(@month as nvarchar(2)))+'.'; set @res=@res+cast(@year as nvarchar(255)); end else if(@format=1) begin set @res=IIF(@month<10,'0'+cast(@month as nvarchar(1)), cast(@month as nvarchar(2)))+'.'; set @res=@res+cast(@year as nvarchar(255)); end else if(@format=2) begin set @res=cast(@year as nvarchar(255)); end RETURN @res; END GO USE [ИМЯ_БАЗЫ_ДАННЫХ] GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE FUNCTION [rep].[GetTimeFormat] ( @dt datetime, -- входное время @format int=0 -- заданный формат ) RETURNS nvarchar(255) AS /* Возвращает время в виде строки по заданному формату и входному времени Проставляет необходимые нули: формат входное время результат 0 17:04 "17:04:00" 1 17:04 "17:04" 1 8:04 "08:04" 2 17:04 "17" */ BEGIN DECLARE @res nvarchar(255); DECLARE @hour int=DATEPART(HOUR, @dt); DECLARE @min int=DATEPART(MINUTE, @dt); DECLARE @sec int=DATEPART(SECOND, @dt); if(@format=0) begin set @res=IIF(@hour<10,'0'+cast(@hour as nvarchar(1)), cast(@hour as nvarchar(2)))+':'; set @res=@res+IIF(@min<10,'0'+cast(@min as nvarchar(1)), cast(@min as nvarchar(2)))+':'; set @res=@res+IIF(@sec<10,'0'+cast(@sec as nvarchar(1)), cast(@sec as nvarchar(2))); end else if(@format=1) begin set @res=IIF(@hour<10,'0'+cast(@hour as nvarchar(1)), cast(@hour as nvarchar(2)))+':'; set @res=@res+IIF(@min<10,'0'+cast(@min as nvarchar(1)), cast(@min as nvarchar(2))); end else if(@format=2) begin set @res=IIF(@hour<10,'0'+cast(@hour as nvarchar(1)), cast(@hour as nvarchar(2))); end RETURN @res; END GO

9. Создадим хранимую процедуру, которая создает HTML-отчет в виде таблицы по сообщениям:

USE [ИМЯ_БАЗЫ_ДАННЫХ] GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE PROCEDURE [srv].[GetHTMLTable] @recipients nvarchar(max) ,@dt datetime -- по какое число читать AS BEGIN /* формирует HTML-код для таблицы */ SET NOCOUNT ON; declare @body nvarchar(max); declare @tbl table(ID int identity(1,1) ,[ERROR_TITLE] nvarchar(max) ,[ERROR_PRED_MESSAGE] nvarchar(max) ,[ERROR_NUMBER] nvarchar(max) ,[ERROR_MESSAGE] nvarchar(max) ,[ERROR_LINE] nvarchar(max) ,[ERROR_PROCEDURE] nvarchar(max) ,[ERROR_POST_MESSAGE] nvarchar(max) ,[InsertDate] datetime ,[StartDate] datetime ,[FinishDate] datetime ,[Count] int ); declare @ID int ,@ERROR_TITLE nvarchar(max) ,@ERROR_PRED_MESSAGE nvarchar(max) ,@ERROR_NUMBER nvarchar(max) ,@ERROR_MESSAGE nvarchar(max) ,@ERROR_LINE nvarchar(max) ,@ERROR_PROCEDURE nvarchar(max) ,@ERROR_POST_MESSAGE nvarchar(max) ,@InsertDate datetime ,@StartDate datetime ,@FinishDate datetime ,@Count int insert into @tbl( [ERROR_TITLE] ,[ERROR_PRED_MESSAGE] ,[ERROR_NUMBER] ,[ERROR_MESSAGE] ,[ERROR_LINE] ,[ERROR_PROCEDURE] ,[ERROR_POST_MESSAGE] ,[InsertDate] ,[StartDate] ,[FinishDate] ,[Count] ) select top 100 [ERROR_TITLE] ,[ERROR_PRED_MESSAGE] ,[ERROR_NUMBER] ,[ERROR_MESSAGE] ,[ERROR_LINE] ,[ERROR_PROCEDURE] ,[ERROR_POST_MESSAGE] ,[InsertDate] ,[StartDate] ,[FinishDate] ,[Count] from [srv].[ErrorInfo] where ([RECIPIENTS]=@recipients) or (@recipients IS NULL) and InsertDate'; set @body=@body+''; set @body=@body+''; set @body=@body+'№ п/п'; set @body=@body+''; set @body=@body+''; set @body=@body+'ДАТА'; set @body=@body+''; set @body=@body+''; set @body=@body+'ОШИБКА'; set @body=@body+''; set @body=@body+''; set @body=@body+'ОПИСАНИЕ'; set @body=@body+''; set @body=@body+''; set @body=@body+'КОД ОШИБКИ'; set @body=@body+''; set @body=@body+''; set @body=@body+'СООБЩЕНИЕ'; set @body=@body+''; set @body=@body+''; set @body=@body+'НАЧАЛО'; set @body=@body+''; set @body=@body+''; set @body=@body+'ОКОНЧАНИЕ'; set @body=@body+''; set @body=@body+''; set @body=@body+'КОЛИЧЕСТВО'; set @body=@body+''; set @body=@body+''; set @body=@body+'НОМЕР СТРОКИ'; set @body=@body+''; set @body=@body+''; set @body=@body+'ПРОЦЕДУРА'; set @body=@body+''; set @body=@body+''; set @body=@body+'ПРИМЕЧАНИЕ'; set @body=@body+''; set @body=@body+''; while((select top 1 1 from @tbl)>0) begin set @body=@body+''; select top 1 @ID =[ID] ,@ERROR_TITLE =[ERROR_TITLE] ,@ERROR_PRED_MESSAGE=[ERROR_PRED_MESSAGE] ,@ERROR_NUMBER =[ERROR_NUMBER] ,@ERROR_MESSAGE =[ERROR_MESSAGE] ,@ERROR_LINE =[ERROR_LINE] ,@ERROR_PROCEDURE =[ERROR_PROCEDURE] ,@ERROR_POST_MESSAGE=[ERROR_POST_MESSAGE] ,@InsertDate =[InsertDate] ,@StartDate =[StartDate] ,@FinishDate =[FinishDate] ,@Count =[Count] from @tbl order by InsertDate asc; set @body=@body+''; set @body=@body+cast(@ID as nvarchar(max)); set @body=@body+''; set @body=@body+''; set @body=@body+rep.GetDateFormat(@InsertDate, default)+' '+rep.GetTimeFormat(@InsertDate, default);--cast(@InsertDate as nvarchar(max)); set @body=@body+''; set @body=@body+''; set @body=@body+isnull(@ERROR_TITLE,''); set @body=@body+''; set @body=@body+''; set @body=@body+isnull(@ERROR_PRED_MESSAGE,''); set @body=@body+''; set @body=@body+''; set @body=@body+isnull(@ERROR_NUMBER,''); set @body=@body+''; set @body=@body+''; set @body=@body+isnull(@ERROR_MESSAGE,''); set @body=@body+''; set @body=@body+''; set @body=@body+rep.GetDateFormat(@StartDate, default)+' '+rep.GetTimeFormat(@StartDate, default);--cast(@StartDate as nvarchar(max)); set @body=@body+''; set @body=@body+''; set @body=@body+rep.GetDateFormat(@FinishDate, default)+' '+rep.GetTimeFormat(@FinishDate, default);--cast(@FinishDate as nvarchar(max)); set @body=@body+''; set @body=@body+''; set @body=@body+cast(@Count as nvarchar(max)); set @body=@body+''; set @body=@body+''; set @body=@body+isnull(@ERROR_LINE,''); set @body=@body+''; set @body=@body+''; set @body=@body+isnull(@ERROR_PROCEDURE,''); set @body=@body+''; set @body=@body+''; set @body=@body+isnull(@ERROR_POST_MESSAGE,''); set @body=@body+''; delete from @tbl where set @body=@body+''; end set @body=@body+''; select @body; END GO

10. Создадим хранимую процедуру, которая отправляет сообщения:

USE [ИМЯ_БАЗЫ_ДАННЫХ] GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE PROCEDURE [srv].[RunErrorInfoProc] @IsRealTime bit =0 -- режим отправки (1-реального времени) AS BEGIN /* выполнить отправку уведомлений об ошибках с указанным режимом */ SET NOCOUNT ON; declare @dt datetime=getdate(); declare @tbl table(Recipients nvarchar(max)); declare @recipients nvarchar(max); declare @recipient nvarchar(255); declare @result nvarchar(max)=''; declare @recp nvarchar(max); declare @ind int; declare @recipients_key nvarchar(max); --получаем все необходимые сообщения insert into @tbl(Recipients) select [RECIPIENTS] from srv.ErrorInfo where InsertDate0) begin --получаем получателей select top (1) @recipients=Recipients from @tbl; set @recipients_key=@recipients; set @result=''; --для каждого получателя while(len(@recipients)>0) begin set @ind=CHARINDEX(';', @recipients); if(@ind>0) begin set @recipient=substring(@recipients,1, @ind-1); set @recipients=substring(@recipients,@ind+1,len(@recipients)-@ind); end else begin set @recipient=@recipients; set @recipients=''; end; --получаем адреса получателя exec [srv].[GetRecipients] @Recipient_Code=@recipient, @Recipients=@recp out; if(len(@recp)=0) begin exec [srv].[GetRecipients] @Recipient_Name=@recipient, @Recipients=@recp out; if(len(@recp)=0) set @recp=@recipient; end --разделенные символом ';' set @result=@result+@recp+';'; end set @result=substring(@result,1,len(@result)-1); set @recipients=@result; --получить HTML-отчет с указанными получателями и датой insert into @rec_body(Body) exec srv.GetHTMLTable @recipients=@recipients_key, @dt=@dt; --получить HTML-отчет select top (1) @body=Body from @rec_body; --непосредственно сама отправка EXEC msdb.dbo.sp_send_dbmail -- Созданный нами профиль администратора почтовых рассылок @profile_name = 'ALARM', -- Адрес получателя @recipients = @recipients, -- Текст письма @body = @body, -- Тема @subject = N'ИНФОРМАЦИЯ ПО ОШИБКАМ ВЫПОЛНЕНИЯ', @body_format='HTML'--, -- Для примера добавим к письму результаты произвольного SQL-запроса --@query = @query--'SELECT TOP 10 name FROM sys.objects'; delete from @tbl where Recipients=@recipients_key; delete from @rec_body; end --помещаем в архив отправленные сообщения INSERT INTO [srv].[ErrorInfoArchive] ([ErrorInfo_GUID] ,[ERROR_TITLE] ,[ERROR_PRED_MESSAGE] ,[ERROR_NUMBER] ,[ERROR_MESSAGE] ,[ERROR_LINE] ,[ERROR_PROCEDURE] ,[ERROR_POST_MESSAGE] ,[RECIPIENTS] ,[StartDate] ,[FinishDate] ,[Count] ,IsRealTime ) SELECT [ErrorInfo_GUID] ,[ERROR_TITLE] ,[ERROR_PRED_MESSAGE] ,[ERROR_NUMBER] ,[ERROR_MESSAGE] ,[ERROR_LINE] ,[ERROR_PROCEDURE] ,[ERROR_POST_MESSAGE] ,[RECIPIENTS] ,[StartDate] ,[FinishDate] ,[Count] ,IsRealTime FROM [srv].[ErrorInfo] where IsRealTime=@IsRealTime and InsertDate

EXECUTE [ИМЯ_БАЗЫ_ДАННЫХ].[srv].[RunErrorInfoProc] @IsRealTime=0; --0 для простых уведомлений и 1 для уведомлений реального времени

begin try exec [ИМЯ_БАЗЫ_ДАННЫХ].[srv].[KillFullOldConnect]; end try begin catch declare @str_mess nvarchar(max)=ERROR_MESSAGE(), @str_num nvarchar(max)=cast(ERROR_NUMBER() as nvarchar(max)), @str_line nvarchar(max)=cast(ERROR_LINE() as nvarchar(max)), @str_proc nvarchar(max)=ERROR_PROCEDURE(), @str_title nvarchar(max)='УДАЛЕНИЕ ЗАВИСШИХ ПРОЦЕССОВ НА СЕРВЕРЕ '+@@servername, @str_pred_mess nvarchar(max)='НА '+@@servername+' СЕРВЕРЕ ВОЗНИКЛА ОШИБКА УДАЛЕНИЯ ЗАВИСШИХ ПРОЦЕССОВ'; exec [ИМЯ_БАЗЫ_ДАННЫХ].srv.ErrorInfoIncUpd @ERROR_TITLE = @str_title, @ERROR_PRED_MESSAGE = @str_pred_mess, @ERROR_NUMBER = @str_num, @ERROR_MESSAGE = @str_mess, @ERROR_LINE = @str_line, @ERROR_PROCEDURE = @str_proc, @ERROR_POST_MESSAGE = NULL, @RECIPIENTS = 'ПОЛУЧАТЕЛЬ1;ПОЛУЧАТЕЛЬ2;'; declare @err int=@@error; raiserror(@str_mess,16,1); end catch