Prev: Using Machine name in DOS Batch
Next: Detect Valid Full backup
From: Jeroen Mostert on 2 Jul 2010 02:12
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.
From: SnapDive on 4 Jul 2010 23:21
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(a)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.

From: Jeroen Mostert on 6 Jul 2010 13:04
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.

From: SnapDive on 6 Jul 2010 14:14
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.




From: Jeroen Mostert on 7 Jul 2010 02:43
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.
First  |  Prev  |  Next  |  Last
Pages: 1 2 3
Prev: Using Machine name in DOS Batch
Next: Detect Valid Full backup