如何在SQL Server代理中为作业失败创建电子邮件警报

最新推荐文章于 2020-09-07 19:57:34 发布

culuo4781

最新推荐文章于 2020-09-07 19:57:34 发布

阅读量950

点赞数

文章标签：数据库 java oracle sql python

原文链接：https://www.sqlshack.com/how-to-create-email-alert-sql-server-agent-for-job-failures/

版权

本文介绍了如何在SQL Server代理中为作业失败创建电子邮件警报。通过使用SQL Server Reporting Service订阅和Ed Pollack的作业警报存储过程，可以在作业步骤错误发生时自动发送详细错误日志的电子邮件通知。

摘要由CSDN通过智能技术生成

SQL Server Agent is a Microsoft Windows service which helps to execute, schedule and automatize T-SQL queries, Integration Service Package, SQL Server Analysis Service queries, executable programs, operating system, and PowerShell commands. These actions which are performed by SQL Server can be called by the SQL Server Agent. Maybe, we can liken SQL Server Agent to an alarm clock because the agent will execute the scheduled task when the time comes

SQL Server代理是一项Microsoft Windows服务，可帮助执行，安排和自动执行T-SQL查询，集成服务包，SQL Server Analysis Service查询，可执行程序，操作系统和PowerShell命令。由SQL Server执行的这些操作可以由SQL Server代理调用。也许，我们可以将SQL Server代理比作一个闹钟，因为代理会在时间到时执行计划的任务

On the other hand, every database administrator or the person whose responsibility for managing SQL Server; needs to have some knowledge about SQL Server Agent. So that the database administrator can execute, schedule and automatize essential database maintenance operations through SQL Server Agent. In terms of essential database maintenance methodology, these operations should be done properly and regularly to avoid catastrophic failure. Such as; taking database backups or indexing are the regular database maintenance operations that can be automatized with help of SQL Server Agent. Regarding this idea, SQL Server Agent is the significant assistant tool for a database administrator.

另一方面，每个数据库管理员或负责管理SQL Server的人员；需要具有有关SQL Server代理的一些知识。使数据库管理员可以通过SQL Server代理执行，计划和自动化基本的数据库维护操作。就基本的数据库维护方法而言，应正确且定期地执行这些操作，以避免灾难性故障。如; 进行数据库备份或建立索引是常规的数据库维护操作，可以在SQL Server代理的帮助下将其自动化。关于此想法，SQL Server代理是数据库管理员的重要辅助工具。

Job Steps and Schedules are an integral part of SQL Agent jobs. A Job step can be defined as task or group of tasks which will be completed by SQL Agent, in addition to when we want to set up a job at least we need to do one job step. When we look at the output of the job step, it can report two results after the completed task. These are successful or failure. Schedules specify when a job will run.

作业步骤和计划是SQL Agent作业的组成部分。可以将一个作业步骤定义为一个任务或一组任务，这些任务或任务组将由SQL Agent完成，此外，当我们要设置一个作业时，至少需要执行一个作业步骤。当我们查看作业步骤的输出时，它可以在完成任务之后报告两个结果。这些是成功还是失败。计划表指定作业何时运行。

So far, we mentioned about SQL Server Agent and the main component definitions and why we need SQL Server Agent. Now, we will talk about the main idea of this article. This article will cover how to alert or notify database administrator when a job reports a failure. If we are notified about SQL Agent job fails, we can handle the issue as soon as possible and prevent the problem without causing the further problems. When we look at this aspect that we need a job fail notification system. We can use SQL Server built-in functions such as database mail or SQL Agent mail, but this solution will not be effective and will not give many details about job steps errors. So, we will overcome this problem with help SQL Server Reporting Service email subscription. Also, we don t need to write some custom html codes to get well formatted emails.

