Quick Help Please

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 HenningsenHard 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
>|||Hi Tibor,
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.
Regards :)
Bobby Henningsen
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:Ob1zjMDPGHA.1288@.TK2MSFTNGP09.phx.gbl...
> 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
>