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:
[expand title =”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
[/expand]
3. Create a table for addresses of the recipients:
[expand title =”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
[/expand]
4. Create a table for a message queue:
[expand title =”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
[/expand]
5. Create an archive table for messages sent from the message queue:
[expand title =”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
[/expand]
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:
[expand title =”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 ([email protected]_TITLE or @ERROR_TITLE is null) and RECIPI[email protected] and ([email protected]_MESSAGE or @ERROR_MESSAGE is null) and ([email protected]_PRED_MESSAGE or @ERROR_PRED_MESSAGE is null) and ([email protected]_POST_MESSAGE or @ERROR_POST_MESSAGE is null) and ([email protected] 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 [email protected]_GUID; end END GO
[/expand]
7. Create a stored procedure that returns a string from the addresses by the code or the primary email address of a recipient:
[expand title =”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 @[email protected]+d.[Address]+';' from srv.Recipient as r inner join srv.[Address] as d on r.Recipient_GUID=d.Recipient_GUID where ([email protected]_Name or @Recipient_Name IS NULL) and ([email protected]_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
[/expand]
8. Create the necessary functions for working with dates and time:
[expand title =”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 @[email protected]+IIF(@month<10,'0'+cast(@month as nvarchar(1)), cast(@month as nvarchar(2)))+'.'; set @[email protected]+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 @[email protected]+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 @[email protected]+IIF(@min<10,'0'+cast(@min as nvarchar(1)), cast(@min as nvarchar(2)))+':'; set @[email protected]+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 @[email protected]+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
[/expand]
9. Create a stored procedure that creates an HTML report on messages in the form of a table:
[expand title =”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][email protected]) or (@recipients IS NULL) and InsertDate<[email protected] // order by InsertDate asc; set @body='<TABLE BORDER=5>'; set @[email protected]+'<TR>'; set @[email protected]+'<TD>'; set @[email protected]+'№ п/п'; set @[email protected]+'</TD>'; set @[email protected]+'<TD>'; set @[email protected]+'DATE'; set @[email protected]+'</TD>'; set @[email protected]+'<TD>'; set @[email protected]+'ERROR'; set @[email protected]+'</TD>'; set @[email protected]+'<TD>'; set @[email protected]+'DESCRIPTION'; set @[email protected]+'</TD>'; set @[email protected]+'<TD>'; set @[email protected]+'ERROR CODE'; set @[email protected]+'</TD>'; set @[email protected]+'<TD>'; set @[email protected]+'MESSAGE'; set @[email protected]+'</TD>'; set @[email protected]+'<TD>'; set @[email protected]+'START'; set @[email protected]+'</TD>'; set @[email protected]+'<TD>'; set @[email protected]+'FINISH'; set @[email protected]+'</TD>'; set @[email protected]+'<TD>'; set @[email protected]+'NUMBER'; set @[email protected]+'</TD>'; set @[email protected]+'<TD>'; set @[email protected]+'LINE NUMBER'; set @[email protected]+'</TD>'; set @[email protected]+'<TD>'; set @[email protected]+'PROCEDURE'; set @[email protected]+'</TD>'; set @[email protected]+'<TD>'; set @[email protected]+'NOTE'; set @[email protected]+'</TD>'; set @[email protected]+'</TR>'; while((select top 1 1 from @tbl)>0) begin set @[email protected]+'<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 @[email protected]+'<TD>'; set @[email protected]+cast(@ID as nvarchar(max)); set @[email protected]+'</TD>'; set @[email protected]+'<TD>'; set @[email protected]+rep.GetDateFormat(@InsertDate, default)+' '+rep.GetTimeFormat(@InsertDate, default); // cast(@InsertDate as nvarchar(max)); set @[email protected]+'</TD>'; set @[email protected]+'<TD>'; set @[email protected]+isnull(@ERROR_TITLE,''); set @[email protected]+'</TD>'; set @[email protected]+'<TD>'; set @[email protected]+isnull(@ERROR_PRED_MESSAGE,''); set @[email protected]+'</TD>'; set @[email protected]+'<TD>'; set @[email protected]+isnull(@ERROR_NUMBER,''); set @[email protected]+'</TD>'; set @[email protected]+'<TD>'; set @[email protected]+isnull(@ERROR_MESSAGE,''); set @[email protected]+'</TD>'; set @[email protected]+'<TD>'; set @[email protected]+rep.GetDateFormat(@StartDate, default)+' '+rep.GetTimeFormat(@StartDate, default); //cast(@StartDate as nvarchar(max)); set @[email protected]+'</TD>'; set @[email protected]+'<TD>'; set @[email protected]+rep.GetDateFormat(@FinishDate, default)+' '+rep.GetTimeFormat(@FinishDate, default); //cast(@FinishDate as nvarchar(max)); set @[email protected]+'</TD>'; set @[email protected]+'<TD>'; set @[email protected]+cast(@Count as nvarchar(max)); set @[email protected]+'</TD>'; set @[email protected]+'<TD>'; set @[email protected]+isnull(@ERROR_LINE,''); set @[email protected]+'</TD>'; set @[email protected]+'<TD>'; set @[email protected]+isnull(@ERROR_PROCEDURE,''); set @[email protected]+'</TD>'; set @[email protected]+'<TD>'; set @[email protected]+isnull(@ERROR_POST_MESSAGE,''); set @[email protected]+'</TD>'; delete from @tbl where [email protected]; set @[email protected]+'</TR>'; end set @[email protected]+'</TABLE>'; select @body; END GO
[/expand]
10. Create a stored procedure that sends messages:
[expand title =”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<[email protected] and [email protected] 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 @[email protected]; 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)[email protected]); end else begin set @[email protected]; set @recipients=''; end; // receive recipient emails exec [srv].[GetRecipients] @[email protected], @[email protected] out; if(len(@recp)=0) begin exec [srv].[GetRecipients] @[email protected], @[email protected] out; if(len(@recp)=0) set @[email protected]; end // separated by symbol ';' set @[email protected][email protected]+';'; end set @result=substring(@result,1,len(@result)-1); set @[email protected]; // receive HTML-report with the specified recipients and date insert into @rec_body(Body) exec srv.GetHTMLTable @[email protected]_key, @[email protected]; // 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 [email protected] = @query--'SELECT TOP 10 name FROM sys.objects'; delete from @tbl where [email protected]_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 [email protected] and InsertDate<[email protected] --order by InsertDate; // delete the sent messages from the message queue delete from [srv].[ErrorInfo] where [email protected] and InsertDate<[email protected]; END GO
[/expand]
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:
[expand title=”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 '[email protected]@servername, @str_pred_mess nvarchar(max)='THE DELETION OF NON-RESPONDING PROCESSES ERROR OCCURRED ON THE '[email protected]@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 [email protected]@error; raiserror(@str_mess,16,1); end catch
[/expand]
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:
Tags: database administration, sql server Last modified: September 23, 2021