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

Conversation Groups

I'm having some troubles with conversation groups. I need to send two messages on the same conversation group so I have the following in my SP....

BEGIN DIALOG CONVERSATION @.providerConversationHandle
FROM SERVICE [ProviderDataService]
TO SERVICE 'CalculatedDataService'
ON CONTRACT [ProviderDataContract]
WITH ENCRYPTION = OFF
, LIFETIME = 600;

BEGIN DIALOG CONVERSATION @.curveConversationHandle
FROM SERVICE [ProviderDataService]
TO SERVICE 'CalculatedDataService'
ON CONTRACT [ProviderDataContract]
WITH RELATED_CONVERSATION = @.providerConversationHandle
, ENCRYPTION = OFF
, LIFETIME = 600;

SEND ON CONVERSATION @.providerConversationHandle
MESSAGE TYPE [ProviderDataMessage] ( @.providerMessage );

SEND ON CONVERSATION @.curveConversationHandle
MESSAGE TYPE [ProviderCurveMessage] ( @.curveMessage );

When I query the queue I see two messages, but they don't have the same conversation_group_id.

Any ideas?

Thanks.

Hello Ian,

The conversation group is a locking primitive. Conversations in the same group are locked together, so that any transaction is guaranteed that is the only transaction processing messages on the current group.

As such, conversation groups are pertinent only for the site declaring the group. The group information does not travel with the message to the far side. The two conversations are related, but only in the SENDER's side. If you actually send back a reply on each dialog, then the replies will have the same conversation_group_id.

If the receiver wants to group dialogs, it can use MOVE CONVERSATION to do it, based on it's own locking policies.

Typically apps receive a message and begin new dialogs related to the dialog on which the message was received, not starting two new related dialogs (of course, the can be valid scenarios on for the later case as well!).

HTH,

~ Remus

|||

Thanks for the information.

So if I read you right conversation groups don't allow the receiver to "group" messages together. Are there any good pieces of sample code that show conversation groups in action? all I have seen so far are repeats of the BOL examples.

In my scenario I have two related messages and it is important that the receiving end of the dialog can retrieve and process both my messages together - so how would I go about achieving this?

Thanks

Ian

|||

You should send them both on the same dialog, instead of beginning separate dialogs for each message.

Note that this will allow the message to be received together, but will not guarantee it. The receiver service can still see only the first message while the second is still in traffic.

From my understanding of you example, what you actually looking for is to send 2 parameters to a calculationservice, right? And I assume the 'CalculateDataService' needs both of these parameters to do the actual calculation.

The easiest solution is to group both parameters on one single message and send one message instead of two. The XML and XPath support in the server make both composing the one message from two parts and retrieveing the separate parts on the receiver side easy, if you parameter values can be converted to text (xml nodes).

Often is the case that the two parameters are not available at the same moment, so the sender would prefer to send them separately. In this case you must have a state on the receiver. A table that uses the conversation_group_id as the key and stores the received @.providerMessage and/or @.curveMessage parameters. When the first message is received, the parameter (whichever is) is stored on the table. When the second message is received, the service will find the first parameter in the table and can now do the calculation and respond to the sender.

HTH,

~ Remus

|||

Thanks for the suggestions.

I want to keep my messages seperate as they are pretty large and I don't want the performance overhead of joining and spliting the data, so I guess I will have to look into your suggestions regarding state.

Thanks again.

Ian

|||

Hi Remus

Can you get this explained in BOL.

I couldn't find anything that explained that conversation groups only apply to the initiator side of the dialog. This does seem to be a recurring question

Cheers

|||

That's not in BOL because it's not true. Conversation groups can apply to either the initiator or the target or both. The issue here is that a conversation group is limited to a single queue. The means you can put conversations into a conversation group on the initiator but the conversation group id is not sent over the network to the target. You can also put conversations into a conversation group on the target queue also but this is independent of any conversation groups that you may have set up on the initiator. The conversation group is primarily used for a locking context for SSB commands. SEND and RECEIVE commands can't span queues in a single command so a lock that locks conversations on two different queues doesn't make sense.

Conversation groups aren't sent along with messages from the initiator to target becausethere's no way for the sender of the message to know whether the targets of the conversations in the group are in the same queue. In fact the destination queues can be changed around by the deployer so in general, the conversation initiator has no knowledge of the queue configuration of the target.

