Sending Service Broker Message on Trigger

Sample Trigger to send Service Broker message on data change

CREATE TRIGGER [Person].[TRG_Person_Contact]
ON [Person].[Contact]
FOR DELETE,INSERT,UPDATE
AS
BEGIN

SET NOCOUNT ON

DECLARE @ACT CHAR(6)
DECLARE @DEL BIT
DECLARE @INS BIT 
DECLARE @XMLDOC XML
DECLARE @XMLTEXT NVARCHAR(MAX)

DECLARE @ContactID [int]
DECLARE @NameStyle [bit]
DECLARE @Title [nvarchar] (8)
DECLARE @FirstName [nvarchar] (50)
DECLARE @MiddleName [nvarchar] (50)
DECLARE @LastName [nvarchar] (50)
DECLARE @Suffix [nvarchar] (10)
DECLARE @EmailAddress [nvarchar] (50)
DECLARE @EmailPromotion [int]
DECLARE @Phone [nvarchar] (25)
DECLARE @PasswordHash [varchar] (128)
DECLARE @PasswordSalt [varchar] (10)
DECLARE @AdditionalContactInfo [xml]
DECLARE @rowguid [uniqueidentifier]
DECLARE @ModifiedDate [datetime]

SET @DEL = 0
SET @INS = 0

IF EXISTS (SELECT TOP 1 1 FROM DELETED) SET @DEL=1
IF EXISTS (SELECT TOP 1 1 FROM INSERTED) SET @INS = 1 

IF @INS = 1 AND @DEL = 1 SET @ACT = 'UPDATE'
IF @INS = 1 AND @DEL = 0 SET @ACT = 'INSERT'
IF @DEL = 1 AND @INS = 0 SET @ACT = 'DELETE'

IF @INS = 0 AND @DEL = 0 RETURN

-- This cursor for inserted or updated records

IF @ACT = 'INSERT' OR @ACT = 'UPDATE'
BEGIN
DECLARE curINSERTED CURSOR LOCAL FAST_FORWARD
FOR
SELECT
[INSERTED].[ContactID]
,[INSERTED].[NameStyle]
,[INSERTED].[Title]
,[INSERTED].[FirstName]
,[INSERTED].[MiddleName]
,[INSERTED].[LastName]
,[INSERTED].[Suffix]
,[INSERTED].[EmailAddress]
,[INSERTED].[EmailPromotion]
,[INSERTED].[Phone]
,[INSERTED].[PasswordHash]
,[INSERTED].[PasswordSalt]
,[INSERTED].[AdditionalContactInfo]
,[INSERTED].[rowguid]
,[INSERTED].[ModifiedDate]
FROM
[INSERTED]

OPEN curINSERTED
FETCH NEXT FROM curINSERTED INTO
@ContactID
,@NameStyle
,@Title
,@FirstName
,@MiddleName
,@LastName
,@Suffix
,@EmailAddress
,@EmailPromotion
,@Phone
,@PasswordHash
,@PasswordSalt
,@AdditionalContactInfo
,@rowguid
,@ModifiedDate

WHILE @@FETCH_STATUS = 0
BEGIN

SET @XMLDOC = (
SELECT
@ACT AS ZZ_ROWACTION
,[Person.Contact].[ContactID]
,[Person.Contact].[NameStyle]
,[Person.Contact].[Title]
,[Person.Contact].[FirstName]
,[Person.Contact].[MiddleName]
,[Person.Contact].[LastName]
,[Person.Contact].[Suffix]
,[Person.Contact].[EmailAddress]
,[Person.Contact].[EmailPromotion]
,[Person.Contact].[Phone]
,[Person.Contact].[PasswordHash]
,[Person.Contact].[PasswordSalt]
,[Person.Contact].[AdditionalContactInfo]
,[Person.Contact].[rowguid]
,[Person.Contact].[ModifiedDate]
FROM 
(
SELECT
@ContactID AS [ContactID] 
,@NameStyle AS [NameStyle] 
,@Title AS [Title] 
,@FirstName AS [FirstName] 
,@MiddleName AS [MiddleName] 
,@LastName AS [LastName] 
,@Suffix AS [Suffix] 
,@EmailAddress AS [EmailAddress] 
,@EmailPromotion AS [EmailPromotion] 
,@Phone AS [Phone] 
,@PasswordHash AS [PasswordHash] 
,@PasswordSalt AS [PasswordSalt] 
,@AdditionalContactInfo AS [AdditionalContactInfo] 
,@rowguid AS [rowguid] 
,@ModifiedDate AS [ModifiedDate] 
) AS [Person.Contact]
FOR XML AUTO, TYPE
)