到目前为止，我们提到了SQL Server代理和主要组件定义以及为什么需要SQL Server代理。现在，我们将讨论本文的主要思想。本文将介绍如何在作业报告失败时提醒或通知数据库管理员。如果我们收到有关SQL Agent作业失败的通知，我们可以尽快处理该问题并防止出现该问题，而不会引起其他问题。当我们看这方面时，我们需要一个工作失败通知系统。我们可以使用SQL Server内置函数（例如数据库邮件或SQL Agent邮件），但是该解决方案将无效，并且不会提供有关作业步骤错误的许多详细信息。因此，我们将通过帮助SQL Server Reporting Service电子邮件订阅来克服此问题。另外，我们无需编写一些自定义html代码即可获得格式正确的电子邮件。

Now we will create a job with the following query. The created job named is DemoJob and scheduled to run every 10 minutes. Through this SQL Agent job, we can create error for every 10 minutes for our demonstration. After the demonstration drop the DemoJob because it will create unnecessary errors.

现在，我们将使用以下查询创建一个作业。创建的作业名为DemoJob，并计划每10分钟运行一次。通过此SQL Agent作业，我们可以为演示每10分钟创建一次错误。在演示之后，放下DemoJob，因为它将创建不必要的错误。

USE tempdb
GO
 
DROP TABLE IF EXISTS  [DemoForSQLAgentLog]
GO
CREATE TABLE [dbo].[DemoForSQLAgentLog](
	[Id] [int] NOT NULL PRIMARY KEY,
	[Dt] [datetime] DEFAULT(GETDATE()))
	
	INSERT INTO DemoForSQLAgentLog VALUES(1,DEFAULT)
GO
USE msdb
GO
 
 
 
 
 
/****** Delete DemoJob ******/
 
DECLARE @JobUniqId AS UNIQUEIDENTIFIER
SELECT TOP 1 @JobUniqId=job_id FROM sysjobs where name='DemoJob'
 
 
 
EXEC msdb.dbo.sp_delete_job @job_id=@JobUniqId, @delete_unused_schedule=1
GO
 
/****** Create Demo Job  ******/
BEGIN TRANSACTION
DECLARE @ReturnCode INT
SELECT @ReturnCode = 0
 
IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'[Uncategorized (Local)]' AND category_class=1)
BEGIN
EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL', @name=N'[Uncategorized (Local)]'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
 
END
 
DECLARE @jobId BINARY(16)
EXEC @ReturnCode =  msdb.dbo.sp_add_job @job_name=N'DemoJob', 
		@enabled=1, 
		@notify_level_eventlog=0, 
		@notify_level_email=0, 
		@notify_level_netsend=0, 
		@notify_level_page=0, 
		@delete_level=0, 
		@description=N'No description available.', 
		@category_name=N'[Uncategorized (Local)]', 
		@owner_login_name=N'sa', @job_id = @jobId OUTPUT
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
 
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'DemoJobStep_1', 
		@step_id=1, 
		@cmdexec_success_code=0, 
		@on_success_action=1, 
		@on_success_step_id=0, 
		@on_fail_action=2, 
		@on_fail_step_id=0, 
		@retry_attempts=3, 
		@retry_interval=0, 
		@os_run_priority=0, @subsystem=N'TSQL', 
		@command=N'INSERT INTO DemoForSQLAgentLog VALUES(1,DEFAULT)', 
		@database_name=N'TempDb', 
		@flags=0
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id=@jobId, @name=N'JobSchedule_1', 
		@enabled=1, 
		@freq_type=4, 
		@freq_interval=1, 
		@freq_subday_type=4, 
		@freq_subday_interval=10, 
		@freq_relative_interval=0, 
		@freq_recurrence_factor=0, 
		@active_start_date=20181122, 
		@active_end_date=99991231, 
		@active_start_time=0, 
		@active_end_time=235959, 
		@schedule_uid=N'c5a34b62-e0fa-4b53-9cca-43d6255dbd3f'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
COMMIT TRANSACTION
GOTO EndSave
QuitWithRollback:
    IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION
EndSave:
GO

After the creation of DemoJob you can see that under the folder.

创建DemoJob之后，您可以在文件夹下看到它。

Note: In this tip, we will use Ed Pollack job alerting procedure who discuss details Reporting and alerting on job failure in SQL Server in this article. This stored procedure logs job steps errors, but we will make some little modifications and adopted this stored procedure for SSRS usage and then we will use it.

