Same sql server instance, 2 different DB: messages are inserted into initiator's sys.transm
Hello,
I have a question..
I set-up my Service Broker comunication on the same SQL Server Instance beetween 2 different DBs.
One DB behave as Initiator and send messages to the SB service setup in the other DB.
I execute the SEND statement from the Initiator and if I count the messages on the sys.transmission_queue before to commit the transaction the count returns 0.
If I try to send a message not compliant with the message type, the count that runs after the SEND returns 1 - far enought.
I'm confused about the first behaviour because from what I understood the Acknolodgment and the removal of the message from the sys.trasmission queue should happen after the COMMIT.
Some expert can aswer to this direct quection?
Thank you
Marina B.
I'm not an expert by any means. However (at least it used to be like this), when you're sending messages within the same server instance, the messages does not go over sys.transmission_queue, but they'd be inserted directly into the other queue. When delivery fails for some reason, they'll be put on the sys.transmission_queue.
HTH
Niels
Thankx ...
I have to write some technical docs and I'm trying to drawn in a diagram that shows what happens within SB related to "my" message flow.
I found documentation here and there in the network but sometimes it is not so very well explained what happens inside SB.
Thankx
Marina
For a local message delivery (within the same SQL Server instance) Service Broker would try first to enqueue the message directly on the target queue, because that is a huge optimization. If that operation fails for whatever reason, then the message is inserted in the transmission queue and delivery is attempted again after the transaction commits.
