I am trying to set up query notification however when I try to create a queue and service I get the error 'There is no Service Broker active in the database. Change to a database context that contains a Service Broker.' When I run the query
select is_broker_enabled from sys.databases where name='db01'
It returns 1 which means that a broker has been enabled on the database
Here are the sql commands I am running to create my queue and service...
CREATE QUEUE DeviceChangeMessages CREATE SERVICE DeviceChangeNotifications ON QUEUE DeviceChangeMessages ([http://schemas.microsoft.com/SQL/Notifications/PostQueryNotification]);