ch16.sql

SQLServer2005基础教程,清华大学出版社

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

 


IF NOT EXISTS( 
	SELECT * FROM sys.databases 
	WHERE name = 'ETravelCom' AND is_broker_enabled = 1) 
BEGIN 
	ALTER DATABASE ETravelCom SET ENABLE_BROKER 
END 
 
USE ETravelCom 
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 ETravelCom
SELECT *
FROM dbo.ReceiverQueueSample


SELECT *
FROM dbo.SenderQueueSample




USE ETravelCom 
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 ETravelCom  
SELECT *  
FROM ReceiverQueueSample 



USE ETravelCom  
SELECT CAST(message_body AS NVARCHAR(MAX))   
FROM ReceiverQueueSample 

USE ETravelCom 
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