Configuring Database Mail Notifications in MS SQL Server

Total: 1 Average: 5

Introduction

Often, there is a need to somehow inform administrators about the problems with a server. Notifications are generally divided into 2 types:

1) real-time notifications, i.e. those that must come immediately when a problem occurs

2) delayed notifications, i.e. those that come after a fairly long time (more than 1 hour) after a problem occurs.

In my work, it was necessary to extend the functionality of the regular SQL Server Database Mail.

In this article, we will consider an example of how to generate notifications in HTML tables and then send them to administrators.

Solution

1. Configure Database Mail

2. Create a table for recipients:

Code

USE [DATABASE_NAME]
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,  // a recipient primary email
    [Recipient_Code] [nvarchar](10) NOT NULL,   // recipient's code
    [IsDeleted] [bit] NOT NULL,                 // a deletion indicator(whether a recipient is used or not)
    [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. Create a table for addresses of the recipients:

Code

USE [DATABASE_NAME]
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,   // recipient
    [Address] [nvarchar](255) NOT NULL,             // email
    [IsDeleted] [bit] NOT NULL,                     // deletion indicator (whether email is used or not)
    [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. Create a table for a message queue:

Code

USE [DATABASE_NAME]
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,             // title
    [ERROR_PRED_MESSAGE] [nvarchar](max) NULL,      // preliminary information
    [ERROR_NUMBER] [nvarchar](max) NULL,            // message (error) code 
    [ERROR_MESSAGE] [nvarchar](max) NULL,           // message
    [ERROR_LINE] [nvarchar](max) NULL,              // line number
    [ERROR_PROCEDURE] [nvarchar](max) NULL,         // stored procedure
    [ERROR_POST_MESSAGE] [nvarchar](max) NULL,      // explanatory information
    [RECIPIENTS] [nvarchar](max) NULL,              // recipints separated by ';'
    [InsertDate] [datetime] NOT NULL,
    [StartDate] [datetime] NOT NULL,                // start data and time
    [FinishDate] [datetime] NOT NULL,               // finish date and time
    [Count] [int] NOT NULL,                         // number of times
    [UpdateDate] [datetime] NOT NULL,
    [IsRealTime] [bit] NOT NULL,                    // real time indicator
    [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. Create an archive table for messages sent from the message queue:

Code

USE [DATABASE_NAME]
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

This information is needed for the history. Besides, this table needs to be cleared from very old data (for example, older than a month).

6. Create a stored procedure that registers a new message in the message queue:

Code

USE [DATABASE_NAME]
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
    /*
       Error logging in the error table to be sent by email
       if the table already has an entry with the same title, content, and sender,
       the end date of the error, the date of the record update, as well as the number 
       of errors will change
    */
    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. Create a stored procedure that returns a string from the addresses by the code or the primary email address of a recipient:

Code

USE [DATABASE_NAME]
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
/*
   The procedure for creating email notifications
*/
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. Create the necessary functions for working with dates and time:

Code

USE [DATABASE_NAME]
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE FUNCTION [rep].[GetDateFormat] 
(
    @dt datetime, // input date
    @format int=0 // preset format
)
RETURNS nvarchar(255)
AS
/*
    Returns date as a string according to the specified format and the input date
    Inserts zeros where necessary:
    format  input date      result
    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 [DATABASE_NAME]
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE FUNCTION [rep].[GetTimeFormat] 
(
    @dt datetime, // input time
    @format int=0 // preset format
)
RETURNS nvarchar(255)
AS
/*
    Returns time as a string according to the specified format and the input time
    Inserts zeros where necessary:
    format  input time      result
    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. Create a stored procedure that creates an HTML report on messages in the form of a table:

Code

USE [DATABASE_NAME]
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [srv].[GetHTMLTable]
    @recipients nvarchar(max)
    ,@dt        datetime // by which date to read
AS
BEGIN
    /*
        generates HTML-code for table
    */
    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+'DATE';
    set @body=@body+'</TD>';

    set @body=@body+'<TD>';
    set @body=@body+'ERROR';
    set @body=@body+'</TD>';

    set @body=@body+'<TD>';
    set @body=@body+'DESCRIPTION';
    set @body=@body+'</TD>';

    set @body=@body+'<TD>';
    set @body=@body+'ERROR CODE';
    set @body=@body+'</TD>';

    set @body=@body+'<TD>';
    set @body=@body+'MESSAGE';
    set @body=@body+'</TD>';

    set @body=@body+'<TD>';
    set @body=@body+'START';
    set @body=@body+'</TD>';

    set @body=@body+'<TD>';
    set @body=@body+'FINISH';
    set @body=@body+'</TD>';

    set @body=@body+'<TD>';
    set @body=@body+'NUMBER';
    set @body=@body+'</TD>';

    set @body=@body+'<TD>';
    set @body=@body+'LINE NUMBER';
    set @body=@body+'</TD>';

    set @body=@body+'<TD>';
    set @body=@body+'PROCEDURE';
    set @body=@body+'</TD>';

    set @body=@body+'<TD>';
    set @body=@body+'NOTE';
    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. Create a stored procedure that sends messages:

Code

USE [DATABAE_NAME]
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [srv].[RunErrorInfoProc]
    @IsRealTime bit =0  // send mode (1-real time)
AS
BEGIN
    /*
        send error notifications with the specified mode
    */
    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);

     // receive all the necessary messages
     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);

     // go through every message
     while((select top 1 1 from @tbl)>0)
     begin
        //receive recipients
        select top (1)
        @recipients=Recipients
        from @tbl;

        set @recipients_key=@recipients;
        set @result='';

        // for each recipient
        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;

            // receive recipient emails
            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

            // separated by symbol ';'
            set @result=@result+@recp+';';
        end

        set @result=substring(@result,1,len(@result)-1);
        set @recipients=@result;

        // receive HTML-report with the specified recipients and date
        insert into @rec_body(Body)
        exec srv.GetHTMLTable @recipients=@recipients_key, @dt=@dt;

        // receive HTML-report
        select top (1)
        @body=Body
        from @rec_body;

         // the actual sending
         EXEC msdb.dbo.sp_send_dbmail
        // emailing admin profile we created
            @profile_name = 'ALARM',
        //recipient email
            @recipients = @recipients,
        // text of a message
            @body = @body,
        // Subject
            @subject = N'INFORMATION ON EXECUTION ERRORS',
            @body_format='HTML'--,
        // For example, let's add the results of a random SQL query to the message
            --@query = @query--'SELECT TOP 10 name FROM sys.objects';

        delete from @tbl
        where Recipients=@recipients_key;

        delete from @rec_body;
     end

    // archive the sent messages
    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 the sent messages from the message queue
    delete from [srv].[ErrorInfo]
    where IsRealTime=@IsRealTime
    and InsertDate<=@dt;
END

GO

This stored procedure takes each message from the message queue and wraps it in an HTML report in the form of a table. For recipients, based on their code or primary email address, it creates a string consisting of email addresses, to which a message is sent. In this way, all the selected messages are processed. Here, the msdb.dbo.sp_send_dbmail stored procedure is used.

11. Create two tasks in Agent (the first one is for real-time notifications (schedule 1 time per minute), the second one is for simple notifications (schedule 1 time per hour)). Add the following to the code of the task:

EXECUTE [DATABASE_NAME].[srv].[RunErrorInfoProc] 
   @IsRealTime=0; // 0 - for simple messages and 1 - for real-time messages

Here is an example of error reporting:

Code

begin try
                exec [DATABASE_NAME].[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)='DELETING NON-RESPONDING PROCESSES ON THE SERVER '+@@servername,
            @str_pred_mess nvarchar(max)='THE DELETION OF NON-RESPONDING PROCESSES ERROR OCCURRED ON THE '+@@servername+' SERVER';

    exec [DATABASE_NAME].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            = 'RECIPIENT1;RECIPIENT2';

     declare @err int=@@error;
     raiserror(@str_mess,16,1);
end catch

Here, the svr.KillFullOldConnect stored procedure is used.

Result

This article includes an example of extending the functionality of a regular Database Mail and an example of how to generate notifications in HTML tables and then email them to administrators. This approach allows notifying administrators about different problems in real time or after a certain time, thereby minimizing the occurrence of a critical problem and failure of DBMS and server, which in turn protects production from workflow delay.

References:

  1. Sp_send_dbmail
  2. Database Mail
  3. Srv.KillFullOldConnect
Evgeniy Gribkov