MS SQL Server - Quick Help Please

Author

Quick Help Please

Bobby Henningsen

2006-02-28, 8:23 pm

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','cur
rent database'

Regards :)
Bobby Henningsen

Tibor Karaszi

2006-02-28, 8:23 pm

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/No...ficatio
n])

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','cur
rent database'
>
> Regards :)
> Bobby Henningsen
>

Bobby Henningsen

2006-02-28, 8:23 pm

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@hotmai
l.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/No...ficatio
n])
>
> 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...
>

Sponsored Links