|||

Ok so I'm new to SB and what I got from BOL was that

1. conversation groups can be used to group conversations.

2. the conversation group can be specified in the begin dialog so conversations don't have to be intiated at the same time

What isn't in BOL is that even if you group conversations on the initiator, they can not be received from the queue of the target service in these groups.

This was what I was expecting. To be able to send messages from triggers to a service, and then use the RECEIVE TOP (x) to take x of these messages off the target queue and process them. However what it seems is that the RECEIVE statement only returns a conversation and so I have to do the RECEIVE statement x times to get a the BULK set of messages to process.

As has been mentioned when repsonses errors come back to the Initiator queue I can use 1 RECEIVE statement to get a bulk set of messages.

I hope that clarifies the confusion I fell into that I would like in BOL, unless I am wrong again (more than likely).

|||If you want to be anle to receive a bunch of messages at a time on the target side, you should put them into the same dialog. This not only allows you to get the efficiencies related to receiving multiple messages at a time, it also reduces the number of dialogs used and this has very positive performance benefits also. The down side is that all the messages will have to be processed on a single thread but that's the same behavior you would have gotten if you put all the dialogs into the same conversation group because the conversation group is locked when a message is received on any dialog in the group. If you want three threads to receive messages then you need three dialogs. There's a bigger discussion of this here: http://blogs.msdn.com/rogerwolterblog/archive/2006/05/20/602938.aspx

Conversation Groups

I'm having some troubles with conversation groups. I need to send two messages on the same conversation group so I have the following in my SP....

BEGIN DIALOG CONVERSATION @.providerConversationHandle
FROM SERVICE [ProviderDataService]
TO SERVICE 'CalculatedDataService'
ON CONTRACT [ProviderDataContract]
WITH ENCRYPTION = OFF
, LIFETIME = 600;

BEGIN DIALOG CONVERSATION @.curveConversationHandle
FROM SERVICE [ProviderDataService]
TO SERVICE 'CalculatedDataService'
ON CONTRACT [ProviderDataContract]
WITH RELATED_CONVERSATION = @.providerConversationHandle
, ENCRYPTION = OFF
, LIFETIME = 600;

SEND ON CONVERSATION @.providerConversationHandle
MESSAGE TYPE [ProviderDataMessage] ( @.providerMessage );

SEND ON CONVERSATION @.curveConversationHandle
MESSAGE TYPE [ProviderCurveMessage] ( @.curveMessage );

When I query the queue I see two messages, but they don't have the same conversation_group_id.

Any ideas?

Thanks.

Hello Ian,

The conversation group is a locking primitive. Conversations in the same group are locked together, so that any transaction is guaranteed that is the only transaction processing messages on the current group.

As such, conversation groups are pertinent only for the site declaring the group. The group information does not travel with the message to the far side. The two conversations are related, but only in the SENDER's side. If you actually send back a reply on each dialog, then the replies will have the same conversation_group_id.

If the receiver wants to group dialogs, it can use MOVE CONVERSATION to do it, based on it's own locking policies.

Typically apps receive a message and begin new dialogs related to the dialog on which the message was received, not starting two new related dialogs (of course, the can be valid scenarios on for the later case as well!).

HTH,

~ Remus

|||

Thanks for the information.

So if I read you right conversation groups don't allow the receiver to "group" messages together. Are there any good pieces of sample code that show conversation groups in action? all I have seen so far are repeats of the BOL examples.

In my scenario I have two related messages and it is important that the receiving end of the dialog can retrieve and process both my messages together - so how would I go about achieving this?

Thanks

Ian

|||

You should send them both on the same dialog, instead of beginning separate dialogs for each message.

Note that this will allow the message to be received together, but will not guarantee it. The receiver service can still see only the first message while the second is still in traffic.

From my understanding of you example, what you actually looking for is to send 2 parameters to a calculationservice, right? And I assume the 'CalculateDataService' needs both of these parameters to do the actual calculation.

The easiest solution is to group both parameters on one single message and send one message instead of two. The XML and XPath support in the server make both composing the one message from two parts and retrieveing the separate parts on the receiver side easy, if you parameter values can be converted to text (xml nodes).

