Sql отправка писем при ошибок создания резервной копии

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

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

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

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

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

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

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

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

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

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