Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Fire and Forget Solution.


Fire and Forget Solution.

Author
Message
Bobby Glover
Bobby Glover
SSC Veteran
SSC Veteran (225 reputation)SSC Veteran (225 reputation)SSC Veteran (225 reputation)SSC Veteran (225 reputation)SSC Veteran (225 reputation)SSC Veteran (225 reputation)SSC Veteran (225 reputation)SSC Veteran (225 reputation)

Group: General Forum Members
Points: 225 Visits: 751
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.
Nils Gustav Stråbø
Nils Gustav Stråbø
SSCrazy
SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)

Group: General Forum Members
Points: 2117 Visits: 3575
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
Bobby Glover
SSC Veteran
SSC Veteran (225 reputation)SSC Veteran (225 reputation)SSC Veteran (225 reputation)SSC Veteran (225 reputation)SSC Veteran (225 reputation)SSC Veteran (225 reputation)SSC Veteran (225 reputation)SSC Veteran (225 reputation)

Group: General Forum Members
Points: 225 Visits: 751
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ø
Nils Gustav Stråbø
SSCrazy
SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)

Group: General Forum Members
Points: 2117 Visits: 3575
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 Smile
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
Bobby Glover
SSC Veteran
SSC Veteran (225 reputation)SSC Veteran (225 reputation)SSC Veteran (225 reputation)SSC Veteran (225 reputation)SSC Veteran (225 reputation)SSC Veteran (225 reputation)SSC Veteran (225 reputation)SSC Veteran (225 reputation)

Group: General Forum Members
Points: 225 Visits: 751
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
Bobby Glover
SSC Veteran
SSC Veteran (225 reputation)SSC Veteran (225 reputation)SSC Veteran (225 reputation)SSC Veteran (225 reputation)SSC Veteran (225 reputation)SSC Veteran (225 reputation)SSC Veteran (225 reputation)SSC Veteran (225 reputation)

Group: General Forum Members
Points: 225 Visits: 751
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
Bobby Glover
SSC Veteran
SSC Veteran (225 reputation)SSC Veteran (225 reputation)SSC Veteran (225 reputation)SSC Veteran (225 reputation)SSC Veteran (225 reputation)SSC Veteran (225 reputation)SSC Veteran (225 reputation)SSC Veteran (225 reputation)

Group: General Forum Members
Points: 225 Visits: 751
Is it best to have 2 queues one for the initiator and one for the target ?
Nils Gustav Stråbø
Nils Gustav Stråbø
SSCrazy
SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)

Group: General Forum Members
Points: 2117 Visits: 3575
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
Bobby Glover
SSC Veteran
SSC Veteran (225 reputation)SSC Veteran (225 reputation)SSC Veteran (225 reputation)SSC Veteran (225 reputation)SSC Veteran (225 reputation)SSC Veteran (225 reputation)SSC Veteran (225 reputation)SSC Veteran (225 reputation)

Group: General Forum Members
Points: 225 Visits: 751
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ø
Nils Gustav Stråbø
SSCrazy
SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)

Group: General Forum Members
Points: 2117 Visits: 3575
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.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search