If this is the wrong place to ask, please redirect me.

I have a table with an after insert trigger that takes some stuff from
the OUTPUT clause, converts it into XML, and then dumps that into a
Service Broker queue. I have an external activation queue setup so the
message can get sent to an external .NET app listening for work.

The .NET app may not get the work done, and it knows it internally.
What is the best way to talk back to the queueing infrastructure so
the previously-received item can get re-queued and re-done by the .NET
app again?

Thanks.

On 2010-06-30 5:02, SnapDive wrote:
> If this is the wrong place to ask, please redirect me.
>
There's a separate service broker newsgroup, but it sees practically no
activity, so you might as well come here.

> I have a table with an after insert trigger that takes some stuff from
> the OUTPUT clause, converts it into XML, and then dumps that into a
> Service Broker queue. I have an external activation queue setup so the
> message can get sent to an external .NET app listening for work.
>
> The .NET app may not get the work done, and it knows it internally.
> What is the best way to talk back to the queueing infrastructure so
> the previously-received item can get re-queued and re-done by the .NET
> app again?
>
There are multiple approaches:

- You can do the RECEIVE and the processing in a transaction and roll it
back if you can't process in time. The drawback of this is that you hold
open a transaction for the duration of processing. I don't know if this can
prevent SEND from working (I don't think so, but I've never tried it), if it
can that's obviously a major problem. Even without blocking SEND, this
limits concurrency -- you can't really have more than one queue reader
active with this approach. This solution guarantees that you will process
requests strictly in order, which may or may not be required.

- Receive as normal and if you're not done in time, do a SEND on the queue
just like the trigger would. This means messages are no longer processed
in-order. The "churn" you cause if most or all messages need to be
reprocessed this way and the timeout is short can seriously hamper
performance, as your SENDs will compete with the trigger's, but unlike the
previous solution there's no problem with multiple readers.

- As before, but do a SEND not on the original queue but a special retry
queue. This allows processing on the original queue to continue smoothly,
which improves concurrency, and you can set different activation policies on
the retry queue. Drawback is that you need a separate queue with separate
services and separate conversations, which means more administration.

For doing the SEND on the proper conversation in the last two solutions I'd
pass the conversation handle to send retries on in the message itself so you
keep the queue details in the database (and not hard- or soft-code the
handle in the application).

All of these solutions have problems with poison messages: if your
application is never finished in time because it can't (the data isn't
processed correctly due to a bug, or it will simply always take longer than
you allow) the queue will grow without bounds. To combat this, you can
include a time stamp and/or retry count with the message so you can discard
it if it goes unprocessed for too long. If discarding messages is not an
option, monitor the queue size so you can manually intervene if processing
is too slow.

--
J.

I think this is the tactic I'll try since it keeps the number of
queues to a minimum. The processing order does not matter. Timing
doesn't matter either now that I think on it more... The console app
is going to launch some stuff on a background thread, and I want to
put that in a try/catch, so I guess if I have to catch I'll delete the
original table row, insert it again, which will fire the trigger which
will load the target queue which will send to the event queue.

I was going to use this code as a base for mine:

http://webcache.googleusercontent.com/search?q=cache:xp8JWDnTOXQJ:rusanu.com/2007/04/11/consuming-event-notifications-from-clr/+%2BPostEventNotification+%22using+system%22&cd=1&hl=en&ct=clnk&gl=us&client=firefox-a


However, this is mentioned:
"
One thing to keep in mind is that the Event Notifications are going to
be fired even when the application is not running. So the application
might start up and find a number of notifications pending from the
period was not running and has to be prepared to deal with this case.
"

I'm not sure what that means, based on the code, wouldn't the previous
notifications just be "missed"?

Thanks.




On Wed, 30 Jun 2010 08:30:52 +0200, Jeroen Mostert
<jmostert@xs4all.nl> wrote:

>
>- Receive as normal and if you're not done in time, do a SEND on the queue
>just like the trigger would. This means messages are no longer processed
>in-order. The "churn" you cause if most or all messages need to be
>reprocessed this way and the timeout is short can seriously hamper
>performance, as your SENDs will compete with the trigger's, but unlike the
>previous solution there's no problem with multiple readers.

