まえがき
多くの場合、サーバーで発生した問題を管理者に何らかの形で通知する必要があります。 さらに、通知の大部分は2つのタイプに分けられます。
1）リアルタイム、つまり問題が発生したときにすぐに来るべきもの
2）遅延時間、つまり問題が発生してから十分に長い時間（1時間以上）後に到着する時間。
私の仕事では、通常のデータベースメールの機能を拡張する必要がありました。
この記事では、HTMLテーブルで通知を生成し、管理者にメールで送信する方法の例を検証します。
解決策
1. データベースメールを設定する
2.受信者用のテーブルを作成します。
コード
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.受信者アドレスのテーブルを作成します。
コード
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.メッセージキューのテーブルを作成します。
コード
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.メッセージキューから送信されたメッセージのアーカイブテーブルを作成します。
コード
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
この情報は、ストーリーに必要です。 ただし、このテーブルでは、非常に古いデータ（たとえば、1か月以上古いデータ）を削除する必要もあります。
6.メッセージキューに新しいメッセージを登録するストアドプロシージャを作成します。
コード
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.受信者のコードまたはプライマリメールアドレスによってアドレスの文字列を返すストアドプロシージャを作成します。
コード
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.日付と時刻を操作するために必要な関数を作成します。
コード
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.メッセージごとに表形式のHTMLレポートを作成するストアドプロシージャを作成します。
コード
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.メッセージを送信するストアドプロシージャを作成します。
コード
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
このストアドプロシージャは、メッセージキューから各メッセージを取得し、テーブル形式でHTMLレポートにラップします。 コードまたはプライマリメーリングアドレスによる受信者の場合、メーリングアドレスで構成される行を作成します。 メッセージが送信されるのはこれらのアドレスです。 そして、選択されたすべてのメッセージが処理されます。 ここでは、 msdb.dbo.sp_send_dbmailストアドプロシージャが使用されます。
11.エージェントで2つのタスクを作成します（1つ目はリアルタイム通知用（スケジュールは1分あたり1回）、2つ目は単純な通知用（スケジュールは1時間あたり1回））。 以下をジョブコードに追加する必要があります。
EXECUTE [__].[srv].[RunErrorInfoProc] @IsRealTime=0; --0 1
エラー登録の例を次に示します。
コード
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
ここでは、 srv.KillFullOldConnectストアドプロシージャが使用されます。
結果
この記事では、通常のデータベースメールの機能を拡張する例と、HTMLテーブルで通知を生成し、管理者にメールで送信する方法の例を検討しました。 このアプローチにより、さまざまな問題をリアルタイムで、または特定の時間後に管理者に通知できます。 したがって、このアプローチにより、将来の重大な問題の発生を最小限に抑え、DBMSとサーバーを停止することができます。これにより、生産プロセスが作業プロセスを停止するのを防ぎます。
ソース：
» Sp_send_dbmail
» データベースメール
» Srv.KillFullOldConnect