\n\t\u5229\u7528\uff1a1\uff0cmssql job\u8fd0\u884c\u30022\uff0c\u94fe\u63a5\u670d\u52a1\u5668\uff0c3\uff0cmssql \u90ae\u4ef6\u53d1\u9001
\n\t\u4f8b\uff1a
\n\tDECLARE @LastRun VARCHAR(10)
\n\tSELECT @SyncDate = SyncDate FROM dbo.SyncTable
\n\tWHERE TableName = ‘JobLogMonitor’
\n\tIF @@ROWCOUNT = 0 BEGIN RETURN END<\/p>\n
\n\tSET @LastRun = CONVERT(VARCHAR(10),@SyncDate,112)<\/p>\n
\n\tDECLARE @Receivers varchar(1000)
\n\tSET @receivers = ‘wukun@lvshou.com;liqihua@lvshou.com;zhengxuesong@lvshou.com’<\/p>\n
\n\tDECLARE @table VARCHAR(max),@body VARCHAR(max),@COUNT INT
\n\tSET @COUNT = 0 SET @SyncCount = 0<\/p>\n
\n\t–2.201
\n\tSET @table = ‘<\/p>\n
‘<\/span> \n\t‘+ ‘<\/p>\n \n\t‘+ ‘<\/p>\n \n\t‘+ ‘<\/p>\n \n\t‘ \n\t‘ \n\tEXEC msdb.dbo.sp_send_dbmail \u5229\u7528\uff1a1\uff0cmssql job\u8fd0\u884c\u30022\uff0c\u94fe\u63a5\u670d\u52a1\u5668\uff0c3\uff0cmssql \u90ae\u4ef6\u53d1\u9001 \u4f8b\uff1a DECLARE @LastR […]<\/p>\n","protected":false},"author":3,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[37],"tags":[],"class_list":["post-1929","post","type-post","status-publish","format-standard","hentry","category-mysql"],"blocksy_meta":[],"_links":{"self":[{"href":"https:\/\/enjoyasp.net\/index.php\/wp-json\/wp\/v2\/posts\/1929","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/enjoyasp.net\/index.php\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/enjoyasp.net\/index.php\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/enjoyasp.net\/index.php\/wp-json\/wp\/v2\/users\/3"}],"replies":[{"embeddable":true,"href":"https:\/\/enjoyasp.net\/index.php\/wp-json\/wp\/v2\/comments?post=1929"}],"version-history":[{"count":0,"href":"https:\/\/enjoyasp.net\/index.php\/wp-json\/wp\/v2\/posts\/1929\/revisions"}],"wp:attachment":[{"href":"https:\/\/enjoyasp.net\/index.php\/wp-json\/wp\/v2\/media?parent=1929"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/enjoyasp.net\/index.php\/wp-json\/wp\/v2\/categories?post=1929"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/enjoyasp.net\/index.php\/wp-json\/wp\/v2\/tags?post=1929"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}
\nSELECT @table = @table + ‘<\/span>
<\/p>\n
\n\tFROM server.msdb.dbo.sysjobhistory<\/span> sjh(NOLOCK)
\n\tJOIN server.msdb.dbo.sysjobs sj(NOLOCK) ON sjh.job_id = sj.job_id
\n\tJOIN server.msdb.dbo.sysjobsteps sjt(NOLOCK) ON sjh.step_id = sjt.step_id AND sjh.job_id = sjt.job_id
\n\tWHERE run_status =0
\n\tAND msdb.dbo.agent_datetime(run_date,run_time)> @LastRun
\n\tORDER BY run_date
\n\tSET @COUNT = @@ROWCOUNT
\n\tIF @COUNT > 0
\n\tBEGIN
\n\tSET @SyncCount = @SyncCount + @COUNT
\n\tSET @table = @table + ‘<\/p>\n\n\n
\n \n\t\t\t\t‘ + CONVERT(VARCHAR(10),ROW_NUMBER() OVER (ORDER BY run_date))+ ‘ job\u540d\u79f0\uff1a’ + CASE(ISNULL(sj.NAME,”))WHEN ” THEN ‘ ’ ELSE sj.NAME END +
\n\t\t\t\t‘<\/td>\n<\/tr>\n\n \n\t\t\t\t\u6b65\u9aa4\uff1a’ + CASE(ISNULL(sjt.step_name,”))WHEN ” THEN ‘ ’ ELSE sjt.step_name END + REPLICATE(‘ ’,4) + ‘\u6267\u884c\u65f6\u95f4\uff1a’ + CASE(ISNULL(sjh.run_date,”))WHEN ” THEN ‘ ’ ELSE CONVERT(VARCHAR(50),sjh.run_date,120) END +
\n\t\t\t\t‘<\/td>\n<\/tr>\n\n \n\t\t\t\t\u9519\u8bef\u6d88\u606f\uff1a’ + CASE(ISNULL(sjh.message,”))WHEN ” THEN ‘ ’ ELSE sjh.message END +
\n\t\t\t\t‘<\/td>\n<\/tr>\n\n \n\t\t\t\t\u6267\u884cSQL:’ + CASE(ISNULL(sjt.command,”))WHEN ” THEN ‘ ’ ELSE sjt.command END +
\n\t\t\t\t‘<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n
\n\tSET @body = dbo.getHTML(@table)
\n\t–SELECT @body<\/p>\n
\n\t@profile_name = ‘LogMonitor’,
\n\t@recipients = @receivers,
\n\t@body_format = ‘HTML’,
\n\t@body = @body,
\n\t@subject = ‘Job\u6267\u884c\u9519\u8bef[201]‘;
\n\tEND<\/p>\n","protected":false},"excerpt":{"rendered":"