The SQL Server Service Broker for the current database is not enabled, and as a result query notifications are not supported

Recently I have faced this problem while I am accessing Sqlserver from my application.

I got this problem because of Service Broker has been disabled for my database. By following below steps I have resolved my problem.

-- Enabling Service Broker --

Depending on how your model database is set up, Service Broker may be disabled by default when you create a new database. Or, you may want to enable it in a database that already exists but in which it is not enabled.

Service Broker is turned on or off on a per-database basis, and you can find out its current state for your database by querying the sys.databases catalog view.

Turning on Service Broker is easy enough, but there is a slight twist: there must either be zero users connected to the database, or you must be the only user connected, when you actually flip the switch. ALTER DATABASE can help us in that regard, but it doesn't change the fact that somehow, those users have to go.

--Let's find out if Service Broker is turned on. SELECT is_broker_enabled  FROM sys.databases  WHERE name = 'DataBase Name'

If result is 0 means your service broker not enabled, by running following query we can enable Service Broker.

ALTER DATABASE DatabaseName SET ENABLE_BROKER  WITH ROLLBACK IMMEDIATE

"Service Broker is a feature in Microsoft SQL Server, internal or external processes can send and receive guaranteed, asynchronous messages by using extensions to Transact-SQL Data Manipulation Language (DML). Messages can be sent to a queue in the same database as the sender, to another database in the same SQL Server instance, or to another SQL Server instance either on the same server or on a remote server."