注意： 在本技巧中，我们将使用 Ed Pollack 作业警报过程，该程序讨论本文中有关 在SQL Server 中 作业失败的报告和警报的 详细信息 。 该存储过程记录了作业步骤错误，但是我们将进行一些小的修改，并将此存储过程用于SSRS，然后再使用它。

The following stored procedure will help us to get error logs of SQL Server Agent.

以下存储过程将帮助我们获取SQL Server代理的错误日志。

USE [tempdb]
 
 
IF NOT EXISTS (SELECT * FROM sys.tables WHERE tables.name = 'sql_server_agent_job')
BEGIN
	CREATE TABLE dbo.sql_server_agent_job
	(	sql_server_agent_job_id INT NOT NULL IDENTITY(1,1) CONSTRAINT PK_sql_server_agent_job PRIMARY KEY CLUSTERED,
		sql_server_agent_job_id_guid UNIQUEIDENTIFIER NOT NULL,
		sql_server_agent_job_name NVARCHAR(128) NOT NULL,
		job_create_datetime_utc DATETIME NOT NULL, 
		job_last_modified_datetime_utc DATETIME NOT NULL,
		is_enabled BIT NOT NULL,
		is_deleted BIT NOT NULL,
		job_category_name VARCHAR(100) NOT NULL);
END
GO
 
IF NOT EXISTS (SELECT * FROM sys.tables WHERE tables.name = 'sql_server_agent_job_failure')
BEGIN
	CREATE TABLE dbo.sql_server_agent_job_failure
	(	sql_server_agent_job_failure_id INT NOT NULL IDENTITY(1,1) CONSTRAINT PK_sql_server_agent_job_failure PRIMARY KEY CLUSTERED,
		sql_server_agent_job_id INT NOT NULL CONSTRAINT FK_sql_server_agent_job_failure_sql_server_agent_job FOREIGN KEY REFERENCES dbo.sql_server_agent_job (sql_server_agent_job_id),
		sql_server_agent_instance_id INT NOT NULL,
		job_start_time_utc DATETIME NOT NULL,
		job_failure_time_utc DATETIME NOT NULL,
		job_failure_step_number SMALLINT NOT NULL,
		job_failure_step_name VARCHAR(250) NOT NULL,
		job_failure_message VARCHAR(MAX) NOT NULL,
		job_step_failure_message VARCHAR(MAX) NOT NULL,
		job_step_severity INT NOT NULL,
		job_step_message_id INT NOT NULL,
		retries_attempted INT NOT NULL,
		has_email_been_sent_to_operator BIT NOT NULL);
 
	CREATE NONCLUSTERED INDEX NCI_sql_server_agent_job_failure_sql_server_agent_job_id ON dbo.sql_server_agent_job_failure (sql_server_agent_job_id);
	CREATE NONCLUSTERED INDEX NCI_sql_server_agent_job_failure_sql_server_agent_instance_id ON dbo.sql_server_agent_job_failure (sql_server_agent_instance_id);
END
GO
 
 
 
 
 
 
 
 
 
 
CREATE OR ALTER   PROCEDURE [dbo].[monitor_job_failures_forReportingService]
	