SET @XMLTEXT = CONVERT(NVARCHAR(MAX),@XMLDOC)

IF @XMLTEXT IS NOT NULL
BEGIN 
--send the message
exec USP_SendBrokerMessage
@SourceService = 'AdventureWorksSourceQueueSourceService',
@TargetService = N'AdventureWorksTargetQueueTargetService',
@MessageContract = 'http://www.servicebroker.com/Contract',
@MessageType = 'http://www.servicebroker.com/SendType',
@MessageContent = @XMLTEXT;
END

FETCH NEXT FROM curINSERTED INTO
@ContactID
,@NameStyle
,@Title
,@FirstName
,@MiddleName
,@LastName
,@Suffix
,@EmailAddress
,@EmailPromotion
,@Phone
,@PasswordHash
,@PasswordSalt
,@AdditionalContactInfo
,@rowguid
,@ModifiedDate

END

CLOSE curINSERTED
DEALLOCATE curINSERTED
END

-- This cursor for deleted records

IF @ACT = 'DELETE'
BEGIN
DECLARE curDELETED CURSOR LOCAL FAST_FORWARD
FOR
SELECT
[DELETED].[ContactID]
,[DELETED].[NameStyle]
,[DELETED].[Title]
,[DELETED].[FirstName]
,[DELETED].[MiddleName]
,[DELETED].[LastName]
,[DELETED].[Suffix]
,[DELETED].[EmailAddress]
,[DELETED].[EmailPromotion]
,[DELETED].[Phone]
,[DELETED].[PasswordHash]
,[DELETED].[PasswordSalt]
,[DELETED].[AdditionalContactInfo]
,[DELETED].[rowguid]
,[DELETED].[ModifiedDate]
FROM
[DELETED]

OPEN curDELETED
FETCH NEXT FROM curDELETED INTO
@ContactID
,@NameStyle
,@Title
,@FirstName
,@MiddleName
,@LastName
,@Suffix
,@EmailAddress
,@EmailPromotion
,@Phone
,@PasswordHash
,@PasswordSalt
,@AdditionalContactInfo
,@rowguid
,@ModifiedDate

WHILE @@FETCH_STATUS = 0
BEGIN

SET @XMLDOC = (
SELECT
@ACT AS ZZ_ROWACTION
,[Person.Contact].[ContactID]
,[Person.Contact].[NameStyle]
,[Person.Contact].[Title]
,[Person.Contact].[FirstName]
,[Person.Contact].[MiddleName]
,[Person.Contact].[LastName]
,[Person.Contact].[Suffix]
,[Person.Contact].[EmailAddress]
,[Person.Contact].[EmailPromotion]
,[Person.Contact].[Phone]
,[Person.Contact].[PasswordHash]
,[Person.Contact].[PasswordSalt]
,[Person.Contact].[AdditionalContactInfo]
,[Person.Contact].[rowguid]
,[Person.Contact].[ModifiedDate]
FROM 
(
SELECT
@ContactID AS [ContactID] 
,@NameStyle AS [NameStyle] 
,@Title AS [Title] 
,@FirstName AS [FirstName] 
,@MiddleName AS [MiddleName] 
,@LastName AS [LastName] 
,@Suffix AS [Suffix] 
,@EmailAddress AS [EmailAddress] 
,@EmailPromotion AS [EmailPromotion] 
,@Phone AS [Phone] 
,@PasswordHash AS [PasswordHash] 
,@PasswordSalt AS [PasswordSalt] 
,@AdditionalContactInfo AS [AdditionalContactInfo] 
,@rowguid AS [rowguid] 
,@ModifiedDate AS [ModifiedDate] 
) AS [Person.Contact]
FOR XML AUTO, TYPE
)

SET @XMLTEXT = CONVERT(NVARCHAR(MAX),@XMLDOC)

