notoriousdba (11/3/2011)
Craig,Ah, that's an excellent idea. I should have thought of that. Thanks!
My pleasure. Sorry it's taken me a while to get back to this, was down ill for a bit.
BTW, no, the messages being sent aren't in the same conversation, but what I found is that this is happening because the app calling the procs has them wrapped in its own transaction. Without the outer transaction surrounding the two procedure calls, things work the way I expect them to.
Has... what... wrapped in the transaction? Sorry, you lost me there.
I'm trying to make this whole setup less brittle, so that things like this wouldn't have any effect. I tried forcing all the messages to be part of the same conversation, and that worked for a while, but has broken down for reasons I haven't figured out yet.
That can be tough to do, and you usually want broker to handle the conversation associations, but you can pull it off if you're careful. We'd have to go through your process with a fine toothed comb though to find the break.
Clearly this process isn't as simple as I had hoped, and I'm probably going to have to do some major re-writing. ... I'd hoped to not have to build something that elaborate, but it looks like I don't have much choice.
-Chris
There's one other choice... turn off the foreign key association and let the database be eventually consistent.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA