Setting up email notifications in MS SQL Server
Foreword
Often there is a need in some way to inform administrators about problems encountered on the server. And most of the notifications are divided into 2 types:
1) real time, i.e. those that should come immediately when a problem occurs
2) deferred time, i.e. those that arrive after a sufficiently long time (more than 1 hour) after the occurrence of the problem.
In my work it was necessary to expand the functionality of the usual Database Mail.
This article will look at an example of how to generate notifications in HTML tables and then mail them to administrators.
Decision
1. Set up Database Mail
2. Create a table for recipients:
Code
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. Create a table for recipient addresses:
Code
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. Create a table for the message queue:
Code
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. Create an archive table for sent messages from the message queue:
Code
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 This information is needed for history. But also this table should be cleaned 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 [__] 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. Create a stored procedure that returns a string from the addresses by the code or the main email address of the recipient:
Code
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. Create the necessary functions for working with date and time:
Code
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. Create a stored procedure that creates an HTML report in the form of a table by message:
Code
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. Create a stored procedure that sends messages:
Code
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 This stored procedure takes each message from the message queue and wraps it into a HTML report in a table. For recipients, by their code or primary email address creates a string consisting of email addresses. The message is sent to these addresses. And so all selected messages are processed. This uses the msdb.dbo.sp_send_dbmail stored procedure
11. Let's create two tasks in the Agent (the first is for real-time notifications (the schedule is 1 time per minute), the second is for simple notifications (the schedule is 1 time per hour)). Add the following to the task code:
EXECUTE [__].[srv].[RunErrorInfoProc] @IsRealTime=0; --0 1 Here is an example of registering an error:
Code
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 This uses the srv.KillFullOldConnect stored procedure
Result
This article has reviewed an example of expanding the functionality of the usual Database Mail, as well as an example of how to create notifications in HTML tables and then send them by mail to administrators. This approach allows administrators to be notified of various problems in real time or after some specific time. Thus, this approach allows minimizing in the future the onset of a critical problem and stopping the operation of the DBMS and the server, which in turn protects production from stopping the workflow.
Sources:
')
Source: https://habr.com/ru/post/314622/
All Articles