Showing posts with label attempting. Show all posts
Showing posts with label attempting. Show all posts

Tuesday, March 20, 2012

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

Saturday, February 25, 2012

Contains(@v1, @v2) Is this legal?

I am attempting to perform a contains of one variable string in another.
here is a simple example of what I am attempting to do, this should return
true, but I am not sure if this is a limitation of sql server, that it will
now allow a contains on two datatypes. Any ideas?
declare @.t1 varchar(30),
@.t2 varchar(30)
set @.t1 = 'Te'
set @.t2 = 'Test'
if (Contains(@.t2, @.t1))
print 'true'
else
print 'false'
Thanks.Hi, kapsolas
You probably want to use the CHARINDEX function:
IF CHARINDEX(@.t1,@.t2)<>0 ...
For more informations, see:
http://msdn2.microsoft.com/en-us/library/ms186323.aspx
Razvan|||"kapsolas" <kapsolas@.discussions.microsoft.com> wrote in message
news:F05CD410-BFDD-4627-8308-5B774805A2AA@.microsoft.com...
>I am attempting to perform a contains of one variable string in another.
> here is a simple example of what I am attempting to do, this should return
> true, but I am not sure if this is a limitation of sql server, that it
> will
> now allow a contains on two datatypes. Any ideas?
> declare @.t1 varchar(30),
> @.t2 varchar(30)
> set @.t1 = 'Te'
> set @.t2 = 'Test'
> if (Contains(@.t2, @.t1))
> print 'true'
> else
> print 'false'
> Thanks.
Another solution:
declare @.t1 varchar(30),
@.t2 varchar(30)
set @.t1 = 'Te'
set @.t2 = 'Test'
if @.t2 like '%' + @.t1 + '%'
print 'true'
else
print 'false'|||Raymond,
that is the solution I have implemented. Using the LIKE. I wanted to clean
it up a bit to make it more readable by using the Contains.
I'll play with the char index as recommended in the other post as well.
"Raymond D'Anjou" wrote:

> "kapsolas" <kapsolas@.discussions.microsoft.com> wrote in message
> news:F05CD410-BFDD-4627-8308-5B774805A2AA@.microsoft.com...
> Another solution:
> declare @.t1 varchar(30),
> @.t2 varchar(30)
> set @.t1 = 'Te'
> set @.t2 = 'Test'
> if @.t2 like '%' + @.t1 + '%'
> print 'true'
> else
> print 'false'
>
>|||"kapsolas" <kapsolas@.discussions.microsoft.com> wrote in message
news:F5F39D6F-0723-4EE5-B032-E425A6942D33@.microsoft.com...
> Raymond,
> that is the solution I have implemented. Using the LIKE. I wanted to clean
> it up a bit to make it more readable by using the Contains.
> I'll play with the char index as recommended in the other post as well.
I have no experience with Contains.
This is the information I got in BOL:
...You can use the CONTAINS predicate to search a database for a specific
phrase. Of course, such a query can be written using the LIKE predicate.
However, many forms of CONTAINS provide far more text query capabilities
than can be obtained with LIKE. Additionally, unlike using the LIKE
predicate, a CONTAINS search is always case insensitive...
So, if you are not using the extra "query capabilities" of Contains, I
suggest you use one of the other solutions that you got for this post.
Of course, the best would be to test all solutions with your database and
data to find the one that performs the best.|||Thanks for that piece Raymond.
For now I'll use the LIKE and as soon as I have a bit more time i'll
investigate the contains a bit more.
Thanks for your help
"Raymond D'Anjou" wrote:

> "kapsolas" <kapsolas@.discussions.microsoft.com> wrote in message
> news:F5F39D6F-0723-4EE5-B032-E425A6942D33@.microsoft.com...
> I have no experience with Contains.
> This is the information I got in BOL:
> ...You can use the CONTAINS predicate to search a database for a specific
> phrase. Of course, such a query can be written using the LIKE predicate.
> However, many forms of CONTAINS provide far more text query capabilities
> than can be obtained with LIKE. Additionally, unlike using the LIKE
> predicate, a CONTAINS search is always case insensitive...
> So, if you are not using the extra "query capabilities" of Contains, I
> suggest you use one of the other solutions that you got for this post.
> Of course, the best would be to test all solutions with your database and
> data to find the one that performs the best.
>
>|||> that is the solution I have implemented. Using the LIKE. I wanted to clean
> it up a bit to make it more readable by using the Contains.
I don't know why you think that's cleaner or more readable. I guess for
someone who has never used T-SQL and only used FTS, but I think that'd be a
pretty rare bird.
A