On 2010-07-01 2:43, SnapDive wrote:
> I think this is the tactic I'll try since it keeps the number of
> queues to a minimum. The processing order does not matter. Timing
> doesn't matter either now that I think on it more... The console app
> is going to launch some stuff on a background thread, and I want to
> put that in a try/catch, so I guess if I have to catch I'll delete the
> original table row, insert it again, which will fire the trigger which
> will load the target queue which will send to the event queue.
>
> I was going to use this code as a base for mine:
>
> http://webcache.googleusercontent.com/search?q=cache:xp8JWDnTOXQJ:rusanu.com/2007/04/11/consuming-event-notifications-from-clr/+%2BPostEventNotification+%22using+system%22&cd=1&hl=en&ct=clnk&gl=us&client=firefox-a
>
Event notifications are a particular application of service broker. You
don't need to use them in the scenario you describe (a trigger inserting a
message into a queue). You can simply do a SEND in the trigger and a RECEIVE
in the application (with the [DEFAULT] contract or a schema of your own).

> However, this is mentioned:
> "
> One thing to keep in mind is that the Event Notifications are going to
> be fired even when the application is not running. So the application
> might start up and find a number of notifications pending from the
> period was not running and has to be prepared to deal with this case.
> "
>
> I'm not sure what that means, based on the code, wouldn't the previous
> notifications just be "missed"?
>
No. The queue will be filled with messages regardless of whether someone's
there to receive them. You may be confusing event notifications with query
notifications, another application of Service Broker which does work this
way: notification events are only produced if an application is "listening".
In .NET, this is implemented through the SqlDependency class. It's a little
finicky to use, but on the plus side, it doesn't require you to create
explicit triggers or even anything specific to Service Broker (this is all
done under the hood).

--
J.

Very cool, thanks for the insight. I was mixing up technologies in my
head. I have the basics working. Thanks!

My follow-up question is -

How can I specify that I only want to receive a message of a
particular type? Right now everything is loose but as I get more clues
I will have multiple queriers each going into the same queue and I
don't want them to get messages that are not created for their
consumption.


Thanks again!



On Thu, 01 Jul 2010 09:08:42 +0200, Jeroen Mostert
<jmostert@xs4all.nl> wrote:

>> However, this is mentioned:
>> "
>> One thing to keep in mind is that the Event Notifications are going to
>> be fired even when the application is not running. So the application
>> might start up and find a number of notifications pending from the
>> period was not running and has to be prepared to deal with this case.
>> "
>>
>> I'm not sure what that means, based on the code, wouldn't the previous
>> notifications just be "missed"?
>>
>No. The queue will be filled with messages regardless of whether someone's
>there to receive them. You may be confusing event notifications with query
>notifications, another application of Service Broker which does work this
>way: notification events are only produced if an application is "listening".
>In .NET, this is implemented through the SqlDependency class. It's a little
>finicky to use, but on the plus side, it doesn't require you to create
>explicit triggers or even anything specific to Service Broker (this is all
>done under the hood).

On 2010-07-01 21:44, SnapDive wrote:
> How can I specify that I only want to receive a message of a
> particular type? Right now everything is loose but as I get more clues
> I will have multiple queriers each going into the same queue and I
> don't want them to get messages that are not created for their
> consumption.
>
The only way to do that is to have multiple conversations, one for each
query. There is no way to selectively receive broker messages other than by
conversation. You can do this as the message is produced or by sending them
all to the same queue and having a stored procedure activate on that queue
which receives the messages and sends them on different conversations
depending on their content. You'll need a second queue for this.

--
J.

Excuse my newbiness.

So right now I have

Trigger-A, Queue-A, EAQueue-A, .NET Listener-A. Listener-A receives on
EAQueue-A, goes to Queue-A, and gets some work to do. This lets
another message from Queue-A notify into EAQueue-A, and work
continues.

