[edit]
1 螳 #
襷 Service Broker 覲企 れ螻 螳 蠏碁殊 覲 . Service Broker 螳 る 蠏碁殊朱 壱ｸ覓殊 覲企企 蟆 觜螻 . 願 蟲 覲伎. ｸ 蠏碁 殊曙 覲企企 Sender 螻, るジ讓曙 覦 Receiver 蟆.
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>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>
--覃語覲企願鍵 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>
--朱覃語覦蠍
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;
--覃語覲企願鍵 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;
