Иногда нужно отправлять оповещение на почту при определённых условиях, делая это прямо на сервере MS SQL.  Для этого мы сделаем так:

1. Настраиваем profile name = ‘Email’  в Database Mail  на наш внутренний  email router (MTA), после проверим отправку через пункт Send Email.
2. Создадим MS SQL Job c нужным временем проверки или повтором, как нам нужно.
3. Запишем в Step Job
   
   DECLARE @subject AS nvarchar(255) DECLARE @body AS nvarchar(max) declare @CountNotification int = -1 SELECT @CountNotification = count(*) FROM [base].[dbo].[Notification] n with (nolock) WHERE n.FzPartImported is null AND n.CreateDateUtc >= DATEADD(day,-3, GETUTCDATE()) IF (@CountNotification >200 ) --IF (1=1) BEGIN SET @subject = N'Notification > 200' SET @body = N'<H4>Notification > 200</H4>' + N'</BR> SELECT @CountNotification = count(*) FROM [base].[dbo].[Notification] n with (nolock) WHERE n.FzPartImported is null AND n.CreateDateUtc >= DATEADD(day,-5, GETUTCDATE()) </BR>' EXEC msdb.dbo.sp_send_dbmail @profile_name = 'Email', -- @recipients = 'a@kepman.ru;other@kepman.ru', @recipients = 'a@kepman.ru', @body = @body, @subject = @subject, @body_format='HTML', @importance = 'High' ; END

   1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30

   DECLARE @subject AS nvarchar(255) DECLARE @body AS nvarchar(max)     declare @CountNotification int = -1   SELECT @CountNotification = count(*) FROM [base].[dbo].[Notification] n with (nolock) WHERE n.FzPartImported is null AND n.CreateDateUtc >= DATEADD(day,-3, GETUTCDATE())   IF (@CountNotification >200 ) --IF (1=1) BEGIN SET @subject = N'Notification > 200' SET @body = N'<H4>Notification > 200</H4>' + N'</BR> SELECT @CountNotification = count(*) FROM [base].[dbo].[Notification] n with (nolock) WHERE n.FzPartImported is null AND n.CreateDateUtc >= DATEADD(day,-5, GETUTCDATE()) </BR>'     EXEC msdb.dbo.sp_send_dbmail         @profile_name = 'Email', --    @recipients = 'a@kepman.ru;other@kepman.ru', @recipients = 'a@kepman.ru',     @body = @body,     @subject = @subject, @body_format='HTML',     @importance = 'High' ; END