BROKER_QUEUE_DISABLED problems

I have created a queue and service all in the same database as follows:

CREATE QUEUE ODS.[Queue Watch];

CREATE SERVICE [Queue Watch Service]

ON QUEUE [Queue Watch];

Next I created an event Notification as:

CREATE EVENT NOTIFICATION [OrderQueueDisabled]

ON QUEUE [ODS].[Order Process Queue]

FOR BROKER_QUEUE_DISABLED

TO SERVICE 'Queue Watch Service', 'current database';

When I get the queue to disable ([ODS].[Order Process Queue] ) by setting the activated stored procedure to do a 'Select 1/0', I don't get a message in the ODS.[Queue Watch] queue. There is nothing returned from any of the following queries:

Select * from sys.transmission_queue

SELECT * FROM sys.server_event_notifications

Select * from [ODS].[Queue Watch]

select * from sys.dm_qn_subscriptions

All of this is running the same database.

.

The rollback handler is a "poison message detection" mechanism. It detects rollbacks after a message has been RECEIVEd from a queue. It should not be understood as an "activated stored proc abort detection" mechanism.

In your case, you aborted having never RECEIVEd a message so the mechanism did not fire.

The same mechanism works for both activated stored procs and external applications that are written using ADO, OLEDB, ODBC, etc.

|||Sorry I didn't point that out but I did recieve a message and it ran until the queue was disabled (5 times). Shouldn't the Notification show up in the Dynamic Managed view regardless?|||

Start the profiler and check (ie. turn on) the broker monitoring events. You should see a trace message like this:

EventClass = Broker:Message Undeliverable

This message could not be delivered because the targeted service does not support the service contract. Targeted service: 'Queue Watch Service', service contract:
'http://schemas.microsoft.com/SQL/Notifications/PostEventNotification'.

If you subscribe to one of SQL Server events, the targetted service, in your case the "Queue Watch Service", must support the message contract used by SQL Server.

Change your CREATE SERVICE to look like this:

create SERVICE [Queue Watch Service]

ON QUEUE ODS.[Queue Watch]

([http://schemas.microsoft.com/SQL/Notifications/PostEventNotification]);

|||

Btw, your original code snippet included several "SELECTs ..." at the end. I'm guessing you were using one (both?) of the following to confirm that your CREATE EVENT worked:

SELECT * FROM sys.server_event_notifications

select * from sys.dm_qn_subscriptions

Neither of them will contain it. sys.dm_qn_subscription is a view that contains rows (subscriptions) resulting from usage of the query notifications feature. sys.server_event notifications is a view that contains rows resulting from CREATE EVENT calls, but for server-scoped events. The particular event you are subscribing to receive (BROKER_QUEUE_DISABLED) is a database-scoped event. The view that will contain a row representing this CREATE EVENT call is:

select * from sys.event_notifications

|||That worked. Thanks Gerald. Sorry for the long delay. We all need a vacation now and then.