conversation groups and processing in order on the target end

Is there any way to ensure that messages sent on different dialogs have the same conversation group id on the target queue? I was attempting to set the conversation group id on the dialog before sending but learned that this only works on the initator end.

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=174976&SiteID=1

I have messages that could be sent from different applications (and at slightly different times) that need to processed exclusively (i.e. have the same conversation group id).

Message ordering is guaranteed within a conversation. Is there any reason that stops you from sending all messages on the same conversation?

HTH,
~ Remus

|||Thanks for the reply - Yes, the sending of messages will be distributed and at different times. i.e. 3 different applications will send messages to the same queue. The messages have to be processed exclusively I tried using one queue as both the initiator and target but the converation group IDs were still different. The move conversation command works but only after messages have been received at the target queue.
|||

Having the conversation from one queue to the same queue does not help, as you noticed, the initiator and target conversation don't share the conversation group. The behavior of conversations that are with local (or event the same) service/queues is in every respect the same as conversations that are with remote services/queues.

But in general, trying to use the conversation group to achieve ordering will get you nowhere. Conversation groups only control what messages can be received by a transaction. That is, they're only relevant to locking. Even if you manage to move all three conversations into the same group and all three messages will be present in the queue, the RECEIVE verb will return them in random order! The messages are guaranteed to be in order within a conversation, not within a group. Actually, you can check the RECEIVE verb execution query plan in the SQL Management Studio and you'll see that it has an ORDER BY clause. This will sort the resultset (a.k.a the messages received) by conversation_handle (among other columns), which is a GUID, so basically the order of conversations within a group is random.

The only way to achieve order is to use one single conversation and send all messages on it. It doesn't matter if they are sent at different times, they will be RECEIVEd in the order sent.

The very problem of preserving order between 3 messages sent from 3 distributed locations is, in my opinion, unsolvable. 3 distributed locations means 3 distinct local times. Which is the authoritative time between them? I.e. if message 'two' was sent one minute after message 'one', but the time on location 'two' is two minutes behind, which message should be considered as sent first? Or if 2 of the 3 messages are sent at identical time 05:10:00 AM, which one was sent first? Or what if message one was sent fine, but the machine hosting location two was destroyed by a suden burst of cosmic rays just before message 'two' being sent, and then message 'three' was sent fine, is message 'three' really message 'three' or is message 'two'?
I only see this problem resolvable if the messages are sent using a primitive that serializes the messages (i.e. the conversation).

Also keep in mind that even if you achieve order by using a single conversation, you still won't be guaranteed that you'll RECEIVE all 3 messages at once (in one single RECEIVE resultset). Messages are RECEIVEd as they show up in the queue, so is perfetcly possible to RECEIVE message 'one' and 'two' while message 'three' is still in traffic.

If you can give some more details on what are you trying to do, I feel we can probably think of a way to achieve what you need.

HTH,
~ Remus

|||

Thanks again for the quick reply

I think I may have phrased the problem incorrectly. I only need the messages in a single conversation to be ordered, but I need for the messages in the three different conversations to be processed exclusively (The processing of the messages will be distributed as well). Unfortunately I cannot send all the messages in a single conversation, so I though I could just designate them to a single conversation group so that only one message from any of the three conversations could be processed at one time.

Any suggestions?

|||You can put the three dialogs in the same conversation group but you will need to do it at the target end when you receive the messages - using MOVE CONVERSATION. I don't know of a practical way to do this. I generally send messages out on different dialogs in the same conversation group so the responses are received together. For example, you could have one of your dialogs send a message to the target and when you receive the message on the target queue, open two new dialogs in the same conversation group as the received message and have those two dialogs send out request messages to get the data back. Pretty convoluted but it should work.|||

You can also add an extra message to your conversation. This message is sent first and contains some form of tag or Id based on which when the target receives this first message, it moves the target conversation endpoint into an appropiate group.

So the initiator does normal BEGIN DIALOG, followed by two SEND (one for the tag/id and one for the real payload). The target receives messages normally in a loop, but reacts differently to the two message types:
- if the message is a tag/id message, it finds the appropiate group and moves the conversation into that group
- if the message is normal payload it process it normally, assured that is the only one processing this group

HTH,
~ Remus

Control Flow Task Error shouldn't fail package

Hi all,

I have a Send Mail Task in my control flow to notify users that the processing is done. I want to avoid the package to fall in error if the Send Mail task failed.

What is the best practice to do that ?

Should I raise the MaximumErrorCount of theSend Mail Task ? Should I play with ErrorHandler ?

Try setting the Send Mail Task's "ForceExecutionResult" to "Success"

