Search code examples
asp.netlinqcachingdatacontextsqlcachedependency

Enabling Service Broker in SQL Server 2008


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


Solution

  • ok here is how to do this if yours is disabled or you need to restore a backup, which seems to disable it.

    just run this script, it will kill all the process's that a database is using (why you carnt in 2008 manually kill process's unlike 2005 is beyond me) and then set the broker

    USE master
    go
    
    DECLARE @dbname sysname
    
    SET @dbname = 'YourDBName'
    
    DECLARE @spid int
    SELECT @spid = min(spid) from master.dbo.sysprocesses where dbid = db_id(@dbname)
    WHILE @spid IS NOT NULL
    BEGIN
    EXECUTE ('KILL ' + @spid)
    SELECT @spid = min(spid) from master.dbo.sysprocesses where dbid = db_id(@dbname) AND spid > @spid
    END
    
    
    ALTER DATABASE @dbname SET ENABLE_BROKER