IF @XMLTEXT IS NOT NULL
BEGIN 
--send the message
exec USP_SendBrokerMessage
@SourceService = 'AdventureWorksSourceQueueSourceService',
@TargetService = N'AdventureWorksTargetQueueTargetService',
@MessageContract = 'http://www.servicebroker.com/Contract',
@MessageType = 'http://www.servicebroker.com/SendType',
@MessageContent = @XMLTEXT;
END

FETCH NEXT FROM curDELETED INTO
@ContactID
,@NameStyle
,@Title
,@FirstName
,@MiddleName
,@LastName
,@Suffix
,@EmailAddress
,@EmailPromotion
,@Phone
,@PasswordHash
,@PasswordSalt
,@AdditionalContactInfo
,@rowguid
,@ModifiedDate

END

CLOSE curDELETED
DEALLOCATE curDELETED
END

END
go

USP_ReceiveResponseAndCloseConversation

Procedure to tidy receiving & responding to a service broker message.
Requires TBL_ServiceBrokerAudit for optional message auditing. -

CREATE PROCEDURE [dbo].[USP_ReceiveAndRespondBrokerMessage]
@Queue NVARCHAR(200),
@MessageType NVARCHAR(200),
@ResponseType NVARCHAR(200),
@ResponseMessage NVARCHAR(4000),
@Audit BIT

AS

BEGIN

DECLARE @RecvReqDlgHandle UNIQUEIDENTIFIER;
DECLARE @RecvReqMsg XML;
DECLARE @RecvReqMsgName sysname;
DECLARE @SqlString NVARCHAR(MAX);
DECLARE @Parameters NVARCHAR(MAX);

SET NOCOUNT ON

BEGIN TRANSACTION;
-- Receive Message

SET @Parameters = ' @RecvReqDlgHandle UNIQUEIDENTIFIER OUTPUT,
       @RecvReqMsg XML OUTPUT,
       @RecvReqMsgName NVARCHAR(MAX) OUTPUT'

SET @SqlString = ' WAITFOR
    ( RECEIVE TOP(1)
     @RecvReqDlgHandle = conversation_handle,
     @RecvReqMsg = message_body,
     @RecvReqMsgName = message_type_name
      FROM [' + @Queue + ']
    ), TIMEOUT 1000;'

EXEC sp_executesql
@SqlString,
@Parameters,
@RecvReqDlgHandle = @RecvReqDlgHandle OUTPUT,
@RecvReqMsg = @RecvReqMsg OUTPUT,
@RecvReqMsgName = @RecvReqMsgName OUTPUT

IF @Audit = 1
BEGIN
INSERT INTO [dbo].[TBL_ServiceBrokerAudit]
     ([Queue]
     ,[MessageHandle]
     ,[MessageName]
     ,[MessageContent]
     ,[AuditSource])
  VALUES
     (@Queue
     ,@RecvReqDlgHandle
     ,@RecvReqMsgName
     ,@RecvReqMsg
     ,'USP_ReceiveAndRespondBrokerMessage')
END
-- Display received message
SELECT @RecvReqMsg AS ReceivedRequestMsg;

IF @RecvReqMsg IS NOT NULL
BEGIN
-- If received message is of the correct type, send response
IF @RecvReqMsgName = @MessageType