AS
BEGIN
	SET NOCOUNT ON;
	declare @minutes_to_monitor SMALLINT = 1440
 
	DECLARE @utc_offset INT;
	SELECT
		@utc_offset = -1 * DATEDIFF(HOUR, GETUTCDATE(), GETDATE());
		
	-- First, collect list of SQL Server agent jobs and update ours as needed.
	-- Update our jobs data with any changes since the last update time.
	MERGE INTO dbo.sql_server_agent_job AS TARGET
		USING (SELECT
					sysjobs.job_id AS sql_server_agent_job_id_guid,
					sysjobs.name AS sql_server_agent_job_name,
					sysjobs.date_created AS job_create_datetime_utc,
					sysjobs.date_modified AS job_last_modified_datetime_utc,
					sysjobs.enabled AS is_enabled,
					0 AS is_deleted,
					ISNULL(syscategories.name, '') AS job_category_name
			   FROM msdb.dbo.sysjobs
			   LEFT JOIN msdb.dbo.syscategories
			   ON syscategories.category_id = sysjobs.category_id) AS SOURCE
		ON (SOURCE.sql_server_agent_job_id_guid = TARGET.sql_server_agent_job_id_guid)
		WHEN NOT MATCHED BY TARGET
			THEN INSERT
				(sql_server_agent_job_id_guid, sql_server_agent_job_name, job_create_datetime_utc, job_last_modified_datetime_utc,
				 is_enabled, is_deleted, job_category_name)
			VALUES	(
				SOURCE.sql_server_agent_job_id_guid,
				SOURCE.sql_server_agent_job_name,
				SOURCE.job_create_datetime_utc,
				SOURCE.job_last_modified_datetime_utc,
				SOURCE.is_enabled,
				SOURCE.is_deleted,
				SOURCE.job_category_name)
		WHEN MATCHED AND SOURCE.job_last_modified_datetime_utc > TARGET.job_last_modified_datetime_utc
			THEN UPDATE
				SET sql_server_agent_job_name = SOURCE.sql_server_agent_job_name,
					job_create_datetime_utc = SOURCE.job_create_datetime_utc,
					job_last_modified_datetime_utc = SOURCE.job_last_modified_datetime_utc,
					is_enabled = SOURCE.is_enabled,
					is_deleted = SOURCE.is_deleted,
					job_category_name = SOURCE.job_category_name;
	-- If a job was deleted, then mark it as no longer enabled.
	UPDATE sql_server_agent_job
		SET is_enabled = 0,
			is_deleted = 1
	FROM dbo.sql_server_agent_job
	LEFT JOIN msdb.dbo.sysjobs
	ON sysjobs.job_id = sql_server_agent_job.sql_server_agent_job_id_guid
	WHERE sysjobs.job_id IS NULL;
	-- Find all recent job failures and log them in the target log table.
	WITH CTE_NORMALIZE_DATETIME_DATA AS (
		SELECT
			sysjobhistory.job_id AS sql_server_agent_job_id_guid,
			CAST(sysjobhistory.run_date AS VARCHAR(MAX)) AS run_date_string, 
			REPLICATE('0', 6 - LEN(CAST(sysjobhistory.run_time AS VARCHAR(MAX)))) + CAST(sysjobhistory.run_time AS VARCHAR(MAX)) AS run_time_string,
			REPLICATE('0', 6 - LEN(CAST(sysjobhistory.run_duration AS VARCHAR(MAX)))) + CAST(sysjobhistory.run_duration AS VARCHAR(MAX)) AS run_duration_string,
			sysjobhistory.run_status,
			sysjobhistory.message,
			sysjobhistory.instance_id
		FROM msdb.dbo.sysjobhistory WITH (NOLOCK)
		WHERE sysjobhistory.run_status = 0
		AND sysjobhistory.step_id = 0),
	CTE_GENERATE_DATETIME_DATA AS (
		SELECT
			CTE_NORMALIZE_DATETIME_DATA.sql_server_agent_job_id_guid,
			CAST(SUBSTRING(CTE_NORMALIZE_DATETIME_DATA.run_date_string, 5, 2) + '/' + SUBSTRING(CTE_NORMALIZE_DATETIME_DATA.run_date_string, 7, 2) + '/' + SUBSTRING(CTE_NORMALIZE_DATETIME_DATA.run_date_string, 1, 4) AS DATETIME) +
			CAST(STUFF(STUFF(CTE_NORMALIZE_DATETIME_DATA.run_time_string, 5, 0, ':'), 3, 0, ':') AS DATETIME) AS job_start_datetime,
			CAST(SUBSTRING(CTE_NORMALIZE_DATETIME_DATA.run_duration_string, 1, 2) AS INT) * 3600 +
				CAST(SUBSTRING(CTE_NORMALIZE_DATETIME_DATA.run_duration_string, 3, 2) AS INT) * 60 + 
				CAST(SUBSTRING(CTE_NORMALIZE_DATETIME_DATA.run_duration_string, 5, 2) AS INT) AS job_duration_seconds,
			CASE CTE_NORMALIZE_DATETIME_DATA.run_status
				WHEN 0 THEN 'Failure'
				WHEN 1 THEN 'Success'
				WHEN 2 THEN 'Retry'
				WHEN 3 THEN 'Canceled'
				ELSE 'Unknown'
			END AS job_status,
			CTE_NORMALIZE_DATETIME_DATA.message,
			CTE_NORMALIZE_DATETIME_DATA.instance_id
		FROM CTE_NORMALIZE_DATETIME_DATA)
	SELECT
		CTE_GENERATE_DATETIME_DATA.sql_server_agent_job_id_guid,
		DATEADD(HOUR, @utc_offset, CTE_GENERATE_DATETIME_DATA.job_start_datetime) AS job_start_time_utc,
		DATEADD(HOUR, @utc_offset, DATEADD(SECOND, ISNULL(CTE_GENERATE_DATETIME_DATA.job_duration_seconds, 0), CTE_GENERATE_DATETIME_DATA.job_start_datetime)) AS job_failure_time_utc,
		ISNULL(CTE_GENERATE_DATETIME_DATA.message, '') AS job_failure_message,
		CTE_GENERATE_DATETIME_DATA.instance_id
	INTO #job_failure
	FROM CTE_GENERATE_DATETIME_DATA
	WHERE DATEADD(HOUR, @utc_offset, CTE_GENERATE_DATETIME_DATA.job_start_datetime) > DATEADD(MINUTE, -1 * @minutes_to_monitor, GETUTCDATE());
 
	WITH CTE_NORMALIZE_DATETIME_DATA AS (
		SELECT
			sysjobhistory.job_id AS sql_server_agent_job_id_guid,
			CAST(sysjobhistory.run_date AS VARCHAR(MAX)) AS run_date_string, 
			REPLICATE('0', 6 - LEN(CAST(sysjobhistory.run_time AS VARCHAR(MAX)))) + CAST(sysjobhistory.run_time AS VARCHAR(MAX)) AS run_time_string,
			REPLICATE('0', 6 - LEN(CAST(sysjobhistory.run_duration AS VARCHAR(MAX)))) + CAST(sysjobhistory.run_duration AS VARCHAR(MAX)) AS run_duration_string,
			sysjobhistory.run_status,
			sysjobhistory.step_id,
			sysjobhistory.step_name,
			sysjobhistory.message,
			sysjobhistory.retries_attempted,
			sysjobhistory.sql_severity,
			sysjobhistory.sql_message_id,
			sysjobhistory.instance_id
		FROM msdb.dbo.sysjobhistory WITH (NOLOCK)
		WHERE sysjobhistory.run_status = 0
		AND sysjobhistory.step_id > 0),
	CTE_GENERATE_DATETIME_DATA AS (
		SELECT
			CTE_NORMALIZE_DATETIME_DATA.sql_server_agent_job_id_guid,
			CAST(SUBSTRING(CTE_NORMALIZE_DATETIME_DATA.run_date_string, 5, 2) + '/' + SUBSTRING(CTE_NORMALIZE_DATETIME_DATA.run_date_string, 7, 2) + '/' + SUBSTRING(CTE_NORMALIZE_DATETIME_DATA.run_date_string, 1, 4) AS DATETIME) +
			CAST(STUFF(STUFF(CTE_NORMALIZE_DATETIME_DATA.run_time_string, 5, 0, ':'), 3, 0, ':') AS DATETIME) AS job_start_datetime,
			CAST(SUBSTRING(CTE_NORMALIZE_DATETIME_DATA.run_duration_string, 1, 2) AS INT) * 3600 +
				CAST(SUBSTRING(CTE_NORMALIZE_DATETIME_DATA.run_duration_string, 3, 2) AS INT) * 60 + 
				CAST(SUBSTRING(CTE_NORMALIZE_DATETIME_DATA.run_duration_string, 5, 2) AS INT) AS job_duration_seconds,
			CASE CTE_NORMALIZE_DATETIME_DATA.run_status
				WHEN 0 THEN 'Failure'
				WHEN 1 THEN 'Success'
				WHEN 2 THEN 'Retry'
				WHEN 3 THEN 'Canceled'
				ELSE 'Unknown'
			END AS job_status,
			CTE_NORMALIZE_DATETIME_DATA.step_id,
			CTE_NORMALIZE_DATETIME_DATA.step_name,
			CTE_NORMALIZE_DATETIME_DATA.message,
			CTE_NORMALIZE_DATETIME_DATA.retries_attempted,
			CTE_NORMALIZE_DATETIME_DATA.sql_severity,
			CTE_NORMALIZE_DATETIME_DATA.sql_message_id,
			CTE_NORMALIZE_DATETIME_DATA.instance_id
		FROM CTE_NORMALIZE_DATETIME_DATA)
	SELECT
		CTE_GENERATE_DATETIME_DATA.sql_server_agent_job_id_guid,
		DATEADD(HOUR, @utc_offset, CTE_GENERATE_DATETIME_DATA.job_start_datetime) AS job_start_time_utc,
		DATEADD(HOUR, @utc_offset, DATEADD(SECOND, ISNULL(CTE_GENERATE_DATETIME_DATA.job_duration_seconds, 0), CTE_GENERATE_DATETIME_DATA.job_start_datetime)) AS job_failure_time_utc,
		CTE_GENERATE_DATETIME_DATA.step_id AS job_failure_step_number,
		ISNULL(CTE_GENERATE_DATETIME_DATA.message, '') AS job_step_failure_message,
		CTE_GENERATE_DATETIME_DATA.sql_severity AS job_step_severity,
		CTE_GENERATE_DATETIME_DATA.retries_attempted,
		CTE_GENERATE_DATETIME_DATA.step_name,
		CTE_GENERATE_DATETIME_DATA.sql_message_id,
		CTE_GENERATE_DATETIME_DATA.instance_id
	INTO #job_step_failure
	FROM CTE_GENERATE_DATETIME_DATA
	WHERE DATEADD(HOUR, @utc_offset, CTE_GENERATE_DATETIME_DATA.job_start_datetime) > DATEADD(MINUTE, -1 * @minutes_to_monitor, GETUTCDATE());
 
	-- Get jobs that failed due to failed steps.
	WITH CTE_FAILURE_STEP AS (
		SELECT
			*,
			ROW_NUMBER() OVER (PARTITION BY job_step_failure.sql_server_agent_job_id_guid, job_step_failure.job_failure_time_utc ORDER BY job_step_failure.job_failure_step_number DESC) AS recent_step_rank
		FROM #job_step_failure job_step_failure)
	INSERT INTO dbo.sql_server_agent_job_failure
		(sql_server_agent_job_id, sql_server_agent_instance_id, job_start_time_utc, job_failure_time_utc, job_failure_step_number, job_failure_step_name,
		 job_failure_message, job_step_failure_message, job_step_severity, job_step_message_id, retries_attempted, has_email_been_sent_to_operator)
	SELECT
		sql_server_agent_job.sql_server_agent_job_id,
		CTE_FAILURE_STEP.instance_id,
		job_failure.job_start_time_utc,
		CTE_FAILURE_STEP.job_failure_time_utc,
		CTE_FAILURE_STEP.job_failure_step_number,
		CTE_FAILURE_STEP.step_name AS job_failure_step_name,
		job_failure.job_failure_message,
		CTE_FAILURE_STEP.job_step_failure_message,
		CTE_FAILURE_STEP.job_step_severity,
		CTE_FAILURE_STEP.sql_message_id AS job_step_message_id,
		CTE_FAILURE_STEP.retries_attempted,
		0 AS has_email_been_sent_to_operator
	FROM #job_failure job_failure
	INNER JOIN dbo.sql_server_agent_job
	ON job_failure.sql_server_agent_job_id_guid = sql_server_agent_job.sql_server_agent_job_id_guid
	INNER JOIN CTE_FAILURE_STEP
	ON job_failure.sql_server_agent_job_id_guid = CTE_FAILURE_STEP.sql_server_agent_job_id_guid
	AND job_failure.job_failure_time_utc = CTE_FAILURE_STEP.job_failure_time_utc
	WHERE CTE_FAILURE_STEP.recent_step_rank = 1
	AND CTE_FAILURE_STEP.instance_id NOT IN (SELECT sql_server_agent_job_failure.sql_server_agent_instance_id FROM dbo.sql_server_agent_job_failure);
	-- Get jobs that failed without any failed steps.
	INSERT INTO dbo.sql_server_agent_job_failure
		(sql_server_agent_job_id, sql_server_agent_instance_id, job_start_time_utc, job_failure_time_utc, job_failure_step_number, job_failure_step_name,
		 job_failure_message, job_step_failure_message, job_step_severity, job_step_message_id, retries_attempted, has_email_been_sent_to_operator)
	SELECT
		sql_server_agent_job.sql_server_agent_job_id,
		job_failure.instance_id,
		job_failure.job_start_time_utc,
		job_failure.job_failure_time_utc,
		0 AS job_failure_step_number,
		'' AS job_failure_step_name,
		job_failure.job_failure_message,
		'' AS job_step_failure_message,
		-1 AS job_step_severity,
		-1 AS job_step_message_id,
		0 AS retries_attempted,
		0 AS has_email_been_sent_to_operator
	FROM #job_failure job_failure
	INNER JOIN dbo.sql_server_agent_job
	ON job_failure.sql_server_agent_job_id_guid = sql_server_agent_job.sql_server_agent_job_id_guid
	WHERE job_failure.instance_id NOT IN (SELECT sql_server_agent_job_failure.sql_server_agent_instance_id FROM dbo.sql_server_agent_job_failure)
	AND NOT EXISTS (SELECT * FROM #job_step_failure job_step_failure WHERE job_failure.sql_server_agent_job_id_guid = job_step_failure.sql_server_agent_job_id_guid	AND job_failure.job_failure_time_utc = job_step_failure.job_failure_time_utc);
	
	-- Get job steps that failed, but for jobs that succeeded.
	WITH CTE_FAILURE_STEP AS (
		SELECT
			*,
			ROW_NUMBER() OVER (PARTITION BY job_step_failure.sql_server_agent_job_id_guid, job_step_failure.job_failure_time_utc ORDER BY job_step_failure.job_failure_step_number DESC) AS recent_step_rank
		FROM #job_step_failure job_step_failure)
	INSERT INTO dbo.sql_server_agent_job_failure
		(sql_server_agent_job_id, sql_server_agent_instance_id, job_start_time_utc, job_failure_time_utc, job_failure_step_number, job_failure_step_name,
		 job_failure_message, job_step_failure_message, job_step_severity, job_step_message_id, retries_attempted, has_email_been_sent_to_operator)
	SELECT
		sql_server_agent_job.sql_server_agent_job_id,
		CTE_FAILURE_STEP.instance_id,
		CTE_FAILURE_STEP.job_start_time_utc,
		CTE_FAILURE_STEP.job_failure_time_utc,
		CTE_FAILURE_STEP.job_failure_step_number,
		CTE_FAILURE_STEP.step_name AS job_failure_step_name,
		'' AS job_failure_message,
		CTE_FAILURE_STEP.job_step_failure_message,
		CTE_FAILURE_STEP.job_step_severity,
		CTE_FAILURE_STEP.sql_message_id AS job_step_message_id,
		CTE_FAILURE_STEP.retries_attempted,
		0 AS has_email_been_sent_to_operator
	FROM CTE_FAILURE_STEP
	INNER JOIN dbo.sql_server_agent_job
	ON CTE_FAILURE_STEP.sql_server_agent_job_id_guid = sql_server_agent_job.sql_server_agent_job_id_guid
	LEFT JOIN #job_failure job_failure
	ON job_failure.sql_server_agent_job_id_guid = CTE_FAILURE_STEP.sql_server_agent_job_id_guid
	AND job_failure.job_failure_time_utc = CTE_FAILURE_STEP.job_failure_time_utc
	WHERE CTE_FAILURE_STEP.recent_step_rank = 1
	AND job_failure.sql_server_agent_job_id_guid IS NULL
	AND CTE_FAILURE_STEP.instance_id NOT IN (SELECT sql_server_agent_job_failure.sql_server_agent_instance_id FROM dbo.sql_server_agent_job_failure);
 
 
	    ---This part of query modified for Reporting Service---
 
		SELECT sql_server_agent_job_name,job_failure_message,job_step_failure_message,job_failure_step_name
		FROM dbo.sql_server_agent_job_failure
		INNER JOIN dbo.sql_server_agent_job
		ON sql_server_agent_job.sql_server_agent_job_id = sql_server_agent_job_failure.sql_server_agent_job_id
		WHERE sql_server_agent_job_failure.has_email_been_sent_to_operator = 0
		ORDER BY sql_server_agent_job_failure.job_failure_time_utc ASC
	
	     
		
 
		UPDATE sql_server_agent_job_failure
			SET has_email_been_sent_to_operator = 1
		FROM dbo.sql_server_agent_job_failure
		WHERE sql_server_agent_job_failure.has_email_been_sent_to_operator = 0;
		-- This part of query modified for reporting service---
    IF @@ROWCOUNT=0
	BEGIN
	RAISERROR ('No Records Found',16,1)
	END
	---******************************************************------
    DROP TABLE #job_step_failure;
	DROP TABLE #job_failure;
 
	
END

After all these preparation steps about SQL Server Agent, we need to configure SQL Server Reporting Service email settings.

关于SQL Server代理的所有这些准备步骤之后，我们需要配置SQL Server Reporting Service电子邮件设置。

Launch the Reporting Service Configuration Manager
启动Reporting Service配置管理器
Connect the SQL Server Reporting Service and click the E-mail Settings tab
连接SQL Server报告服务，然后单击“电子邮件设置”选项卡
Apply 应用

After these steps, we will start to design job error alert report which will be sent by SSRS subscription

完成这些步骤后，我们将开始设计作业错误警报报告，该报告将通过SSRS订阅发送

Microsoft SQL Server Report Builder Microsoft SQL Server报表生成器
Add Data Source 添加数据源”

Test Connection and ensure about the connection settings of SQL Server than click 测试连接”，并确保有关SQL Server的连接设置，然后单击“ OK 确定”。
Datasets folder and click 数据集”文件夹，然后单击“ Add Dataset 添加数据集”

Query type as 查询类型作为Text and paste the query 文本并粘贴查询
OK 确定

Expression 表达式”
Save As 另存为
Edit Schedule 编辑时间表”
Create a schedule which looks like the below figure
创建一个如下图所示的时间表

In this step, we will schedule to subscription for every five minutes, because in the procedure we set the looking parameter to five minutes 1440 second. If you want, you can change this schedule setting, but you need to make the same changing in the procedure.

在此步骤中，我们将计划每五分钟订阅一次，因为在此过程中，我们将looking参数设置为五分钟1440秒。如果需要，可以更改此计划设置，但是需要在过程中进行相同的更改。

Select Deliver the report to as E-Mail and write an email address of any person who will be notified
选择“将报告发送为电子邮件”并写任何将被通知的人的电子邮件地址
Create Subscription 创建订阅

You will get an email which looks like the figure below

您将收到一封电子邮件，如下图所示

结论 (Conclusion)

In this article, we created a mail notification for SQL Server Agent failed jobs. We used a stored procedure which helps to log detail error of job steps. Through the SQL Server Reporting Service subscription, we created an email notification. The benefit of this approach is that we don’t need any email settings in SQL Server or SQL Server Agent.

在本文中，我们为SQL Server代理失败的作业创建了邮件通知。我们使用了一个存储过程来帮助记录作业步骤的详细错误。通过SQL Server Reporting Service订阅，我们创建了电子邮件通知。这种方法的好处是我们在SQL Server或SQL Server代理中不需要任何电子邮件设置。

翻译自: https://www.sqlshack.com/how-to-create-email-alert-sql-server-agent-for-job-failures/

culuo4781

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
如何在SQL Server代理中为作业失败创建电子邮件警报

SQL Server Agent is a Microsoft Windows service which helps to execute, schedule and automatize T-SQL queries, Integration Service Package, SQL Server Analysis Service queries, executable programs...
复制链接

扫一扫