The SQL Server Service Broker for the current database is not enabled -when using SqlCacheDependency

I was in the process of using SqlSiteMapProvider class today by jeff which is a new feature of ASP.NET 2.0 and i have always wanted to try this out
My main goal is to have a database driven site navigation interface with a securitytrimming feature which i know is possible

But after running the sql script to insert some sample tables to the DB and setting upt the project in VS 2005.
Ran F5 to run a sample page  and bingo error:

The SQL Server Service Broker for the current database is not enabled

After doing some search i saw a post that said you just have to run the below from query analyzer:

ALTER DATABASE DatabaseName SET ENABLE_BROKER;

But the query kept running for minutes and never stopped.So i taught there must be some conflicts.
So i started looking for a way to force the close off conflicting sessions.

So i ran
ALTER DATABASE DatabaseName SET NEW_BROKER WITH ROLLBACK IMMEDIATE;
and then
ALTER DATABASE Databasename SET ENABLE_BROKER;

and then the query ran successfully

make sure you run:

SELECT is_broker_enabled FROM sys.databases WHERE name = 'DatabaseName'

to get the correct value : is_broker_enabled = 1

Hope that helps

 

Print | posted on Wednesday, October 15, 2008 6:18 PM

Feedback

# re: The SQL Server Service Broker for the current database is not enabled -when using SqlCacheDependency

Left by Paul at 4/24/2009 7:14 AM
Gravatar Thanks a lot for posting the solution, saved me a lot of time!

# re: The SQL Server Service Broker for the current database is not enabled -when using SqlCacheDependency

Left by Nelly at 6/10/2009 3:44 PM
Gravatar Thank you!

# re: The SQL Server Service Broker for the current database is not enabled -when using SqlCacheDependency

Left by DotNetGuts at 8/14/2009 6:34 AM
Gravatar You are awesome.

That was really helpful.

Many Thanks.

# re: The SQL Server Service Broker for the current database is not enabled -when using SqlCacheDependency

Left by Jason Frain at 11/12/2009 1:00 PM
Gravatar Thank you very much. Its very kind of you to share this information.

# re: The SQL Server Service Broker for the current database is not enabled -when using SqlCacheDependency

Left by snopbear at 11/24/2009 6:06 AM
Gravatar thanks from my heart it's very helpfull

# re: The SQL Server Service Broker for the current database is not enabled -when using SqlCacheDependency

Left by Itay Blustein at 1/13/2010 6:46 AM
Gravatar Thanks a lot!!!
Finally someone who's answer is short and good!
g'day!

# re: The SQL Server Service Broker for the current database is not enabled -when using SqlCacheDependency

Left by Magnus at 2/5/2010 4:40 AM
Gravatar Cheers for the straight to the point post, saved me loads of time :)

# re: The SQL Server Service Broker for the current database is not enabled -when using SqlCacheDependency

Left by Better Gnomes and Gardens at 4/7/2010 4:27 PM
Gravatar Very cool. Worked like a charm

# re: The SQL Server Service Broker for the current database is not enabled -when using SqlCacheDependency

Left by Ryan White at 5/15/2010 5:16 PM
Gravatar Thank you. It worked great and was quick and easy to follow.

# re: The SQL Server Service Broker for the current database is not enabled -when using SqlCacheDependency

Left by dumbo167 at 6/14/2010 1:25 PM
Gravatar It's awesome. Thanks

# re: The SQL Server Service Broker for the current database is not enabled -when using SqlCacheDependency

Left by someguy at 7/29/2011 7:35 AM
Gravatar You're a boss.

# re: The SQL Server Service Broker for the current database is not enabled -when using SqlCacheDependency

Left by Steve at 8/16/2012 9:07 AM
Gravatar Thanks worked great

# re: The SQL Server Service Broker for the current database is not enabled -when using SqlCacheDependency

Left by Christine at 1/22/2014 9:56 PM
Gravatar I had the error message, but when I ran the Enable broker command it said it was already enabled. Your post to set the new broker did the trick.
Thanks - great work.

Your comment:





 

Copyright © Patrick.O. Ige

Design by Bartosz Brzezinski

Design by Phil Haack Based On A Design By Bartosz Brzezinski