Service Broker Assistant Event Logging Example

The 'Event Logging' example provides a simple application that registers for, receives, and archives event notification messages.

Enable Service Broker

Check that the Service Broker is enabled in the database by selecting 'Configure Service Broker...'


Create Queue

Next, create a queue to store the notification messages. Name it 'NotifyQueue' and set it's 'Status' to available selecting the check box.


Create Service

Now, we can create a service, which is the component that ties together a contract and a queue together for creating the application. Name the service 'NotifyService' and select the queue created above. Choose the 'PostEventNotification' contract, a default contract that is in each database.


Create Log Tables

For this example, we will use 2 tables. The first holds event notification information in relational form:

CREATE TABLE [dbo].[LoggedEvents] (
EventNumber INT IDENTITY PRIMARY KEY,
EventType NVARCHAR(256),
EventTime DATETIME,
LoginName sysname NULL,
UserName sysname NULL,
ServerName sysname NULL,
DatabaseName sysname NULL,
SchemaName sysname NULL,
ObjectName sysname NULL,
ObjectType sysname NULL,
TSQLCmdText NVARCHAR(MAX) NULL
) ;

The second stores the event type and time along with the entire raw XML message:

CREATE TABLE [LoggedEventsXML] (
EventNumber INT IDENTITY PRIMARY KEY,
EventType NVARCHAR(256),
EventTime DATETIME,
EventData XML
) ;

Create Activation Procedure

The stored procedure that will be activated with each event notification is next.

CREATE PROCEDURE [dbo].[LogEventsProc]
AS
SET NOCOUNT ON;
DECLARE @message_body XML,
@message_type_name NVARCHAR(256),
@dialog UNIQUEIDENTIFIER ;
-- This procedure continues to process messages in the queue until the
-- queue is empty.
WHILE (1 = 1)
BEGIN
BEGIN TRANSACTION ;
-- Receive the next available message
WAITFOR (
RECEIVE TOP(1) -- just handle one message at a time
@message_type_name=message_type_name, --the type of message received
@message_body=message_body, -- the message contents
@dialog = conversation_handle -- identifier of the dialog this message was received on
FROM NotifyQueue
), TIMEOUT 2000 ; -- if the queue is empty for two seconds, give up and go away
-- If RECEIVE did not return a message, roll back the transaction
-- and break out of the while loop, exiting the procedure.
IF (@@ROWCOUNT = 0)
BEGIN
ROLLBACK TRANSACTION ;
BREAK ;
END ;
-- Check to see if the message is an end dialog message.
IF (@message_type_name = 'http://schemas.microsoft.com/SQL/ServiceBroker/EndDialog')
BEGIN
PRINT 'End Dialog received for dialog # ' + CAST(@dialog AS NVARCHAR(40)) ;
END CONVERSATION @dialog ;
END ;
ELSE
BEGIN
-- Extract the event information using XQuery.
-- Use XQuery to extract XML values to be inserted into the log table
INSERT INTO [dbo].[LoggedEvents] (
EventType,
EventTime,
LoginName,
UserName,
ServerName,
DatabaseName,
SchemaName,
ObjectName,
ObjectType,
TSQLCmdText
)
VALUES
(
CAST(@message_body.query('/EVENT_INSTANCE/EventType/text()') AS NVARCHAR(256)),
CAST(CAST(@message_body.query('/EVENT_INSTANCE/PostTime/text()') AS NVARCHAR(MAX)) AS DATETIME),
CAST(@message_body.query('/EVENT_INSTANCE/LoginName/text()') AS sysname),
CAST(@message_body.query('/EVENT_INSTANCE/UserName/text()') AS sysname),
CAST(@message_body.query('/EVENT_INSTANCE/ServerName/text()') AS sysname),
CAST(@message_body.query('/EVENT_INSTANCE/DatabaseName/text()') AS sysname),
CAST(@message_body.query('/EVENT_INSTANCE/SchemaName/text()') AS sysname),
CAST(@message_body.query('/EVENT_INSTANCE/ObjectName/text()') AS sysname),
CAST(@message_body.query('/EVENT_INSTANCE/ObjectType/text()') AS sysname),
CAST(@message_body.query('/EVENT_INSTANCE/TSQLCommand/CommandText/text()') AS NVARCHAR(MAX))
) ;
-- Insert the message body as XML into the loggedeventsXML table
INSERT INTO [dbo].[LoggedEventsXML] (
EventType,
EventTime,
EventData
)
VALUES
(
CAST(@message_body.query('/EVENT_INSTANCE/EventType/text()') AS NVARCHAR(256)),
CAST(CAST(@message_body.query('/EVENT_INSTANCE/PostTime/text()') AS NVARCHAR(MAX)) AS datetime),
@message_body
) ;
END ;
COMMIT TRANSACTION ;
END ;


Alter Queue

Now, we have to go back and alter the queue we created to use that procedure. Select the 'Activation' check box. The activation options are now enabled.

• Select the stored procedure, LogEventsProc
  
• Set the 'Max Queue Readers' to 2
• Select 'Execute as Self'
• Check 'Activate stored procedure'
  

Click 'OK'

Create Event Notifications

With Service Broker, we can setup event notifications to alert our logging application when certain events take place. We can choose from server, database or queue level events. In this example, we will choose the server level event 'CREATE_DATABASE' and the database level event 'CREATE_TABLE'.

Right-click 'Service Broker' in the SSMS object explorer and select 'Create Event Notifications...'


Create the server level event 'CreateDatabaseNotification'. Select the 'Server' scope. Click the '...' next to 'DDL event type' and select 'CREATE_DATABASE' event.



Click the '...' next to 'Remote service name' and select the service we created earlier, 'NotifyService'. You can leave the 'Broker service GUID' as 'current database', or browse to the Service Broker instance where the service was created.

Click 'OK'.

Follow the same steps to create the database level event, but choose the 'CREATE_TABLE' event instead.



You will now see the 2 event notifications listed in the grid below.


We are now ready to test the application. Execute a script to create a database and a table. Nothing fancy, we just want to see the results of the application.


Run 2 select statements to view the contents of the tables.

The first result set is from the relational table log and the second is the message orientated log (with the raw XML posted below it.)




<EVENT_INSTANCE>
<EventType>CREATE_TABLE</EventType>
<PostTime>2009-06-15T19:37:42.313</PostTime>
<SPID>51</SPID>
<ServerName>IRON\CARBON</ServerName>
<LoginName>sa</LoginName>
<UserName>dbo</UserName>
<DatabaseName>Application</DatabaseName>
<SchemaName>dbo</SchemaName>
<ObjectName>TestNotificationTable</ObjectName>
<ObjectType>TABLE</ObjectType>
<TSQLCommand>
<SetOptions ANSI_NULLS="ON" ANSI_NULL_DEFAULT="ON" ANSI_PADDING="ON" QUOTED_IDENTIFIER="ON" ENCRYPTED="FALSE" />
<CommandText>CREATE TABLE TestNotificationTable (Column1 int, Cloumn1 int)</CommandText>
</TSQLCommand>
</EVENT_INSTANCE>