Event Notifications Sample



The event notification sample (EventNotificationSample.sql) provides an example of how event notifications can be used in an OLTP database application. For more information, see Event Notifications (Database Engine).

Important:
Samples are provided for educational purposes only. They are not intended to be used in a production environment and have not been tested in a production environment. Microsoft does not provide technical support for these samples. Sample applications should not be connected to or used with your production SQL Server database without the permission of the system administrator.



Scenario

Event notifications can be used to monitor and respond to database activity. EventNotificationSample.sql monitors the following kinds of events:
  • CreateDatabaseNotification event notification monitors when databases are created and dropped on the server instance.
  • AuditLoginLogoutNotification event notification monitors when logins, logouts, and failed logins occur on the server instance. The event notifications send XML data about these events to the Microsoft SQL Server 2005 Service Broker service specified in the sample. The sample also defines a queue to receive the messages and a route that specifies the service address; in this case, the address is the local database. A WAITFOR clause, which is enclosed in commenting characters, instructs the server to wait 60 seconds until the service receives the message and casts it as xml. Additional scripts, which are also enclosed in commenting characters, test the event notifications by causing the events on which they are created to occur on the server instance.

Languages

Transact-SQL

Prerequisites

Before running this sample, make sure the following software is installed:
  • Microsoft SQL Server 2005 with the following components:
  • SQL Server 2005 Database Engine
  • SQL Server Management Studio
  • AdventureWorks (OLTP) sample database which is included with SQL Server 2005, and is also available at the Microsoft SQL Server Developer Center.
  • SQL Server Database Engine samples (SQLServerSample.msi). These samples are included with SQL Server 2005. You can download the latest version of the samples at the Microsoft SQL Server Developer Center.

Running the Sample

The following procedure shows how to install and run the event notification sample.
To run the event notification sample
  1. From SQL Server Management Studio, open EventNotificationSample.sql. The default installation directory is C:\Program Files\Microsoft SQL Server\90\Samples\Engine\Administration\EventNotifications\Scripts\.
  2. Click Execute. © 2007 Microsoft Corporation. All rights reserved.

Last edited Nov 6, 2008 at 10:47 PM by bonniefe, version 15

Comments

sirpadk May 16, 2007 at 12:10 PM 
Hej guys!

I cannot get this sample to work. I believe IO checked all the prerequisites to match, and I just cannot get any information when I run the script from the sample files.

Did any one had the same problem as I do?
I'm new to service broker, and I need to build a login audit table, for an SQL server 2005.

Your help will be appriciated,
Regards

sirpadk