How to log that a message has been retained in the transmission_queue when the queue is disabled

Hi was wondering whether it is possible to log somewhere outside SB that there are messages in the transmission_queue because the Target queue was disabled.

I was testing this scenario:

try to send messages on a disabled queue and log the problem.

But the transmission_status from the trasmission_queue is always empty.

This is the code that I tried to execute between the send and the commit and after the commit:

WHILE(1=1)

BEGIN

BEGINDIALOGCONVERSATION .....

SEND ON CONVERSATION ......

if selectcount(*)fromsys.transmission_queue <> 0
BEGIN

set @.transmission_status =(select transmission_status fromsys.transmission_queuewhere conversation_handle=@.dialog_handle);

if @.transmission_status = ''

--Successful send - Exit the LOOP

BEGIN

UPDATE Mytableset isReceivedSuccessfully = 1 where ID = @.IDMessageXML;

BREAK;

END

ELSE

raiserror(@.transmission_status,1,1)withlog;

END

ELSE

BEGIN

UPDATE [dbo].[tblDumpMsg] set isReceivedSuccessfully = 1 where ID = @.IDMessageXML;

BREAK;

END

END

COMMIT TRANSACTION;

As I wrote before the @.transmission_status variable is always empty and I have the same result even if I put the code after the commit transaction!

Maybe what I'm trying to reach has no sense?

With the event notification I can notify when the queue is disable because the receive rollsback 5 times but what if by mistake the target queue is disabled outside the SB environment? I can I catch it and handle it properly?

Thank you!

Marina B

Hi Marina

I assume you are using some sort of persistant conversations, since you do not seem to end conversations, but maybe you simply did not include that code.

Ok so i am guessing that basically you want track if sending the message was successful.

Alot of errors are outside of the scope of the instance sending the message and often result in the transmission status being empty. In order to get the proper error you have to use profiler.

So it is not really possible to capture all possible errors that may occur at the remote service. When you say catch it and handle it properly what exactly do you mean ? If i was sending messages to a disabled queue i would keep sending them and let them build up, as soon as the issue is resolved on the remote service the message would be sent and carry on processing.

What i do is send messages regardless, and i have a sql job that runs as desired, every 15 minutes say, is checks the sys.transmission queue, if there are messages in the transmisison_queue it will log the problem and send db mails, then investigation maybe required (like running profiler).

As far as tracking if messages are successfull i record this at the other side for example message 1 can be sent to Server B, only in the activated SP at server B do i update a table to say the message has been received.

The transmisison queue is not always the best indicator , acknowledgements are not visible in the transmission queue, say for example you are sending a big message from Server B to server A on the same converation, any acknowledgements for messages sent from Server A to B would be stuck after the large message so even though server B has received the messages acknowledgements to Server A are delyed and the transmission_queue will not be clear even though messages have been received.

Hope this helps.

Thanx

|||

Hi Dietz,

I will try to reply to all your doubts regarding my post.

1) I don't close the conversation because I end the conversation on the Activated SP on the Target queue when I receive the message. I don't end the conversation after the SEND statement.

2) if I want to update a table that contains a flag to show whether the message has been received or not it is better that I performs the check on the Activated Sp on the Target queue and I update this table within that SP, because the Count(*) on the initiator's transmission queue is not completely reliable.

3) For sure I'm going to setup an Event Notification that alert somebody when the queue is disabled and I know that I should be so worried about the messaged that are sent in the meanwhile because anyway they stay in the Initiator's transmission_queue but I would like to LOG anywhere these messages.
If I execute the code that I wrote in my blog there is no way to get the transmission_queue record.

I thought that SB checking whether the target queue is enable between the SEND and the COMMIT.

I hope that my doubt/problem is more clear now

Cheers

Marina B.

|||

Hi Marina

1.Ok Cool.

2.Yes i would reccomend checking if a message has been received at the target side activated sp.

3.If you can create a linked server between the 2 instances then you could check the remote instances, sys.service_queues for the service status before you send a message. If you cannot query the other instance remotely then you cannot really be sure of the targer servcies status.

Thanx