Enabling Service Broker in SQL Server 2008

Question

Hello,

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 1

ok here is how to do this is yours is disabled or you 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 Escots_DB SET ENABLE_BROKER