No service broker active on database

Question

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]);

Answer 1

Well I think it's quite simple.

1. You just get the message that Service Broker isn't enabled. But you query the system databases, not your current Database. Just as an idea, might it be, that you run the Script for setting up the notification in your master database while you forget to change the database context to your db01 where the Service Broker is enabled?
2. Another edge case can be the msdb, recheck if it is enabled to.
3. Otherwise try to enable/disable the Service Broker in your db01 and retry.

You can use this:

ALTER DATABASE dbo1 SET DISABLE_BROKER
ALTER DATABASE dbo1 SET ENABLE_BROKER