Sunday, May 2, 2010

How to start service broker on a database

enable service broker
-- Alter the database to enable ServiceBroker functionality

DECLARE @dbname VARCHAR(30)

SELECT @dbname=db_name()

PRINT 'Setting up service broker on ' + @dbname



EXEC ('ALTER DATABASE ' + @dbname + ' SET SINGLE_USER WITH ROLLBACK IMMEDIATE')

EXEC ('ALTER DATABASE ' + @dbname + ' set NEW_BROKER')

EXEC ('ALTER DATABASE ' + @dbname + ' SET ENABLE_BROKER')

EXEC ('ALTER DATABASE ' + @dbname + ' SET MULTI_USER')

IF (SELECT is_broker_enabled FROM sys.databases WHERE name = @dbname) = 0

BEGIN

-- PRINT 'The Service broker wasn''t enabled !!'

RAISERROR ('The Service broker wasn''t enabled !!', 1, 1)

END

2 comments: