thanx for your answer. I got it to work. Actually it was a problem with the
ownership of the database. Changing it to 'sa' solved it. But I'm not quite
sure why. It's some security context problem.
> Hard to say what it might be, perhaps trustworty, master key or something
> else (I'm no SB expert...). Here's the relevant part of the script that I
> got working:
>
> ALTER DATABASE AdventureWorks SET ENABLE_BROKER
>
> ALTER DATABASE AdventureWorks SET TRUSTWORTHY ON
>
> USE AdventureWorks
>
> CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'P@ssw0rd'
>
> -- AuditLog table for storing event notification message information
>
> IF OBJECT_ID('dbo.AuditLog') IS NOT NULL DROP TABLE dbo.AuditLog
>
> CREATE TABLE AuditLog
>
> (Command NVARCHAR(1000),
>
> PostTime NVARCHAR(24),
>
> HostName NVARCHAR(100),
>
> LoginName NVARCHAR(100)
>
> )
>
> GO
>
> CREATE QUEUE NotifyQueue
>
> GO
>
>
>
> -- create a service on the queue that references the event notifications
> contract
>
> CREATE SERVICE NotifyService
>
> ON QUEUE NotifyQueue
>
> ([
http://schemas.microsoft.com/SQL/Notifications/PostEventNotification])
>
> GO
>
>
>
> -- create a route on the service to define the address to
>
> -- which Service Broker sends messages for the service
>
> CREATE ROUTE NotifyRoute
>
> WITH SERVICE_NAME = 'NotifyService', ADDRESS = 'LOCAL'
>
> GO
>
>
>
> -- create the database event notification
>
> CREATE EVENT NOTIFICATION NotifyCREATE_TABLE
>
> ON DATABASE
>
> FOR CREATE_TABLE
>
> TO SERVICE 'NotifyService', 'current database'
>
>
>
> -- create a table to fire the NotifyCREATE_TABLE event
>
> CREATE TABLE T1 (col1 int)
>
> GO
>
> --Check the physical queue table
>
> SELECT * FROM dbo.NotifyQueue
>
>
> --
> Tibor Karaszi, SQL Server MVP
>
http://www.karaszi.com/sqlserver/default.asp >
http://www.solidqualitylearning.com/ > Blog:
http://solidqualitylearning.com/blogs/tibor/ >
>
> "Bobby Henningsen" <bobhen@mail.dk> wrote in message
> news:erPG6a9OGHA.3936@TK2MSFTNGP12.phx.gbl...
>> I've made a demo showing the use of an Event Notification. It has worked
>> a 100 times before but all of a sudden it doesn't !!?? Here's the code
>> and everything else is in place. I've just installed the server and it's
>> a Developåer Edition and I'm using the good old Northwind Database. It
>> has the right level and I've enabled the Service Broker. So what is
>> wrong. Am I missing some options that has to be set ??? it can't be the
>> code. Worked before. Changing the code to use the server_guid doesn't
>> work either. My queue is empty!!
>>
>> Create Event Notification NotifyDeadlock
>> On Server
>>
>> For Deadlock_Graph
>>
>> To Service 'NotifyService','current database'
>>
>> Regards :)
>> Bobby Henningsen
>>
>
