notoriousdba (10/25/2011)

---

No, no different queues. All messages are going into the same service in the source database, to be delivered to the same service on the target database.

Let me be more specific:

1) The app calls a proc to add a new person.

2) The proc inserts a new record in the person table.

3) A trigger on the person table sees the insert and sends a message via service X to service Y to add the new person.

4) The app calls a proc to link the new person to an org.

5) The proc inserts a new record in an xref table linking the person to the org.

6) A trigger on the xref table sees the insert and sends a message via service X to service Y to add the new link.

7) When the messages arrive in the queue for service Y in the target database, sometimes the message to add the new person arrives after the message to add the link between the new person and an org.

Clearly the message to add the link couldn't be sent before the message to add the user, so given that service broker guarantees that messages are delivered in the order they are sent, I'm a bit nonplussed as to how they are arriving in a different order than it seems they logically should.