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
Developer 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
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/Not...ntNotification])
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 Developer 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/Not...ntNotification])
> 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...
>