Service Broker基础应用(下）

-- 设置会话优先级

--启用会话优先级选项
ALTER DATABASE BookStore
SET HONOR_BROKER_PRIORITY ON

--启用会话优先级选项
ALTER DATABASE BOOKDistribution
SET HONOR_BROKER_PRIORITY ON

--查看设置结果
SELECT name, is_honor_broker_priority_on
FROM sys.databases
WHERE name IN ('BookStore', 'BookDistribution')

/*
name is_honor_broker_priority_on
BookStore 1
BookDistribution 1
*/

USE BookStore
GO
CREATE BROKER PRIORITY Conv_Priority_BookOrderContract_BookOrderService
FOR CONVERSATION
SET (CONTRACT_NAME = [//SackConsulting/BookOrderContract],--特定的契约
LOCAL_SERVICE_NAME = [//SackConsulting/BookOrderService],--本地服务
REMOTE_SERVICE_NAME = ANY,--远程服务为ANY,即Service Broker端点的任何相关服务
PRIORITY_LEVEL = 10)--设置优先级为10 

SELECT name, priority, service_contract_id,
local_service_id,remote_service_name
FROM sys.conversation_priorities cp

/*
name priority service_contract_id local_service_id remote_service_name
Conv_Priority_BookOrderContract_BookOrderService 10 65536 65536 NULL
*/

USE BookDistribution
GO
--创建目标服务的优先级，与发起方的优先级保持一致,
--以使会话的优先级设置覆盖整个会话的生命周期

CREATE BROKER PRIORITY Conv_Priority_BookOrderContract_BookDistributionService
FOR CONVERSATION
SET (CONTRACT_NAME = [//SackConsulting/BookOrderContract],
LOCAL_SERVICE_NAME = [//SackConsulting/BookDistributionService],
REMOTE_SERVICE_NAME = ANY,
PRIORITY_LEVEL = 10)

USE BookStore
GO
ALTER BROKER PRIORITY Conv_Priority_BookOrderContract_BookOrderService
FOR CONVERSATION
SET (REMOTE_SERVICE_NAME = '//SackConsulting/BookDistributionService')
--修改远程服务名称

ALTER BROKER PRIORITY Conv_Priority_BookOrderContract_BookOrderService
FOR CONVERSATION
SET (PRIORITY_LEVEL = 9)
--设置优先级

--删除优先级设置
DROP BROKER PRIORITY Conv_Priority_BookOrderContract_BookOrderService

-- Creating the Bookstore Stored Procedure

USE BookDistribution
GO
CREATE PROCEDURE dbo.usp_SB_ReceiveOrders
AS
DECLARE @Conv_Handler uniqueidentifier
DECLARE @Conv_Group uniqueidentifier
DECLARE @OrderMsg xml
DECLARE @TextResponseMsg varchar(8000)
DECLARE @ResponseMsg xml
DECLARE @Message_Type_Name nvarchar(256);
DECLARE @OrderID int;

-- XACT_ABORT automatically rolls back the transaction when a runtime error occurs
SET XACT_ABORT ON

BEGIN TRAN;

RECEIVE TOP(1) @OrderMsg = message_body,
@Conv_Handler = conversation_handle,
@Conv_Group = conversation_group_id,
@Message_Type_Name = message_type_name
FROM dbo.BookDistributionQueue;

IF @Message_Type_Name = '//SackConsulting/SendBookOrder'
BEGIN
INSERT dbo.BookOrderReceived
(conversation_handle, conversation_group_id, message_body)
VALUES
(@Conv_Handler,@Conv_Group, @OrderMsg )
SELECT @OrderID = @OrderMsg.value('(/order/@id)[1]', 'int' )
SELECT @TextResponseMsg =
'<orderreceived id= "' +
CAST(@OrderID as varchar(10)) +
'"/>';
SELECT @ResponseMsg = CAST(@TextResponseMsg as xml);
SEND ON CONVERSATION @Conv_Handler
MESSAGE TYPE [//SackConsulting/BookOrderReceived]
(@ResponseMsg );
END

IF @Message_Type_Name = 'http://schemas.microsoft.com/SQL/ServiceBroker/EndDialog'
BEGIN
END CONVERSATION @Conv_Handler;
END
COMMIT TRAN
GO

----使用Alter Queue命令将存储过程绑定到既有的队列
ALTER QUEUE dbo.BookDistributionQueue
WITH ACTIVATION (STATUS = ON,
PROCEDURE_NAME = dbo.usp_SB_ReceiveOrders,
MAX_QUEUE_READERS = 2,--独立处理队列中不同消息的同一存储过程同时执行的最大数量
EXECUTE AS SELF)--即存储过程将以与执行Alter Queue命令的主体的相同的权限来执行

Use BookStore
GO

DECLARE @Conv_Handler uniqueidentifier
DECLARE @OrderMsg xml;

BEGIN DIALOG CONVERSATION @conv_handler
FROM SERVICE [//SackConsulting/BookOrderService]
TO SERVICE '//SackConsulting/BookDistributionService'
ON CONTRACT [//SackConsulting/BookOrderContract];

SET @OrderMsg =
'<order id="3490" customer="29" orderdate="7/22/2008">
<LineItem ItemNumber="1" ISBN="1-59059-592-0" Quantity="2" />
</order>';

SEND ON CONVERSATION @Conv_Handler
MESSAGE TYPE [//SackConsulting/SendBookOrder]
(@OrderMsg); 

SELECT conversation_handle, CAST(message_body as xml) message
FROM dbo.BookStoreQueue
/*
conversation_handle message
A7B7FA73-5B5F-E011-8B4E-001C23FA56DD <orderreceived id="3439" />
*/