Often is the case that the two parameters are not available at the same moment, so the sender would prefer to send them separately. In this case you must have a state on the receiver. A table that uses the conversation_group_id as the key and stores the received @.providerMessage and/or @.curveMessage parameters. When the first message is received, the parameter (whichever is) is stored on the table. When the second message is received, the service will find the first parameter in the table and can now do the calculation and respond to the sender.

HTH,

~ Remus

|||

Thanks for the suggestions.

I want to keep my messages seperate as they are pretty large and I don't want the performance overhead of joining and spliting the data, so I guess I will have to look into your suggestions regarding state.

Thanks again.

Ian

|||

Hi Remus

Can you get this explained in BOL.

I couldn't find anything that explained that conversation groups only apply to the initiator side of the dialog. This does seem to be a recurring question

Cheers

|||

That's not in BOL because it's not true. Conversation groups can apply to either the initiator or the target or both. The issue here is that a conversation group is limited to a single queue. The means you can put conversations into a conversation group on the initiator but the conversation group id is not sent over the network to the target. You can also put conversations into a conversation group on the target queue also but this is independent of any conversation groups that you may have set up on the initiator. The conversation group is primarily used for a locking context for SSB commands. SEND and RECEIVE commands can't span queues in a single command so a lock that locks conversations on two different queues doesn't make sense.

Conversation groups aren't sent along with messages from the initiator to target becausethere's no way for the sender of the message to know whether the targets of the conversations in the group are in the same queue. In fact the destination queues can be changed around by the deployer so in general, the conversation initiator has no knowledge of the queue configuration of the target.

|||

Ok so I'm new to SB and what I got from BOL was that

1. conversation groups can be used to group conversations.

2. the conversation group can be specified in the begin dialog so conversations don't have to be intiated at the same time

What isn't in BOL is that even if you group conversations on the initiator, they can not be received from the queue of the target service in these groups.

This was what I was expecting. To be able to send messages from triggers to a service, and then use the RECEIVE TOP (x) to take x of these messages off the target queue and process them. However what it seems is that the RECEIVE statement only returns a conversation and so I have to do the RECEIVE statement x times to get a the BULK set of messages to process.

As has been mentioned when repsonses errors come back to the Initiator queue I can use 1 RECEIVE statement to get a bulk set of messages.

I hope that clarifies the confusion I fell into that I would like in BOL, unless I am wrong again (more than likely).

|||If you want to be anle to receive a bunch of messages at a time on the target side, you should put them into the same dialog. This not only allows you to get the efficiencies related to receiving multiple messages at a time, it also reduces the number of dialogs used and this has very positive performance benefits also. The down side is that all the messages will have to be processed on a single thread but that's the same behavior you would have gotten if you put all the dialogs into the same conversation group because the conversation group is locked when a message is received on any dialog in the group. If you want three threads to receive messages then you need three dialogs. There's a bigger discussion of this here: http://blogs.msdn.com/rogerwolterblog/archive/2006/05/20/602938.aspxsqlsql

Conversation Groups

I am thinking of updating my SQL monitoring application to use Service Broker.

Right now I loop through my list of servers performing various checks on each server. Things like 'check last database backup', 'check for new databases', 'check for server restart'. I loop through, one server at a time, doing one check at a time. The more servers I have the longer it is taking.

So, I want to multi-thread the servers, but single-thread the checks on each individual server. This way I can check say, 5 servers at a time, but on each server I will only do one check at a time. This way I won't flood an individual server with multiple checks.

Is this possible? It looks like Conversation groups might be the way to go but I'm not sure.

Conversatiuon groups are always local, as in no conversation group info ever goes across the wire with a message. The purpose of conversation groups is to lock together logically related conversations.

To have each server do only one check at a time, is eanough to restrict the activated max count to 1. This way there's at most one instance of the activated procedure doing one check. Multiple requests can be sent to the same server, they'll simply be queued up and wait their turn.

