Event Notification in SQL

--Check if the database is enabled for Service Broker
--If not then enable it
IF EXISTS (SELECT * FROM sys.databases WHERE name = 'Demostart'
AND is_broker_enabled = 0)
ALTER DATABASE Demostart SET ENABLE_BROKER;
GO
USE Demostart
GO
--Create a queue which will hold the tracked information
CREATE QUEUE dbo.EventNotificationQueue
GO
--Check if the queue is created or not
SELECT * FROM sys.service_queues
WHERE name = 'EventNotificationQueue'
GO
--Create a service on which tracked information will be sent
CREATE SERVICE [//Demostart/EventNotificationService]
ON QUEUE dbo.EventNotificationQueue
([http://schemas.microsoft.com/SQL/Notifications/PostEventNotification])
GO
--Check if the service is created or not
SELECT * FROM sys.services
WHERE name = '//Demostart/EventNotificationService'
GO

---------------------------------------------------------------------------------------

--Create a notification to track create table command
CREATE EVENT NOTIFICATION NotifyCREATETABLEEvents
ON DATABASE
FOR CREATE_TABLE
TO SERVICE '//Demostart/EventNotificationService' , 'current database'
GO
--Create a notification to track alter table command
CREATE EVENT NOTIFICATION NotifyALTERTABLEEvents
ON DATABASE
FOR ALTER_TABLE
TO SERVICE '//Demostart/EventNotificationService' , 'current database'
GO
--Check if both the above notifications created or not
SELECT * FROM sys.event_notifications
WHERE name IN ('NotifyCREATETABLEEvents','NotifyALTERTABLEEvents')
GO

----------------------------------------------------------------------------------------
--Create a notification to error occuring at server level
CREATE EVENT NOTIFICATION NotifyERROREvents
ON SERVER WITH FAN_IN
FOR ERRORLOG
TO SERVICE '//Demostart/EventNotificationService', 'current database'
GO
--Check if the above notification was created or not
SELECT * FROM sys.server_event_notifications
WHERE name IN ('NotifyERROREvents')
GO

----------------------------------------------------------------------------------------

--Generate a create table event
CREATE TABLE EventMasterTable
(
COL1 INT,
COL2 INT
)
GO
--Generate an alter table event
ALTER TABLE EventMasterTable
ADD COL3 INT
GO
--Generate a server level event
RAISERROR (N'Generating error for Event Notification testing...', 16, 1)
WITH LOG
GO
--Review if the events were tracked in queue
SELECT CAST(message_body AS XML) AS message_in_xml
FROM dbo.EventNotificationQueue
GO

----------------------------------------------------------------------------------------

DECLARE @TargetDialogHandle UNIQUEIDENTIFIER;
DECLARE @EventMessage XML;
DECLARE @EventMessageTypeName sysname;
WAITFOR
( RECEIVE TOP(1)
@TargetDialogHandle = conversation_handle,
@EventMessage = CONVERT(XML, message_body),
@EventMessageTypeName = message_type_name
FROM dbo.EventNotificationQueue
), TIMEOUT 1000;
SELECT @TargetDialogHandle AS DialogHandle, @EventMessageTypeName AS MessageTypeName,
@EventMessage.value('(/EVENT_INSTANCE/EventType)[1]', 'varchar(128)' ) as EventType,
@EventMessage.value('(/EVENT_INSTANCE/ServerName)[1]', 'varchar(128)' ) as ServerName,
@EventMessage.value('(/EVENT_INSTANCE/DatabaseName)[1]', 'varchar(128)' ) as DatabaseName,
@EventMessage.value('(/EVENT_INSTANCE/LoginName)[1]', 'varchar(128)' ) as LoginName,
@EventMessage.value('(/EVENT_INSTANCE/TSQLCommand/CommandText)[1]','nvarchar(max)') AS TSQLCommand,
@EventMessage.value('(/EVENT_INSTANCE/TextData)[1]', 'varchar(128)' ) AS TextData,
@EventMessage.value('(/EVENT_INSTANCE/Severity)[1]', 'varchar(128)' ) AS Severity,
@EventMessage.value('(/EVENT_INSTANCE/Error)[1]', 'varchar(128)' ) AS ErrorNumber