{"id":2715,"date":"2013-06-14T08:06:26","date_gmt":"2013-06-14T08:06:26","guid":{"rendered":"http:\/\/enjoyasp.net\/?p=2715"},"modified":"2013-06-14T08:36:23","modified_gmt":"2013-06-14T08:36:23","slug":"service-broker%e5%85%a5%e9%97%a8","status":"publish","type":"post","link":"https:\/\/enjoyasp.net\/index.php\/2013\/06\/14\/service-broker%e5%85%a5%e9%97%a8\/","title":{"rendered":"Service Broker\u5165\u95e8"},"content":{"rendered":"<pre class=\"brush:sql;first-line:1;pad-line-numbers:true;highlight:null;collapse:false;\">\r\nService Broker\uff1a\u591a\u670d\u52a1\u5668\u5355\u4fe1\u606f\u4f20\u9012\uff0c\u5927\u5c0f\u5199\u654f\u611f\r\n\u7ec4\u6210\uff1a\r\nMessage:\u4fe1\u5185\u5bb9\uff0c\u4e3a\u4fbf\u4e8e\u533a\u5206\u4fdd\u8bc1\u552f\u4e00\u6027\uff0c\u4fe1\u606f\u7684\u524d\u7f00\u7528\u57df\u540d\u597d\u4e00\u4e9b\u3002\r\nContracts\uff1a\u4fe1\u5934\uff0c\u7ec4\u5408message\u53camessage\u7531\u54ea\u8fb9\u53d1\u9001\uff0c\u53d1\u8d77\u8005\u6216\u8005\u63a5\u6536\u8005\r\nQueue\uff1a\u4fe1\u5c01,\u6536\u5230\u7684\u4fe1\u606f\u5b58\u50a8\u7684\u4f4d\u7f6e\r\n\r\n\u5efa\u7acb\u65b9\u5f0f\uff1a\r\n1\uff0c\u5b9a\u4e49 SERVICE\uff1a\u5c06\u4e0a\u9762\u4e09\u8005\u7ec4\u5408\r\n2\uff0c\u4fe1\u606f\u53d1\u9001\uff1a\r\nBEGIN TRY\r\n BEGIN TRANSACTION;\r\n  DECLARE @ch UNIQUEIDENTIFIER\r\n  DECLARE @msg NVARCHAR(MAX);\r\n\r\n  BEGIN DIALOG CONVERSATION @ch\r\n   FROM SERVICE [InitiatorService]\r\n   TO SERVICE &#39;TargetService&#39;\r\n   ON CONTRACT [http:\/\/ssb.csharp.at\/SSB_Book\/c03\/HelloWorldContract]\r\n   WITH ENCRYPTION = OFF;\r\n\r\n  SET @msg = \r\n   &#39;&lt;HelloWorldRequest&gt;\r\n     Klaus Aschenbrenner\r\n   &lt;\/HelloWorldRequest&gt;&#39;;\r\n\r\n  SEND ON CONVERSATION @ch MESSAGE TYPE [http:\/\/ssb.csharp.at\/SSB_Book\/c03\/RequestMessage] (@msg);\r\n COMMIT\r\nEND TRY\r\nBEGIN CATCH\r\n ROLLBACK TRANSACTION\r\nEND CATCH\r\nGO\r\n3\uff0c\u63a5\u6536\u4fe1\u606f\r\n--***********************************************************************************\r\n--*  Retrieve the sent message from the queue &quot;TargetQueue&quot; with a WAITFOR statement\r\n--***********************************************************************************\r\nDECLARE @cg UNIQUEIDENTIFIER\r\nDECLARE @ch UNIQUEIDENTIFIER\r\nDECLARE @messagetypename NVARCHAR(256)\r\nDECLARE @messagebody XML;\r\n\r\nBEGIN TRY\r\n BEGIN TRANSACTION;\r\n\r\n  WAITFOR (\r\n   RECEIVE TOP (1)\r\n    @cg = conversation_group_id,\r\n    @ch = conversation_handle,\r\n    @messagetypename = message_type_name,\r\n    @messagebody = CAST(message_body AS XML)\r\n   FROM TargetQueue\r\n  ), TIMEOUT 60000\r\n\r\n  IF (@@ROWCOUNT &gt; 0)\r\n  BEGIN\r\n   PRINT &#39;Conversation group: &#39; + CAST(@cg AS NVARCHAR(MAX))\r\n   PRINT &#39;Conversation handle: &#39; + CAST(@ch AS NVARCHAR(MAX))\r\n   PRINT &#39;Message type: &#39; + @messagetypename\r\n   PRINT &#39;Message body: &#39; + CAST(@messagebody AS NVARCHAR(MAX))\r\n  END\r\n\r\n COMMIT\r\nEND TRY\r\nBEGIN CATCH\r\n ROLLBACK TRANSACTION\r\nEND CATCH\r\nGO\r\n4\uff0c\u76d1\u63a7\u5bf9\u5217\r\nCREATE EVENT NOTIFICATION PoisonMessageNotification ON QUEUE TargetQueue\r\nFOR Broker_Queue_Disabled\r\nTO SERVICE &#39;PoisonMessageNotifyService&#39;, &#39;current database&#39;\r\nGO\r\n\r\n\r\n---------------------------\u660e\u7ec6\r\nUSE master;\r\n\r\nIF EXISTS (SELECT * FROM sys.databases WHERE name = &#39;Chapter3_HelloWorldSvc&#39;)\r\nBEGIN\r\n\tPRINT &#39;Dropping database &#39;&#39;Chapter3_HelloWorldSvc&#39;&#39;&#39;;\r\n\tDROP DATABASE Chapter3_HelloWorldSvc;\r\nEND\r\nGO\r\n\r\nCREATE DATABASE Chapter3_HelloWorldSvc\r\nGO\r\n\r\nUSE Chapter3_HelloWorldSvc\r\nGO\r\n\r\n--*********************************************\r\n--*  Create the message type &quot;RequestMessage&quot;\r\n--*********************************************\r\nCREATE MESSAGE TYPE\r\n[http:\/\/ssb.csharp.at\/SSB_Book\/c03\/RequestMessage]\r\nVALIDATION = NONE\r\nGO\r\n\r\n--*********************************************\r\n--*  Create the message type &quot;ResponseMessage&quot;\r\n--*********************************************\r\nCREATE MESSAGE TYPE\r\n[http:\/\/ssb.csharp.at\/SSB_Book\/c03\/ResponseMessage]\r\nVALIDATION = NONE\r\nGO\r\n\r\n--*********************************************\r\n--*  Show the created message types\r\n--*********************************************\r\nSELECT * FROM sys.service_message_types \r\nGO\r\n\r\n--************************************************\r\n--*  Changing the validation of the message types\r\n--************************************************\r\nALTER MESSAGE TYPE [http:\/\/ssb.csharp.at\/SSB_Book\/c03\/RequestMessage]\r\nVALIDATION = WELL_FORMED_XML\r\nGO\r\n\r\nALTER MESSAGE TYPE [http:\/\/ssb.csharp.at\/SSB_Book\/c03\/ResponseMessage]\r\nVALIDATION = WELL_FORMED_XML\r\nGO\r\n\r\n--************************************************\r\n--*  Create the contract &quot;HelloWorldContract&quot;\r\n--************************************************\r\nCREATE CONTRACT [http:\/\/ssb.csharp.at\/SSB_Book\/c03\/HelloWorldContract]\r\n(\r\n\t[http:\/\/ssb.csharp.at\/SSB_Book\/c03\/RequestMessage] SENT BY INITIATOR,\r\n\t[http:\/\/ssb.csharp.at\/SSB_Book\/c03\/ResponseMessage] SENT BY TARGET\r\n)\r\nGO\r\n\r\n--*************************************************************\r\n--*  Getting some information about the newly created contract\r\n--*************************************************************\r\nSELECT \r\n\tsc.name AS &#39;Contract&#39;, \r\n\tmt.name AS &#39;Message type&#39;, \r\n\tcm.is_sent_by_initiator,\r\n\tcm.is_sent_by_target,\r\n\tmt.validation\r\nFROM sys.service_contract_message_usages cm \r\n\tINNER JOIN sys.service_message_types mt ON cm.message_type_id = mt.message_type_id\r\n\tINNER JOIN sys.service_contracts sc ON sc.service_contract_id = cm.service_contract_id\r\nGO\r\n\r\n--********************************************************\r\n--*  Create the queues &quot;InitiatorQueue&quot; and &quot;TargetQueue&quot;\r\n--********************************************************\r\nCREATE QUEUE InitiatorQueue\r\nWITH STATUS = ON\r\nGO\r\n\r\nCREATE QUEUE TargetQueue\r\nWITH STATUS = ON\r\nGO\r\n\r\n--*************************************************************\r\n--*  Getting some information about the newly created queues\r\n--*************************************************************\r\nSELECT * FROM sys.service_queues\r\nGO\r\n\r\n--************************************************************\r\n--*  Create the queues &quot;InitiatorService&quot; and &quot;TargetService&quot;\r\n--************************************************************\r\nCREATE SERVICE InitiatorService\r\nON QUEUE InitiatorQueue \r\n(\r\n\t[http:\/\/ssb.csharp.at\/SSB_Book\/c03\/HelloWorldContract]\r\n)\r\nGO\r\n\r\nCREATE SERVICE TargetService\r\nON QUEUE TargetQueue\r\n(\r\n\t[http:\/\/ssb.csharp.at\/SSB_Book\/c03\/HelloWorldContract]\r\n)\r\nGO\r\n\r\n--*************************************************************\r\n--*  Getting some information about the newly created services\r\n--*************************************************************\r\nSELECT\r\n\tsv.name AS &#39;Service&#39;,\r\n\tsc.name AS &#39;Contract&#39;\r\nFROM sys.services sv\r\n\tINNER JOIN sys.service_contract_usages scu ON scu.service_id = sv.service_id\r\n\tINNER JOIN sys.service_contracts sc ON sc.service_contract_id = scu.service_contract_id\r\nGO\r\n\r\n--********************************************************************\r\n--*  Sending a message from the InitiatorService to the TargetService\r\n--********************************************************************\r\nBEGIN TRY\r\n\tBEGIN TRANSACTION;\r\n\t\tDECLARE @ch UNIQUEIDENTIFIER\r\n\t\tDECLARE @msg NVARCHAR(MAX);\r\n\r\n\t\tBEGIN DIALOG CONVERSATION @ch\r\n\t\t\tFROM SERVICE [InitiatorService]\r\n\t\t\tTO SERVICE &#39;TargetService&#39;\r\n\t\t\tON CONTRACT [http:\/\/ssb.csharp.at\/SSB_Book\/c03\/HelloWorldContract]\r\n\t\t\tWITH ENCRYPTION = OFF;\r\n\r\n\t\tSET @msg = \r\n\t\t\t&#39;&lt;HelloWorldRequest&gt;\r\n\t\t\t\t\tKlaus Aschenbrenner\r\n\t\t\t&lt;\/HelloWorldRequest&gt;&#39;;\r\n\r\n\t\tSEND ON CONVERSATION @ch MESSAGE TYPE [http:\/\/ssb.csharp.at\/SSB_Book\/c03\/RequestMessage] (@msg);\r\n\tCOMMIT\r\nEND TRY\r\nBEGIN CATCH\r\n\tROLLBACK TRANSACTION\r\nEND CATCH\r\nGO\r\n\r\n--********************************************************************\r\n--*  View the sent message on the queue &quot;TargetQueue&quot;\r\n--********************************************************************\r\nSELECT * FROM TargetQueue\r\nGO\r\n\r\n--********************************************************************\r\n--*  View the created conversation endpoints\r\n--********************************************************************\r\nSELECT * FROM sys.conversation_endpoints\r\nGO\r\n\r\n--********************************************************************\r\n--*  Retrieve the sent message from the queue &quot;TargetQueue&quot;\r\n--********************************************************************\r\nDECLARE @cg UNIQUEIDENTIFIER\r\nDECLARE @ch UNIQUEIDENTIFIER\r\nDECLARE @messagetypename NVARCHAR(256)\r\nDECLARE\t@messagebody XML;\r\n\r\nBEGIN TRY\r\n\tBEGIN TRANSACTION;\r\n\r\n\t\tRECEIVE TOP(1)\r\n\t\t\t@cg = conversation_group_id,\r\n\t\t\t@ch = conversation_handle,\r\n\t\t\t@messagetypename = message_type_name,\r\n\t\t\t@messagebody = CAST(message_body AS XML)\r\n\t\tFROM TargetQueue\r\n\r\n\t\tPRINT &#39;Conversation group: &#39; + CAST(@cg AS NVARCHAR(MAX))\r\n\t\tPRINT &#39;Conversation handle: &#39; + CAST(@ch AS NVARCHAR(MAX))\r\n\t\tPRINT &#39;Message type: &#39; + @messagetypename\r\n\t\tPRINT &#39;Message body: &#39; + CAST(@messagebody AS NVARCHAR(MAX))\r\n\r\n\tCOMMIT\r\nEND TRY\r\nBEGIN CATCH\r\n\tROLLBACK TRANSACTION\r\nEND CATCH\r\nGO\r\n\r\n--***********************************************************************************\r\n--*  Retrieve the sent message from the queue &quot;TargetQueue&quot; with a WAITFOR statement\r\n--***********************************************************************************\r\nDECLARE @cg UNIQUEIDENTIFIER\r\nDECLARE @ch UNIQUEIDENTIFIER\r\nDECLARE @messagetypename NVARCHAR(256)\r\nDECLARE\t@messagebody XML;\r\n\r\nBEGIN TRY\r\n\tBEGIN TRANSACTION;\r\n\r\n\t\tWAITFOR (\r\n\t\t\tRECEIVE TOP (1)\r\n\t\t\t\t@cg = conversation_group_id,\r\n\t\t\t\t@ch = conversation_handle,\r\n\t\t\t\t@messagetypename = message_type_name,\r\n\t\t\t\t@messagebody = CAST(message_body AS XML)\r\n\t\t\tFROM TargetQueue\r\n\t\t), TIMEOUT 60000\r\n\r\n\t\tIF (@@ROWCOUNT &gt; 0)\r\n\t\tBEGIN\r\n\t\t\tPRINT &#39;Conversation group: &#39; + CAST(@cg AS NVARCHAR(MAX))\r\n\t\t\tPRINT &#39;Conversation handle: &#39; + CAST(@ch AS NVARCHAR(MAX))\r\n\t\t\tPRINT &#39;Message type: &#39; + @messagetypename\r\n\t\t\tPRINT &#39;Message body: &#39; + CAST(@messagebody AS NVARCHAR(MAX))\r\n\t\tEND\r\n\r\n\tCOMMIT\r\nEND TRY\r\nBEGIN CATCH\r\n\tROLLBACK TRANSACTION\r\nEND CATCH\r\nGO\r\n\r\n--**************************************************\r\n--*  Create a table to store the processed messages\r\n--**************************************************\r\nCREATE TABLE ProcessedMessages\r\n(\r\n\tID UNIQUEIDENTIFIER NOT NULL,\r\n\tMessageBody XML NOT NULL,\r\n\tServiceName NVARCHAR(MAX) NOT NULL\r\n)\r\nGO\r\n\r\n--*******************************************************************\r\n--*  Send a response message back to the service &quot;InitiatorService&quot;\r\n--*******************************************************************\r\nDECLARE @ch UNIQUEIDENTIFIER\r\nDECLARE @messagetypename NVARCHAR(256)\r\nDECLARE\t@messagebody XML\r\nDECLARE @responsemessage XML;\r\n\r\nBEGIN TRY\r\n\tBEGIN TRANSACTION\r\n\t\tWAITFOR (\r\n\t\t\tRECEIVE TOP (1)\r\n\t\t\t\t@ch = conversation_handle,\r\n\t\t\t\t@messagetypename = message_type_name,\r\n\t\t\t\t@messagebody = CAST(message_body AS XML)\r\n\t\t\tFROM TargetQueue\r\n\t\t), TIMEOUT 60000\r\n\r\n\t\tIF (@@ROWCOUNT &gt; 0)\r\n\t\tBEGIN\r\n\t\t\tIF (@messagetypename = &#39;http:\/\/ssb.csharp.at\/SSB_Book\/c03\/RequestMessage&#39;)\r\n\t\t\tBEGIN\r\n\t\t\t\t-- Store the received request message in a table\r\n\t\t\t\tINSERT INTO ProcessedMessages (ID, MessageBody, ServiceName) VALUES (NEWID(), @messagebody, &#39;TargetService&#39;)\r\n\r\n\t\t\t\t-- Construct the response message\r\n\t\t\t\tSET @responsemessage = &#39;&lt;HelloWorldResponse&gt;&#39; + @messagebody.value(&#39;\/HelloWorldRequest[1]&#39;, &#39;NVARCHAR(MAX)&#39;) + &#39;&lt;\/HelloWorldResponse&gt;&#39;;\r\n\r\n\t\t\t\t-- Send the response message back to the initiating service\r\n\t\t\t\tSEND ON CONVERSATION @ch MESSAGE TYPE [http:\/\/ssb.csharp.at\/SSB_Book\/c03\/ResponseMessage] (@responsemessage);\r\n\r\n\t\t\t\t-- End the conversation on the target&#39;s side\r\n\t\t\t\tEND CONVERSATION @ch;\r\n\t\t\tEND\r\n\t\tEND\r\n\tCOMMIT\r\nEND TRY\r\nBEGIN CATCH\r\n\tROLLBACK TRANSACTION\r\nEND CATCH\r\nGO\r\n\r\n\r\n--********************************************************************\r\n--*  View the sent message on the queue &quot;InitiatorQueue&quot;\r\n--********************************************************************\r\nSELECT * FROM InitiatorQueue\r\nGO\r\n\r\n--********************************************************************\r\n--*  View the processed message in the table &quot;ProcessedMessages&quot;\r\n--********************************************************************\r\nSELECT * FROM ProcessedMessages\r\nGO\r\n\r\n--*******************************************************************\r\n--*  Service program for the service &quot;InitiatorService&quot;\r\n--*******************************************************************\r\nDECLARE @ch UNIQUEIDENTIFIER\r\nDECLARE @messagetypename NVARCHAR(256)\r\nDECLARE\t@messagebody XML;\r\n\r\nBEGIN TRY\r\n\tBEGIN TRANSACTION\r\n\t\tWAITFOR (\r\n\t\t\tRECEIVE TOP (1)\r\n\t\t\t\t@ch = conversation_handle,\r\n\t\t\t\t@messagetypename = message_type_name,\r\n\t\t\t\t@messagebody = CAST(message_body AS XML)\r\n\t\t\tFROM InitiatorQueue\r\n\t\t), TIMEOUT 60000\r\n\r\n\t\tIF (@@ROWCOUNT &gt; 0)\r\n\t\tBEGIN\r\n\t\t\tIF (@messagetypename = &#39;http:\/\/ssb.csharp.at\/SSB_Book\/c03\/ResponseMessage&#39;)\r\n\t\t\tBEGIN\r\n\t\t\t\t-- Store the received response) message in a table\r\n\t\t\t\tINSERT INTO ProcessedMessages (ID, MessageBody, ServiceName) VALUES (NEWID(), @messagebody, &#39;InitiatorService&#39;)\r\n\t\t\tEND\r\n\r\n\t\t\tIF (@messagetypename = &#39;http:\/\/schemas.microsoft.com\/SQL\/ServiceBroker\/EndDialog&#39;)\r\n\t\t\tBEGIN\r\n\t\t\t\t-- End the conversation on the initiator&#39;s side\r\n\t\t\t\tEND CONVERSATION @ch;\r\n\t\t\tEND\r\n\t\tEND\r\n\tCOMMIT\r\nEND TRY\r\nBEGIN CATCH\r\n\tROLLBACK TRANSACTION\r\nEND CATCH\r\nGO\r\n\r\n--***********************************************************************************\r\n--*  Endless receive loop for processing incoming messages on the initiator&#39;s queue\r\n--***********************************************************************************\r\nDECLARE @ch UNIQUEIDENTIFIER\r\nDECLARE @messagetypename NVARCHAR(256)\r\nDECLARE\t@messagebody XML;\r\n\r\nWHILE (1=1)\r\nBEGIN\r\n\tBEGIN TRY\r\n\t\tBEGIN TRANSACTION\r\n\r\n\t\tWAITFOR (\r\n\t\t\tRECEIVE TOP (1)\r\n\t\t\t\t@ch = conversation_handle,\r\n\t\t\t\t@messagetypename = message_type_name,\r\n\t\t\t\t@messagebody = CAST(message_body AS XML)\r\n\t\t\tFROM InitiatorQueue\r\n\t\t), TIMEOUT 60000\r\n\r\n\t\tIF (@@ROWCOUNT = 0)\r\n\t\tBEGIN\r\n\t\t\tROLLBACK TRANSACTION\r\n\t\t\tBREAK\r\n\t\tEND\r\n\r\n\t\tIF (@messagetypename = &#39;http:\/\/ssb.csharp.at\/SSB_Book\/c03\/ResponseMessage&#39;)\r\n\t\tBEGIN\r\n\t\t\t-- Store the received response) message in a table\r\n\t\t\tINSERT INTO ProcessedMessages (ID, MessageBody, ServiceName) VALUES (NEWID(), @messagebody, &#39;InitiatorService&#39;)\r\n\t\tEND\r\n\r\n\t\tIF (@messagetypename = &#39;http:\/\/schemas.microsoft.com\/SQL\/ServiceBroker\/EndDialog&#39;)\r\n\t\tBEGIN\r\n\t\t\t-- End the conversation on the initiator&#39;s side\r\n\t\t\tEND CONVERSATION @ch;\r\n\t\tEND\r\n\r\n\t\tCOMMIT TRANSACTION\r\n\tEND TRY\r\n\tBEGIN CATCH\r\n\t\tROLLBACK TRANSACTION\r\n\tEND CATCH\r\nEND\r\nGO\r\n\r\n--********************************************************************************\r\n--*  Endless receive loop for processing incoming messages on the target&#39;s queue\r\n--********************************************************************************\r\nDECLARE @ch UNIQUEIDENTIFIER\r\nDECLARE @messagetypename NVARCHAR(256)\r\nDECLARE\t@messagebody XML\r\nDECLARE @responsemessage XML;\r\n\r\nWHILE (1=1)\r\nBEGIN\r\n\tBEGIN TRY\r\n\t\tBEGIN TRANSACTION\r\n\r\n\t\tWAITFOR (\r\n\t\t\tRECEIVE TOP (1)\r\n\t\t\t\t@ch = conversation_handle,\r\n\t\t\t\t@messagetypename = message_type_name,\r\n\t\t\t\t@messagebody = CAST(message_body AS XML)\r\n\t\t\tFROM TargetQueue\r\n\t\t), TIMEOUT 60000\r\n\r\n\t\tIF (@@ROWCOUNT = 0)\r\n\t\tBEGIN\r\n\t\t\tROLLBACK TRANSACTION\r\n\t\t\tBREAK\r\n\t\tEND\r\n\r\n\t\tIF (@messagetypename = &#39;http:\/\/ssb.csharp.at\/SSB_Book\/c03\/RequestMessage&#39;)\r\n\t\tBEGIN\r\n\t\t\t-- Store the received request message in a table\r\n\t\t\tINSERT INTO ProcessedMessages (ID, MessageBody, ServiceName) VALUES (NEWID(), @messagebody, &#39;TargetService&#39;)\r\n\r\n\t\t\t-- Construct the response message\r\n\t\t\tSET @responsemessage = &#39;&lt;HelloWorldResponse&gt;&#39; + @messagebody.value(&#39;\/HelloWorldRequest[1]&#39;, &#39;nvarchar(max)&#39;) + &#39;&lt;\/HelloWorldResponse&gt;&#39;;\r\n\r\n\t\t\t-- Send the response message back to the initiating service\r\n\t\t\tSEND ON CONVERSATION @ch MESSAGE TYPE [http:\/\/ssb.csharp.at\/SSB_Book\/c03\/ResponseMessage] (@responsemessage);\r\n\r\n\t\t\t-- End the conversation on the target&#39;s side\r\n\t\t\tEND CONVERSATION @ch;\r\n\t\tEND\r\n\r\n\t\tIF (@messagetypename = &#39;http:\/\/schemas.microsoft.com\/SQL\/ServiceBroker\/EndDialog&#39;)\r\n\t\tBEGIN\r\n\t\t\t-- End the conversation\r\n\t\t\tEND CONVERSATION @ch;\r\n\t\tEND\r\n\r\n\t\tCOMMIT TRANSACTION\r\n\tEND TRY\r\n\tBEGIN CATCH\r\n\t\tROLLBACK TRANSACTION\r\n\tEND CATCH\r\nEND\r\nGO\r\n\r\n--**********************************************************\r\n--*  Error handling with a savepoint on the target&#39;s queue\r\n--**********************************************************\r\nDECLARE @ch UNIQUEIDENTIFIER\r\nDECLARE @messagetypename NVARCHAR(256)\r\nDECLARE\t@messagebody XML\r\nDECLARE @responsemessage XML;\r\n\r\nWHILE (1=1)\r\nBEGIN\r\n\tBEGIN TRANSACTION\r\n\r\n\tWAITFOR (\r\n\t\tRECEIVE TOP (1)\r\n\t\t\t@ch = conversation_handle,\r\n\t\t\t@messagetypename = message_type_name,\r\n\t\t\t@messagebody = CAST(message_body AS XML)\r\n\t\tFROM TargetQueue\r\n\t), TIMEOUT 60000\r\n\r\n\tIF (@@ROWCOUNT = 0)\r\n\tBEGIN\r\n\t\tROLLBACK TRANSACTION\r\n\t\tBREAK\r\n\tEND\r\n\r\n\tSAVE TRANSACTION MessageReceivedSavepoint\r\n\r\n\tIF (@messagetypename = &#39;http:\/\/ssb.csharp.at\/SSB_Book\/c03\/RequestMessage&#39;)\r\n\tBEGIN\r\n\t\tBEGIN TRY\r\n\t\t\t-- Store the received request message in a table\r\n\t\t\tINSERT INTO ProcessedMessages (ID, MessageBody, ServiceName) VALUES (NEWID(), @messagebody, &#39;TargetService&#39;)\r\n\r\n\t\t\t-- Construct the response message\r\n\t\t\tSET @responsemessage = &#39;&lt;HelloWorldResponse&gt;&#39; + @messagebody.value(&#39;\/HelloWorldRequest[1]&#39;, &#39;nvarchar(max)&#39;) + &#39;&lt;\/HelloWorldResponse&gt;&#39;;\r\n\r\n\t\t\t-- Send the response message back to the initiating service\r\n\t\t\tSEND ON CONVERSATION @ch MESSAGE TYPE [http:\/\/ssb.csharp.at\/SSB_Book\/c03\/ResponseMessage] (@responsemessage);\r\n\r\n\t\t\t-- End the conversation on the target&#39;s side\r\n\t\t\tEND CONVERSATION @ch;\r\n\t\tEND TRY\r\n\t\tBEGIN CATCH\r\n\t\t\tIF (ERROR_NUMBER() = 1205) \r\n\t\t\tBEGIN\r\n\t\t\t\t-- A deadlock occurred. \r\n\t\t\t\t-- We can try it again...\r\n\t\t\t\tROLLBACK TRANSACTION\r\n\t\t\t\tCONTINUE\r\n\t\t\tEND\r\n\t\t\tELSE\r\n\t\t\tBEGIN\r\n\t\t\t\t-- A other error occurred.\r\n\t\t\t\t-- The message can&#39;t be processed successfully, because it&#39;s a poison message\r\n\t\t\t\tROLLBACK TRANSACTION MessageReceivedSavepoint\r\n\t\t\t\tPRINT &#39;Error occured: &#39; + CAST(@messagebody AS NVARCHAR(MAX))\r\n\t\t\tEND\r\n\t\tEND CATCH\r\n\tEND\r\n\r\n\tIF (@messagetypename = &#39;http:\/\/schemas.microsoft.com\/SQL\/ServiceBroker\/EndDialog&#39;)\r\n\tBEGIN\r\n\t\t-- End the conversation\r\n\t\tEND CONVERSATION @ch;\r\n\tEND\r\n\r\n\tCOMMIT TRANSACTION\r\nEND\r\nGO\r\n\r\n--**************************************\r\n--*  Poison messages in Service Broker\r\n--**************************************\r\n-- Reactivating the queue\r\nALTER QUEUE TargetQueue WITH STATUS = ON\r\nGO\r\n\r\n-- Service program for handling the poison message\r\nDECLARE @ch UNIQUEIDENTIFIER\r\nDECLARE @messagetypename NVARCHAR(256)\r\nDECLARE\t@messagebody XML\r\n\r\nWHILE (1=1)\r\nBEGIN\r\n\tBEGIN TRANSACTION\r\n\r\n\tWAITFOR (\r\n\t\tRECEIVE TOP (1)\r\n\t\t\t@ch = conversation_handle,\r\n\t\t\t@messagetypename = message_type_name,\r\n\t\t\t@messagebody = CAST(message_body AS XML)\r\n\t\tFROM TargetQueue\r\n\t), TIMEOUT 60000\r\n\r\n\tIF (@@ROWCOUNT = 0)\r\n\tBEGIN\r\n\t\tROLLBACK TRANSACTION\r\n\t\tBREAK\r\n\tEND\r\n\r\n\t-- Rollling back the current transaction\r\n\tPRINT &#39;Rollback the current transaction - simulating a poison message...&#39;\r\n\tROLLBACK TRANSACTION\r\nEND\r\nGO\r\n\r\n--**********************************************************\r\n--*  Setting up the event notification for poison messages\r\n--**********************************************************\r\n-- Create the queue which stores the event notification messages\r\nCREATE QUEUE PoisonMessageNotifyQueue\r\nGO\r\n\r\n-- Create the service that accepts the event notification messages\r\nCREATE SERVICE PoisonMessageNotifyService ON QUEUE PoisonMessageNotifyQueue\r\n(\r\n\t[http:\/\/schemas.microsoft.com\/SQL\/Notifications\/PostEventNotification]\r\n);\r\nGO\r\n\r\n-- Create the event notification itself\r\nCREATE EVENT NOTIFICATION PoisonMessageNotification ON QUEUE TargetQueue\r\nFOR Broker_Queue_Disabled\r\nTO SERVICE &#39;PoisonMessageNotifyService&#39;, &#39;current database&#39;\r\nGO\r\n\r\n-- Select the received event notification message\r\nSELECT * FROM PoisonMessageNotifyQueue\r\nGO\r\n\r\n--**********************************************************\r\n--*  End an conversation with an error\r\n--**********************************************************\r\nDECLARE @ch UNIQUEIDENTIFIER\r\nDECLARE @messagetypename NVARCHAR(256)\r\nDECLARE\t@messagebody XML\r\n\r\nBEGIN TRY\r\n\tBEGIN TRANSACTION\r\n\t\tWAITFOR (\r\n\t\t\tRECEIVE TOP (1)\r\n\t\t\t\t@ch = conversation_handle,\r\n\t\t\t\t@messagetypename = message_type_name,\r\n\t\t\t\t@messagebody = CAST(message_body AS XML)\r\n\t\t\tFROM TargetQueue\r\n\t\t), TIMEOUT 60000\r\n\r\n\t\tIF (@messagetypename = &#39;http:\/\/ssb.csharp.at\/SSB_Book\/c03\/RequestMessage&#39;)\r\n\t\tBEGIN\r\n\t\t\t-- End the conversation with an error\r\n\t\t\tEND CONVERSATION @ch WITH ERROR = 4242 DESCRIPTION = &#39;My custom error message&#39;\r\n\t\tEND\r\n\tCOMMIT\r\nEND TRY\r\nBEGIN CATCH\r\n\tROLLBACK TRANSACTION\r\nEND CATCH\r\nGO\r\n\r\n-- Table that stores error information\r\nCREATE TABLE ErrorLog\r\n(\r\n\tID UNIQUEIDENTIFIER NOT NULL,\r\n\tErrorCode INT NOT NULL,\r\n\tErrorMessage NVARCHAR(MAX) NOT NULL\r\n)\r\nGO\r\n\r\n-- Service program for the InitiatorService, that handles also error message types\r\nDECLARE @ch UNIQUEIDENTIFIER \r\nDECLARE @messagetypename NVARCHAR(256)\r\nDECLARE\t@messagebody XML\r\nDECLARE @errorcode INT\r\nDECLARE @errormessage NVARCHAR(3000);\r\n\r\nBEGIN TRY\r\n\tBEGIN TRANSACTION\r\n\t\tWAITFOR (\r\n\t\t\tRECEIVE TOP(1)\r\n\t\t\t\t@ch = conversation_handle,\r\n\t\t\t\t@messagetypename = message_type_name,\r\n\t\t\t\t@messagebody = CAST(message_body AS XML)\r\n\t\t\tFROM InitiatorQueue\r\n\t\t), TIMEOUT 60000\r\n\r\n\t\tIF (@@ROWCOUNT &gt; 0)\r\n\t\tBEGIN\r\n\t\t\tIF (@messagetypename = &#39;http:\/\/ssb.csharp.at\/SSB_Book\/c03\/ResponseMessage&#39;)\r\n\t\t\tBEGIN\r\n\t\t\t\t-- Store the received response) message in a table\r\n\t\t\t\tINSERT INTO ProcessedMessages (ID, MessageBody, ServiceName) VALUES (NEWID(), @messagebody, &#39;InitiatorService&#39;)\r\n\t\t\tEND\r\n\r\n\t\t\tIF (@messagetypename = &#39;http:\/\/schemas.microsoft.com\/SQL\/ServiceBroker\/EndDialog&#39;)\r\n\t\t\tBEGIN\r\n\t\t\t\t-- End the conversation on the initiator&#39;s side\r\n\t\t\t\tEND CONVERSATION @ch;\r\n\t\t\tEND\r\n\r\n\t\t\tIF (@messagetypename = &#39;http:\/\/schemas.microsoft.com\/SQL\/ServiceBroker\/Error&#39;)\r\n\t\t\tBEGIN\r\n\t\t\t\t-- Extract the error information from the sent message\r\n\t\t\t\tSET @errorcode = (SELECT @messagebody.value(\r\n\t\t\t\t\tN&#39;declare namespace brokerns=&quot;http:\/\/schemas.microsoft.com\/SQL\/ServiceBroker\/Error&quot;; \r\n\t\t\t\t\t(\/brokerns:Error\/brokerns:Code)[1]&#39;, &#39;int&#39;));\r\n\t\t\t\tSET @errormessage = (SELECT @messagebody.value(\r\n\t\t\t\t\t&#39;declare namespace brokerns=&quot;http:\/\/schemas.microsoft.com\/SQL\/ServiceBroker\/Error&quot;;\r\n\t\t\t\t\t(\/brokerns:Error\/brokerns:Description)[1]&#39;, &#39;nvarchar(3000)&#39;));\r\n\r\n\t\t\t\t-- Log the error\r\n\t\t\t\tINSERT INTO ErrorLog(ID, ErrorCode, ErrorMessage)\r\n\t\t\t\tVALUES (NEWID(), @errorcode, @errormessage)\r\n\r\n\t\t\t\t-- End the conversation on the initiator&#39;s side\r\n\t\t\t\tEND CONVERSATION @ch;\r\n\t\t\tEND\r\n\t\tEND\r\n\tCOMMIT\r\nEND TRY\r\nBEGIN CATCH\r\n\tROLLBACK TRANSACTION\r\nEND CATCH\r\nGO\r\n<\/pre>\n","protected":false},"excerpt":{"rendered":"<p>Service Broker\uff1a\u591a\u670d\u52a1\u5668\u5355\u4fe1\u606f\u4f20\u9012\uff0c\u5927\u5c0f\u5199\u654f\u611f \u7ec4\u6210\uff1a Message:\u4fe1\u5185\u5bb9\uff0c\u4e3a\u4fbf\u4e8e\u533a\u5206\u4fdd\u8bc1\u552f [&hellip;]<\/p>\n","protected":false},"author":3,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[36],"tags":[],"class_list":["post-2715","post","type-post","status-publish","format-standard","hentry","category-sql-server"],"blocksy_meta":[],"_links":{"self":[{"href":"https:\/\/enjoyasp.net\/index.php\/wp-json\/wp\/v2\/posts\/2715","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/enjoyasp.net\/index.php\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/enjoyasp.net\/index.php\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/enjoyasp.net\/index.php\/wp-json\/wp\/v2\/users\/3"}],"replies":[{"embeddable":true,"href":"https:\/\/enjoyasp.net\/index.php\/wp-json\/wp\/v2\/comments?post=2715"}],"version-history":[{"count":0,"href":"https:\/\/enjoyasp.net\/index.php\/wp-json\/wp\/v2\/posts\/2715\/revisions"}],"wp:attachment":[{"href":"https:\/\/enjoyasp.net\/index.php\/wp-json\/wp\/v2\/media?parent=2715"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/enjoyasp.net\/index.php\/wp-json\/wp\/v2\/categories?post=2715"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/enjoyasp.net\/index.php\/wp-json\/wp\/v2\/tags?post=2715"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}