Sunday, February 24, 2013

Enabling SQL Server Service Broker

Below is the common script we used to enable the Service Broker in SQL Server 2008.

ALTER DATABASE <DatabaseName> SET ENABLE_BROKER;

Example:


ALTER DATABASE Northwind SET ENABLE_BROKER;

If you feel your database has an outstanding open connection, you have to clear it first before running the script. For you to terminate all connection, you have to set the current database to be single-user and call the rollback keyword to terminate it all. After executing the script, set back the database to be a multi-user database.

Example script:

ALTER DATABASE Northwind SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
GO
ALTER DATABASE Northwind SET ENABLE_BROKER;
GO
ALTER DATABASE Northwind SET MULTI_USER;
GO

Microsoft Documentation: Please visit this link for more information about SQL Server Service Broker.

No comments:

Post a Comment

Place your comments and ideas