Fire and Forget Solution.

Question

Fire and Forget Solution.

Bobby Glover

SSCarpal Tunnel

Points: 4143
More actions
March 25, 2013 at 9:30 am

#275549

Fire and Forget Solution
The solution is let the target end the conversation first. The initiator can simply send the message to the target and then continue. The target will receive the message, end the conversation from the target side and this will send an EndDialog message back to the initiator. All we have to do is attach an activated stored procedure to the initiator’s queue, and in this stored procedure we can end the initiator side of the conversation:
How does this work if the initiator is part of a trigger. If it is changed to a SYNCHRONOUS setup. Does the trigger close after the send message is fired or does it have to wait for the full conversation cycle to complete. If so how can this be avoided in a synchronous setup.

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

Nils Gustav Stråbø SSChampion Points: 11259 More actions · Answer 1

Bobby Glover (3/25/2013)
Fire and Forget Solution
The solution is let the target end the conversation first. The initiator can simply send the message to the target and then continue. The target will receive the message, end the conversation from the target side and this will send an EndDialog message back to the initiator. All we have to do is attach an activated stored procedure to the initiator’s queue, and in this stored procedure we can end the initiator side of the conversation:
How does this work if the initiator is part of a trigger. If it is changed to a SYNCHRONOUS setup. Does the trigger close after the send message is fired or does it have to wait for the full conversation cycle to complete. If so how can this be avoided in a synchronous setup.

1. What you describe is not a fire and forget solution. You describe is a conversation where both sides end the conversation correctly.

2. There is nothing called a synchronous service broker setup. Service broker as asynchronous, unless something changed in SQL Server 2012 that is not documentet in BOL.

Sending a message from a trigger is no different from sending from a sproc or ad-hoc T-SQL. The execution will continue once the message has been placed in the transmission queue. It does not wait for the message to be sent to the target, nor does it wait for the target to respond. You have to handle message processing manually or by using activation procedures.

Bobby Glover SSCarpal Tunnel Points: 4143 More actions · Answer 2

The first paragraph was a comment online to resolving a fire and forget setup. What I’m trying to do is setup a SYNCHRONOUS process and change it from an ASYNCHRONOUS process F and F.

OK so let me get this straight as this will save me countless hours. If the initiator is part of the trigger. Once it fires the trigger is released and allows the user to continue, ending the transaction service broker then takes over and completes the remainder in the back ground.

This is based on the assumption that the initiator doesn’t end the conversation first.

Nils Gustav Stråbø SSChampion Points: 11259 More actions · Answer 3

Bobby Glover (3/25/2013)
The first paragraph was a comment online to resolving a fire and forget setup. What I’m trying to do is setup a SYNCHRONOUS process and change it from an ASYNCHRONOUS process F and F.
OK so let me get this straight as this will save me countless hours. If the initiator is part of the trigger. Once it fires the trigger is released and allows the user to continue, ending the transaction service broker then takes over and completes the remainder in the back ground.
This is based on the assumption that the initiator doesn’t end the conversation first.

You got it 🙂

The only thing the trigger will have to wait for is placing the message on the transmission queue.

The message will not be sent and received until your transaction commits, so it is a very lightweigh operation.

We actually use this trigger pattern for two relatively heavy processes in one of our production environments. One of them is to maintain a de-normalized table of tables related to documents in our in-house developed document management system. This table has a full text index that we use for full text queries.

The other thing we use it for is to maintain row-level ACL inheritance in the same document management system.

Both of the service broker "installations" have been running without problems for years now.

Bobby Glover SSCarpal Tunnel Points: 4143 More actions · Answer 4

Thanks, that will save me hours sitting at my desk looking totallyt p*ssed off.

At last there is light at the end of the tunnel.

Thanks again.

Bobby Glover SSCarpal Tunnel Points: 4143 More actions · Answer 5

Of course Service Broker is always ASYNC. It wouldn't be a queueing system if it wasn't. That little fact did slip me. Thanks

Bobby Glover SSCarpal Tunnel Points: 4143 More actions · Answer 6

Is it best to have 2 queues one for the initiator and one for the target ?

Nils Gustav Stråbø SSChampion Points: 11259 More actions · Answer 7

I prefer two queued, even though there is nothing that stops you from using the same queje for multiple services

The reason I prefer having separate queues for each service is the activation procedures. I like to have target handling and initiator handling in separate procedures, and that of cpurse requires separate queues.

Bobby Glover SSCarpal Tunnel Points: 4143 More actions · Answer 8

Thanks, do you mind if a bug you a little more on this one. There is very little on the NET that is useful in this area. Not specific enough.

I'm getting closer to my my goal with every one of your replies.

Nils Gustav Stråbø SSChampion Points: 11259 More actions · Answer 9

Feel free to ask 🙂

It's easter holiday here in Norway now, and I'm up in the mpintain where the internet connection is very limited, so I can't guarantee an answer before I'm back in civilization.