servicebroker.sql

< SQL Server2005程序设计>

-- Enable Service Broker
ALTER DATABASE AdventureWorks SET ENABLE_BROKER
USE AdventureWorks

-- Create Message Types
CREATE MESSAGE TYPE [PicklistRequest]
	VALIDATION = NONE;
CREATE MESSAGE TYPE [PicklistResponse]
	VALIDATION = NONE;

-- Create Contract
CREATE CONTRACT [PicklistContract]
	(
		[PicklistRequest] SENT BY INITIATOR,
		[PicklistResponse] SENT BY TARGET
	);

-- Create Queues
CREATE QUEUE [WarehouseQueue];
CREATE QUEUE [SalesOfficeQueue];

-- Create Services
CREATE SERVICE [WarehouseService] 
	ON QUEUE [WarehouseQueue]
	(PicklistContract);
CREATE SERVICE [SalesOfficeService] 
	ON QUEUE [SalesOfficeQueue];

-- Create Sending Service Program
CREATE PROCEDURE SendPicklistRequest
	@Message NVARCHAR(MAX)
AS 
SET NOCOUNT ON;
DECLARE @ConversationHandle UNIQUEIDENTIFIER
BEGIN TRANSACTION
BEGIN DIALOG CONVERSATION @ConversationHandle
	FROM SERVICE [SalesOfficeService]
	TO SERVICE 'WarehouseService'
	ON CONTRACT [PicklistContract]
WITH ENCRYPTION = OFF;
SEND ON CONVERSATION @ConversationHandle 
    MESSAGE TYPE  [PicklistRequest] (@Message);
COMMIT TRANSACTION;

-- Create Receiving Service Program
CREATE PROCEDURE ReceivePicklistRequest
AS
SET NOCOUNT ON;
DECLARE @ConversationHandle UNIQUEIDENTIFIER,
		@ConversationGroupID UNIQUEIDENTIFIER,
		@MessageBody NVARCHAR(2000),
		@MessageTypeName NVARCHAR(128);
BEGIN TRANSACTION;
RECEIVE TOP(1)
	@MessageTypeName = message_type_name,         
	@ConversationHandle = conversation_handle, 
	@MessageBody = message_body
		FROM WarehouseQueue;
DECLARE @Message AS NVARCHAR(MAX);
SET @Message = 'Message received: ' +  @MessageBody;
-- Print Picklist, etc...
SEND ON CONVERSATION @ConversationHandle 
    MESSAGE TYPE  [PicklistResponse] (@Message);
END CONVERSATION @ConversationHandle;
COMMIT TRANSACTION;

-- Invoke Sending Procedure
EXEC SendPicklistRequest @Message='Picklist Request'

-- View Warehouse Queue
SELECT conversation_handle, conversation_group_id, service_name, service_contract_name, message_type_name
	FROM WarehouseQueue;

-- View Message in Warehouse Queue
SELECT CAST(message_body AS NVARCHAR(MAX)) FROM WarehouseQueue;

-- Invoke Receiving Procedure
EXEC ReceivePicklistRequest 

-- View Warehouse Queue (to verify message has been processed)
SELECT conversation_handle, conversation_group_id, service_name, service_contract_name, message_type_name
	FROM WarehouseQueue;

-- View Sales Office Queue
SELECT conversation_handle, conversation_group_id, service_name,  
   service_contract_name, message_type_name 
	FROM SalesOfficeQueue;

-- View Message in Sales Office Queue
SELECT CAST(message_body AS NVARCHAR(MAX)) FROM SalesOfficeQueue;