ch14 servicebroker.sql

SQL+Server实用简明教程电子教案和实例源文件

USE ElecTravelCom
DROP MASTER KEY
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'aaaaaaaaaafjklaa2434154##$$$$$sdfjkAJKFZSDLKSDKLJ'

 
 



IF NOT EXISTS( 
	SELECT * FROM sys.databases 
	WHERE name = 'ElecTravelCom' AND is_broker_enabled = 1) 
BEGIN 
	ALTER DATABASE ElecTravelCom SET ENABLE_BROKER 
END 


 


USE ElecTravelCom 
GO 
CREATE MESSAGE TYPE ETCNoticeMessageSample 
GO 
CREATE CONTRACT ETCContractSample 
	(ETCNoticeMessageSample SENT BY INITIATOR) 
GO 
CREATE QUEUE dbo.ReceiverQueueSample 
GO 
CREATE QUEUE dbo.SenderQueueSample 
GO 
CREATE SERVICE SenderServiceSample  
	ON QUEUE dbo.SenderQueueSample(ETCContractSample) 
GO 
CREATE SERVICE ReceiverServiceSample  
	ON QUEUE dbo.ReceiverQueueSample(ETCContractSample) 
GO 

 




USE ElecTravelCom
SELECT *
FROM dbo.ReceiverQueueSample
GO



SELECT *
FROM dbo.SenderQueueSample






USE ElecTravelCom 
GO 
BEGIN TRANSACTION 
GO 
DECLARE @ETCNoticeMessage NVARCHAR(128) 
SET @ETCNoticeMessage = N'你好，这是一个测试通知'
DECLARE  @conversationID UNIQUEIDENTIFIER 
BEGIN DIALOG CONVERSATION @conversationID 
	FROM SERVICE SenderServiceSample 
	TO SERVICE 'ReceiverServiceSample' 
	ON CONTRACT ETCContractSample; 
SEND ON CONVERSATION @conversationID 
	MESSAGE TYPE ETCNoticeMessageSample(@ETCNoticeMessage) 
END CONVERSATION @conversationID 
GO 
COMMIT TRANSACTION 
GO 






USE ElecTravelCom  
SELECT *  
FROM ReceiverQueueSample 
GO







USE ElecTravelCom  
SELECT CAST(message_body AS NVARCHAR(MAX)) AS 通知内容
FROM ReceiverQueueSample 
GO





USE ElecTravelCom 
GO 
DECLARE @conversationID UNIQUEIDENTIFIER 
DECLARE @message_type_id INT 
DECLARE @message_body NVARCHAR(1000) 
DECLARE @message NVARCHAR(1000) 
WHILE (1 = 1) 
BEGIN 
	BEGIN TRANSACTION 
		WAITFOR (RECEIVE top(1) 
			@message_type_id = message_type_id, 
			@message_body = message_body, 
			@conversationID = conversation_handle 
			FROM ReceiverQueueSample), TIMEOUT 200 
		IF @@ROWCOUNT = 0 OR @@ERROR <> 0 BREAK 
		IF @message_type_id = 2 
		BEGIN  
			PRINT N'对话结束' 
			END CONVERSATION @conversationID 
		END 
		SELECT @message = '收到: ' + @message_body 
		PRINT CONVERT(NVARCHAR(100), @message) 
	COMMIT TRANSACTION 
END 
COMMIT TRANSACTION	 




SELECT transmission_status
FROM sys.transmission_queue