_ | 覦覈襦 | 豕蠏手 | 殊螳 | 譯殊碁
FrontPage › 螳覃讌/

Contents

1
2 襦蠏碁 蠍磯
3
4 ろ 蟆
5 Contract 覦 Service, Message Queue
6 Message 覲企企 襦蠏碁(伎 れ狩蟆 覃讌襯 覲企碁)
7 覃讌襯 覦 Msg 企 ロ 襦蠏碁
8 豬 覿覈 ろ
9 蟆磯


1 #

襷 Service Broker 覲企 れ螻 螳 蠏碁殊 覲 . Service Broker 螳 る 蠏碁殊朱 壱ク覓殊 覲企企 蟆 觜螻 . 願 蟲 覲伎. ク 蠏碁 殊曙 覲企企 Sender 螻, るジ讓曙 覦 Receiver 蟆.

sssb01.jpg

2 襦蠏碁 蠍磯 #

  1. Person 企 譟伎 る朱Μ 讓曙襯 譯手 覦.
  2. 襷 覦 譟伎讌 朱 豬碁覈 覃讌襯 Sender蟆 .

3 #

  1. 伎 れ狩蟆 覃讌襯 覲企碁.
  2. Person 企 覦 れ狩 譟伎覃 Msg 企 覃讌襯
  3. 襷 覦 れ狩 Person 企 譟伎讌 朱 Sender 伎蟆 豬 覿覈 覃讌襯 覲企碁.
  4. 伎 覃讌襯 蟆 覃 ろ蟯襴 襦覿 豬碁覈 覃讌襯 覦蟆 .

4 ろ 蟆 #

sssb02.jpg

IF OBJECT_ID('Msg') IS NOT NULL
	DROP TABLE Msg;

IF OBJECT_ID('Person') IS NOT NULL
	DROP TABLE Person;

CREATE TABLE Person(
	PersonName varchar(20) PRIMARY KEY
);
INSERT Person VALUES('ろ蟯襴'); --豌覿郁鍵覲語朱企れ伎伎狩. 
INSERT Person VALUES('伎');
INSERT Person VALUES('襦');
INSERT Person VALUES('れ狩');
INSERT Person VALUES('蟾');



CREATE TABLE Msg(
	Sender varchar(20)
,	Receiver varchar(20)
,	Msg nvarchar(max)
,	ReceiveDT datetime
);
GO

--蟯螻れ
ALTER TABLE dbo.Msg  WITH CHECK ADD  CONSTRAINT FK_Msg_Person FOREIGN KEY(Sender)
REFERENCES dbo.Person (PersonName)
GO
ALTER TABLE dbo.Msg CHECK CONSTRAINT FK_Msg_Person
GO

ALTER TABLE dbo.Msg  WITH CHECK ADD  CONSTRAINT FK_Msg_Person1 FOREIGN KEY(Receiver)
REFERENCES dbo.Person (PersonName)
GO
ALTER TABLE dbo.Msg CHECK CONSTRAINT FK_Msg_Person1
GO
<1>

5 Contract 覦 Service, Message Queue #

  1. Message Type
  2. Contract
  3. Queue
  4. Service

CREATE MESSAGE TYPE SenderMsg VALIDATION = NONE;
CREATE MESSAGE TYPE ReceiverMsg VALIDATION = NONE;

CREATE CONTRACT DeliveryMsgContract
(
	SenderMsg SENT BY initiator
,	ReceiverMsg SENT BY target
);

CREATE QUEUE SendedMsgQueue;
CREATE QUEUE ReceivedMsgQueue;

CREATE SERVICE SendMsgService
ON QUEUE ReceivedMsgQueue(DeliveryMsgContract);

CREATE SERVICE ReceiveMsgService
ON QUEUE SendedMsgQueue(DeliveryMsgContract);
GO
<2>

6 Message 覲企企 襦蠏碁(伎 れ狩蟆 覃讌襯 覲企碁) #

--覃語覲企願鍵
DECLARE 
	@handle uniqueidentifier
,	@Sender varchar(20)
,	@Receiver varchar(20)
,	@Msg nvarchar(max);

SET @Sender = '伎';
SET @Receiver = 'れ狩';
SET @Msg = N'~ 覦螳覦螳(^^)/';