Friday, February 24, 2012

CONTAINS function and OR

I am attempting to create a query with a "dynamic" CONTAINS query, e.g.
DECLARE @.Keywords VARCHAR(128)
SET @.Keywords = NULL
SELECT * FROM Jobs WHERE (@.Keywords IS NULL OR CONTAINS(JobTitle,
@.Keywords))
However, the CONTAINS function seems to behave very weirdly when OR is
involved. Even though @.Keywords is null, it still evaluates the contains,
and fails because of the null.
Server: Msg 7603, Level 15, State 1, Line 40
Syntax error in search condition, or empty or null search condition ''.
On the other and, if I change the query to this:
SELECT * FROM Jobs WHERE (@.Keywords IS NULL OR CONTAINS(JobTitle,
@.Keywords)) AND JobID IN (SELECT JobID FROM JobSkills WHERE SkillID = 2)
It works fine.
But that's not it. If I move the order of the clauses:
SELECT * FROM Jobs WHERE JobID IN (SELECT JobID FROM JobSkills WHERE SkillID
= 2) AND (@.Keywords IS NULL OR CONTAINS(JobTitle, @.Keywords))
I get the original error. Same goes for adding another contains clause at
the beginning. It seems that the original CONTAINS works fine, others defy
all logic.
Can someone suggest what is causing this behaviour (or why it works like
this - doesn't seem to make any sense) and a possible workaround short of
using hideous dynamic SQL?
Edward wrote on Fri, 20 Jan 2006 09:38:58 +1100:

> I am attempting to create a query with a "dynamic" CONTAINS query, e.g.
> DECLARE @.Keywords VARCHAR(128)
> SET @.Keywords = NULL
> SELECT * FROM Jobs WHERE (@.Keywords IS NULL OR CONTAINS(JobTitle,
> @.Keywords))
> However, the CONTAINS function seems to behave very weirdly when OR is
> involved. Even though @.Keywords is null, it still evaluates the contains,
> and fails because of the null.
> Server: Msg 7603, Level 15, State 1, Line 40
> Syntax error in search condition, or empty or null search condition
> ''.
> On the other and, if I change the query to this:
> SELECT * FROM Jobs WHERE (@.Keywords IS NULL OR CONTAINS(JobTitle,
> @.Keywords)) AND JobID IN (SELECT JobID FROM JobSkills WHERE SkillID = 2)
> It works fine.
> But that's not it. If I move the order of the clauses:
> SELECT * FROM Jobs WHERE JobID IN (SELECT JobID FROM JobSkills WHERE
> SkillID = 2) AND (@.Keywords IS NULL OR CONTAINS(JobTitle, @.Keywords))
> I get the original error. Same goes for adding another contains clause at
> the beginning. It seems that the original CONTAINS works fine, others defy
> all logic.
> Can someone suggest what is causing this behaviour (or why it works like
> this - doesn't seem to make any sense) and a possible workaround short of
> using hideous dynamic SQL?
It depends on the query parser and how it decides to process the query.
Depending on the order it processes clauses, and what they contain, it might
skip the CONTAINS clause completely (which it appears to do in the 2nd
case). Using FTS clauses when unnecessary will impact performance as the FTS
process is external to SQL Server. You could try doing the following:
IF COALESCE(@.Keywords,'') = ''
SELECT * FROM Jobs
ELSE
SELECT * FROM Jobs WHERE CONTAINS(JobTitle, @.Keywords)
END
This avoids dynamic SQL, and prevents the error is @.Keywords is NULL or
empty (an empty string will also cause an error, not just a NULL)
Dan