7

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]);
  • Is broker enabled on ‘msdb`? – Kin Shah Jul 11 '15 at 12:40
  • Check if you have a duplicate service_broker_guid in sys.databases – Remus Rusanu Jul 11 '15 at 20:32
5

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
|improve this answer|||||

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.