1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
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
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
|
--------------------------------------------------------------------
-- Summary: Script for EventLogging sample.
--
-- This file is part of the Microsoft SQL Server Code Samples.
-- Copyright (C) Microsoft Corporation. All Rights reserved.
-- This source code is intended only as a supplement to Microsoft
-- Development Tools and/or on-line documentation. See these other
-- materials for detailed information regarding Microsoft code samples.
--
-- THIS CODE AND INFORMATION ARE PROVIDED "AS IS" WITHOUT WARRANTY OF
-- ANY KIND, EITHER EXPRESSED OR IMPLIED, INCLUDING BUT NOT LIMITED TO
-- THE IMPLIED WARRANTIES OF MERCHANTABILITY AND/OR FITNESS FOR A
-- PARTICULAR PURPOSE.
--------------------------------------------------------------------
-- This script sets up a simple event logging sample in AdventureWorks.
-- The sample receives event notifications and logs information from the
-- notifications into two tables.
--------------------------------------------------------------------
-- Activate Service Broker message delivery in AdventureWorks.
-- Activate Service Broker message delivery in AdventureWorks. A Beta 2
-- limitation of the setup procedure deactivates message delivery during installation.
use master ;
GO
-- If Service Broker message delivery is not active in AdventureWorks, alter the
-- database to make message delivery active.
IF NOT EXISTS (SELECT * FROM sys.databases
WHERE name = 'AdventureWorks'
AND is_broker_enabled = 1)
BEGIN
ALTER DATABASE AdventureWorks SET ENABLE_BROKER ;
END ;
GO
--------------------------------------------------------------------
--------------------------------------------------------------------
-- Create the EventLogging service.
-- This section creates a queue for the service to use and then
-- creates the service. Services that receive event notification
-- messages implement the contract
-- http://schemas.microsoft.com/SQL/Notifications/PostEventNotification .
use AdventureWorks ;
GO
-- If necessary, drop the service and then the queue.
IF EXISTS(SELECT * FROM sys.services WHERE name = 'NotifyService')
DROP SERVICE NotifyService ;
GO
IF OBJECT_ID('[dbo].[NotifyQueue]') IS NOT NULL AND
EXISTS(SELECT * FROM sys.service_queues WHERE name = 'NotifyQueue')
DROP QUEUE [dbo].[NotifyQueue];
GO
-- Create the queue for the service. For the time being, do not specify activation.
CREATE QUEUE NotifyQueue;
GO
-- Create a Service to receive Event Notifications. The service
-- accepts conversations on the system event notification contract.
CREATE SERVICE NotifyService
ON QUEUE NotifyQueue
(
[http://schemas.microsoft.com/SQL/Notifications/PostEventNotification]
);
GO
--------------------------------------------------------------------
--------------------------------------------------------------------
-- Create tables to store event information.
-- This part of the script creates two tables to store the contents of
-- event notification messages. The first table, LoggedEvents, stores the
-- information as relational data. The second table, LoggedEventsXML, stores
-- the event type and the time of the event as relational data, while storing
-- the event message itself as XML. This script includes both approaches
-- for demonstration purposes. A production application would likely choose
-- one or the other of the two strategies.
-- The LoggedEvents table holds event notification information in relational format.
IF OBJECT_ID('[dbo].[LoggedEvents]') IS NOT NULL
AND OBJECTPROPERTY(OBJECT_ID('[dbo].[LoggedEvents]'), 'IsTable') = 1
DROP TABLE [dbo].[LoggedEvents]
GO
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
) ;
GO
-- The LoggedEventsXML table shows another way of logging event data.
-- In this case, the event type and time are extracted from the event
-- message. The entire message is saved in the table.
IF OBJECT_ID('[dbo].[LoggedEventsXML]') IS NOT NULL
AND OBJECTPROPERTY(OBJECT_ID('[dbo].[LoggedEventsXML]'), 'IsTable') = 1
DROP TABLE [dbo].[LoggedEventsXML]
GO
CREATE TABLE [LoggedEventsXML] (
EventNumber INT IDENTITY PRIMARY KEY,
EventType NVARCHAR(256),
EventTime DATETIME,
EventData XML
) ;
GO
--------------------------------------------------------------------
--------------------------------------------------------------------
-- Create the stored procedure for processing event messages.
-- This stored procedure is the service program for the event logging application.
-- The procedure reads each event notification message from the queue and
-- logs the information from the message into both tables.
IF OBJECT_ID(N'[dbo].[LogEventsProc]') IS NOT NULL AND
OBJECTPROPERTY(OBJECT_ID(N'[dbo].[LogEventsProc]'), N'IsProcedure') = 1
DROP PROCEDURE [dbo].[LogEventsProc] ;
GO
-- Set options required for the XML data type.
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
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 -- the 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 ;
GO
--------------------------------------------------------------------
--------------------------------------------------------------------
-- Configure the queue for activation.
-- Alter the queue to add the SP we just created as an activated stored procedure
-- This will cause this procedure to run automatically when events are being received.
ALTER QUEUE [dbo].[NotifyQueue]
WITH ACTIVATION (
-- Activation turned on
STATUS = ON,
-- The name of the proc to process messages for this queue
PROCEDURE_NAME = [dbo].[LogEventsProc] ,
-- The maximum number of copies of the proc to start
MAX_QUEUE_READERS = 2,
-- Start the procedure as the user who created the queue.
EXECUTE AS SELF
)
;
--------------------------------------------------------------------
--------------------------------------------------------------------
-- Summary: Demonstrates EventLogging sample.
--
--------------------------------------------------------------------
-- This file is part of the Microsoft SQL Server Code Samples.
-- Copyright (C) Microsoft Corporation. All Rights reserved.
-- This source code is intended only as a supplement to Microsoft
-- Development Tools and/or on-line documentation. See these other
-- materials for detailed information regarding Microsoft code samples.
--
-- THIS CODE AND INFORMATION ARE PROVIDED "AS IS" WITHOUT WARRANTY OF
-- ANY KIND, EITHER EXPRESSED OR IMPLIED, INCLUDING BUT NOT LIMITED TO
-- THE IMPLIED WARRANTIES OF MERCHANTABILITY AND/OR FITNESS FOR A
-- PARTICULAR PURPOSE.
--------------------------------------------------------------------
-- This script registers event notifications and creates events.
USE AdventureWorks ;
GO
--------------------------------------------------------------------
-- Register a notification for the CREATE_DATABASE server event.
--------------------------------------------------------------------
-- Drop the notification if it already exists.
IF EXISTS(SELECT * FROM sys.server_event_notifications
WHERE name = 'CreateDatabaseNotification')
DROP EVENT NOTIFICATION CreateDatabaseNotification ON SERVER ;
GO
-- Set up the notification.
CREATE EVENT NOTIFICATION CreateDatabaseNotification
-- This notification is scoped to the server.
ON SERVER
-- This notification is for Create Database events.
FOR CREATE_DATABASE
-- Send messages for this notification to the NotifyService.
TO SERVICE 'NotifyService', 'current database';
GO
--------------------------------------------------------------------
--------------------------------------------------------------------
-- Generate CREATE_DATABASE events.
--------------------------------------------------------------------
-- Drop the database if it already exists.
IF DB_ID('TestNotification') IS NOT NULL
DROP DATABASE TestNotification;
GO
CREATE DATABASE TestNotification ;
GO
DROP DATABASE TestNotification ;
GO
CREATE Database TestNotification ;
GO
--------------------------------------------------------------------
--------------------------------------------------------------------
-- Register a notification for the CREATE_TABLE database event
-- in the TestNotification database.
--------------------------------------------------------------------
USE TestNotification
GO
-- Set up the notification.
CREATE EVENT NOTIFICATION CreateTableNotification
-- This notification is scoped to the current database
ON DATABASE
-- We want to be notified of Create Table events
FOR CREATE_TABLE
-- When a database is created, send a message to the NotifyService
TO SERVICE 'NotifyService', 'current database';
GO
--------------------------------------------------------------------
--------------------------------------------------------------------
-- Generate a CREATE_TABLE message.
--------------------------------------------------------------------
USE TestNotification
GO
Create Table [TestTable1] (Column1 int, Column2 int)
GO
USE TestNotification
GO
--------------------------------------------------------------------
--------------------------------------------------------------------
-- Display the results.
--------------------------------------------------------------------
use AdventureWorks
GO
-- Wait to allow message delivery.
waitfor DELAY '0:00:1'
-- Select from the table that stores events as relational data.
select * from [dbo].[LoggedEvents]
-- Select from the table that stores the original XML message.
select * from [dbo].[LoggedEventsXML]
GO
--------------------------------------------------------------------
--------------------------------------------------------------------
-- Summary: Removes objects created by EventLogging sample.
--
--------------------------------------------------------------------
-- This file is part of the Microsoft SQL Server Code Samples.
-- Copyright (C) Microsoft Corporation. All Rights reserved.
-- This source code is intended only as a supplement to Microsoft
-- Development Tools and/or on-line documentation. See these other
-- materials for detailed information regarding Microsoft code samples.
--
-- THIS CODE AND INFORMATION ARE PROVIDED "AS IS" WITHOUT WARRANTY OF
-- ANY KIND, EITHER EXPRESSED OR IMPLIED, INCLUDING BUT NOT LIMITED TO
-- THE IMPLIED WARRANTIES OF MERCHANTABILITY AND/OR FITNESS FOR A
-- PARTICULAR PURPOSE.
--------------------------------------------------------------------
use master ;
GO
-- Drop the database created to show CREATE_DATABASE events, if necessary.
IF DB_ID('TestNotification') IS NOT NULL
DROP DATABASE TestNotification;
GO
use AdventureWorks ;
GO
-- Drop the server event notification.
IF EXISTS(SELECT * FROM sys.server_event_notifications
WHERE name = 'CreateDatabaseNotification')
DROP EVENT NOTIFICATION CreateDatabaseNotification ON SERVER ;
GO
-- Drop the database event notification.
IF EXISTS(SELECT * FROM sys.event_notifications
WHERE name = 'CreateTableNotification')
DROP EVENT NOTIFICATION CreateTableNotification ON DATABASE ;
GO
-- Drop the service.
IF EXISTS(SELECT * FROM sys.services WHERE name = 'NotifyService')
DROP SERVICE NotifyService ;
GO
-- Drop the queue.
IF OBJECT_ID('[dbo].[NotifyQueue]') IS NOT NULL AND
EXISTS(SELECT * FROM sys.service_queues WHERE name = 'NotifyQueue')
DROP QUEUE [dbo].[NotifyQueue];
GO
-- Drop the LoggedEvents table.
IF OBJECT_ID('[dbo].[LoggedEvents]') IS NOT NULL
AND OBJECTPROPERTY(OBJECT_ID('[dbo].[LoggedEvents]'), 'IsTable') = 1
DROP TABLE [dbo].[LoggedEvents]
GO
-- Drop the LoggedEventsXML table.
IF OBJECT_ID('[dbo].[LoggedEventsXML]') IS NOT NULL
AND OBJECTPROPERTY(OBJECT_ID('[dbo].[LoggedEventsXML]'), 'IsTable') = 1
DROP |