Service BrokerService Broker

Service BrokerSQL Server

SQL Server

SQL Server 2008Create Broker Priority

Service BrokerBookDistribution BookStore

2Service BrokerSQL Server

NT

3Alter DatabaseEnable_brokerTruseworthy

6Contract

— Enabling Databases for Service Broker Activity

IF NOT EXISTS (SELECT name FROM sys.databases WHERE name = BookStore)

IF NOT EXISTS (SELECT name FROM sys.databases WHERE name = BookDistribution)

ALTER DATABASE BookDistribution SET ENABLE_BROKER

ALTER DATABASE BookDistribution SET TRUSTWORTHY ON

Begin Dialog ConversationHTTP://msdn.microsoft.com/en-us/library/ms187377.aspx

SendHTTP://msdn.microsoft.com/en-us/library/ms188407.aspx

End ConversationHTTP://msdn.microsoft.com/en-us/library/ms177521.aspx

BEGIN DIALOG CONVERSATION @Conv_Handler–

FROM SERVICE [//SackConsulting/BookOrderService]

TO SERVICE //SackConsulting/BookDistributionService

ON CONTRACT [//SackConsulting/BookOrderContract];

order id=3439 customer=22 orderdate=2/15/2011

LineItem ItemNumber=1 ISBN=1-59059-592-0 Quantity=1 /

SEND ON CONVERSATION @Conv_Handler–BookDistribution

MESSAGE TYPE [//SackConsulting/SendBookOrder]

(BookOrderReceivedID int IDENTITY (1,1) NOT Null,

conversation_handle uniqueidentifier NOT Null,

conversation_group_id uniqueidentifier NOT Null,

— DECLARE @Conv_Handler uniqueidentifierDECLARE @Conv_Group uniqueidentifierDECLARE @OrderMsg xmlDECLARE @TextResponseMsg Varchar(8000)DECLARE @ResponseMsg xmlDECLARE @OrderID int;

–RECEIVE TOP(1) @OrderMsg = message_body,–TOP@Conv_Handler = conversation_handle,@Conv_Group = conversation_group_idFROM dbo.BookDistributionQueue;

— INSERT dbo.BookOrderReceived(conversation_handle, conversation_group_id, message_body)VALUES(@Conv_Handler,@Conv_Group, @OrderMsg )

— XQuerySELECT @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_HandlerMESSAGE TYPE [//SackConsulting/BookOrderReceived]

USE BookStoreGO– CREATE TABLE dbo.BookOrderConfirmation(BookOrderConfirmationID int IDENTITY (1,1) NOT Null,conversation_handle uniqueidentifier NOT Null,DateReceived datetime NOT Null DEFAULT GETDATE(),message_body xml NOT Null)

DECLARE @Conv_Handler uniqueidentifierDECLARE @Conv_Group uniqueidentifierDECLARE @OrderMsg xmlDECLARE @TextResponseMsg Varchar(8000);

RECEIVE TOP(1) @Conv_Handler = conversation_handle,@OrderMsg = message_bodyFROM dbo.BookStoreQueue

INSERT dbo.BookOrderConfirmation(conversation_handle, message_body)VALUES (@Conv_Handler,@OrderMsg );

USE BookDistributionGODECLARE @Conv_Handler uniqueidentifierDECLARE @Conv_Group uniqueidentifierDECLARE @OrderMsg xmlDECLARE @message_type_name Nvarchar(256);

RECEIVE TOP(1) @Conv_Handler = conversation_handle,@OrderMsg = message_body,@message_type_name = message_type_nameFROM dbo.BookDistributionQueue

— IF@message_type_name = /SQL/ServiceBroker/EndDialogBEGINEND CONVERSATION @Conv_Handler;END

END ConservationService Broker1SQL Server 2005 Service Broker 2SQL Server 2008 Transact-SQL Recipes: A Problem-Solution ApproachHTTP://Service BrokerService BrokerSQL Server 2008Service BrokerCREATE BROKER /en-us/library/bb934170.aspx110105HONOR_BROKER_PRIORITY–

–ALTER DATABASE BookStoreSET HONOR_BROKER_PRIORITY ON

–ALTER DATABASE BOOKDistributionSET HONOR_BROKER_PRIORITY ON

–SELECT name, is_honor_broker_priority_onFROM sys.databasesWHERE name IN (BookStore, BookDistribution)

/*name is_honor_broker_priority_onBookStore 1BookDistribution 1*/

USE BookStoreGOCREATE BROKER PRIORITY Conv_Priority_BookOrderContract_BookOrderServiceFOR CONVERSATIONSET (CONTRACT_NAME = [//SackConsulting/BookOrderContract],–LOCAL_SERVICE_NAME = [//SackConsulting/BookOrderService],–REMOTE_SERVICE_NAME = ANY,–ANY,Service BrokerPRIORITY_LEVEL = 10)–10SELECT name, priority, service_contract_id,local_service_id,remote_service_nameFROM sys.conversation_priorities cp

/*name priority service_contract_id local_service_id remote_service_nameConv_Priority_BookOrderContract_BookOrderService 10 65536 65536 Null*/sys.conversation_prioritiesHTTP://msdn.microsoft.com/zh-cn/library/bb895280%28v=sql.100%29.aspxIDsys.service_contractsHTTP://msdn.microsoft.com/en-us/library/ms184378.aspxsys.servicesHTTP://msdn.microsoft.com/en-us/library/ms174429.aspxUSE BookDistributionGO–,–

CREATE BROKER PRIORITY Conv_Priority_BookOrderContract_BookDistributionServiceFOR CONVERSATIONSET (CONTRACT_NAME = [//SackConsulting/BookOrderContract],LOCAL_SERVICE_NAME = [//SackConsulting/BookDistributionService],REMOTE_SERVICE_NAME = ANY,PRIORITY_LEVEL = 10)

USE BookStoreGOALTER BROKER PRIORITY Conv_Priority_BookOrderContract_BookOrderServiceFOR CONVERSATIONSET (REMOTE_SERVICE_NAME = //SackConsulting/BookDistributionService)–

ALTER BROKER PRIORITY Conv_Priority_BookOrderContract_BookOrderServiceFOR CONVERSATIONSET (PRIORITY_LEVEL = 9)–

–DROP BROKER PRIORITY Conv_Priority_BookOrderContract_BookOrderServiceService BrokerT-SQLService broker Create QueueHTTP://msdn.microsoft.com/en-us/library/ms190495.aspxAlter QueueHTTP://msdn.microsoft.com/en-us/library/ms189529.aspx– Creating the Bookstore Stored Procedure

USE BookDistributionGOCREATE PROCEDURE p_SB_ReceiveOrdersASDECLARE @Conv_Handler uniqueidentifierDECLARE @Conv_Group uniqueidentifierDECLARE @OrderMsg xmlDECLARE @TextResponseMsg Varchar(8000)DECLARE @ResponseMsg xmlDECLARE @Message_Type_Name Nvarchar(256);DECLARE @OrderID int;

— XACT_ABORT automatically rolls back the transaction when a runtime error occursSET XACT_ABORT ON

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

IF @Message_Type_Name = //SackConsulting/SendBookOrderBEGININSERT 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_HandlerMESSAGE TYPE [//SackConsulting/BookOrderReceived](@ResponseMsg );END

IF @Message_Type_Name = HTTP://schemas.microsoft.com/SQL/ServiceBroker/EndDialogBEGINEND CONVERSATION @Conv_Handler;ENDCOMMIT TRANGO//SackConsulting/SendBookOrderHTTP://schemas.microsoft.com/SQL/ServiceBroker/EndDialogAlter QueueCreate QueueAlter QueueDropAlter Queue—-Alter QueueALTER QUEUE dbo.BookDistributionQueueWITH ACTIION (STATUS = ON,PROCEDURE_NAME = dbo.usp_SB_ReceiveOrders,MAX_QUEUE_READERS = 2,–EXECUTE AS SELF)–Alter QueueBookStoreUse BookStoreGO

DECLARE @Conv_Handler uniqueidentifierDECLARE @OrderMsg xml;

BEGIN DIALOG CONVERSATION @conv_handlerFROM SERVICE [//SackConsulting/BookOrderService]TO SERVICE //SackConsulting/BookDistributionServiceON CONTRACT [//SackConsulting/BookOrderContract];

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

MESSAGE TYPE [//SackConsulting/SendBookOrder]dbo.SELECT conversation_handle, CAST(message_body as xml) messageA7B7FA73-5B5F-E011-8B4E-001C23FA56DD orderreceived id=3439 /

2013:—

2013:Microsoft SQL Server 2005Microsoft SQL Management Studio Package

2013:SQL Server 2005 & 2008 mastermsdb

2011:[Flash Lite] WINCE FLASH LITE 3.1 localTrusted

2011:iphone How To Inject JavaScript Functions Into a UIWebView

2011:iPhone Programming: Using a Tab Bar App with a Navigation Controller

2011:[Android] INSTALL_FAILED_INSUFFICIENT_STORAGE