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.