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: