設定MSSQL排程工作通知功能

填入設定檔名稱(自訂)

填入相關資訊，這邊是使用GMAIL來發信，所以SSL要打勾並填上PORT號587

最後下方的驗證記得填入

再來回到上一步，此部份可有可無(主要是預設值寄出檔案為1MB，可以依需求修改)

我將預設值改為10MB

至此可以試發郵件，如無誤會收到信件

再來點擊 操作員 並新增

填入自訂值

點選SQL Server Agent

選用先前設定的帳號

找一隻自訂的JOB測試

 在"通知"項目，可以見到要在何時通知，自訂即可

另外也附上直接在SQL執行完時，通知執行結果TSQL

EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'sam',
@recipients = 'sam小老鼠myla.gotdns.com',
@subject = 'SQLJOB24HRS-',
@query = 'Select 
        [Job Name] = CONVERT(NVARCHAR(50), j.name)
   , ROW_NUMBER() Over (Partition By j.name Order By h.run_time) as [SN]
      , [LastRunDate] = h.run_date
      , CONVERT(VARCHAR,DATEADD(S,(run_time/10000)*60*60 /* hours */
          +((run_time - (run_time/10000) * 10000)/100) * 60 /* mins */
          + (run_time - (run_time/100) * 100)  /* secs */
      , CONVERT(DATETIME,RTRIM(run_date),113)),120) AS [Time Run]
      , CONVERT(VARCHAR(10),CONVERT(DATETIME,RTRIM(19000101))+(h.run_duration * 9 + h.run_duration % 10000 * 6 + h.run_duration % 100 * 10) / 216e4,108) AS [Run_Duration]
      , CASE h.run_status
  when 0 then ''Failed''
  when 1 then ''Pass''
  when 2 then ''Retry''
  when 3 then ''Cancel''
  when 4 then ''Running''
 end as [JobStatus]
    From
        msdb.dbo.sysjobhistory h
      Left join msdb.dbo.sysjobs j On j.job_id = h.job_id
    Where h.step_id=0
    AND DATEADD(S,
  (run_time/10000)*60*60 /* hours */
  +((run_time - (run_time/10000) * 10000)/100) * 60 /* mins */
  + (run_time - (run_time/100) * 100)  /* secs */
  , CONVERT(DATETIME,RTRIM(run_date),113)) >= DATEADD(d,-1,GetDate()) ',
@attach_query_result_as_file = 1,
@query_attachment_filename = 'SQLJOB24HRS.TXT'

#######160827######

check notify

select name, notify_level_email, notify_email_operator_id
from msdb..sysjobs
order by name, notify_level_email

update notify setting

UPDATE S
SET S.[notify_level_email] = 2,
S.[notify_email_operator_id] = 1
FROM MSDB.dbo.sysjobs S
WHERE S.[Notify_Level_Email] = 0
AND S.[Enabled] = 1;
GO

• Prev
• Next