BEGIN
  SET @SqlString = ' SEND ON CONVERSATION ''' + CAST(@RecvReqDlgHandle AS NVARCHAR(MAX)) + '''
        MESSAGE TYPE
       [' + @ResponseType + '] (''' + @ResponseMessage + ''');
      END CONVERSATION ''' + CAST(@RecvReqDlgHandle AS NVARCHAR(MAX)) + ''';'

  EXEC sp_executesql @SqlString
  -- Uncomment next line to debug
  --SELECT @ResponseMessage AS SentReplyMsg;

END
ELSE
 SELECT 'Not Expected Message Type'
END
ELSE
SELECT 'No Message to Receive'
COMMIT TRANSACTION;

END
GO

Usage :

exec adventureworks.dbo.USP_ReceiveResponseAndCloseConversation
  @Queue = N'AdventureWorksSourceQueue'
 ,@Audit = 1
go

USP_ReceiveAndRespondBrokerMessage

Procedure to tidy service broker message response and conversation closure.
Requires TBL_ServiceBrokerAudit for optional message auditing.

CREATE PROCEDURE [dbo].[USP_ReceiveResponseAndCloseConversation]
@Queue NVARCHAR(200)
,@Audit BIT
AS

BEGIN

DECLARE @RecvReqDlgHandle UNIQUEIDENTIFIER;
DECLARE @RecvReqMsg XML;
DECLARE @RecvReqMsgName sysname;
DECLARE @SqlString NVARCHAR(MAX);
DECLARE @Parameters NVARCHAR(MAX);

SET NOCOUNT ON

BEGIN TRANSACTION;
-- Receive Response

SET @Parameters = ' @RecvReqDlgHandle UNIQUEIDENTIFIER OUTPUT,
@RecvReqMsg XML OUTPUT,
@RecvReqMsgName NVARCHAR(MAX) OUTPUT'

SET @SqlString = ' WAITFOR
( RECEIVE TOP(1)
@RecvReqDlgHandle = conversation_handle,
@RecvReqMsg = message_body,
@RecvReqMsgName = message_type_name
FROM [' + @Queue + ']
), TIMEOUT 1000;'



EXEC sp_executesql
@SqlString,
@Parameters,
@RecvReqDlgHandle = @RecvReqDlgHandle OUTPUT,
@RecvReqMsg = @RecvReqMsg OUTPUT,
@RecvReqMsgName = @RecvReqMsgName OUTPUT

IF @Audit = 1
BEGIN
INSERT INTO [dbo].[TBL_ServiceBrokerAudit]
([Queue]
,[MessageHandle]
,[MessageName]
,[MessageContent]
,[AuditSource])
VALUES
(@Queue
,@RecvReqDlgHandle
,@RecvReqMsgName
,@RecvReqMsg
,'USP_ReceiveResponseAndCloseConversation')
END

-- Uncomment next line to debug
-- SELECT @RecvReqMsg AS ReceivedRequestMsg;

SET @SqlString = ' END CONVERSATION ''' + CAST(@RecvReqDlgHandle AS NVARCHAR(MAX)) + ''';'
EXEC sp_executesql @SqlString


COMMIT TRANSACTION;
END
GO

Usage :

exec USP_ReceiveAndRespondBrokerMessage
@Queue = N'AdventureWorksTargetQueue'
,@MessageType = N'http://www.servicebroker.com/SendType'
,@ResponseType = N'http://www.servicebroker.com/ResponseType'
,@ResponseMessage = N'Reply Service Broker Message from AdventureWorksTarget'
,@Audit = 1
go

TBL_ServiceBrokerAudit

Audit table for Service Broker Procedures (USP_ReceiveAndRespondBrokerMessage & USP_ReceiveResponseAndCloseConversation) -

CREATE TABLE [dbo].[TBL_ServiceBrokerAudit](
 [ArbitaryID] [int] IDENTITY(1,1) NOT NULL,
 [MessageReceived] [datetime] NOT NULL CONSTRAINT [DF_TBL_ServiceBrokerAudit_MessageReceived]  DEFAULT (getdate()),
 [Queue] [nvarchar](100) NULL,
 [MessageHandle] [nvarchar](100) NULL,
 [MessageName] [nvarchar](100) NULL,
 [MessageContent] [xml] NULL,
 [AuditSource] [varchar](50) NULL
) ON [PRIMARY]

GO

USP_SendBrokerMessage

Procedure to tidy sending a service broker message -

CREATE PROCEDURE [dbo].[USP_SendBrokerMessage] 
 @SourceService NVARCHAR(200),
 @TargetService NVARCHAR(200),
 @MessageContract NVARCHAR(200),
 @MessageType NVARCHAR(200),
 @MessageContent NVARCHAR(4000)

AS

BEGIN

-- Sending a Service Broker Message
DECLARE @InitDlgHandle UNIQUEIDENTIFIER;
DECLARE @RequestMsg NVARCHAR(4000);

SET NOCOUNT ON

BEGIN TRANSACTION;

 BEGIN DIALOG @InitDlgHandle
   FROM SERVICE @SourceService
   TO SERVICE @TargetService 
   ON CONTRACT @MessageContract
   WITH ENCRYPTION = OFF;

 SELECT @RequestMsg = @MessageContent;

 SEND ON CONVERSATION @InitDlgHandle
   MESSAGE TYPE @MessageType
    (@RequestMsg);

 --Uncomment next line to debug >
 --SELECT @RequestMsg AS SentRequestMsg;

COMMIT TRANSACTION;

END
GO

Usage :

exec USP_SendBrokerMessage
@SourceService = 'AdventureWorksSourceQueueSourceService',
@TargetService = N'AdventureWorksTargetQueueTargetService',
@MessageContract = 'http://www.servicebroker.com/Contract',
@MessageType = 'http://www.servicebroker.com/SendType',
@MessageContent =  N'Initial Service Broker Message from AdventureWorks'

Service Broker Example #1 - Configuration, Send, Receive & Respond

Service Broker Example #1 - Configuration, Send, Receive & Respond

This example uses AdventureWorks and AdventureWorksTarget (an empty AdventureWorks db).

1 Initial Setup of databases -

-- Allow databases access to external object 

USE Master
ALTER DATABASE AdventureWorks SET TRUSTWORTHY ON
ALTER DATABASE AdventureWorksTarget SET TRUSTWORTHY ON

-- Enable Databases for Service Broker

ALTER DATABASE AdventureWorks SET NEW_BROKER
ALTER DATABASE AdventureWorksTarget SET NEW_BROKER

-- Needed these as had downloaded Adventureworks and user that created it was not present.
-- Good practice anyway...
ALTER AUTHORIZATION ON DATABASE::AdventureWorks TO sa;
ALTER AUTHORIZATION ON DATABASE::AdventureWorksTarget TO sa;

2 Configuration of Target DB (do before source!) -

USE AdventureWorksTarget
GO
-- Set Key for Service Broker Communication
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'PASSWORD2008'

-- Configure Message Types on target
CREATE MESSAGE TYPE [http://www.servicebroker.com/SendType]
    VALIDATION = WELL_FORMED_XML;
CREATE MESSAGE TYPE [http://www.servicebroker.com/ResponseType]
    VALIDATION = WELL_FORMED_XML;
GO

-- Configure Contract
-- Contract defines a message broker conversation
CREATE CONTRACT [http://www.servicebroker.com/Contract]
   ([http://www.servicebroker.com/SendType]
   SENT BY INITIATOR,
    [http://www.servicebroker.com/ResponseType]
   SENT BY TARGET
   );
GO

-- Configure Queue
CREATE QUEUE [AdventureWorksTargetQueue];

-- Turn Queue on
ALTER QUEUE [AdventureWorksTargetQueue]
WITH STATUS = ON

-- Configure Service
CREATE SERVICE [AdventureWorksTargetQueueTargetService]
    ON QUEUE [AdventureWorksTargetQueue]
    ([http://www.servicebroker.com/Contract]);
GO

3 Configuration of the Source DB -

USE AdventureWorks
GO

-- Set Key for Service Broker Communication
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'PASSWORD2008'

-- Configure Message Types on source
CREATE MESSAGE TYPE [http://www.servicebroker.com/SendType]
    VALIDATION = WELL_FORMED_XML;
CREATE MESSAGE TYPE [http://www.servicebroker.com/ResponseType]
    VALIDATION = WELL_FORMED_XML;
GO

-- Configure Contract
-- Contract defines a message broker conversation
CREATE CONTRACT [http://www.servicebroker.com/Contract]
   ([http://www.servicebroker.com/SendType]
   SENT BY INITIATOR,
    [http://www.servicebroker.com/ResponseType]
   SENT BY TARGET
   );
GO

-- Configure Queue
CREATE QUEUE [AdventureWorksSourceQueue];

-- Turn Queue on
ALTER QUEUE [AdventureWorksSourceQueue]
WITH STATUS = ON

-- Configure Service
CREATE SERVICE [AdventureWorksSourceQueueSourceService]
   ON QUEUE [AdventureWorksSourceQueue];
GO

4 Examining the contents of the message queues.
Run this, no records will be returned just yet though -

-- Monitoring Queue contents
select * from AdventureWorks..[AdventureWorksSourceQueue]
select * from AdventureWorksTarget..[AdventureWorksTargetQueue]

-- Where is my message? (Look at transmission_status for error information)
select transmission_status,* from AdventureWorks.sys.transmission_queue
select transmission_status,* from AdventureWorksTarget.sys.transmission_queue

5 Sending a Message -

-- Send the Message
DECLARE @InitDlgHandle UNIQUEIDENTIFIER;
DECLARE @RequestMsg NVARCHAR(100);

BEGIN TRANSACTION;

 BEGIN DIALOG @InitDlgHandle
   FROM SERVICE [AdventureWorksSourceQueueSourceService]
   TO SERVICE N'AdventureWorksTargetQueueTargetService' 
   ON CONTRACT [http://www.servicebroker.com/Contract]
   WITH ENCRYPTION = OFF;

 SELECT @RequestMsg =
    N'Initial Service Broker Message from AdventureWorks';

 SEND ON CONVERSATION @InitDlgHandle
   MESSAGE TYPE [http://www.servicebroker.com/SendType]
    (@RequestMsg);

 SELECT @RequestMsg AS SentRequestMsg;

COMMIT TRANSACTION;
GO

-- Examine the queues to see the message just transmitted in the target one >
select * from AdventureWorks..[AdventureWorksSourceQueue]
select * from AdventureWorksTarget..[AdventureWorksTargetQueue]

6 Read & Respond to the message -

-- Read the message on the target server and respond to it
USE AdventureWorksTarget

DECLARE @RecvReqDlgHandle UNIQUEIDENTIFIER;
DECLARE @RecvReqMsg NVARCHAR(100);
DECLARE @RecvReqMsgName sysname;

BEGIN TRANSACTION;

 WAITFOR
 ( RECEIVE TOP(1)
  @RecvReqDlgHandle = conversation_handle,
  @RecvReqMsg = message_body,
  @RecvReqMsgName = message_type_name
   FROM [AdventureWorksTargetQueue]
 ), TIMEOUT 1000;

 SELECT @RecvReqMsg AS ReceivedRequestMsg;

 IF @RecvReqMsgName =
    N'http://www.servicebroker.com/SendType'
 BEGIN
   DECLARE @ReplyMsg NVARCHAR(100);
   SELECT @ReplyMsg =
   N'Reply Service Broker Message from AdventureWorksTarget';
  
   SEND ON CONVERSATION @RecvReqDlgHandle
     MESSAGE TYPE
    [http://www.servicebroker.com/ResponseType] (@ReplyMsg);

   END CONVERSATION @RecvReqDlgHandle;
 END

 SELECT @ReplyMsg AS SentReplyMsg;

COMMIT TRANSACTION;
GO

-- Examine the queues to see the response message back on the source queue, 
-- along with an EndDialog message for it. >
-- It is no longer in the target queue.
select * from AdventureWorks..[AdventureWorksSourceQueue]
select * from AdventureWorksTarget..[AdventureWorksTargetQueue]

7 Send message response & close the conversation -

-- read the response on the sender & close conversation.
use AdventureWorks
go

DECLARE @RecvReplyMsg NVARCHAR(100);
DECLARE @RecvReplyDlgHandle UNIQUEIDENTIFIER;

BEGIN TRANSACTION;

WAITFOR
( RECEIVE TOP(1)
@RecvReplyDlgHandle = conversation_handle,
@RecvReplyMsg = message_body
FROM [AdventureWorksSourceQueue]
), TIMEOUT 1000;

END CONVERSATION @RecvReplyDlgHandle;

-- Display recieved request.
SELECT @RecvReplyMsg AS ReceivedReplyMsg;

COMMIT TRANSACTION;
GO

-- Examine the queues once more.
-- Having verified receipt of the message. the Source queue is empty again.
select * from AdventureWorks..[AdventureWorksSourceQueue]
select * from AdventureWorksTarget..[AdventureWorksTargetQueue]

SQL 101 : Service Broker

Quick Facts about Service Broker -

Service Broker is a sql server message queuing system introduced in sql 2005 which provides guaranteed delivery.

Messages can be based in 1 sql instance or go between instances via tcp/ip.

XML is preferred message content.

Service Broker Objects -

• message type - specifies validation of the xml message
• contract - defines that a message direction of travel for message types
• queue - store of messages
• service - ties contracts & queues together, a wrapper for conversations.

MSDN Service Broker Forums
http://forums.microsoft.com/MSDN/ShowForum.aspx?ForumID=91&SiteID=1
Service Broker vs MSMQ
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=2489857&SiteID=1

Enabling Service Broker -

USE MASTER;
GO

ALTER DATABASE databasename SET ENABLE_BROKER;
GO

USE databasename ;
GO