BEGIN TRAN;
	BEGIN DIALOG CONVERSATION @handle
	FROM SERVICE SendMsgService
	TO SERVICE 'ReceiveMsgService'
	ON CONTRACT DeliveryMsgContract
	WITH 
		ENCRYPTION = OFF
	,	LIFETIME = 600;

	SEND ON CONVERSATION @handle
	MESSAGE TYPE SenderMsg
	(N'
	<Letter>
		<Sender>' + @Sender + '</Sender>' + '
		<Receiver>' + @Receiver + '</Receiver>
		<Message><![CDATA[' + @Msg + ']]></Message>
	</Letter>
	');
COMMIT;
GO
<3>

7 覃讌襯 覦 Msg 企 ロ 襦蠏碁 #

--朱覃語覦蠍
DECLARE 
	@handle uniqueidentifier
,	@message_body xml
,	@Sender varchar(20)
,	@Receiver varchar(20)
,	@Msg nvarchar(max)
,	@PersonName varchar(20);
BEGIN TRY
BEGIN TRAN;

WHILE(1=1)
BEGIN
	--覲企誤ク讌襯朱. 
	RECEIVE TOP(1)
		@handle = conversation_handle
	,	@message_body = CONVERT(nvarchar(max), message_body)
	FROM SendedMsgQueue;

	IF @@ROWCOUNT = 1 
	BEGIN
		--DECLARE @message_body xml;
		--SET @message_body = '
		--	<Letter>
		--		<Sender>yasi</Sender>
		--		<Receiver>mkex</Receiver>
		--		<Message><![CDATA[~~ 覦螳覦螳~~]]></Message>
		--	</Letter>';
		
		SELECT
			@Sender = A.Sender
		,	@Receiver = A.Receiver
		,	@Msg = A.Msg
		,	@PersonName = B.PersonName
		FROM (
			SELECT 
				x.item.value('Sender[1]', 'varchar(20)') AS Sender
			,	x.item.value('Receiver[1]', 'varchar(20)') AS Receiver
			,	x.item.value('Message[1]', 'nvarchar(max)') AS Msg
			FROM @message_body.nodes('/Letter') AS x(item)) A 
			LEFT OUTER JOIN Person B
			ON A.Receiver = B.PersonName;

		IF @PersonName IS NOT NULL
		BEGIN
			INSERT Msg(Sender, Receiver, Msg, ReceiveDT) 
			VALUES(@Sender, @Receiver, @Msg, GETDATE());
		END ELSE
		BEGIN
			SEND ON CONVERSATION @handle
			MESSAGE TYPE ReceiverMsg
			(N'
				<Letter>
					<Sender><![CDATA[ろ蟯襴]]></Sender>' + '
					<Receiver>' + @Sender + '</Receiver>
					<Message><![CDATA[
					豬碁覈!! れ覃語襯殊讌覈詩給. 
					覲企語:' + @Sender  + ' 
					覦:' + @Receiver+ '
					覃語:' + @Msg + ']]></Message>
				</Letter>
			');
			END CONVERSATION @handle;
		END
	END ELSE
	BEGIN
		END CONVERSATION @handle;
		BREAK;
	END
END

COMMIT;
END TRY
BEGIN CATCH
	IF (XACT_STATE()) = -1 ROLLBACK; 
    ELSE IF (XACT_STATE()) =  1 COMMIT;

/*
	SELECT
         ERROR_NUMBER() AS ErrorNumber
	,	ERROR_SEVERITY() AS ErrorSeverity
	,	ERROR_STATE() AS ErrorState
	,	ERROR_PROCEDURE() AS ErrorProcedure
	,	ERROR_LINE() AS ErrorLine
	,	ERROR_MESSAGE() AS ErrorMessage
	,	GETDATE() AS ErrorDT
*/
END CATCH;
GO

--覃讌 谿 
SELECT * FROM Msg;
sssb03.jpg
<4>

8 豬 覿覈 ろ #

  1. <3>覿覿 @Receiver襯 れ螻 螳
  2. <4>襯 覃讌襯 覦螻, 蟆郁骸襯
  3. <5>襯 豬 覿覈 覃讌襯 螻, 蟆郁骸

--覃語覲企願鍵
DECLARE 
	@handle uniqueidentifier
,	@Sender varchar(20)
,	@Receiver varchar(20)
,	@Msg nvarchar(max);

SET @Sender = '伎';
SET @Receiver = '';   --螻豺覿覿
SET @Msg = N'~ 覦螳覦螳(^^)/';
<5>

豬 覿覈 覃讌 豌襴
--豬 覿覈 覃語
DECLARE 
	@handle uniqueidentifier
,	@message_body xml
,	@Sender varchar(20)
,	@Receiver varchar(20)
,	@Msg nvarchar(max)
,	@PersonName varchar(20)
,   @message_type sysname;
BEGIN TRY
BEGIN TRAN;

WHILE(1=1)
BEGIN
	--覲企誤ク讌襯朱. 
	RECEIVE TOP(1)
		@handle = conversation_handle
	,	@message_body = CONVERT(nvarchar(max), message_body)
    ,   @message_type = message_type_name
	FROM ReceivedMsgQueue;

	IF @@ROWCOUNT = 1 
	BEGIN	
		SELECT
			@Sender = A.Sender
		,	@Receiver = A.Receiver
		,	@Msg = A.Msg
		FROM (
			SELECT 
				x.item.value('Sender[1]', 'varchar(20)') AS Sender
			,	x.item.value('Receiver[1]', 'varchar(20)') AS Receiver
			,	x.item.value('Message[1]', 'nvarchar(max)') AS Msg
			FROM @message_body.nodes('/Letter') AS x(item)) A 
		LEFT OUTER JOIN Person B
		ON A.Receiver = B.PersonName;

		IF @message_type = 
			'http://schemas.microsoft.com/SQL/ServiceBroker/EndDialog'
        	BEGIN 
            		END CONVERSATION @handle;
        	END ELSE IF @message_type = 
			'http://schemas.microsoft.com/SQL/ServiceBroker/Error'
	        BEGIN 
        	    END CONVERSATION @handle;
	        END ELSE IF @Sender IS NOT NULL
		BEGIN
			INSERT Msg(Sender, Receiver, Msg, ReceiveDT) 
			VALUES(@Sender, @Receiver, @Msg, GETDATE());
		END ELSE
		BEGIN
			END CONVERSATION @handle;
		END

	END ELSE
	BEGIN
		END CONVERSATION @handle;
		BREAK;
	END
END

COMMIT;
END TRY
BEGIN CATCH
	IF (XACT_STATE()) = -1 ROLLBACK; 
    ELSE IF (XACT_STATE()) =  1 COMMIT;
/*
    SELECT
		ERROR_NUMBER() AS ErrorNumber
	,	ERROR_SEVERITY() AS ErrorSeverity
	,	ERROR_STATE() AS ErrorState
	,	ERROR_PROCEDURE() AS ErrorProcedure
	,	ERROR_LINE() AS ErrorLine
	,	ERROR_MESSAGE() AS ErrorMessage
	,	GETDATE() AS ErrorDT
*/
END CATCH;
GO

--覃讌 谿 
SELECT * FROM Msg;
sssb04.jpg
<6>

9 蟆磯 #

螳 SQL Server 2005 Service Broker 觜蠍 覃讌 / 螻殊 蟲 覲伎. れ襦 るジ SQL Script覲企る 覲旧″ 蟆 れ企. 襷 覓語 螳 蟲 り り 讌襷 SQL 企朱 語伎 覯譯殊 蟒 襷 貊 伎 . 讌襷 觜蠍 旧, 覃讌 豌襴, 覲企伎 襦 豌襴 煙 企 豌襴 蟲る 企 襦蠏碁襾碁 蟒 螻豺螳 蟆企 螳. 襷 企 覿覿 螳 觜蟲 螳 觜蠍 覃讌 豌襴 ろ 蟲 る 襷 蟆企.
蠍 蠍郁鍵..
企: : るジ讓曙 襦螻豺 企Ν 譯殊語. 襦螻豺
EditText : Print : Mobile : FindPage : DeletePage : LikePages : Powered by MoniWiki : Last modified 2018-04-13 23:12:52

蟲蟆 襷 . (伎)