Month: July 2018

Change Job notification Operators

I wanted to standardize job error notifications, so created an new operator called ‘DBA’ (with multiple email addresses).

This code semi-automates the process of updating all the jobs by listing them, along with the code needed to change them …

/* ChangeNotifications.sql */

SELECT 
   J.[name] JobName, 
   O.[name] OperatorName, 
   O.email_address Email, 
   'EXEC msdb.dbo.sp_update_job @job_name = N''' + J.[name] + ''', 
     @notify_level_email=2, 
     @notify_email_operator_name = N''DBA''' CommandToChangeIt 
FROM msdb..sysjobs J 
LEFT JOIN msdb..sysoperators O 
  ON O.id = J.notify_email_operator_id 
ORDER BY Email, JobName;

Bulk Email Sender

Although its inherently a bad idea to use a database server for application processes, and the built in function “sp_send_dbmail” can be a bit temperamental.

Never-the-less, the brief was to send emails this way, and individually (IE: no long strings of addresses).

The environment was mature, with a working Email Profile, a database and tables already in-place and holding HTML style emails ready to go.

-- SendEmailProcess.sql

USE [SomeDatabase]
GO

CREATE PROCEDURE [dbo].[SendEmailProcess]

		@Test varchar(100) = null

AS

/* clear out the sysmail tables */

	DELETE FROM [msdb].[dbo].[sysmail_allitems]
	
/* parameters */

	DECLARE @ID uniqueidentifier,
			@To varchar(100),
			@Subject varchar(255),
			@Html varchar(max),
			@Return int

/* start of loop */

	WHILE (SELECT COUNT(*)	
		   FROM [SomeDatabase].[dbo].[EmailMessage] EM
		   JOIN [SomeDatabase].[dbo].[Recipient] R 
			 ON EM.Id = R.EmailMessage_Id2
		   WHERE EM.[Status] = 'Submitted') > 0
	BEGIN

	/* get any one email message */

		SELECT TOP 1
			@ID = EM.ID, 
			@To = isnull(@Test, R.EmailAddress),
			@Subject = EM.[Subject],
			@Html = EM.HtmlContent
		FROM [SomeDatabase].[dbo].[EmailMessage] EM
		JOIN [SomeDatabase].[dbo].[Recipient] R 
		  ON EM.Id = R.EmailMessage_Id2
		WHERE EM.[Status] = 'Submitted';

	/* send it */

		EXEC [msdb].[dbo].[sp_send_dbmail]
			 @profile_name = 'BulkMail',
			 @recipients = @To,
			 @subject = @Subject,
			 @body = @Html,
			 @body_format = 'HTML';

	/* check it worked */

		SET @Return = @@error
		
	/* if it worked - mark it as Sent */
	
		IF @Return = 0
		BEGIN
			UPDATE [SomeDatabase].[dbo].[EmailMessage]
			SET [Status] = 'Sent'
			WHERE Id = @ID
		END

	/* if it failed - flag it and move on */

		IF @Return != 0 

	/* less-than greater-than does not work in WordPress */

		BEGIN
			UPDATE [SomeDatabase].[dbo].[EmailMessage]
			SET [Status] = 'Failed'
			WHERE Id = @ID
		END 
		
/* end of loop */

	END

GO