1st Timer Question ... I Can't Seem To Get Internal Activation To Work.

New to service broker, and my books haven't come in yet. I'm trying to figure out how Internal Activation works.

The following code seems to "work", in terms of the inline code, but it doesn't seem to be going through the activation PROCs. Is there something else I need to set or do?

TIA, Bob


USE master;

GO

-- Ensure Service Broker functionality is enabled.

ALTER DATABASE ICEBOX SET ENABLE_BROKER;

GO

USE ICEBOX;

GO

-- Drop all sample objects if present.

IF EXISTS (SELECT * FROM sys.services WHERE name = N'BobsReceiveService') DROP SERVICE BobsReceiveService;

IF EXISTS (SELECT * FROM sys.service_queues WHERE name = N'BobsReceiveQueue') DROP QUEUE BobsReceiveQueue;

IF EXISTS (SELECT * FROM sys.services WHERE name = N'BobsSendService') DROP SERVICE BobsSendService;

IF EXISTS (SELECT * FROM sys.service_queues WHERE name = N'BobsSendQueue') DROP QUEUE BobsSendQueue;

IF EXISTS (SELECT * FROM sys.service_contracts WHERE name = N'BobsContract') DROP CONTRACT BobsContract;

IF EXISTS (SELECT * FROM sys.service_message_types WHERE name = N'BobsSendMessageType') DROP MESSAGE TYPE BobsSendMessageType;

IF EXISTS (SELECT * FROM sys.service_message_types WHERE name = N'BobsReceiveMessageType') DROP MESSAGE TYPE BobsReceiveMessageType;

IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_TYPE='PROCEDURE' AND ROUTINE_NAME = 'USP_BobsReceiveQueueMonitor') DROP PROCEDURE dbo.USP_BobsReceiveQueueMonitor;

IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_TYPE='PROCEDURE' AND ROUTINE_NAME = 'USP_BobsSendQueueMonitor') DROP PROCEDURE dbo.USP_BobsSendQueueMonitor;

GO

CREATE PROCEDURE dbo.USP_BobsSendQueueMonitor

AS

DECLARE @QueueTable TABLE

(status tinyint --Status of the message. For messages returned by the RECEIVE command, the status is always 1. Messages in the queue may contain one of the following values: 0=Received message1=Ready2=Not yet complete3=Retained sent message

,queuing_order bigint --Message order number within the queue.

,conversation_group_id uniqueidentifier --Identifier for the conversation group that this message belongs to.

,conversation_handle uniqueidentifier --Handle for the conversation that this message is part of.

,message_sequence_number bigint --Sequence number of the message within the conversation.

,service_name nvarchar(512) --Name of the service that the conversation is to.

,service_id int --SQL Server object identifier of the service that the conversation is to.

,service_contract_name nvarchar(256) --Name of the contract that the conversation follows.

,service_contract_id int --SQL Server object identifier of the contract that the conversation follows.

,message_type_name nvarchar(256) --Name of the message type that describes the message.

,message_type_id int --SQL Server object identifier of the message type that describes the message.

,validation nchar(2) --Validation used for the message. E=Empty N=None X=XML

,message_body varbinary(MAX) --Content of the message.

,message_id uniqueidentifier --Unique identifier for the message.

);

-- FROM BoL

-- The WHERE clause of the RECEIVE statement may only contain search conditions that use conversation_handle or conversation_group_id.

-- The search condition may not contain any of the other columns in the queue.

-- The conversation_handle or conversation_group_id may not be an expression.

DECLARE @SendDialog_Handle uniqueidentifier;

DECLARE @SendMsg varbinary(MAX);

DECLARE @SendMsgName nvarchar(256);

BEGIN

PRINT 'SEND' + CONVERT(VARCHAR,GETDATE(),109);

SELECT TOP (1)

@SendDialog_Handle = conversation_handle

,@SendMsg = message_body

,@SendMsgName = message_type_name

FROM

BobsSendQueue;

SELECT 'SEND',@SendDialog_Handle AS conversation_handle,CAST(@SendMsg AS VARCHAR(MAX)) AS message_body,@SendMsgName AS message_type_name;

END;

GO

CREATE PROCEDURE dbo.USP_BobsReceiveQueueMonitor

AS

DECLARE @QueueTable TABLE

(status tinyint --Status of the message. For messages returned by the RECEIVE command, the status is always 1. Messages in the queue may contain one of the following values: 0=Received message1=Ready2=Not yet complete3=Retained sent message

,queuing_order bigint --Message order number within the queue.

,conversation_group_id uniqueidentifier --Identifier for the conversation group that this message belongs to.

,conversation_handle uniqueidentifier --Handle for the conversation that this message is part of.

,message_sequence_number bigint --Sequence number of the message within the conversation.

,service_name nvarchar(512) --Name of the service that the conversation is to.

,service_id int --SQL Server object identifier of the service that the conversation is to.

,service_contract_name nvarchar(256) --Name of the contract that the conversation follows.

,service_contract_id int --SQL Server object identifier of the contract that the conversation follows.

,message_type_name nvarchar(256) --Name of the message type that describes the message.

,message_type_id int --SQL Server object identifier of the message type that describes the message.

,validation nchar(2) --Validation used for the message. E=Empty N=None X=XML

,message_body varbinary(MAX) --Content of the message.

,message_id uniqueidentifier --Unique identifier for the message.

);

