convert a linklist to relational database

I have a database has the following linklist structure. A, B, B1, B2 and B3 are records.
A is a group and the others are group members
***************************************
A.PointertoB
B.ForwardPointertoB1
B1.ForwardPointertoB2
B2.ForwardPointertoB3
B3.end
***************************************
I want to convert them to relational database structure so I need to find following pointers

B.PointertoA
B1.PointertoA
B2.PointertoA

Can I use sql to find the pointers? Thanks in advance.

Hi,

In your exisiting system do you have the ability to extract data out as char or text etc. if yes you can extract the data out,

Import all of it into a SQL table and then a write a T-SQL routine that will manipulate or extract data out of it. Once your happy that it works you can embedd the routine within DTS or SSIS.

I would do this. Some one might have a different idea.

There might also be third party tools that you can use.

Jag

convert / group by date

Hi,
I have a datetime column named dtDateTime.
its format is "Oct 27 2006 12:00:00 "
I want to group by only date part of it and count

my code is

$sql1="SELECT convert(varchar,J1708Data.dtDateTime,120),
count(convert(varchar,J1708Data.dtDateTime,120))

FROM Vehicle INNER JOIN J1708Data ON Vehicle.iID = J1708Data.iVehicleId

WHERE (J1708Data.iPidId = 303) AND
(J1708Date.dtDateTime between '2006-10-25' AND '2006-10-28')
AND (Vehicle.sDescription = $VehicleID)

GROUP BY convert(varchar,J1708Data.dtDateTime,120)";