Consolidation - Changing replicated data in a central subscribing site

Hi all,

I am new to replication and have a few questions.

1) Are there any "hooks" available to insert processing when a subscriber is about to copy data from a replicating site?

2) Is it possible for a subscriber to change only his local copy of the data - without replicating the changes back to the publisher?

I realise that once the data changes in one place it isn't really replicated anymore, and I realise that my limited knowledge of the subject might well mean I'm not even asking the right questions. Therefore, I shall try to describe as best I can my scenario.

I wish to use many servers for transactional input (to distribute the workload) and use replication to publish the inputted data to a subscribing central site. One of the tables I wish to replicate has an identity column as primary key, but the records should otherwise be unique - i.e. no two records should differ only in the value of the key. Another table, which should also be replicated, uses this id value as a foreign key.

I can use the identity increment and seed to guarantee no key violations will occur when copying data to the central server. However, there is another issue: Several servers can create the same record but with different id values.

I need to "merge" such records by deleting duplicate entries in the table with the identifier as primary key, and update the foreign keys correspondingly. To clarify (I hope!), here's an example of what data I might have on the central site after copying data from two input sites:

TRANSACTION table

amount = 200, metadata_id = 1001 // Replicated from server INPUT_1

amount = -117, metadata_id = 2001 // Replicated from server INPUT_2

METADATA table:

id=1001 Actitiy=Sales, Country=USA

id=2001 Activity=Sales, Country=USA

What I would like is basically for the central site to identify that metadata 2001 is really the same as metadata 1001, update the foreign key in the TRANSACTION record accordingly and not import (or delete, if this "merging" is done in a post-treatment) the duplicate metadata record.

If anyone can offer any advice on how to achieve this I would appreciate your input.

Hi,

Do you use transactional replication and merge replication? For transactional replication, you can use @.ins_cmd parameter in sp_addarticle to create your custom insert stored procs. For details, you can refer to the following documents: http://msdn2.microsoft.com/en-us/library/ms152489.aspx. For readonly transactional replication, the replication is one way and the changes won't be relayed back to the publisher.

Peng

|||

Hi,

Thank you for your response. We're not actually using any kind of replication yet, merely planning to do so. But it seems from what you're saying that "readonly transactional replication" fits the bill nicely.

Cheers,

Dag

Consolidation - Changing replicated data in a central subscribing site

Hi all,

I am new to replication and have a few questions.

1) Are there any "hooks" available to insert processing when a subscriber is about to copy data from a replicating site?

2) Is it possible for a subscriber to change only his local copy of the data - without replicating the changes back to the publisher?

I realise that once the data changes in one place it isn't really replicated anymore, and I realise that my limited knowledge of the subject might well mean I'm not even asking the right questions. Therefore, I shall try to describe as best I can my scenario.

I wish to use many servers for transactional input (to distribute the workload) and use replication to publish the inputted data to a subscribing central site. One of the tables I wish to replicate has an identity column as primary key, but the records should otherwise be unique - i.e. no two records should differ only in the value of the key. Another table, which should also be replicated, uses this id value as a foreign key.

I can use the identity increment and seed to guarantee no key violations will occur when copying data to the central server. However, there is another issue: Several servers can create the same record but with different id values.

I need to "merge" such records by deleting duplicate entries in the table with the identifier as primary key, and update the foreign keys correspondingly. To clarify (I hope!), here's an example of what data I might have on the central site after copying data from two input sites:

TRANSACTION table

amount = 200, metadata_id = 1001 // Replicated from server INPUT_1

amount = -117, metadata_id = 2001 // Replicated from server INPUT_2

METADATA table:

id=1001 Actitiy=Sales, Country=USA

id=2001 Activity=Sales, Country=USA

What I would like is basically for the central site to identify that metadata 2001 is really the same as metadata 1001, update the foreign key in the TRANSACTION record accordingly and not import (or delete, if this "merging" is done in a post-treatment) the duplicate metadata record.

If anyone can offer any advice on how to achieve this I would appreciate your input.

Hi,

Do you use transactional replication and merge replication? For transactional replication, you can use @.ins_cmd parameter in sp_addarticle to create your custom insert stored procs. For details, you can refer to the following documents: http://msdn2.microsoft.com/en-us/library/ms152489.aspx. For readonly transactional replication, the replication is one way and the changes won't be relayed back to the publisher.

Peng

|||

Hi,

Thank you for your response. We're not actually using any kind of replication yet, merely planning to do so. But it seems from what you're saying that "readonly transactional replication" fits the bill nicely.

Cheers,

Dag