-- FROM BoL

-- The WHERE clause of the RECEIVE statement may only contain search conditions that use conversation_handle or conversation_group_id.

-- The search condition may not contain any of the other columns in the queue.

-- The conversation_handle or conversation_group_id may not be an expression.

DECLARE @ReceiveDialog_Handle uniqueidentifier;

DECLARE @ReceiveMsg varbinary(MAX);

DECLARE @ReceiveMsgName nvarchar(256);

BEGIN

PRINT 'RECEIVE' + CONVERT(VARCHAR,GETDATE(),109);

SELECT TOP (1)

@ReceiveDialog_Handle = conversation_handle

,@ReceiveMsg = message_body

,@ReceiveMsgName = message_type_name

FROM

BobsReceiveQueue;

SELECT 'RECEIVE',@ReceiveDialog_Handle AS conversation_handle,CAST(@ReceiveMsg AS VARCHAR(MAX)) AS message_body,@ReceiveMsgName AS message_type_name;

END;

GO

CREATE MESSAGE TYPE BobsSendMessageType VALIDATION = NONE;

CREATE MESSAGE TYPE BobsReceiveMessageType VALIDATION = NONE;

CREATE CONTRACT BobsContract

(BobsSendMessageType SENT BY INITIATOR

,BobsReceiveMessageType SENT BY TARGET

);

CREATE QUEUE BobsSendQueue

WITH ACTIVATION

(STATUS = ON

,PROCEDURE_NAME = dbo.USP_BobsSendQueueMonitor

,MAX_QUEUE_READERS = 1

,EXECUTE AS OWNER

)

;

CREATE SERVICE BobsSendService ON QUEUE BobsSendQueue;

CREATE QUEUE BobsReceiveQueue

WITH ACTIVATION

(STATUS = ON

,PROCEDURE_NAME = dbo.USP_BobsReceiveQueueMonitor

,MAX_QUEUE_READERS = 1

,EXECUTE AS OWNER

)

;

CREATE SERVICE BobsReceiveService ON QUEUE BobsReceiveQueue (BobsContract);

GO





DECLARE @BobsDialog_Handle UNIQUEIDENTIFIER;

DECLARE @SendMsg NVARCHAR(100);

SET @SendMsg = N'Hello Bob';

BEGIN TRANSACTION;

BEGIN DIALOG CONVERSATION @BobsDialog_Handle FROM SERVICE BobsSendService TO SERVICE N'BobsReceiveService' ON CONTRACT BobsContract WITH ENCRYPTION = OFF;

SEND ON CONVERSATION @BobsDialog_Handle MESSAGE TYPE BobsSendMessageType (@SendMsg);

COMMIT TRANSACTION;

SELECT 'Conversation started on Handle ' + CAST(@BobsDialog_Handle AS VARCHAR(36));

GO



DECLARE @BobsDialog_Handle UNIQUEIDENTIFIER;

DECLARE @ReceiveMsg NVARCHAR(100);

DECLARE @ResponseMsg NVARCHAR(100);

DECLARE @ReceiveMsgName SYSNAME;

SET @ResponseMsg = N'Back at ya';

BEGIN TRANSACTION;

RECEIVE TOP(1)

@BobsDialog_Handle = conversation_handle

,@ReceiveMsg = message_body

,@ReceiveMsgName = message_type_name

FROM

BobsReceiveQueue;

COMMIT TRANSACTION;

SELECT @ReceiveMsgName AS ReceiveMsgName,@ReceiveMsg AS ReceiveMsg;

SEND ON CONVERSATION @BobsDialog_Handle MESSAGE TYPE BobsReceiveMessageType (@ResponseMsg);

END CONVERSATION @BobsDialog_Handle;

GO



DECLARE @ResponseMsg NVARCHAR(100);

DECLARE @ResponseMsgName SYSNAME;

DECLARE @BobsDialog_Handle UNIQUEIDENTIFIER;

BEGIN TRANSACTION;

RECEIVE TOP(1)

@BobsDialog_Handle = conversation_handle

,@ResponseMsg = message_body

,@ResponseMsgName = message_type_name

FROM BobsSendQueue;

END CONVERSATION @BobsDialog_Handle;

COMMIT TRANSACTION;

SELECT @ResponseMsgName AS ResponseMsgName, @ResponseMsg AS ResponseMsg;

GO

IF EXISTS (SELECT * FROM sys.services WHERE name = N'BobsReceiveService') DROP SERVICE BobsReceiveService;

IF EXISTS (SELECT * FROM sys.service_queues WHERE name = N'BobsReceiveQueue') DROP QUEUE BobsReceiveQueue;

IF EXISTS (SELECT * FROM sys.services WHERE name = N'BobsSendService') DROP SERVICE BobsSendService;

IF EXISTS (SELECT * FROM sys.service_queues WHERE name = N'BobsSendQueue') DROP QUEUE BobsSendQueue;

IF EXISTS (SELECT * FROM sys.service_contracts WHERE name = N'BobsContract') DROP CONTRACT BobsContract;

IF EXISTS (SELECT * FROM sys.service_message_types WHERE name = N'BobsSendMessageType') DROP MESSAGE TYPE BobsSendMessageType;

IF EXISTS (SELECT * FROM sys.service_message_types WHERE name = N'BobsReceiveMessageType') DROP MESSAGE TYPE BobsReceiveMessageType;

GO