Предисловие
Часто возникает потребность каким-либо способом сообщать администраторам о проблемах, возникших на сервере. Причем уведомления в большинстве своем делятся на 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<=@dt --order by InsertDate asc; set @body='<TABLE BORDER=5>'; set @body=@body+'<TR>'; set @body=@body+'<TD>'; set @body=@body+'№ п/п'; set @body=@body+'</TD>'; set @body=@body+'<TD>'; set @body=@body+'ДАТА'; set @body=@body+'</TD>'; set @body=@body+'<TD>'; set @body=@body+'ОШИБКА'; set @body=@body+'</TD>'; set @body=@body+'<TD>'; set @body=@body+'ОПИСАНИЕ'; set @body=@body+'</TD>'; set @body=@body+'<TD>'; set @body=@body+'КОД ОШИБКИ'; set @body=@body+'</TD>'; set @body=@body+'<TD>'; set @body=@body+'СООБЩЕНИЕ'; set @body=@body+'</TD>'; set @body=@body+'<TD>'; set @body=@body+'НАЧАЛО'; set @body=@body+'</TD>'; set @body=@body+'<TD>'; set @body=@body+'ОКОНЧАНИЕ'; set @body=@body+'</TD>'; set @body=@body+'<TD>'; set @body=@body+'КОЛИЧЕСТВО'; set @body=@body+'</TD>'; set @body=@body+'<TD>'; set @body=@body+'НОМЕР СТРОКИ'; set @body=@body+'</TD>'; set @body=@body+'<TD>'; set @body=@body+'ПРОЦЕДУРА'; set @body=@body+'</TD>'; set @body=@body+'<TD>'; set @body=@body+'ПРИМЕЧАНИЕ'; set @body=@body+'</TD>'; set @body=@body+'</TR>'; while((select top 1 1 from @tbl)>0) begin set @body=@body+'<TR>'; 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+'<TD>'; set @body=@body+cast(@ID as nvarchar(max)); set @body=@body+'</TD>'; set @body=@body+'<TD>'; set @body=@body+rep.GetDateFormat(@InsertDate, default)+' '+rep.GetTimeFormat(@InsertDate, default);--cast(@InsertDate as nvarchar(max)); set @body=@body+'</TD>'; set @body=@body+'<TD>'; set @body=@body+isnull(@ERROR_TITLE,''); set @body=@body+'</TD>'; set @body=@body+'<TD>'; set @body=@body+isnull(@ERROR_PRED_MESSAGE,''); set @body=@body+'</TD>'; set @body=@body+'<TD>'; set @body=@body+isnull(@ERROR_NUMBER,''); set @body=@body+'</TD>'; set @body=@body+'<TD>'; set @body=@body+isnull(@ERROR_MESSAGE,''); set @body=@body+'</TD>'; set @body=@body+'<TD>'; set @body=@body+rep.GetDateFormat(@StartDate, default)+' '+rep.GetTimeFormat(@StartDate, default);--cast(@StartDate as nvarchar(max)); set @body=@body+'</TD>'; set @body=@body+'<TD>'; set @body=@body+rep.GetDateFormat(@FinishDate, default)+' '+rep.GetTimeFormat(@FinishDate, default);--cast(@FinishDate as nvarchar(max)); set @body=@body+'</TD>'; set @body=@body+'<TD>'; set @body=@body+cast(@Count as nvarchar(max)); set @body=@body+'</TD>'; set @body=@body+'<TD>'; set @body=@body+isnull(@ERROR_LINE,''); set @body=@body+'</TD>'; set @body=@body+'<TD>'; set @body=@body+isnull(@ERROR_PROCEDURE,''); set @body=@body+'</TD>'; set @body=@body+'<TD>'; set @body=@body+isnull(@ERROR_POST_MESSAGE,''); set @body=@body+'</TD>'; delete from @tbl where ID=@ID; set @body=@body+'</TR>'; end set @body=@body+'</TABLE>'; 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 InsertDate<=@dt and IsRealTime=@IsRealTime group by [RECIPIENTS]; declare @rec_body table(Body nvarchar(max)); declare @body nvarchar(max); declare @query nvarchar(max); --пробегаем по каждому сообщению while((select top 1 1 from @tbl)>0) 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<=@dt --order by InsertDate; --удаляем отправленные сообщения из очереди сообщений delete from [srv].[ErrorInfo] where IsRealTime=@IsRealTime and InsertDate<=@dt; END GO
Данная хранимая процедура берет каждое сообщение из очереди сообщений и обертывает его в HTML-отчет в виде таблицы. Для получателей по их коду или основному почтовому адресу создает строку, состоящую из почтовых адресов. Именно на эти адреса и отправляется сообщение. И так обрабатываются все выбранные сообщения. Здесь используется хранимая процедура msdb.dbo.sp_send_dbmail
11) Создадим два задания в Агенте (первое-для уведомлений реального времени (расписание-1 раз в минуту), второе-для простых уведомлений (расписание-1 раз в час)). В код задания нужно добавить следующее:
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
Здесь используется хранимая процедура srv.KillFullOldConnect
Результат
В данной статье был рассмотрен пример расширения функционала обычного Database Mail, а также разобран пример как формировать уведомления в HTML-таблицы с последующей отправкой по почте администраторам. Данный подход позволяет уведомлять администраторов о разных проблемах в реальном времени или через какое-то определенное время. Таким образом, данный подход позволяет минимизировать в будущем наступления критической проблемы и остановки работы СУБД и сервера, что в свою очередь защищает производство от остановки рабочих процессов.
Источники:
» sp_send_dbmail
» Database Mail
» srv.KillFullOldConnect
ссылка на оригинал статьи https://habrahabr.ru/post/314622/
Добавить комментарий