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
