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 
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.

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
*/
1.
2.
3.
4.
5.
6.
7.
8.

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
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.

-- 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
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
35.
36.
37.
38.
39.
40.
41.
42.
43.
44.
45.
46.
47.
48.

----使用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命令的主体的相同的权限来执行
1.
2.
3.
4.
5.
6.

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); 
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.

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