The problem you describe can also be aproached as a pub-sub problem (see https://blogs.msdn.com/remusrusanu/archive/2005/12/12/502942.aspx). The publisher (your central administrative service) publishes 'check requests'. All instances you monitor are subscribed to this publisher and receive the request, perform the check and report the result.

HTH,
~ Remus

|||

Thanks for the reply.

I don't think I gave you enough info. All my monitoring processes are running on the same server. I connect out to the 'monitored' servers using linked servers. I can't have the queues on the 'monitored' server as most of them are SQL 2000.

The whole monitoring is run from one SQL 2005 server.

I will take a look at the article you mentioned.

Thanks

Constraint Doubt

Hi. In a table called Groups with fields GroupID (PK), Name, how can I set a Constraint to make Name filed Unique to avoid:
GroupID | Name
==========
1 | Admin
2 | Admin
Check out ALTER TABLE in BooksOnLine and look at the CONSTRAINT section.
Here is an example directly from BOL:
CREATE TABLE doc_exc ( column_a INT)
GO
ALTER TABLE doc_exc ADD column_b VARCHAR(20) NULL
CONSTRAINT exb_unique UNIQUE
Andrew J. Kelly SQL MVP
"KenA" <KenA@.discussions.microsoft.com> wrote in message
news:1DD9B537-20E5-4010-A469-03C4259F2C3D@.microsoft.com...
> Hi. In a table called Groups with fields GroupID (PK), Name, how can I set
a Constraint to make Name filed Unique to avoid:
> GroupID | Name
> ==========
> 1 | Admin
> 2 | Admin
|||Thanks Andrew:
But suppose that I already have the 'doc_exc' Table created, but still haven′t add a constraint to column_b ... I can find a way to add the constraint, unless I delete the table and run the script again?
KenA.
"Andrew J. Kelly" wrote:

> Check out ALTER TABLE in BooksOnLine and look at the CONSTRAINT section.
> Here is an example directly from BOL:
> CREATE TABLE doc_exc ( column_a INT)
> GO
> ALTER TABLE doc_exc ADD column_b VARCHAR(20) NULL
> CONSTRAINT exb_unique UNIQUE
>
> --
> Andrew J. Kelly SQL MVP
>
> "KenA" <KenA@.discussions.microsoft.com> wrote in message
> news:1DD9B537-20E5-4010-A469-03C4259F2C3D@.microsoft.com...
> a Constraint to make Name filed Unique to avoid:
>
>
|||In that case, just run the Alter table command alone.
John
"KenA" <KenA@.discussions.microsoft.com> wrote in message
news:B982E393-F58D-4224-A883-32E8B10AAAB7@.microsoft.com...
> Thanks Andrew:
> But suppose that I already have the 'doc_exc' Table created, but still
havent add a constraint to column_b ... I can find a way to add the
constraint, unless I delete the table and run the script again?[vbcol=seagreen]
> KenA.
> "Andrew J. Kelly" wrote:
set[vbcol=seagreen]

Constraint Doubt

Hi. In a table called Groups with fields GroupID (PK), Name, how can I set a Constraint to make Name filed Unique to avoid:
GroupID | Name
==========
1 | Admin
2 | Admin
Check out ALTER TABLE in BooksOnLine and look at the CONSTRAINT section.
Here is an example directly from BOL:
CREATE TABLE doc_exc ( column_a INT)
GO
ALTER TABLE doc_exc ADD column_b VARCHAR(20) NULL
CONSTRAINT exb_unique UNIQUE
Andrew J. Kelly SQL MVP
"KenA" <KenA@.discussions.microsoft.com> wrote in message
news:1DD9B537-20E5-4010-A469-03C4259F2C3D@.microsoft.com...
> Hi. In a table called Groups with fields GroupID (PK), Name, how can I set
a Constraint to make Name filed Unique to avoid:
> GroupID | Name
> ==========
> 1 | Admin
> 2 | Admin
|||Thanks Andrew:
But suppose that I already have the 'doc_exc' Table created, but still haven′t add a constraint to column_b ... I can find a way to add the constraint, unless I delete the table and run the script again?
KenA.
"Andrew J. Kelly" wrote:

> Check out ALTER TABLE in BooksOnLine and look at the CONSTRAINT section.
> Here is an example directly from BOL:
> CREATE TABLE doc_exc ( column_a INT)
> GO
> ALTER TABLE doc_exc ADD column_b VARCHAR(20) NULL
> CONSTRAINT exb_unique UNIQUE
>
> --
> Andrew J. Kelly SQL MVP
>
> "KenA" <KenA@.discussions.microsoft.com> wrote in message
> news:1DD9B537-20E5-4010-A469-03C4259F2C3D@.microsoft.com...
> a Constraint to make Name filed Unique to avoid:
>
>
|||In that case, just run the Alter table command alone.
John
"KenA" <KenA@.discussions.microsoft.com> wrote in message
news:B982E393-F58D-4224-A883-32E8B10AAAB7@.microsoft.com...
> Thanks Andrew:
> But suppose that I already have the 'doc_exc' Table created, but still
havent add a constraint to column_b ... I can find a way to add the
constraint, unless I delete the table and run the script again?[vbcol=seagreen]
> KenA.
> "Andrew J. Kelly" wrote:
set[vbcol=seagreen]

Constraint Doubt

Hi. In a table called Groups with fields GroupID (PK), Name, how can I set a
Constraint to make Name filed Unique to avoid:
GroupID | Name
==========
1 | Admin
2 | AdminCheck out ALTER TABLE in BooksOnLine and look at the CONSTRAINT section.
Here is an example directly from BOL:
CREATE TABLE doc_exc ( column_a INT)
GO
ALTER TABLE doc_exc ADD column_b VARCHAR(20) NULL
CONSTRAINT exb_unique UNIQUE
Andrew J. Kelly SQL MVP
"KenA" <KenA@.discussions.microsoft.com> wrote in message
news:1DD9B537-20E5-4010-A469-03C4259F2C3D@.microsoft.com...
> Hi. In a table called Groups with fields GroupID (PK), Name, how can I set
a Constraint to make Name filed Unique to avoid:
> GroupID | Name
> ==========
> 1 | Admin
> 2 | Admin|||Thanks Andrew:
But suppose that I already have the 'doc_exc' Table created, but still haven
′t add a constraint to column_b ... I can find a way to add the constraint,
unless I delete the table and run the script again?
KenA.
"Andrew J. Kelly" wrote:

> Check out ALTER TABLE in BooksOnLine and look at the CONSTRAINT section.
> Here is an example directly from BOL:
> CREATE TABLE doc_exc ( column_a INT)
> GO
> ALTER TABLE doc_exc ADD column_b VARCHAR(20) NULL
> CONSTRAINT exb_unique UNIQUE
>
> --
> Andrew J. Kelly SQL MVP
>
> "KenA" <KenA@.discussions.microsoft.com> wrote in message
> news:1DD9B537-20E5-4010-A469-03C4259F2C3D@.microsoft.com...
> a Constraint to make Name filed Unique to avoid:
>
>|||In that case, just run the Alter table command alone.
John
"KenA" <KenA@.discussions.microsoft.com> wrote in message
news:B982E393-F58D-4224-A883-32E8B10AAAB7@.microsoft.com...
> Thanks Andrew:
> But suppose that I already have the 'doc_exc' Table created, but still
havent add a constraint to column_b ... I can find a way to add the
constraint, unless I delete the table and run the script again?[vbcol=seagreen]
> KenA.
> "Andrew J. Kelly" wrote:
>
set[vbcol=seagreen]

Constraint Doubt

Hi. In a table called Groups with fields GroupID (PK), Name, how can I set a Constraint to make Name filed Unique to avoid:
GroupID | Name
========== 1 | Admin
2 | AdminCheck out ALTER TABLE in BooksOnLine and look at the CONSTRAINT section.
Here is an example directly from BOL:
CREATE TABLE doc_exc ( column_a INT)
GO
ALTER TABLE doc_exc ADD column_b VARCHAR(20) NULL
CONSTRAINT exb_unique UNIQUE
Andrew J. Kelly SQL MVP
"KenA" <KenA@.discussions.microsoft.com> wrote in message
news:1DD9B537-20E5-4010-A469-03C4259F2C3D@.microsoft.com...
> Hi. In a table called Groups with fields GroupID (PK), Name, how can I set
a Constraint to make Name filed Unique to avoid:
> GroupID | Name
> ==========> 1 | Admin
> 2 | Admin|||Thanks Andrew:
But suppose that I already have the 'doc_exc' Table created, but still haven´t add a constraint to column_b ... I can find a way to add the constraint, unless I delete the table and run the script again?
KenA.
"Andrew J. Kelly" wrote:
> Check out ALTER TABLE in BooksOnLine and look at the CONSTRAINT section.
> Here is an example directly from BOL:
> CREATE TABLE doc_exc ( column_a INT)
> GO
> ALTER TABLE doc_exc ADD column_b VARCHAR(20) NULL
> CONSTRAINT exb_unique UNIQUE
>
> --
> Andrew J. Kelly SQL MVP
>
> "KenA" <KenA@.discussions.microsoft.com> wrote in message
> news:1DD9B537-20E5-4010-A469-03C4259F2C3D@.microsoft.com...
> > Hi. In a table called Groups with fields GroupID (PK), Name, how can I set
> a Constraint to make Name filed Unique to avoid:
> >
> > GroupID | Name
> > ==========> > 1 | Admin
> > 2 | Admin
>
>|||In that case, just run the Alter table command alone.
John
"KenA" <KenA@.discussions.microsoft.com> wrote in message
news:B982E393-F58D-4224-A883-32E8B10AAAB7@.microsoft.com...
> Thanks Andrew:
> But suppose that I already have the 'doc_exc' Table created, but still
haven´t add a constraint to column_b ... I can find a way to add the
constraint, unless I delete the table and run the script again?
> KenA.
> "Andrew J. Kelly" wrote:
> > Check out ALTER TABLE in BooksOnLine and look at the CONSTRAINT section.
> > Here is an example directly from BOL:
> >
> > CREATE TABLE doc_exc ( column_a INT)
> > GO
> > ALTER TABLE doc_exc ADD column_b VARCHAR(20) NULL
> > CONSTRAINT exb_unique UNIQUE
> >
> >
> > --
> > Andrew J. Kelly SQL MVP
> >
> >
> > "KenA" <KenA@.discussions.microsoft.com> wrote in message
> > news:1DD9B537-20E5-4010-A469-03C4259F2C3D@.microsoft.com...
> > > Hi. In a table called Groups with fields GroupID (PK), Name, how can I
set
> > a Constraint to make Name filed Unique to avoid:
> > >
> > > GroupID | Name
> > > ==========> > > 1 | Admin
> > > 2 | Admin
> >
> >
> >

Constant variables - round 2

Hi!
I'm forwarding this because I couldn't find another way to avoid
multiposting once I have forgotten to cross-post to other groups. Sorry,
anyway.
"Agoston Bejo" <gusz1@.freemail.hu> wrote in message
news:ePy07CA6EHA.3472@.TK2MSFTNGP09.phx.gbl...
> Hi!
> Is there a way to create constants (i.e. constant variables) in stored
> procedures? Basically I'm looking for the T-SQL counterpart of Oracle
> PL/SQL's
> "x_var constant integer := 999;"-type declarations.
> Thx,
> Agoston
>
No. TSQL Programming doesnt have any keyword for constant variable.
but if you want to store constants, it is recommended to keep them in
separate table and read it in the TSQL block. one level of security you can
provide is not to allow anyone to update the constants table.
In sql world, constants are data values. like string literals, numeric and
decimal values.
Av.
http://dotnetjunkies.com/WebLog/avnrao
http://www28.brinkster.com/avdotnet
"Agoston Bejo" <gusz1@.freemail.hu> wrote in message
news:#Y6k8PA6EHA.2572@.tk2msftngp13.phx.gbl...
> Hi!
> I'm forwarding this because I couldn't find another way to avoid
> multiposting once I have forgotten to cross-post to other groups. Sorry,
> anyway.
> "Agoston Bejo" <gusz1@.freemail.hu> wrote in message
> news:ePy07CA6EHA.3472@.TK2MSFTNGP09.phx.gbl...
>

Consolidating group names

Hello All,

I have some groups set up in a matrix that basically group by transaction names. I am trying to consolidate all but one into the same group. Right now I have the expression of...

Code Snippet

=iif(Fields!TestName.Value="Name Search","Name Search","Logon Function")

this consolidates the aggregate results and group fine but it does not label the groups as expected. "Name Search" comes up as "Name Search" but instead instead of "Logon Function", It displays the rolled up group as the name of the fist group field. Is there a way to make an alias inside of an expression?

Never mind. Answered my own question. Had to put the code into the field properties too.