Service Broker Check after Receive?
in [Programming SQL]
|
From: SnapDive on 7 Jul 2010 11:18 > >With activation, it's not possible to filter the activation to a specific >conversation. This design won't work if you need the listeners separate: any >listener will be activated when a message arrives on the queue, not >necessarily the one you want. You'd need to post-filter in the listeners >themselves, i.e. have one listener and route the traffic as necessary. OK I think I understand that part now. I think (dont know) the EA thing from the MS SQL Server 2008 Feature Pack does this exact thing, ie it is the only listener and it routes to (new) external exes. >- In the trigger, determine whether it's a type A or a type B message and >SEND on the appropriate conversation. >- In listeners A and B, execute a WAITFOR(RECEIVE) loop with a suitable >timeout: WAITFOR (RECEIVE message_name, CONVERT(XML, message_body)) FROM >DemoQueue WHERE conversation_handle = @ch), TIMEOUT 30000. Then what I'd need to do is have the trigger store the conversation handle after the send to a table with some key, and then the listener would have to select on the most recent key to get the conversation handle in order to do the receive where conversation_handle=.... Which means the listener would require a short timeout so it could constantly look at the conversation table for new conversations to receive. Unless there is another 'central' way to keep the conversation handles known to SB as well as the external EXE listeners. Am I getting closer in my understanding? Thanks.
From: Jeroen Mostert on 7 Jul 2010 14:08
On 2010-07-07 17:18, SnapDive wrote: >> - In the trigger, determine whether it's a type A or a type B message and >> SEND on the appropriate conversation. >> - In listeners A and B, execute a WAITFOR(RECEIVE) loop with a suitable >> timeout: WAITFOR (RECEIVE message_name, CONVERT(XML, message_body)) FROM >> DemoQueue WHERE conversation_handle = @ch), TIMEOUT 30000. > > Then what I'd need to do is have the trigger store the conversation > handle after the send to a table with some key No, there are only two conversations in this entire setup and their handles remain constant. So you could either simply plug those handles into the trigger, or store them in a table, but either way the trigger doesn't need to store anything. If you want more flexibility in how to classify messages and the number of listeners, this setup has to change accordingly, but in any case you'd expect the trigger to just look it up. You should reuse conversations as much as possible, because setting them up and tearing them down is fairly expensive. You certainly don't want to use one conversation per message, that's a killer. > Unless there is another 'central' way to keep the conversation handles > known to SB as well as the external EXE listeners. > Conversation handles are recorded in sys.conversation_endpoints, and you *can* look them up there if you want -- but this requires a set of permissions I'm not fully aware of (I know that GRANT VIEW ANY DEFINITION does the trick, but I've not taken the time to narrow down the permission set). Take a look at http://code-bin.homedns.org/729, which contains some helper code I've found invaluable in creating and looking up conversations. If service names are not sufficient and you want more detailed control over what conversations are used for, you'll to maintain your own table. -- J. |