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

  • Tutorial

Предисловие


Часто возникает потребность каким-либо способом сообщать администраторам о проблемах, возникших на сервере. Причем уведомления в большинстве своем делятся на 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

Поделиться публикацией

Комментарии 17

    0
    Не совсем понятно зачем хранить получателей в таблицах, когда есть группы рассылки для почтовых систем
      0
      Весьма интересно, приведите пример
      +1
      Если почтовый адрес пользователя удаляется на почтовом сервере, то отчет может не до ставится получателям вообще, если их несколько при отправке почты из SQL server. Чтобы решить данную проблему создается группа рассылки на почтовом сервере и уже проблема доставки сообщений ложится на email сервер
        0
        И как группу настроить на MS SQL Server?
        Мы ведь говорим о MS SQL Server, а почтовый сервер я в глаза не видел-этим занимаются системные администраторы.
          +1
          тут надо работать в команде, я описал случай из своего опыта, когда ваша рассылка работать не будет.
            0
            Все-таки давайте конкретнее-приведу алгоритм:
            1) на почтовом сервере создается группа рассылки
            2) все то, что я описал в статье
            Какие будут замечания к п.2 и опишите или дайте ссылку на п.1
              +1
              1) да, туда сразу включаются нужные почтовые адреса
              2) у вас будет только один адрес для отправки сообщений, и из логики вашей работы в п. 1-2 вашего алгоритма отпадает надобность, или сводится к гораздо меньшему числу записей, надо просто более глубоко посмотреть как и что вы используете вашим методом.
                0
                За информацию спасибо. Буду иметь это в виду. Просто системные администраторы со стороны заказчиков и молчат, а я о таком не знал. Ваш предложение хорошее. Но мой метод позволяет универсально распространить на все точки и не мучать ни системных админов, компетенция которых некоторых из них оставляет желать лучшего, ни себе-долго объясняя некоторым из них как это делается. И в случае переноса базы обслуживания к другому заказчику тоже меньше работы с системным администратором. Т. е. группы прописать 5 мин. Переносишь к другому заказчику-еще 5 мин., а так ничего не нужно. Но метод, предложенный Вами, все-равно нужно учесть.
                  +1
                  Ваш метод хороший, сам такой использовал пока не столкнулся с проблемой удаленный почтовых ящиков, когда часть пользователей получают почту а часть нет. И как мне кажется с точки зрения заказчиков проще добавить в почтовую группу, чем добавлять в таблицу. Тем более если заказчик крупный обычно добавление в группу даже не админ делают, а выделенный сотрудник какого то отдела.
                    0
                    Дело в том, что у нас используются почты наши, т. е. мы обслуживаем базы данных. НО! Вот если им нужно будет что-то слать, то благодаря Вашему предложению, я их пошлю настраивать группы для самих себя, т. к. не хочу отслеживать у кого какие почты меняются. А у нас кто что поменял-сразу заранее сообщает.
        +1
        Небольшое замечание по поводу форматирования даты/времени. Для этих целей, вероятно, лучше подойдет системная функция FORMAT
        FORMAT (Transact-SQL)
          0
          Спасибо за совет. Согласен, что приведенный мною подход, был не самым лучшим, буду теперь Вашим предложением пользоваться для формата дат и времени.
          +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;
          
          


          В коде могут быть неточности, но идея должны быть понятна.
            0
            А Вы пользовались Servie Broker?
            Просто как-то пробовали и поняли, что он не для отправки писем, а для общения между серверами.
            Поэтому и не стали его использовать.
              +1
              Конечно не только для отправки писем. Service Broker очень удобная штука, чтобы выполнить какие-то действия асинхронно не снижая производительность пользовательской работы. В одном из проектов (стандартный клиент-сервер) использовал для отправки уведомлений пользователям системы об изменении состояния документов. Отправил сообщение и забыл, дальше работает Service Broker. Простой пример, пользователь отправляет документ на утверждение, при этом руководитель (подразделения, проекта и т.п.) в зависимости от настроек и типа документа должен получить уведомление (в системе, по почте или SMS) о том, что в системе появился документ, который необходимо утвердить. Вот логика формированием и отправкой уведомлений и была реализована с использование Service Broker. Иначе возникали тормоза при выполнении пользовательских действий.
                0
                спасибо) попробуем
            +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')

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

            Самое читаемое