How to create a mail alert with SQL Server Agent for job failures

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

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.

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.

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.

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.

USE tempdb

DROP TABLE IF EXISTS [DemoForSQLAgentLog]

CREATE TABLE [dbo].[DemoForSQLAgentLog](

[Id] [int] NOT NULL PRIMARY KEY,

[Dt] [datetime] DEFAULT(GETDATE()))

INSERT INTO DemoForSQLAgentLog VALUES(1,DEFAULT)

USE msdb

/****** 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

/****** 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:

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

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.

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

100

101

102

103

104

105

106

107

108

109

110

111

112

113

114

115

116

117

118

119

120

121

122

123

124

125

126

127

128

129

130

131

132

133

134

135

136

137

138

139

140

141

142

143

144

145

146

147

148

149

150

151

152

153

154

155

156

157

158

159

160

161

162

163

164

165

166

167

168

169

170

171

172

173

174

175

176

177

178

179

180

181

182

183

184

185

186

187

188

189

190

191

192

193

194

195

196

197

198

199

200

201

202

203

204

205

206

207

208

209

210

211

212

213

214

215

216

217

218

219

220

221

222

223

224

225

226

227

228

229

230

231

232

233

234

235

236

237

238

239

240

241

242

243

244

245

246

247

248

249

250

251

252

253

254

255

256

257

258

259

260

261

262

263

264

265

266

267

268

269

270

271

272

273

274

275

276

277

278

279

280

281

282

283

284

285

286

287

288

289

290

291

292

293

294

295

296

297

298

299

300

301

302

303

304

305

306

307

308

309

310

311

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

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

CREATE OR ALTER PROCEDURE [dbo].[monitor_job_failures_forReportingService]

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(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.

Launch the Reporting Service Configuration Manager
Connect the SQL Server Reporting Service and click the E-mail Settings tab
Fill the SMTP server credentials and email details to this screen
Click Apply

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

Launch the Microsoft SQL Server Report Builder
Open a blank report
Right click Data Sources and click Add Data Source

Select Use a connection embedded in my report then click Build define SQL Server connection settings
Click Test Connection and ensure about the connection settings of SQL Server than click OK
Right click the Datasets folder and click Add Dataset

Select Use a dataset embedded in my report and select the data source which created previous step. Select Query type as Text and paste the query
Click Refresh fields and populate the monitor_job_failures_forReportingService stored procedure columns to dataset. Click OK

Drag and drop text box to report design panel then click the right click context menu and select Expression
Add a table to the design panel and then drag and drop the dataset fields to a table
Click to File menu then select Save As
Write the reporting service web service URL and file name and in which server you want to publish then click save
Open the report server web URL and then click (…) button on the report and select Manage in the context menu
Click Subscriptions tab then click +New subscriptions
Give a description to New Subscription then click 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.

Select Deliver the report to as E-Mail and write an email address of any person who will be notified
Click 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.

Author
Recent Posts

Esat Erkec

Esat Erkec is a SQL Server professional who began his career 8+ years ago as a Software Developer. He is a SQL Server Microsoft Certified Solutions Expert.

Most of his career has been focused on SQL Server Database Administration and Development. His current interests are in database administration and Business Intelligence. You can find him on LinkedIn.

View all posts by Esat Erkec

SQLShack

How to create an email alert in SQL Server Agent for job failures

Conclusion

Conclusion

Related posts: