Hello. I enabled service broker on a database of my server.  I made a table in which I store all my services. I want to make a log table in order to se what message was send and what response he get. But I am not sure how can I do that. I can't identify a unique column in queue. Can you help me with some advice, is there a method to do this?

The combination of (conversation_handle, message_sequence_number) will uniquely identify a message.

Although you may want to also join sys.conversation_endpoints and grab the conversation_id which will be the same on both ends of the conversation.

David

How can I log this part? I have a "send_request" procedure there I log information ( sequence_number, conversation_handle), and a "send_reques_response" procedure in where I process the message and I send back a response. How can I corelate log info from first procedure with one in the second? (I mean, in the first procedure I log initial message + date at it was send and in the second I want to log processed_message and the date it was processed)

• Edited by BogdanSlc 11 hours 22 minutes ago

I would just log the conversation_handle (or conversation_id) and the timestamp.  On SEND it's not easy to see the message_sequence_number of the message you generate, and access to a conversation_handle never concurrent.  SEND and RECEIVE both lock the conversation handle.

David

Ok then. But still not 100% sure how should I log that. I mean, how can I read informations from the log table if I only store the handler and timestamp? How can I interpret that?

> how can I read informations from the log table if I only store the handler and timestamp

I just meant how to key the data.  Capture additional data about the message too, like the message type, and message body.

eg

(conversation_handle, sent_time), message_type, message_body, ...

David

Ok, good, I understand now. But I want to see something like that:

initial_message_body, initial_date, processed_message_body, processed_date, response_date (response_message_body = processed_message_body)

I dont think I will be able to obtain that because at the second procedure I can't make update with a "where" condition on the log table

Best is probably to make sure that your message includes some information, and not relying on handle and message number alone.

Keep in mind that you should have different tables on sender and receiver side - as you may later put them in different databases, even on different servers.

In my case I need those 2 tables in the same server, on the same database. But if I create 2 log tables I won't be able to see info like: initial_message was send at, response came after ... minutes, response was ...