Now I want to add Listener-B and have it go to Queue-A for some
processing. I don't want Listener-B to block Listener-A from doing
work. I want each Listener to be ignorant of what other Listener apps
might be doing.

Are you saying that my current Trigger-A should be replaced with
conditional logic to create a different conversation/message for
Queue-B? Or that there needs to be a different conversation between
Queue-A and EAQueue-A?



Thanks again for helping guide me on this!



* I am aware that the SQL Server Feature Pack has a great EA
technology download but I am currently prevented from installing those
bits in Production.


On Fri, 02 Jul 2010 08:12:46 +0200, Jeroen Mostert
<jmostert@xs4all.nl> wrote:

>On 2010-07-01 21:44, SnapDive wrote:
>> How can I specify that I only want to receive a message of a
>> particular type? Right now everything is loose but as I get more clues
>> I will have multiple queriers each going into the same queue and I
>> don't want them to get messages that are not created for their
>> consumption.
>>
>The only way to do that is to have multiple conversations, one for each
>query. There is no way to selectively receive broker messages other than by
>conversation. You can do this as the message is produced or by sending them
>all to the same queue and having a stored procedure activate on that queue
>which receives the messages and sends them on different conversations
>depending on their content. You'll need a second queue for this.

On 2010-07-05 5:21, SnapDive wrote:
> So right now I have
>
> Trigger-A, Queue-A, EAQueue-A, .NET Listener-A.

Your identifiers are rather confusing. What's "EAQueue"?

> Listener-A receives on EAQueue-A, goes to Queue-A, and gets some work to
> do. This lets another message from Queue-A notify into EAQueue-A, and
> work continues.
>
> Now I want to add Listener-B and have it go to Queue-A for some
> processing. I don't want Listener-B to block Listener-A from doing work.
> I want each Listener to be ignorant of what other Listener apps might be
> doing.
>
If the queue's items are first-come, first-serve and all messages are equal,
you can have as many receivers on it as you like for throughput. They won't
block each other (except for the brief periods of time when they're
receiving a message, which is unlikely to become a bottleneck).

However, if B can only process *certain* items and A can only process
*certain* items, you need separate conversations.

> Are you saying that my current Trigger-A should be replaced with
> conditional logic to create a different conversation/message for
> Queue-B? Or that there needs to be a different conversation between
> Queue-A and EAQueue-A?
>
Conversations are between services, not queues. Services can only receive on
conversations from one particular queue, but can send to any conversation.

If A and B were substantially different (in that A never does the kind of
work B does and vice versa) then your trigger should contain logic that
determines what conversation should be used (the one A is receiving on or
the the one B is receiving on). The conversations can be on the same queue
or different queues, that doesn't matter much.

If it's important that, say, items A cannot process in time are picked up
again by A and not B, then again you'd need a separate conversation to
ensure only A picks up the retry.

--
J.

First of all, thanks for sticking with me on this!


Here is my trigger code on a table. I want to replicate this
functionality in triggers on different tables. I tried to hardcode the
conversation GUID so that when the externally-activated app goes into
the queue, it can put the conversation handle in the where clause. But
my hardcoded handle is not making it into the DB.
    AFTER INSERT
    AS Begin
    Set NoCount On
    Declare @msgXml VarChar(Max) =
     '<?xml version="1.0" encoding="UTF-8" ?>'
    + Cast ( ( Select * From Inserted For Xml Auto , Type ,
Root('testMessage')  ) as VarChar(Max) )
    
    Declare @Handle UniqueIdentifier =
'8493219D-FAC9-4C96-9C72-70ED47E0E2E2'
    Begin Dialog Conversation @Handle
    From Service [DemoService]    
    To Service 'DemoService'
    On Contract [DemoContract]
    With Encryption = Off;
    Send On Conversation @Handle Message Type
[DemoSendMessageType] (@msgXml);
    select @Handle ,  @msgXml



