Enabling Service Broker in SQL Server 2008

asp.net linq caching datacontext sqlcachedependency
JGilmartin picture JGilmartin · May 3, 2010 · Viewed 79.4k times · Source

I am integrating SqlCacheDependency to use in my LinqToSQL datacontext.

I am using an extension class for Linq querys found here - http://code.msdn.microsoft.com/linqtosqlcache

I have wired up the code and when I open the page I get this exception -

"The SQL Server Service Broker for the current database is not enabled, and as a result query notifications are not supported. Please enable the Service Broker for this database if you wish to use notifications."

its coming from this event in the global.asax

        protected void Application_Start()
    {
        RegisterRoutes(RouteTable.Routes);
        //In Application Start Event
        System.Data.SqlClient.SqlDependency.Start(new dataContextDataContext().Connection.ConnectionString);

    }

my question is...

  1. how do i enable Service Broker in my SQL server 2008 database? I have tried to run this query.. ALTER DATABASE tablename SET ENABLE_BROKER but it never ends and runs for ever, I have to manually stop it.

  2. once I have this set in SQL server 2008, will it filter down to my DataContext, or do I need to configure something there too ?

thanks for any help

Truegilly

Answer

Matt picture Matt · Jan 24, 2011

In case anyone else is looking for a solution to this problem, the following command worked great for me. It releases all other connections to the database instead of waiting.

ALTER DATABASE [DBNAME] SET ENABLE_BROKER WITH ROLLBACK IMMEDIATE

Related questions

Sequence contains more than one element

I'm having some issues with grabbing a list of type "RhsTruck" through Linq and getting them to display. RhsTruck just has properites Make, Model, Serial etc... RhsCustomer has properties CustomerName, CustomerAddress, etc... I keep getting the error "Sequence contains more …

Read More
Cannot convert lambda expression to type 'string' because it is not a delegate type

I am using a LINQ lambda expression like so: int Value = 1; qryContent objContentLine; using (Entities db = new Entities()) { objContentLine = (from q in db.qryContents where q.LineID == Value orderby q.RowID descending select q).FirstOrDefault(); } However, I am getting the …

Read More
How to convert DataTable to class Object?

I have already developed an application which returns DataTable everywhere. Now my client wants to convert (use some part using service stack), so I need to return DTO (objects) in my application. I don't want to change my existing stored …

Read More