Настройка почтовых уведомлений в 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]
GO3) Создадим таблицу для адресов получателей:
Код
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]
GO4) Создадим таблицу для очереди сообщений:
Код
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]
GO5) Создадим архивную таблицу для отправленных сообщений из очереди сообщений:
Код
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
GO7) Создадим хранимую процедуру, которая возвращает строку из адресов по коду или основному почтовому адресу получателя:
Код
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
GO8) Создадим необходимые функции для работы с датой и временем:
Код
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
GO9) Создадим хранимую процедуру, которая создает 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='';
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 ID=@ID;
set @body=@body+' ';
end
set @body=@body+'
';
select @body;
END
GO10) Создадим хранимую процедуру, которая отправляет сообщения:
Код
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
