利用：1，mssql job运行。2，链接服务器，3，mssql 邮件发送
例：
DECLARE @LastRun VARCHAR(10)
SELECT @SyncDate = SyncDate FROM dbo.SyncTable
WHERE TableName = ‘JobLogMonitor’
IF @@ROWCOUNT = 0 BEGIN RETURN END
SET @LastRun = CONVERT(VARCHAR(10),@SyncDate,112)
DECLARE @Receivers varchar(1000)
SET @receivers = ‘wukun@lvshou.com;liqihua@lvshou.com;zhengxuesong@lvshou.com’
DECLARE @table VARCHAR(max),@body VARCHAR(max),@COUNT INT
SET @COUNT = 0 SET @SyncCount = 0
–2.201
SET @table = ‘
‘
SELECT @table = @table + ‘
‘+ ‘
‘+ ‘
‘+ ‘
‘
FROM server.msdb.dbo.sysjobhistory sjh(NOLOCK)
JOIN server.msdb.dbo.sysjobs sj(NOLOCK) ON sjh.job_id = sj.job_id
JOIN server.msdb.dbo.sysjobsteps sjt(NOLOCK) ON sjh.step_id = sjt.step_id AND sjh.job_id = sjt.job_id
WHERE run_status =0
AND msdb.dbo.agent_datetime(run_date,run_time)> @LastRun
ORDER BY run_date
SET @COUNT = @@ROWCOUNT
IF @COUNT > 0
BEGIN
SET @SyncCount = @SyncCount + @COUNT
SET @table = @table + ‘
|
‘ + CONVERT(VARCHAR(10),ROW_NUMBER() OVER (ORDER BY run_date))+ ‘ job名称：’ + CASE(ISNULL(sj.NAME,”))WHEN ” THEN ‘ ’ ELSE sj.NAME END + ‘ |
|
步骤：’ + CASE(ISNULL(sjt.step_name,”))WHEN ” THEN ‘ ’ ELSE sjt.step_name END + REPLICATE(‘ ’,4) + ‘执行时间：’ + CASE(ISNULL(sjh.run_date,”))WHEN ” THEN ‘ ’ ELSE CONVERT(VARCHAR(50),sjh.run_date,120) END + ‘ |
|
错误消息：’ + CASE(ISNULL(sjh.message,”))WHEN ” THEN ‘ ’ ELSE sjh.message END + ‘ |
|
执行SQL:’ + CASE(ISNULL(sjt.command,”))WHEN ” THEN ‘ ’ ELSE sjt.command END + ‘ |
‘
SET @body = dbo.getHTML(@table)
–SELECT @body
EXEC msdb.dbo.sp_send_dbmail
@profile_name = ‘LogMonitor’,
@recipients = @receivers,
@body_format = ‘HTML’,
@body = @body,
@subject = ‘Job执行错误[201]‘;
END