Предисловие
Часто возникает потребность каким-либо способом сообщать администраторам о проблемах, возникших на сервере. Причем уведомления в большинстве своем делятся на 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='<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
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-таблицы с последующей отправкой по почте администраторам. Данный подход позволяет уведомлять администраторов о разных проблемах в реальном времени или через какое-то определенное время. Таким образом, данный подход позволяет минимизировать в будущем наступления критической проблемы и остановки работы СУБД и сервера, что в свою очередь защищает производство от остановки рабочих процессов.
Источники:
Комментарии (17)
ionofff
11.11.2016 10:43+1Если почтовый адрес пользователя удаляется на почтовом сервере, то отчет может не до ставится получателям вообще, если их несколько при отправке почты из SQL server. Чтобы решить данную проблему создается группа рассылки на почтовом сервере и уже проблема доставки сообщений ложится на email сервер
jobgemws
11.11.2016 10:45И как группу настроить на MS SQL Server?
Мы ведь говорим о MS SQL Server, а почтовый сервер я в глаза не видел-этим занимаются системные администраторы.ionofff
11.11.2016 10:50+1тут надо работать в команде, я описал случай из своего опыта, когда ваша рассылка работать не будет.
jobgemws
11.11.2016 10:55Все-таки давайте конкретнее-приведу алгоритм:
1) на почтовом сервере создается группа рассылки
2) все то, что я описал в статье
Какие будут замечания к п.2 и опишите или дайте ссылку на п.1ionofff
11.11.2016 11:08+11) да, туда сразу включаются нужные почтовые адреса
2) у вас будет только один адрес для отправки сообщений, и из логики вашей работы в п. 1-2 вашего алгоритма отпадает надобность, или сводится к гораздо меньшему числу записей, надо просто более глубоко посмотреть как и что вы используете вашим методом.jobgemws
11.11.2016 11:12За информацию спасибо. Буду иметь это в виду. Просто системные администраторы со стороны заказчиков и молчат, а я о таком не знал. Ваш предложение хорошее. Но мой метод позволяет универсально распространить на все точки и не мучать ни системных админов, компетенция которых некоторых из них оставляет желать лучшего, ни себе-долго объясняя некоторым из них как это делается. И в случае переноса базы обслуживания к другому заказчику тоже меньше работы с системным администратором. Т. е. группы прописать 5 мин. Переносишь к другому заказчику-еще 5 мин., а так ничего не нужно. Но метод, предложенный Вами, все-равно нужно учесть.
ionofff
11.11.2016 11:26+1Ваш метод хороший, сам такой использовал пока не столкнулся с проблемой удаленный почтовых ящиков, когда часть пользователей получают почту а часть нет. И как мне кажется с точки зрения заказчиков проще добавить в почтовую группу, чем добавлять в таблицу. Тем более если заказчик крупный обычно добавление в группу даже не админ делают, а выделенный сотрудник какого то отдела.
jobgemws
11.11.2016 11:30Дело в том, что у нас используются почты наши, т. е. мы обслуживаем базы данных. НО! Вот если им нужно будет что-то слать, то благодаря Вашему предложению, я их пошлю настраивать группы для самих себя, т. к. не хочу отслеживать у кого какие почты меняются. А у нас кто что поменял-сразу заранее сообщает.
ideatum
11.11.2016 14:55+1Небольшое замечание по поводу форматирования даты/времени. Для этих целей, вероятно, лучше подойдет системная функция FORMAT
FORMAT (Transact-SQL)jobgemws
11.11.2016 14:56Спасибо за совет. Согласен, что приведенный мною подход, был не самым лучшим, буду теперь Вашим предложением пользоваться для формата дат и времени.
ideatum
11.11.2016 17:41+1Еще небольшое замечание, не очень хорошо что агент «дергает» процедуру достаточно часто раз в минуту, ведь ошибок может и не возникать весьма длительное время. Я бы воспользовался возможностями SQL Server Service Broker и в этом случае доставка сообщений будет действительно realtime и без участия SQL Server Agent.
Идея в следующем:
1. Создаем свой тип сообщения (CREATE MESSAGE TYPE)
2. Создаем контракт (CREATE CONTRACT)
3. Создаем процедуру для отправки сообщений
4. Создаем очередь (CREATE QUEUE), которая использует процедуру из п.3
5. Создаем службу (CREATE SERVICE), которая использует очередь из п.4 и контракт из п.3
Пример-- активируем service broker IF NOT EXISTS(SELECT * FROM master.sys.databases WHERE name = DB_NAME() AND is_broker_enabled = 1) BEGIN DECLARE @sql nvarchar(max); SET @sql = N'USE [master]; ALTER DATABASE [' + DB_NAME() + N'] SET NEW_BROKER; USE [' + DB_NAME() + '];'; EXECUTE(@sql) SET @sql = N'USE [master]; ALTER DATABASE [' + DB_NAME() + N'] SET ENABLE_BROKER; USE [' + DB_NAME() + '];'; EXECUTE(@sql) END; GO CREATE MESSAGE TYPE [http://tempuri.org/Notifications/SendErrorInfoEvent] VALIDATION = WELL_FORMED_XML; GO CREATE CONTRACT [http://tempuri.org/Notifications/PostEventNotification] ( [http://tempuri.org/Notifications/SendErrorInfoEvent] SENT BY ANY ); GO CREATE PROCEDURE [ProcessNotificationEvents] AS BEGIN SET NOCOUNT ON; DECLARE @message_body XML, @message_type_name NVARCHAR(256), @dialog UNIQUEIDENTIFIER ; WHILE (1 = 1) BEGIN WAITFOR( RECEIVE TOP(1) @message_type_name = message_type_name, @message_body = message_body, @dialog = conversation_handle FROM [EventNotificationQueue] ), TIMEOUT 2000; IF @@ROWCOUNT = 0 BEGIN BREAK; END IF (@message_type_name = 'http://schemas.microsoft.com/SQL/ServiceBroker/EndDialog') BEGIN END CONVERSATION @dialog ; END ELSE BEGIN /* здесь реальный код обработки сообщения данные в @message_body */ RETURN 0 END END RETURN 0; END GO CREATE QUEUE [EventNotificationQueue] WITH STATUS = ON, ACTIVATION ( STATUS = ON, PROCEDURE_NAME = ProcessNotificationEvents, MAX_QUEUE_READERS = 1, EXECUTE AS OWNER ); GO CREATE SERVICE [http://tempuri.org/Notifications/EventNotificationService] ON QUEUE [EventNotificationQueue] ( [http://tempuri.org/Notifications/PostEventNotification] ); GO -- -- использование, можно оформить в виде процедуры -- DECLARE @dialog UNIQUEIDENTIFIER; BEGIN DIALOG CONVERSATION @dialog FROM SERVICE [http://tempuri.org/Notifications/EventNotificationService] TO SERVICE 'http://tempuri.org/Notifications/EventNotificationService' ON CONTRACT [http://tempuri.org/Notifications/PostEventNotification] WITH ENCRYPTION = OFF; DECLARE @EventData XML; -- формируем данные для отправки по электронной почте SEND ON CONVERSATION @dialog MESSAGE TYPE [http://tempuri.org/Notifications/SendErrorInfoEvent] (@EventData); END CONVERSATION @dialog;jobgemws
11.11.2016 19:49А Вы пользовались Servie Broker?
Просто как-то пробовали и поняли, что он не для отправки писем, а для общения между серверами.
Поэтому и не стали его использовать.ideatum
11.11.2016 21:00+1Конечно не только для отправки писем. Service Broker очень удобная штука, чтобы выполнить какие-то действия асинхронно не снижая производительность пользовательской работы. В одном из проектов (стандартный клиент-сервер) использовал для отправки уведомлений пользователям системы об изменении состояния документов. Отправил сообщение и забыл, дальше работает Service Broker. Простой пример, пользователь отправляет документ на утверждение, при этом руководитель (подразделения, проекта и т.п.) в зависимости от настроек и типа документа должен получить уведомление (в системе, по почте или SMS) о том, что в системе появился документ, который необходимо утвердить. Вот логика формированием и отправкой уведомлений и была реализована с использование Service Broker. Иначе возникали тормоза при выполнении пользовательских действий.
ideatum
11.11.2016 22:57+1Ну а это уже в качестве развлечения :o). Формирование HTML таблицы при помощи SQL-запроса.
select 5 as [BORDER], (select TH from ( values ('Column #1'), ('Column #2'), ('Column #3') ) as T(TH) for xml raw(''), elements, type) as TR, (select C1 as TD, C2 as TD, C3 as TD from ( values ('Item 1-1', 'Item 1-2', 'Item 1-3'), ('Item 2-1', 'Item 2-2', 'Item 2-3') ) as T(C1, C2, C3) for xml raw('TR'), elements, type) for xml raw('TABLE')
ionofff
Не совсем понятно зачем хранить получателей в таблицах, когда есть группы рассылки для почтовых систем
jobgemws
Весьма интересно, приведите пример