Настройка почтовых уведомлений в MS SQL Server

-- активируем service broker
IF NOT EXISTS(SELECT * FROM master.sys.databases WHERE name = DB_NAME() 
			  AND is_broker_enabled = 1)
BEGIN
	DECLARE @sql nvarchar(max);
	SET @sql = N'USE [master]; ALTER DATABASE [' + DB_NAME() +  N'] SET NEW_BROKER; USE [' + DB_NAME() + '];';
	EXECUTE(@sql)
	
	SET @sql = N'USE [master]; ALTER DATABASE [' + DB_NAME() +  N'] SET ENABLE_BROKER; USE [' + DB_NAME() + '];';
	EXECUTE(@sql)
END;
GO

CREATE MESSAGE TYPE [http://tempuri.org/Notifications/SendErrorInfoEvent]
	VALIDATION = WELL_FORMED_XML;
GO
CREATE CONTRACT [http://tempuri.org/Notifications/PostEventNotification] 
(
	[http://tempuri.org/Notifications/SendErrorInfoEvent] SENT BY ANY
);
GO

CREATE PROCEDURE [ProcessNotificationEvents]
AS
BEGIN
	SET NOCOUNT ON;

	DECLARE @message_body XML,
			@message_type_name NVARCHAR(256),
            @dialog UNIQUEIDENTIFIER ;

	WHILE (1 = 1)
	BEGIN
		WAITFOR(
			RECEIVE TOP(1) 
				@message_type_name = message_type_name,
				@message_body = message_body,
				@dialog = conversation_handle
			FROM [EventNotificationQueue]
		), TIMEOUT 2000;

		IF @@ROWCOUNT = 0
		BEGIN
			BREAK;
		END

		IF (@message_type_name = 'http://schemas.microsoft.com/SQL/ServiceBroker/EndDialog')
		BEGIN
	        END CONVERSATION @dialog ;
		END
		ELSE
		BEGIN
			/*
				здесь реальный код обработки сообщения данные в @message_body
			*/
			
			RETURN 0
		END
	END
	RETURN 0;
END
GO
CREATE QUEUE [EventNotificationQueue] WITH 
	STATUS = ON,
	ACTIVATION (
		STATUS = ON, 
		PROCEDURE_NAME = ProcessNotificationEvents, 
		MAX_QUEUE_READERS = 1,
		EXECUTE AS OWNER
	);
GO
CREATE SERVICE [http://tempuri.org/Notifications/EventNotificationService] 
	ON QUEUE [EventNotificationQueue] 
	(
	[http://tempuri.org/Notifications/PostEventNotification] 
	);
GO
--
-- использование, можно оформить в виде процедуры
--
DECLARE @dialog UNIQUEIDENTIFIER;

BEGIN DIALOG CONVERSATION @dialog
	FROM SERVICE [http://tempuri.org/Notifications/EventNotificationService]
	TO SERVICE 'http://tempuri.org/Notifications/EventNotificationService'
	ON CONTRACT [http://tempuri.org/Notifications/PostEventNotification]
	WITH ENCRYPTION = OFF;
	
DECLARE @EventData XML;
-- формируем данные для отправки по электронной почте

SEND ON CONVERSATION @dialog
	MESSAGE TYPE [http://tempuri.org/Notifications/SendErrorInfoEvent] (@EventData);
	
END CONVERSATION @dialog;