如何設定當job執行失敗時sql server自動寄發mail給管理者2

問題：之前的文章有提到SQL JOB失敗時寄發的EMAIL訊息太過簡單而無法讓管理者知道SQL JOB失敗的詳細原因，相信也有許多人也有跟我一樣的問題。

解決：為了解決使用SQL JOB搭配OPERATOR所寄發的EMAIL錯誤訊息太過簡單這個問題，我不使用OPERATOR，而是在SQL JOB新增一個STEP，而這個STEP會取得SQL JOB執行失敗的詳細資訊，所以當SQL JOB執行錯誤時，會跳到這個STEP，如此一來管理者就能在EMAIL上面取得SQL JOB執行失敗的原因了。

--JOB失敗的詳細內容

--1.使用msdb的系統資料表取得執行JOB的詳細結果

SELECT TOP 1

   J.name,

   J.[description],

   S.step_id,

   S.step_name,

   H.run_date,

   H.run_time, 

   H.[message]

FROM msdb..sysjobhistory H

INNER JOIN msdb..sysjobsteps S

ON H.step_id = S.step_id AND H.job_id = S.job_id

INNER JOIN msdb..sysjobs J

ON J.job_id = H.job_id

WHERE  h.run_status = 0 AND [name]='InsertError'

ORDER BY h.instance_id DESC

--2.使用FOR XML PATH把內容轉成TR與TD的XML格式

SELECT TOP 1

   td =J.name,'',

   td =J.[description],'',

   td =S.step_name,'',

   td =H.run_date,'',

   td =H.run_time,'',

   td =H.[message]

FROM msdb..sysjobhistory H

INNER JOIN msdb..sysjobsteps S

ON H.step_id = S.step_id AND H.job_id = S.job_id

INNER JOIN msdb..sysjobs J

ON J.job_id = H.job_id

WHERE  h.run_status = 0 AND [name]='InsertError'

ORDER BY h.instance_id DESC

FOR XML PATH('tr')

--2.把執行JOB的結果轉成HTML格式

DECLARE @tableHTML  NVARCHAR(MAX) ;

SET @tableHTML =

    CAST ( ( SELECT TOP 1

               td =J.name,'',

               td =J.[description],'',

               td =S.step_name,'',

               td =H.run_date,'',

               td =H.run_time,'',

               td =H.[message]

            FROM msdb..sysjobhistory H

            INNER JOIN msdb..sysjobsteps S

            ON H.step_id = S.step_id AND H.job_id = S.job_id

            INNER JOIN msdb..sysjobs J

            ON J.job_id = H.job_id

            WHERE  h.run_status = 0 AND [name]='InsertError'

            ORDER BY h.instance_id DESC

            FOR XML PATH('tr'), TYPE

    ) AS NVARCHAR(MAX) ) +

    N'' ;

--將SQL JOB錯誤訊息寄給指定的管理員EMAIL帳號

EXEC msdb.dbo.sp_send_dbmail @recipients='ryotest@gmail.com',

    @subject = 'SQL Job Error Report',

    @body = @tableHTML,

    @profile_name = 'dba',

    @body_format = 'HTML' ;

--3.在JOB上設定當JOB執行失敗會自動寄發JOB執行失敗的詳細結果。

--圖.1 設定一個新的STEP:寄發SQL JOB執行錯誤的詳細訊息給管理者，其功能為當SQL JOB執行失敗時寄送錯誤訊息給特定的EMAIL帳號

--圖.2 在寄發SQL JOB執行錯誤的詳細訊息給管理者這個STEP，成功時的動作設定結束回報失敗的作業

--圖.3 新增一個StepInsertError，引發提供的數值數量與資料表定義不相符這個錯誤

--圖.4 將StepInsertError的失敗時的動作設定為寄發SQL JOB執行錯誤的詳細訊息給管理者

--圖.5 設定完成的SQL JOB的STEP清單

--圖.6 停用OPERATOR

--4.測試結果