SQLServer实战经验分享－－ServiceBroker安全配置和使用示例

核心提示： 定义消息类型CREATEMESSAGETYPE[http://Samples/SQL/ServiceBroker/msgOperation]VALIDATION=VALID_XMLWITHSCHEMACOLLECTION[http://Samples/SQL/ServiceBroker/m

定义消息类型

CREATEMESSAGETYPE[http://Samples/SQL/ServiceBroker/msgOperation]
VALIDATION=VALID_XMLWITHSCHEMACOLLECTION
[http://Samples/SQL/ServiceBroker/msgOperationSchema];

定义消息契约　

CREATECONTRACT[http://Samples/SQL/ServiceBroker/msgOperationContract]
(
　　[http://Samples/SQL/ServiceBroker/msgOperation]
　　SENTBYINITIATOR
);

初始方

定义队列

CREATEQUEUEmsgOperationInitQueue
WITH
STATUS=ON,
RETENTION=OFF
GO

定义初始服务

CREATESERVICE[http://Samples/SQL/ServiceBroker/msgOperationInitService]
ONQUEUEmsgOperationInitQueue
([http://Samples/SQL/ServiceBroker/msgOperationContract]);
GO

定义初始存储过程

CREATEPROCEDUREdbo.usp_msgOperation_SET
　　@msgIdint,
　　@msgContentnvarchar(2000)
AS
　　declare@message_bodyasxml([http://Samples/SQL/ServiceBroker/msgOperationSchema]);
　　declare@dialogasuniqueidentifier;
　　
　　--填充消息体
　　SET@message_body='<msgOperation>
　　<msgId>'+cast(@msgIdasvarchar)+'</msgId>
　　<msgContent>'+@msgContent+'</msgContent>
　　</msgOperation>';
　　
　　BEGINDIALOG@dialog
　　　　FROMSERVICE[http://Samples/SQL/ServiceBroker/msgOperationInitService]
　　　　TOSERVICE'http://Samples/SQL/ServiceBroker/msgOperationProcessService'
　　　　ONCONTRACT[http://Samples/SQL/ServiceBroker/msgOperationContract];
　　　　--WITHENCRYPTION=OFF,LIFETIME=3600;
　　--发送消息
　　SENDONCONVERSATION@dialog
　　　　MESSAGETYPE[http://Samples/SQL/ServiceBroker/msgOperation](@message_body);
　　ENDCONVERSATION@dialog;
GO