Here is the code used to create the queueing infrastructure:
    Create Message Type [DemoSendMessageType]
    Validation = WELL_FORMED_XML;
    Go
    Create Contract [DemoContract]
    (     [DemoSendMessageType] SENT BY Any )
    Go
    Create Queue DemoQueue
    Go
    Create SERVICE DemoService ON Queue [DemoQueue]
    ( [DemoContract]    )
    Go
    ALTER QUEUE [DemoQueue] WITH ACTIVATION (DROP)
    Go
    Create Queue ExtActivationQueue
    Go
    Create Service ExtActivatorService
    On Queue ExtActivationQueue (

[http://schemas.microsoft.com/SQL/Notifications/PostEventNotification]
)
    Go
    Create Event Notification DemoEventNotif ON
    QUEUE DemoQueue FOR QUEUE_ACTIVATION
    TO SERVICE 'ExtActivatorService', 'current database'



My client application is wired up to ExtActivationQueue, and when I do
an insert into the primary table, I see this on my client app console:
<EVENT_INSTANCE>
  <EventType>queue_activation</eventtype>
  <PostTime>2010-07-06t12:09:10.370</posttime>
  <SPID>25</SPID>
  <ServerName>demosvr\inst02</servername>
  <LoginName>sa</loginname>
  <UserName>dbo</username>
  <DatabaseName>demodb1</databasename>
  <SchemaName>dbo</schemaname>
  <ObjectName>demoqueue</objectname>
  <ObjectType>queue</objecttype>
</EVENT_INSTANCE>

This tells my app that there is something new on DemoQueue, so I
thought I could receive it like this (from ADO.NET):

RECEIVE   message_type_name,CAST(message_body as XML)
FROM [DemoQueue]
where conversation_handle = '8493219D-FAC9-4C96-9C72-70ED47E0E2E2'

Since I have hardcoded the conversation handle. However, the handle
does not appear in DemoQueue or ExtActivationQueue.




Right now I just have this single listener, but eventually I will have
two. Each listener should go to the same source for work, but only
receive work that is for them. I am willing to filter by conversation
or actual message content or whatever, but I can't seem to get the
design correct. What am I missing?


Thanks.

On 2010-07-06 20:14, SnapDive wrote:
> Here is my trigger code on a table. I want to replicate this
> functionality in triggers on different tables. I tried to hardcode the
> conversation GUID so that when the externally-activated app goes into
> the queue, it can put the conversation handle in the where clause. But
> my hardcoded handle is not making it into the DB.
>      AFTER INSERT
>      AS Begin
>     Set NoCount On
>     Declare @msgXml VarChar(Max) =
>      '<?xml version="1.0" encoding="UTF-8" ?>'

Lose the prolog, it's unnecessary and wrong (XML is not stored as UTF-8 in
SQL Server).

>     + Cast ( ( Select * From Inserted For Xml Auto , Type ,
> Root('testMessage')  ) as VarChar(Max) )
>     
I think this should simply be

   DECLARE @msgXml XML = SELECT * FROM inserted FOR XML AUTO, TYPE,
ROOT('testMessage');

>     Declare @Handle UniqueIdentifier =
> '8493219D-FAC9-4C96-9C72-70ED47E0E2E2'
>     Begin Dialog Conversation @Handle
>     From Service [DemoService]    
>     To Service 'DemoService'
>     On Contract [DemoContract]
>     With Encryption = Off;

BEGIN DIALOG CONVERSATION will open a new conversation and store the sender
handle in the variable. Assigning a GUID yourself first has no effect. You
can find the sender and receiver handles (or initiator and target handles as
SQL Server calls them) in sys.conversation_endpoints. Open the conversation
once as part of the infrastructure, then keep using the same handles. You
only need to create a new conversation if the old one encounters an error
(which shouldn't happen with a local conversation). For flexibility you may
want to record the handles in a table so the code doesn't need to change if
this happens.

>     Send On Conversation @Handle Message Type
> [DemoSendMessageType] (@msgXml);
>     select @Handle ,  @msgXml
>
The handle you're selecting here is the sender handle, not the receiver handle.

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.

You don't really need activation here. Note that a WAITFOR(RECEIVE)
statement doesn't return until there's a message in the queue. You can
simply achieve what you want by doing the following:

- 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.

And that's it. Activation buys you nothing here.

--
J.