However, convert part, group by part doesnt' work at all.
(i couldn't check count part)

can you find where's the problem?
Thx.kirke wrote:

Quote:

Originally Posted by

Hi,
I have a datetime column named dtDateTime.
its format is "Oct 27 2006 12:00:00 "
I want to group by only date part of it and count
>
my code is
>
>
$sql1="SELECT convert(varchar,J1708Data.dtDateTime,120),
count(convert(varchar,J1708Data.dtDateTime,120))
>
FROM Vehicle INNER JOIN J1708Data ON Vehicle.iID = J1708Data.iVehicleId
>
WHERE (J1708Data.iPidId = 303) AND
(J1708Date.dtDateTime between '2006-10-25' AND '2006-10-28')
AND (Vehicle.sDescription = $VehicleID)
>
GROUP BY convert(varchar,J1708Data.dtDateTime,120)";
>
>
However, convert part, group by part doesnt' work at all.
(i couldn't check count part)
>
can you find where's the problem?

--BEGIN PGP SIGNED MESSAGE--
Hash: SHA1

I'd have done it like this (use VARCHAR(10) or CHAR(10) for the date
instead of an unspecified size):

SELECT CONVERT(VARCHAR(10),J.dtDateTime,120) As theDate,
COUNT(*) As theCount
FROM Vehicle As V INNER JOIN J1708Data As J
ON V.iID = J.iVehicleId
WHERE J.iPidId = 303
AND J.dtDateTime BETWEEN '2006-10-25' AND '2006-10-28 23:23:59'
AND V.sDescription = @.VehicleID
GROUP BY CONVERT(VARCHAR(10),J.dtDateTime,120)
--
MGFoster:::mgf00 <atearthlink <decimal-pointnet
Oakland, CA (USA)
** Respond only to this newsgroup. I DO NOT respond to emails **

--BEGIN PGP SIGNATURE--
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBRUrBwIechKqOuFEgEQKUTQCg1zGcAeAViDrJQWxENdcn2t xbhxYAoO4o
1Mks6W+FiXviMMrZi/lt4e3z
=vWR9
--END PGP SIGNATURE--|||On 2 Nov 2006 10:53:08 -0800, kirke wrote:

Quote:

Originally Posted by

>Hi,
>I have a datetime column named dtDateTime.
>its format is "Oct 27 2006 12:00:00 "
>I want to group by only date part of it and count
>
>my code is
>
>
>$sql1="SELECT convert(varchar,J1708Data.dtDateTime,120),
>count(convert(varchar,J1708Data.dtDateTime,120))
>
>FROM Vehicle INNER JOIN J1708Data ON Vehicle.iID = J1708Data.iVehicleId
>
>WHERE (J1708Data.iPidId = 303) AND
>(J1708Date.dtDateTime between '2006-10-25' AND '2006-10-28')
>AND (Vehicle.sDescription = $VehicleID)
>
>GROUP BY convert(varchar,J1708Data.dtDateTime,120)";
>
>
>However, convert part, group by part doesnt' work at all.
>(i couldn't check count part)
>
>can you find where's the problem?
>Thx.

Hi kirke,

Have you tried to run the query? If so, what were the results? Were they
incoorrect, or did you get an error message. If the latter, then what
was that message?

I don't see any real problems with your data, thoough I would change a
few things:

* The date format. yyyy-mm-dd is not safe, becuase it can be interpreted
as yyyy-dd-mm for some country settings. Remve the dashes to get the
unambiguous yyyymmdd format.

* The use of BETWEEN means that rows with a startdate of 28th oct 2006
at exactly midnight will be included, but startdates on the same day
with a later time are excluded. The solution MGFoster proposes for this
(to include a time portion of 23:59:59) is not good enough - for
smalldatetime, this will be rounded up to the next minute, which is
midnight of the 29th of october; for datetime, you'll still miss rows
with a startdate in the last second of the day. You should replace
BETWEEN with a >= and a < condition:
AND J1708Date.dtDateTime >= '20061025'
AND J1708Date.dtDateTime < '20061029' -- Note the increased end day!
If you store all dates with the default time component of midnight, then
this is not necessary - but since it doesn't hurt either, I'd advice you
to accustom yourself to always using this techniques when comparing
datetimes.

The expression GROUP BY convert(varchar,J1708Data.dtDateTime,120) won't
group by daym, since the conversion doesn't chop off the time portion.
The result of select convert(varchar, current_timestamp, 120) for
instance is "2006-11-03 23:18:22", so you end up grouping by second.

Here's what I would try:

SELECT convert(varchar,J1708Data.dtDateTime,120),
count(convert(varchar,J1708Data.dtDateTime,120))
SELECT DATEADD(day, DATEDIFF(day, 0, d.DateTime), 0) AS TheDate,
COUNT(*) AS TheCount
FROM Vehicle AS v
INNER JOIN J1708Data AS d
ON v.VehicleID = d.VehicleId
WHERE d.PidId = 303
AND d.DateTime >= '20061025'
AND d.DateTime < '20061029'
AND v.Description = $VehicleID
GROUP BY DATEDIFF(day, 0, d.DateTime);

--
Hugo Kornelis, SQL Server MVPsqlsql

Conversion Probs..

Hi Group,
I am trying to display the multiplication through this way
-------
select 1163436036*100
-------
Getting the error
============================
Server: Msg 8115, Level 16, State 2, Line 1
Arithmetic overflow error converting expression to data type int.
============================
For that reason I was tried to convert that to nvarchar
--------
select convert(numeric(36,2),1163436036*100)
--------
But still getting the error
=============================
Server: Msg 8115, Level 16, State 2, Line 1
Arithmetic overflow error converting expression to data type int.
=============================
Please help me to solve it out..
Thanks and Regards
Arijit Chatterjee(arijitchatterjee123@.yahoo.co.in) writes:
> Hi Group,
> I am trying to display the multiplication through this way
> -------
> select 1163436036*100
> -------
> Getting the error
>============================
> Server: Msg 8115, Level 16, State 2, Line 1
> Arithmetic overflow error converting expression to data type int.
>============================
> For that reason I was tried to convert that to nvarchar
> --------
> select convert(numeric(36,2),1163436036*100)
> --------
> But still getting the error
>=============================
> Server: Msg 8115, Level 16, State 2, Line 1
> Arithmetic overflow error converting expression to data type int.
>=============================
> Please help me to solve it out..

You need to convert one of the numbers in the expression to the
target type you want, for instance:

select 1163436036*convert(bigint, 100)

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||> Arithmetic overflow error converting expression to data type int.

This error message provides the clue as to the underlying cause. You get an
integer result when you multiply 2 integers (1163436036*100) before the
CONVERT. You'll get a numeric(36, 2) result if you CONVERT or CAST at least
one of the values to numeric(36, 2):

SELECT CONVERT(numeric(36,2), 1163436036)*100
SELECT CAST(1163436036 AS numeric(36,2))*100

Since both of the values are integers, you might consider using bigint
instead of numeric if your are using SQL Server 2000:

SELECT CONVERT(bigint, 1163436036)*100
SELECT CAST(1163436036 AS bigint)*100

--
Hope this helps.

Dan Guzman
SQL Server MVP

<arijitchatterjee123@.yahoo.co.in> wrote in message
news:1118148077.784344.140590@.g43g2000cwa.googlegr oups.com...
> Hi Group,
> I am trying to display the multiplication through this way
> -------
> select 1163436036*100
> -------
> Getting the error
> ============================
> Server: Msg 8115, Level 16, State 2, Line 1
> Arithmetic overflow error converting expression to data type int.
> ============================
> For that reason I was tried to convert that to nvarchar
> --------
> select convert(numeric(36,2),1163436036*100)
> --------
> But still getting the error
> =============================
> Server: Msg 8115, Level 16, State 2, Line 1
> Arithmetic overflow error converting expression to data type int.
> =============================
> Please help me to solve it out..
> Thanks and Regards
> Arijit Chatterjee|||Thanks,
Thanks for your great support.
Regards
Arijit Chatterjee

Conversion Error...nvarchar to Datetime

Hi Group,
I am new with SQL Server..I am working with SQL Server 2000.
I am storing the date in a nvarchar column of atable... Now I want to
show the data of Weekends..Everything is OK...But the problem is
arising with Conversion of nvarchar to date...to identify the
weekends...Like..Here DATEVALUE is a nvarchar column...But getting the
error..Value of DATEVALUE like dd-mm-yyyy...04-08-2004

------------------
Server: Msg 8115, Level 16, State 2, Line 1
Arithmetic overflow error converting expression to data type datetime.
------------------
------Actual Query----------
Select DATEVALUE,<Other Column Names> from Result where
Datepart(dw,convert(Datetime,DATEVALUE))<>1 and
Datepart(dw,convert(Datetime,DATEVALUE))<>7
------------------
Thanks in advance..
Regards
Arijit Chatterjee(arijitchatterjee123@.yahoo.co.in) writes:
> I am new with SQL Server..I am working with SQL Server 2000.
> I am storing the date in a nvarchar column of atable... Now I want to
> show the data of Weekends..Everything is OK...But the problem is
> arising with Conversion of nvarchar to date...to identify the
> weekends...Like..Here DATEVALUE is a nvarchar column...But getting the
> error..Value of DATEVALUE like dd-mm-yyyy...04-08-2004

Best is to store date values in datetime columns. If you use character
format, you should use char (the n just doubles the space with no gain
for it, and the var is pointless since size is fixed), and you should use
the format YYYYMMDD. Furthermore, you should attach a constraint to the
columns

datecol char(8) CONSTRAINT ck_tbl_datecol CHECK (isdate(datecol) = 1)

to ascertain that you don't get illegal values.

Storing dates in a format like DD-MM-YYYY is going to give all sorts of
headache. 04-08-2004 could be interpreted as Aug 4th or April 8th, depending
on language and datefromat settings. (And, in case of humans, of the
perceptions of the user.) You can't sort on this format (unless you really
want 3 Aug to come before 4 June).

The format YYYYMMDD sorts well, and is always interpreted in the same way.

See also http://www.karaszi.com/SQLServer/info_datetime.asp.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||See this

declare @.t table (d varchar(20))
insert into @.t values('10-apr-2005')
insert into @.t values('10-MAy-2005')
insert into @.t values('10-Jun-2005')
insert into @.t values('10-Jul-2005')
Select * from @.t where Datepart(dw,convert(Datetime,d))<>1 and
Datepart(dw,convert(Datetime,d))<>7

Madhivanan|||Thanks for your great help..
Regards
Arijit Chatterjee

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 Group Question

I'm trying to use Service Broker to relate a set of messages together and was trying to use a related conversation group id. From what I can gather (looking at other threads) I can't use this.....

Basically, my ideas was.... I have several tables being updated within a database transaction. These tables will have triggers associated with them which send a message to a SB queue detailing the table that has been affected and the key information.

After the database transaction commits, I wanted to retrieve the group of messages in order to identify exactly what happened to the database during the transaction (for business reasons). I don't need necessarily need them in the same order, but do need them grouped by database transaction.

Service Broker seemed to be ideal i.e. the messages wouldn't commit if the database transaction rolled back and I wouldn't be able to access them until the entire transaction was committed........ My only problem is that I don't seem to be able to associate them with each other!!!!

Can anyone help with a way I can do this with Service Broker, or am I just trying to use the wrong technology?

If you can avoid doing the SEND in the trigger but instead do it in the batch executing the updates, then you could use a single dialog for sending all your messages as shown below:

DECLARE @.dh UNIQUEIDENTIFIER;
BEGIN TRANSACTION;
BEGIN DIALOG @.dh ...;
UPDATE TABLE t0 ...;
SEND ON CONVERSATION @.dh ...;
UPDATE TABLE t1 ...;
SEND ON CONVERSATION @.dh...;
...
COMMIT;

If you must do the SEND in the trigger, then you will have to somehow pass the conversation handle of an opened dialog or a conversation group id to bind new dialogs to from your batch to the trigger. I suppose that will involve some sort of temporary table.

Conversation group id question

HI

I have an example ( see below ).

I expect to have all messages sent using this code to have the same group id but they are all different. what I am doing wrong?

Leonid.

DECLARE @.conversationHandle uniqueidentifier

DECLARE @.usergroup uniqueidentifier

select @.usergroup = uid from bvuser where userid = 1

select @.usergroup

Begin Transaction

BEGIN DIALOG @.conversationHandle

FROM SERVICE [BvMainResponseService]

TO SERVICE 'BvMainService'

ON CONTRACT [BvMainContract]

WITH RELATED_CONVERSATION_GROUP = @.usergroup;

-- Send a message on the dialog

SEND ON CONVERSATION @.conversationHandle

MESSAGE TYPE [BvTaskMsg]

(N'Test')

commit

As far as i understand it you expand a conversation group by adding additional dialogs related to the first one:

For example:

DECLARE @.conversationHandle uniqueidentifier

Begin Transaction

BEGIN DIALOG @.conversationHandle

FROM SERVICE [BvMainResponseService]

TO SERVICE 'BvMainService'

ON CONTRACT [BvMainContract]

WITH RELATED_CONVERSATION_GROUP = @.conversationHandle;

-- Send a message on the dialog

SEND ON CONVERSATION @.conversationHandle

MESSAGE TYPE [BvTaskMsg]

(N'Test')

commit

You keep using the conversation handle from the begin dialog to keep the same conversation, i could be mistaken as i have not really tried it , but i think that is the theory anyway.

Thanx

|||

this is from BOL

If related_conversation_group_id does not reference an existing conversation group, the service broker creates a new conversation group with the specified related_conversation_group_id and relates the new dialog to that conversation group.

so as I understand this - new conversation group id is created when BEGIN DIALOG is used for the first time with specified ID, and then ... here is BOL again

Specifies the existing conversation group that the new dialog is added to. When this clause is present, the new dialog will be added to the conversation group specified by related_conversation_group_id.

But obviously I am doing something wrong here becuase it doesn't work as I expect it.

Leonid.

|||

In the test you've shown the conversation should have the same conversation group id. How are you looking up the conversations?

Here is a test script that shows that the related_conversation_group creates conversation in the same group, and the first BEGIN CONVERSATION creates the group itself, just as you expect:

use [tempdb];

go

create queue [testQueue];

create service [testService] on queue [testQueue];

go

create queue [targetQueue];

create service [targetService] on queue [targetQueue] ([DEFAULT]);

go

declare @.cg uniqueidentifier;

declare @.h uniqueidentifier;

select @.cg = newid();

begin dialog conversation @.h

from service [testService]

to service N'targetService', N'current database'

with related_conversation_group = @.cg,

encryption = off;

send on conversation @.h;

begin dialog conversation @.h

from service [testService]

to service N'targetService', N'current database'

with related_conversation_group = @.cg,

encryption = off;

send on conversation @.h;

begin dialog conversation @.h

from service [testService]

to service N'targetService', N'current database'

with related_conversation_group = @.cg,

encryption = off;

send on conversation @.h;

select * from sys.conversation_endpoints where conversation_group_id = @.cg;

HTH,
~ Remus

Conversation Group

I have not been successfull in getting conversation group to work. My understanding is that I can specify a 'guid' for a conversation group id in the create dialog and when I send a message on this conversation it will have that specific guid for its conversation group. When I do this it does not appear this way in the "target" queue.

I am looking for an example to help me understand how to use a conversation group. The MSDN has not really provided that run able example that I can run and verify and tweak.

The idea that I would like to try is that the initiator must send 5 different XML messages to a target. These 5 messages are all related and must exist together. What I assume is that if I want the target to get all 5 messages together out of the queue all messages must be sent in their own conversation but all linked with the same conversation group Id. I have not been able to get this to work.

The communication is really a one way where the initiator sends the data to the target and does not process or need a message sent back from the target.

Thank you for your time.

Here is how I am going to manage this. I have knowledge and control over the initiator and target. Which means I can make a few "assumptions"

I will define a message for each of the 5 messages to be sent to be used to identify the message in the conversation group. This will cut down on the processing of the data to determine its message type.

The contract is only one way and so the initiator will be listed as publishing each message type in the contract.

When sending to the target service the initiator will create a dialog and send all 5 messages in the same dialog.

The target will process the queue and get the conversation group. The target processing will need to account for undelivered messages and "poll" the queue for delivery. Plus a little more logic to stop polling after so many tries in case all five messages are never sent.

|||

You can also avoid pooling. You can turn on retention on the queue and have an activated procedure that is very simple: if the message is not the last one in the sequence, ignore it (commit the RECEIVE, though). If the message is the last one (5th, if there are 5 messages), the the procedure can use SELECT against the queue to find all the previous messages. When retention is ON, a queue keeps RECEIVEed messages until the conversation is ended.

HTH,
~ Remus

Conversation Group

I have not been successfull in getting conversation group to work. My understanding is that I can specify a 'guid' for a conversation group id in the create dialog and when I send a message on this conversation it will have that specific guid for its conversation group. When I do this it does not appear this way in the "target" queue.

I am looking for an example to help me understand how to use a conversation group. The MSDN has not really provided that run able example that I can run and verify and tweak.

The idea that I would like to try is that the initiator must send 5 different XML messages to a target. These 5 messages are all related and must exist together. What I assume is that if I want the target to get all 5 messages together out of the queue all messages must be sent in their own conversation but all linked with the same conversation group Id. I have not been able to get this to work.

The communication is really a one way where the initiator sends the data to the target and does not process or need a message sent back from the target.

Thank you for your time.

Here is how I am going to manage this. I have knowledge and control over the initiator and target. Which means I can make a few "assumptions"

I will define a message for each of the 5 messages to be sent to be used to identify the message in the conversation group. This will cut down on the processing of the data to determine its message type.

The contract is only one way and so the initiator will be listed as publishing each message type in the contract.

When sending to the target service the initiator will create a dialog and send all 5 messages in the same dialog.

The target will process the queue and get the conversation group. The target processing will need to account for undelivered messages and "poll" the queue for delivery. Plus a little more logic to stop polling after so many tries in case all five messages are never sent.

|||

You can also avoid pooling. You can turn on retention on the queue and have an activated procedure that is very simple: if the message is not the last one in the sequence, ignore it (commit the RECEIVE, though). If the message is the last one (5th, if there are 5 messages), the the procedure can use SELECT against the queue to find all the previous messages. When retention is ON, a queue keeps RECEIVEed messages until the conversation is ended.

HTH,
~ Remus

sqlsql

Control the WorkSheet Names when export to Excel

When I exported the report to Excel File with Multiple Worksheets, can I name
the worksheets with the values of the field I group on?
--
Thanks,
Albert ChowUnfortunately, that's not supported yet.
--
Adrian M.
MCP
"Albert" <Albert@.discussions.microsoft.com> wrote in message
news:DDD282FE-DEB3-41F6-8E1D-AEED8AB4B17C@.microsoft.com...
> When I exported the report to Excel File with Multiple Worksheets, can I
> name
> the worksheets with the values of the field I group on?
> --
> Thanks,
> Albert Chow

Contracting Rates in Canada

Appologies if this isnt the correct group for this post.
I'm about to start looking for my first Short term Contract in Canada
probably Toronto.
Have been in Perm DB Dev/DBA position in London for 3.5 years,
Exp is SQL Server (3.5 yrs) & Oracle (1 yr).
What sort of daily rates should I be looking/asking for. Am feeling
like I'm at the mercy of recruitment sharks having never contracted
before.
Any comments at all would be appreciated.
Bob
Wait for Tom Moreau reading the post. (He is a respected SQL Server
professional from Toronto)
"Bob" <casey_182@.hotmail.com> wrote in message
news:1e95514c.0411240419.1d6ba99f@.posting.google.c om...
> Appologies if this isnt the correct group for this post.
> I'm about to start looking for my first Short term Contract in Canada
> probably Toronto.
> Have been in Perm DB Dev/DBA position in London for 3.5 years,
> Exp is SQL Server (3.5 yrs) & Oracle (1 yr).
> What sort of daily rates should I be looking/asking for. Am feeling
> like I'm at the mercy of recruitment sharks having never contracted
> before.
> Any comments at all would be appreciated.

Contracting Rates in Canada

Appologies if this isnt the correct group for this post.
I'm about to start looking for my first Short term Contract in Canada
probably Toronto.
Have been in Perm DB Dev/DBA position in London for 3.5 years,
Exp is SQL Server (3.5 yrs) & Oracle (1 yr).
What sort of daily rates should I be looking/asking for. Am feeling
like I'm at the mercy of recruitment sharks having never contracted
before.
Any comments at all would be appreciated.Bob
Wait for Tom Moreau reading the post. (He is a respected SQL Server
professional from Toronto)
"Bob" <casey_182@.hotmail.com> wrote in message
news:1e95514c.0411240419.1d6ba99f@.posting.google.com...
> Appologies if this isnt the correct group for this post.
> I'm about to start looking for my first Short term Contract in Canada
> probably Toronto.
> Have been in Perm DB Dev/DBA position in London for 3.5 years,
> Exp is SQL Server (3.5 yrs) & Oracle (1 yr).
> What sort of daily rates should I be looking/asking for. Am feeling
> like I'm at the mercy of recruitment sharks having never contracted
> before.
> Any comments at all would be appreciated.

Contracting Rates in Canada

Appologies if this isnt the correct group for this post.
I'm about to start looking for my first Short term Contract in Canada
probably Toronto.
Have been in Perm DB Dev/DBA position in London for 3.5 years,
Exp is SQL Server (3.5 yrs) & Oracle (1 yr).
What sort of daily rates should I be looking/asking for. Am feeling
like I'm at the mercy of recruitment sharks having never contracted
before.
Any comments at all would be appreciated.Bob
Wait for Tom Moreau reading the post. (He is a respected SQL Server
professional from Toronto)
"Bob" <casey_182@.hotmail.com> wrote in message
news:1e95514c.0411240419.1d6ba99f@.posting.google.com...
> Appologies if this isnt the correct group for this post.
> I'm about to start looking for my first Short term Contract in Canada
> probably Toronto.
> Have been in Perm DB Dev/DBA position in London for 3.5 years,
> Exp is SQL Server (3.5 yrs) & Oracle (1 yr).
> What sort of daily rates should I be looking/asking for. Am feeling
> like I'm at the mercy of recruitment sharks having never contracted
> before.
> Any comments at all would be appreciated.

Continuos Starting up database 'myDatabase'

Hi group. I have this problem. When I review the Sql Log appears the same
message second by second for each database in th server.
Starting up database 'myDatabase'.
Starting up database 'myDatabase'.
Starting up database 'myDatabase'.
Starting up database 'myDatabase'.
I don't know if is a problem but I think who isn't normal.
Any help will be apprtiate.
Regards,
RodrigoRodrigo wrote:
> Hi group. I have this problem. When I review the Sql Log appears the same
> message second by second for each database in th server.
> Starting up database 'myDatabase'.
> Starting up database 'myDatabase'.
> Starting up database 'myDatabase'.
> Starting up database 'myDatabase'.
> I don't know if is a problem but I think who isn't normal.
> Any help will be apprtiate.
> Regards,
> Rodrigo|||Looks like you have the database option 'Auto close' set.
Rodrigo wrote:
> Hi group. I have this problem. When I review the Sql Log appears the same
> message second by second for each database in th server.
> Starting up database 'myDatabase'.
> Starting up database 'myDatabase'.
> Starting up database 'myDatabase'.
> Starting up database 'myDatabase'.
> I don't know if is a problem but I think who isn't normal.
> Any help will be apprtiate.
> Regards,
> Rodrigo

Continuos Starting up database 'myDatabase'

Hi group. I have this problem. When I review the Sql Log appears the same
message second by second for each database in th server.
Starting up database 'myDatabase'.
Starting up database 'myDatabase'.
Starting up database 'myDatabase'.
Starting up database 'myDatabase'.
I don't know if is a problem but I think who isn't normal.
Any help will be apprtiate.
Regards,
RodrigoRodrigo wrote:
> Hi group. I have this problem. When I review the Sql Log appears the same
> message second by second for each database in th server.
> Starting up database 'myDatabase'.
> Starting up database 'myDatabase'.
> Starting up database 'myDatabase'.
> Starting up database 'myDatabase'.
> I don't know if is a problem but I think who isn't normal.
> Any help will be apprtiate.
> Regards,
> Rodrigo|||Looks like you have the database option 'Auto close' set.
Rodrigo wrote:
> Hi group. I have this problem. When I review the Sql Log appears the same
> message second by second for each database in th server.
> Starting up database 'myDatabase'.
> Starting up database 'myDatabase'.
> Starting up database 'myDatabase'.
> Starting up database 'myDatabase'.
> I don't know if is a problem but I think who isn't normal.
> Any help will be apprtiate.
> Regards,
> Rodrigo

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.