SQL Server 2005 Beta 2 Service Broker: State of conversation endpoint

fateame

SQL Server 2005 Beta 2 Service Broker: State of conversation endpoint

Posted by: Rickie Lee (www.iyunv.com/rickie)

Date: Apr. 2005

1. Conversation Endpoints can be looked up via querying the sys.conversation_endpoints catalog view in the local broker’s database.
Use [DatabaseName]
Select * From sys.conversation_endpoints

Part of fields of sys.conversation_endpoints table:
(1) conversation_handle - uniqueidentifier, identifier for the conversation endpoint, one side of the conversation.
(2) conversation_id – uniqueidentifier, indentifier for the conversation. This identifier is shared by both participants in the conversation.
(3) state and state_desc fileds – description of endpoint converstation state.
There are the following states available for different cases.

STARTED_OUTBOUND STARTED_INBOUND CONVERSING DISCONNECTED_INBOUND DISCONNECTED_OUTBOUND CLOSED

• ERROR
  

2. Purge Conversation Endpoints without notifying the remote service
Using the “with cleanup” clause, you can remove the left conversation endpoint records in the sys.conversation_endpoints table.

Select * From sys.conversation_endpoints
End Conversation [conversation_handle] with cleanup;

For example,
End Conversation '2f442c49-5b57-4078-b6e8-af2d9414e241' with cleanup;

The above example ends the dialog specified by DIALOG_HANDLE, without transmitting any further messages to the remote service. Since ending a dialog with cleanup does not notify the remote service, you should only use this in case where the remote service is not available to receive an EndDialog or Error message.

3. Demo
(1) Send message at the Initiator EndPoint
Assume you have already created the necessary Service Broker objects, such as Message Type, Contract, Queue and Service, etc.
Moreover, 'A727462B-52E7-4405-9EEE-D19923729790' is just the demo Broker Instance, which specifies the database that hosts the target service.

You can use the following SQL script the get the Broker Instance ID of the target database.
Select service_broker_guid from sys.databases
where database_id=DB_ID('HelloWorldDB')

The following is sending the message from the Initiator.
Declare @handle uniqueidentifier
Begin Dialog Conversation @handle
From Service SendingService
To Service 'ReceivingService','A727462B-52E7-4405-9EEE-D19923729790'
On Contract XMLContract;

Send on Conversation @handle
Message Type XMLMessage
('Welcome to Rickie Lee''s blog, www.iyunv.com/rickie');

Select * From sys.conversation_endpoints

End Conversation @handle;


(2) Check the messages in the Target Queue
Select * From ReceivingQueue
　　


Well, there are two messages in the Target Queue. The first one is the message of XMLMessage type, the next one is the End Dialog message, which notifies the remote service to end the dialog conversation.

(3) Check the Target Endpoint
Select * From sys.conversation_endpoints
　　


From the above query result, we can see that the Endpoint record for the dialog conversation at the Target’s broker database has the state of DI, or DISCONNECTED_INBOUND.


References:
1. A First Look at SQL Server 2005 Service Broker, Roger Wolter, http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsql90/html/sqlsvcbroker.asp?frame=true
2. Michael G., Service Broker Architecture, http://www.dotnetfun.com/articles/sql/sql2005/SQL2005ServiceBrokerBasicArchitecture.aspx
3. Mike Taulty’s Weblog, SQL Server 2005 Service Broker & WSE 2.0, http://mtaulty.com/blog/archive/2005/02/14/1484.aspx