CODEWITHSUNDEEP
asp.netsql-serversql-server-2005
GurdeepS
GurdeepS

Reputation: 67283

SQL Server service broker reporting as off when I have written a query to turn it on

I have made a small ASP.NET website. It uses sqlcachedependency

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.

Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details: System.InvalidOperationException: 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.

Source Error:

Line 12: System.Data.SqlClient.SqlDependency.Start(connString);

This is the erroneous line in my global.asax.

However, in sql server (2005), I enabled service broker like so (I connect and run the SQL Server service when I debug my site):

ALTER DATABASE mynewdatabase SET ENABLE_BROKER with rollback immediate

And this was successful.

What am I missing? I am trying to use sql caching dependency and have followed all procedures.

Thanks

Upvotes: 1

Views: 5064

Answers (4)

Madhan N
Madhan N

Reputation: 1

You can change it by

Data Base - > Properties -> Options -> Service Broker

Upvotes: 0

Asif Huque
Asif Huque

Reputation: 21

I faced the same problem on one of our servers. this solved the issue (replace DBNAME with database name)

ALTER DATABASE DBNAME SET NEW_BROKER WITH ROLLBACK IMMEDIATE;

ALTER DATABASE DBNAME SET ENABLE_BROKER;

Finally, check whether you get 1 for is_broker_enabled flag:

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

Upvotes: 2

mrdenny
mrdenny

Reputation: 5078

Ditch the "with rollback immediate" and try running the statement again.

Does it hang? If so kill any other sessions that are in the database and are blocking the session trying to alter the database.

Here's the script I mentioned in my comment.

use master
go
declare @parent int
declare @cmd varchar(100)

set @parent = 53

while exists (select * from sys.sysprocesses where spid = @parent and blocked <> 0)
begin
    select @cmd = 'kill ' + cast(blocked as varchar(10))
    from sys.sysprocesses
    where spid = @parent 

    print @cmd

    exec (@cmd)
end

Upvotes: 0

Remus Rusanu
Remus Rusanu

Reputation: 294437

Do you use a normal SQL instance, including Express, or are you using User Instances? ie. your connection string uses "AttachDbFilename" option? On user instances because the MDF is attached each time the instance is created, the Service Broker gets disabled each time because attach and restore operations always disable the broker in the database.

Upvotes: 0

Related Questions