Receiving SQL Server Error messages in SQL Server Log

Hi:
I've been receiving the below mentioned error messages in
my SQL Server Log for all databases ;
1. IO is thawed
2. IO is frozen for snapshot
Any idea as to for what reason this would be happening.
Thanks very much in advance!Most likely due to using dbcc freeze_io and thaw_io.
The commands are depreciated in SQL Server 2000 and are not
recommended to be used due to the depreciation as well as
the potential for these commands to hang the server.
-Sue
On Tue, 13 Apr 2004 07:39:20 -0700, "Arpit"
<apatel@.esricanada.com> wrote:
>Hi:
>I've been receiving the below mentioned error messages in
>my SQL Server Log for all databases ;
>1. IO is thawed
>2. IO is frozen for snapshot
>Any idea as to for what reason this would be happening.
>Thanks very much in advance!|||Thanks Sue!
I haven't use the dbcc freeze_io and thaw_io. Would there
any process of job that would have used this in the
background?
Thanks
>--Original Message--
>Most likely due to using dbcc freeze_io and thaw_io.
>The commands are depreciated in SQL Server 2000 and are
not
>recommended to be used due to the depreciation as well as
>the potential for these commands to hang the server.
>-Sue
>On Tue, 13 Apr 2004 07:39:20 -0700, "Arpit"
><apatel@.esricanada.com> wrote:
>>Hi:
>>I've been receiving the below mentioned error messages
in
>>my SQL Server Log for all databases ;
>>1. IO is thawed
>>2. IO is frozen for snapshot
>>Any idea as to for what reason this would be happening.
>>Thanks very much in advance!
>.
>|||I've read a couple posts where it seems some third party
vendor for backups or shadow copy backups uses this. SQL
Server itself or any of native functionality isn't likely to
be implementing this. I'd look at whatever third party
tools, products you may be using. You could look at the
times you are getting the messages logged and try to figure
out what's running at those times.
-Sue
On Tue, 13 Apr 2004 08:52:43 -0700, "Arpit"
<anonymous@.discussions.microsoft.com> wrote:
>Thanks Sue!
>I haven't use the dbcc freeze_io and thaw_io. Would there
>any process of job that would have used this in the
>background?
>Thanks
>>--Original Message--
>>Most likely due to using dbcc freeze_io and thaw_io.
>>The commands are depreciated in SQL Server 2000 and are
>not
>>recommended to be used due to the depreciation as well as
>>the potential for these commands to hang the server.
>>-Sue
>>On Tue, 13 Apr 2004 07:39:20 -0700, "Arpit"
>><apatel@.esricanada.com> wrote:
>>Hi:
>>I've been receiving the below mentioned error messages
>in
>>my SQL Server Log for all databases ;
>>1. IO is thawed
>>2. IO is frozen for snapshot
>>Any idea as to for what reason this would be happening.
>>Thanks very much in advance!
>>.

Receiving SQL Server Error messages in SQL Server Log

Hi:
I've been receiving the below mentioned error messages in
my SQL Server Log for all databases ;
1. IO is thawed
2. IO is frozen for snapshot
Any idea as to for what reason this would be happening.
Thanks very much in advance!
Most likely due to using dbcc freeze_io and thaw_io.
The commands are depreciated in SQL Server 2000 and are not
recommended to be used due to the depreciation as well as
the potential for these commands to hang the server.
-Sue
On Tue, 13 Apr 2004 07:39:20 -0700, "Arpit"
<apatel@.esricanada.com> wrote:

>Hi:
>I've been receiving the below mentioned error messages in
>my SQL Server Log for all databases ;
>1. IO is thawed
>2. IO is frozen for snapshot
>Any idea as to for what reason this would be happening.
>Thanks very much in advance!
|||Thanks Sue!
I haven't use the dbcc freeze_io and thaw_io. Would there
any process of job that would have used this in the
background?
Thanks
>--Original Message--
>Most likely due to using dbcc freeze_io and thaw_io.
>The commands are depreciated in SQL Server 2000 and are
not[color=darkblue]
>recommended to be used due to the depreciation as well as
>the potential for these commands to hang the server.
>-Sue
>On Tue, 13 Apr 2004 07:39:20 -0700, "Arpit"
><apatel@.esricanada.com> wrote:
in
>.
>
|||I've read a couple posts where it seems some third party
vendor for backups or shadow copy backups uses this. SQL
Server itself or any of native functionality isn't likely to
be implementing this. I'd look at whatever third party
tools, products you may be using. You could look at the
times you are getting the messages logged and try to figure
out what's running at those times.
-Sue
On Tue, 13 Apr 2004 08:52:43 -0700, "Arpit"
<anonymous@.discussions.microsoft.com> wrote:
[color=darkblue]
>Thanks Sue!
>I haven't use the dbcc freeze_io and thaw_io. Would there
>any process of job that would have used this in the
>background?
>Thanks
>not
>in

Receiving SQL Server Error messages in SQL Server Log

Hi:
I've been receiving the below mentioned error messages in
my SQL Server Log for all databases ;
1. IO is thawed
2. IO is frozen for snapshot
Any idea as to for what reason this would be happening.
Thanks very much in advance!Most likely due to using dbcc freeze_io and thaw_io.
The commands are depreciated in SQL Server 2000 and are not
recommended to be used due to the depreciation as well as
the potential for these commands to hang the server.
-Sue
On Tue, 13 Apr 2004 07:39:20 -0700, "Arpit"
<apatel@.esricanada.com> wrote:

>Hi:
>I've been receiving the below mentioned error messages in
>my SQL Server Log for all databases ;
>1. IO is thawed
>2. IO is frozen for snapshot
>Any idea as to for what reason this would be happening.
>Thanks very much in advance!|||Thanks Sue!
I haven't use the dbcc freeze_io and thaw_io. Would there
any process of job that would have used this in the
background?
Thanks
>--Original Message--
>Most likely due to using dbcc freeze_io and thaw_io.
>The commands are depreciated in SQL Server 2000 and are
not
>recommended to be used due to the depreciation as well as
>the potential for these commands to hang the server.
>-Sue
>On Tue, 13 Apr 2004 07:39:20 -0700, "Arpit"
><apatel@.esricanada.com> wrote:
>
in
>.
>|||I've read a couple posts where it seems some third party
vendor for backups or shadow copy backups uses this. SQL
Server itself or any of native functionality isn't likely to
be implementing this. I'd look at whatever third party
tools, products you may be using. You could look at the
times you are getting the messages logged and try to figure
out what's running at those times.
-Sue
On Tue, 13 Apr 2004 08:52:43 -0700, "Arpit"
<anonymous@.discussions.microsoft.com> wrote:
>Thanks Sue!
>I haven't use the dbcc freeze_io and thaw_io. Would there
>any process of job that would have used this in the
>background?
>Thanks
>not
>insql

Receiving queue does not fire stored procedure

I am doing my first SSB application where two different servers send messages to each other. The logic was all previously tested on a single server between two databases and it worked OK.

The problem I am having is that when the message is received at the target server (I see this in profiler), the stored procedure associated with the queue does not fire.

I see an acknowledgment fire back to the initiator, but it is like the target server does nothing with the initial message.

Any ideas on how I can further troubleshoot this? FWIW, I used the setup tool provided by RemusResanu to set up the routes and service bindings.

Thanks for any help!
John
Here is what my target server shows in profiler

Event Class/Sub Class

Broker:Conversation Group 1 - Create
Broker:Conversation 12 - Dialog Created
Broker:Conversation 6 - Received Sequenced Message
Broker:Remote Message Acknowledgement 3 - Message with Acknowledgement Received
Broker:Message Classify 2 - Remote
|||Also, when I query the queue, I can see all of the messages just sitting there. They are reaching the right place, just nothing happens from there.

Please help!
John

Code Snippet

CREATE QUEUE [dbo].[SiteChangeNotifyReceiveQueue] WITH STATUS = ON , RETENTION = ON , ACTIVATION ( STATUS = ON , PROCEDURE_NAME = [dbo].[SiteChangeQueueReader] , MAX_QUEUE_READERS = 10 , EXECUTE AS N'sqlservices' ) ON [PRIMARY]

|||Have you tried to execute the procedure manually?
Can the user 'sqlservices' be impersonated? I.e. executing EXECUTE AS USER='sqlservices' works fine w/o errors?
Do you see any errors in the ERRORLOG file or in the NT application event log (eventvwr.exe)?|||Yes, I just tried that and it worked fine. In fact, it seemed to go in to the queue and process all of the pending messages.

So, here is the really strange part:

Now, everything works fine.

After I ran the SP manually, my messages come through and the SP fires by itself again.

I would really like it if someone could give me an explanation of this. Is this part of Service Broker enforcing the order of transactions- there were errors in my SP at one time. Perhaps the SSB was not processing the later messages until the old ones went through?

I don't t like it when I can't explain how things went from broken to working!
|||If the situation repeats please look at sys.dm_broker_queue_monitors and tell us what is the state of the queue with the problem.

RECEIVING messages from a Windows service

I've done a bit of work with the External Activator but I think it may be a bit overkill for what I need to do (which is RECEIVE messages from a single queue and process them with managed code). I've tried creating a Service Broker Interface service that retrieves messages from this queue, but I notice that if I set the timeout to -1 to watch for messages indefinitely, the Service never completes the OnStart code.

I notice if I change the service's timeout to something greater than 0, the message is retrieved, but this defeats the purpose of using a Windows Service app, which I want to continuously monitor the queue. I noticed the External Activator spawns a thread to start monitoring an EventNotification queue, which I can bypass since I want to monitor the notification's target queue.

Rushi, can you point me in the right direction to create a Windows Service that constantly monitors a queue? Also, I'd like the ability to monitor multiple databases (the queue name would be the same) as well, so if that is not feasible from a Windows Service please let me know.

Also, am I sacrificing scalability by NOT using the External Activator and switching to a Windows Service (I believe the External Activator will spawn multiple instances of the processing executable)?

Thanks,

Chris

If you do not need dynamic launching of one or more instances of your service program then you are fine with your approach of writing a single threaded process (which could be a Windows Service) that receives messages from the queue serially. (Of course, you could get fancy and have multiple (but a fixed number of) threads in your process simultaneously pulling messages from the queue). But if you need the ability to control the number of instances of your service program (processes or threads) based on rate of incoming messages, you will need to either implement the external activator based on the QUEUE_ACTIVATION event notification or use the sample.

If you choose the non-activation approach (i.e. your process is always running and waiting for messages), then setting WaitforTimeout to -1 seems appropriate to me. The Service class does not have an OnStart method, so I'm not sure what you are referring to. It does have a Run() method; and if you set WaitforTimeout to -1, Run will never return. While it would be nice to have a Cancel() method that would gracefully tear down the Service class, but since the Service Broker Interface is only a sample, we did not implement that. It should be fine to terminate the process while Run() has not returned. (The transaction will automatically be rolled back).

Rushi

|||

Rushi,

Thanks for the reply. Sorry, I was using the term "service" to refer to the Service class defined in the ServiceBrokerInterface project and a Windows Service app. What I mean is, in the OnStart() method of a Windows Service app, I would need to call the Service class' Run() method, which will never return. This means that the Windows Service's OnStart() method never completes, and you end up getting an error message from the ServiceInstaller (I think). I realize this may be more of a development question, and if so, I can try posting in another forum. I was just hoping you'd seen an example of RECEIVEing messages from a queue with a timeout of -1 from a Windows Service app.

Thanks,

Chris

|||

You will need to create a Thread in the constructor of your Windows Service class which is started by the OnStart() method. The ThreadStart should point to the ServiceBrokerInterface Service.Run() method.

Hope that helps,
Rushi

|||

Rushi:

You say...."While it would be nice to have a Cancel() method that would gracefully tear down the Service class, but since the Service Broker Interface is only a sample"

What would be those graceful tear down steps ..If I want to implement one?. I can see the following...

If there are still pending messages read from the queue but not yet processed ...return such entries back to the queue by rolling back the transaction.

Implementation wise.... "cancel()" can set a flag and the Dispatch function does not dispatch messages if this flag is set ....this way messages that are already read from queue can be prevented from getting processed.

What else I am missing.?

RECEIVING messages from a Windows service

I've done a bit of work with the External Activator but I think it may be a bit overkill for what I need to do (which is RECEIVE messages from a single queue and process them with managed code). I've tried creating a Service Broker Interface service that retrieves messages from this queue, but I notice that if I set the timeout to -1 to watch for messages indefinitely, the Service never completes the OnStart code.

I notice if I change the service's timeout to something greater than 0, the message is retrieved, but this defeats the purpose of using a Windows Service app, which I want to continuously monitor the queue. I noticed the External Activator spawns a thread to start monitoring an EventNotification queue, which I can bypass since I want to monitor the notification's target queue.

Rushi, can you point me in the right direction to create a Windows Service that constantly monitors a queue? Also, I'd like the ability to monitor multiple databases (the queue name would be the same) as well, so if that is not feasible from a Windows Service please let me know.

Also, am I sacrificing scalability by NOT using the External Activator and switching to a Windows Service (I believe the External Activator will spawn multiple instances of the processing executable)?

Thanks,

Chris

If you do not need dynamic launching of one or more instances of your service program then you are fine with your approach of writing a single threaded process (which could be a Windows Service) that receives messages from the queue serially. (Of course, you could get fancy and have multiple (but a fixed number of) threads in your process simultaneously pulling messages from the queue). But if you need the ability to control the number of instances of your service program (processes or threads) based on rate of incoming messages, you will need to either implement the external activator based on the QUEUE_ACTIVATION event notification or use the sample.

If you choose the non-activation approach (i.e. your process is always running and waiting for messages), then setting WaitforTimeout to -1 seems appropriate to me. The Service class does not have an OnStart method, so I'm not sure what you are referring to. It does have a Run() method; and if you set WaitforTimeout to -1, Run will never return. While it would be nice to have a Cancel() method that would gracefully tear down the Service class, but since the Service Broker Interface is only a sample, we did not implement that. It should be fine to terminate the process while Run() has not returned. (The transaction will automatically be rolled back).

Rushi

|||

Rushi,

Thanks for the reply. Sorry, I was using the term "service" to refer to the Service class defined in the ServiceBrokerInterface project and a Windows Service app. What I mean is, in the OnStart() method of a Windows Service app, I would need to call the Service class' Run() method, which will never return. This means that the Windows Service's OnStart() method never completes, and you end up getting an error message from the ServiceInstaller (I think). I realize this may be more of a development question, and if so, I can try posting in another forum. I was just hoping you'd seen an example of RECEIVEing messages from a queue with a timeout of -1 from a Windows Service app.

Thanks,

Chris

|||

You will need to create a Thread in the constructor of your Windows Service class which is started by the OnStart() method. The ThreadStart should point to the ServiceBrokerInterface Service.Run() method.

Hope that helps,
Rushi

|||

Rushi:

You say...."While it would be nice to have a Cancel() method that would gracefully tear down the Service class, but since the Service Broker Interface is only a sample"

What would be those graceful tear down steps ..If I want to implement one?. I can see the following...

If there are still pending messages read from the queue but not yet processed ...return such entries back to the queue by rolling back the transaction.

Implementation wise.... "cancel()" can set a flag and the Dispatch function does not dispatch messages if this flag is set ....this way messages that are already read from queue can be prevented from getting processed.

What else I am missing.?

sql

RECEIVE vs RECEIVE TOP(1)

In working through some examples, sometimes I will see this pattern for receiving messages: What is the purpose of the "nested" WAITFOR (RECEIVE? What is this actually doing? Is it receiving the same message in both RECEIVE?

WAITFOR (

RECEIVE @.dh = [conversation_handle],

@.message_type = [message_type_name],

@.message_body = CAST([message_body] AS NVARCHAR(4000))

FROM [Queue]), TIMEOUT 1000;

WHILE @.dh IS NOT NULL

BEGIN

IF @.message_type = N'http://schemas.microsoft.com/SQL/ServiceBroker/Error'

BEGIN

RAISERROR (N'Received error %s from service [Target]', 10, 1, @.message_body) WITH LOG;

END

END CONVERSATION @.dh;

COMMIT;

SELECT @.dh = NULL;

BEGIN TRANSACTION;

WAITFOR (

RECEIVE @.dh = [conversation_handle],

@.message_type = [message_type_name],

@.message_body = CAST([message_body] AS NVARCHAR(4000))

FROM [Queue]), TIMEOUT 1000;

END

COMMIT;

Other times I will see this pattern for receiving messages: Why do a RECEIVE TOP(1) instead of just a RECEIVE?

WAITFOR(RECEIVE TOP(1)

@.conversationHandle = conversation_handle,

@.messageTypeName = message_type_name,

@.messageBody = message_body

FROM [Queue]), TIMEOUT 1000;

And other times I will see this pattern for receiving messages: What is the purpose of RECEIVING into an in-memory table when you can just process the message directly?

WAITFOR(RECEIVE

queuing_order,

conversation_handle,

message_type_name,

message_body

FROM [Queue]

INTO @.tableMessages), TIMEOUT 1000;

IF (@.@.ROWCOUNT = 0)

BEGIN

COMMIT;

BREAK;

END

What is the difference between the three approaches from an architectural and performance perspective? I need to process messages as fast as possible and I'm not sure why or when each should be used. Also, does the timeout have any impact on how FAST messages will be processed, or is it exactly what it says - a timeout - if a message is not found within the period then the procedure will break?

The samples all use RECEIVE TOP(1) because it's much easier code to write and explain. Receiving all the messages available in a conversation group is much more efficient but it makes for more complex code that's harder to understand so I don't usually write samples that way. Remus has some great tips for writing efficient services here: http://blogs.msdn.com/remusrusanu/archive/2006/10/14/writing-service-broker-procedures.aspx|||

Well, I've reviewed that blog from Remus and actually ran the exact benchmarks and its true that I get pretty good performance. However, let's say that you have thousands of messages coming in per second and each message needs to be processed with some logic - what is the best way to do that using service broker. It seems counter-productive to load them into a temporary table as the example illustrates (but the example is only trying to show the capacity to clear the queue). Also, simply loading a conversation group could be unpredictable if you are recycling dialogs (every message would be a part of the same or a few dialogs). I see alot of benefit to the technology and I'm trying to use it in a very high transaction environment, but some of the advice seems to go against other recommendations and/or the examples are limited to single message processing. How about a boilerplate example of what one should strive for in the activated stored procedure when expecting the kind of load I mentioned above (thousands/sec).

|||

Well, if you trust your judgement more than the espertise of the guys who wrote Service Broker, I suupose you're free to make those judgements. The best way to scale to thousands of messages is going to depend on how big the messages are, how the dialogs are structured, etc. You may find that receiving a bunch of messages into a recordset in a .Net application running on a seperate server scales best for you if there's a significant amount of comutationally intensive processing for each message. A receive top(1) works just like a select top(1) - the whole query is executed and then after the first result is retreived the query is cancelled. If there are a lot of messages in a conversation group, this can be pretty inefficient but if each conversation group has only one message, it doesn't really matter whether you do a top(1) or not. Here are some scaleability thoughts I posted on another thread last week:

Beginning and ending a conversation takes longer than sending a message so starting up a new dialog for every message is pretty inefficient. Some thoughts here: http://blogs.msdn.com/rogerwolterblog/archive/2006/05/20/602938.aspx

Receiving one message at a time and commiting after each message is not a good way to scale an SSB application. Processing multiple messages per transaction makes error handling complex and receiving many messages per Receive statement is harder to do but if performance is a major issue, you need to do it. http://blogs.msdn.com/remusrusanu/archive/2006/10/14/writing-service-broker-procedures.aspx

If you have to do a lot of processing per message - complex XML manipulation, serializing and deserial,izing, etc. A stored procedure probably isn't a good option. Moving the messaging processing off the database server can help scaleability.

Here's a discussion of some of the things I've learned about SSB applications: http://msdn2.microsoft.com/en-us/library/aa964144.aspx

The key to understanding total throughput is understanding what it means to process a message. I had one customer complain that he could only process 300 messages a second but when I asked for his code I found out processing a message involved doing 40 or 50 inserts into the database so his 300 messages a second translated to 15,000 inserts a second. It's not unusuall to see Service Broker process a couple thousand messages a second but not on a server that's also doing a thousand order-entry transactions a second.

|||

Well, of course I trust the expertise of the guys who wrote Service Broker. My message size is configurable because they are essentially batches of data - so I can configure them based on the performance of the processing procedures. So I guess my question still remains...how best to process multiple messages using RECEIVE. Since it works just like SELECT, do I have to create some sort of cursor or does service broker offer a better pattern or syntax for processing each message in a RECEIVE without having to create a table to hold the data? Can I do some sort of join or apply or something? A simple code sample illustrating would be great. I have read those articles and links many times, but they don't quite answer the question.

|||Have a look at my blog at http://blogs.msdn.com/remusrusanu/archive/2006/10/14/writing-service-broker-procedures.aspx, it covers many of the questions you ask.sql

receive top 20

HI

I am trying to set up a stored procedure to retrieve to 20 messages from a queue into a table to implement a batched process. I have the following code in a stored procedure.

WAITFOR (
RECEIVE top (20) -- get batched so that we can process same listid once
message_type_name,
message_body, -- the message contents
conversation_handle -- the identifier of the dialog this message was received on
FROM dbo.target
into @.PayloadData
), TIMEOUT 3000 -- if the queue is empty for three second, give UPDATE and go away

However, the stored procedure is only retrieving 1 message at a time from the queue. Did I miss some other setting

thanks

P

RECEIVE can only return messages on one conversation group. Normally each conversation is its own conversation group. If you sent only one message on each conversation, RECEIVE cannot get more that one message at a time, even if there are more messages in the queue.|||

Hi

so, in your blog on T-SQL RECEIVE. Fast. : Set based Processing.

How are you able to receive the message in bulk? Is it because of the way you send the message in LoadQueueReceivePerfBlog?

P|||Yes. This is also the reason why I recommend reusing dialogs in my other entry at http://blogs.msdn.com/remusrusanu/archive/2007/04/24/reusing-conversations.aspx|||

one follow up,

when I send the message using the same conversation handle, the receive top (20) statement waits until the previous batch is committed before it will start retrieving the next 20. I guess this is because of the its now part of the same conversation group and service broker need to guarantee process order?

This is what my proc looks like

BEGIN TRANSACTION

WAITFOR (

RECEIVE top (20) -- get batched so that we can process same listid once

message_type_name,

message_body, -- the message contents

conversation_handle -- the identifier of the dialog this message was received on

FROM dbo.target

into @.PayloadData

), TIMEOUT 3000 -- if the queue is empty for three second, give UPDATE and go away

-- do some processing of the records in @.PayloadData

COMMIT TRANSACTION

if "-- do some processing of the records in @.PayloadData" is taking a long time, its going to block the messages in the queue.

If I remove the begin and commit transaction block, it only wait for the 3 seconds I specified.

Question: is the transaction block necessary in the activated procedure.

thanks

Paul

Receive messages from queue based on message_type_name

I have 2 messages types for a queue and would like to use one application that receives messages of one type from the queue and another application that will receieve messages of a different type from the same queue.

Is that possible and if yes how?

SampK

You must send the two message types on separate conversations, to two different services, bound to to different queues. Then you can have one app receive from one queue, the other form the second queue. It is not possible to selectively receive messages based on attributes (type, contract, payload etc) because it could break the order guarantee of the conversations.

HTH,
~ Remus

Receive all messages on queue

Hi i am trying to create a batch process then commit for all messages on the queue. The problem i am having is when i run my query (As below) I only receive the first message and the corresponding end dialog for the message although i have 2000 records sitting in the queue. It is my understanding that receive without any criteria i.e top(1) or where clause should select everything of the queue. I tried receive top(100) expecting 100 records but still only got 2 back.

any help appreciated.

WAITFOR(RECEIVE

queuing_order,

conversation_handle,

message_type_name,

message_body

FROM [RMIS_COMMS_Queue]

INTO @.tableMessages), TIMEOUT 2000;

Each RECEIVE returns only messages belonging to conversation in one conversation group only. If each conversation is its own group (which is true unless you use RELATED_CONVERSATION clause in BEGIN DIALOG or you use MOVE CONVERSATION) then it means you can RECEIVE only one conversation in one call. The TOP clause applies to this resultset (one conversation).

HTH,
~ Remus

|||Thanks for that. Can i just refer to your article on batch processing messages (the example with the cursor) this is the method i am try ing to implement but my scenario is many shops with many tills, where each message/conversation is a complete sale from a till i.e many conversations with one message. Is it safe to say this is not the best method for trying to speed this up, what would be the best way to process these messages, bearing in mind sometimes a shop can lose comms and send down a batch of sales when it reconnects.|||

I'm using triggers to cause an update of a seperate database via service broker, in our activated stored procedure we are seeing similiar situations, Could you give an example of a script that a trigger might use to take advantage of the RELATED_CONVERSATION clause? I looked at the documentation and didn't see how I could use this in our situation. (trigger on update sends inserted table with for xml clause as message to queue)

Thanks,

Bill

Receive all messages on queue

Hi i am trying to create a batch process then commit for all messages on the queue. The problem i am having is when i run my query (As below) I only receive the first message and the corresponding end dialog for the message although i have 2000 records sitting in the queue. It is my understanding that receive without any criteria i.e top(1) or where clause should select everything of the queue. I tried receive top(100) expecting 100 records but still only got 2 back.

any help appreciated.

WAITFOR(RECEIVE

queuing_order,

conversation_handle,

message_type_name,

message_body

FROM [RMIS_COMMS_Queue]

INTO @.tableMessages), TIMEOUT 2000;

Each RECEIVE returns only messages belonging to conversation in one conversation group only. If each conversation is its own group (which is true unless you use RELATED_CONVERSATION clause in BEGIN DIALOG or you use MOVE CONVERSATION) then it means you can RECEIVE only one conversation in one call. The TOP clause applies to this resultset (one conversation).

HTH,
~ Remus

|||Thanks for that. Can i just refer to your article on batch processing messages (the example with the cursor) this is the method i am try ing to implement but my scenario is many shops with many tills, where each message/conversation is a complete sale from a till i.e many conversations with one message. Is it safe to say this is not the best method for trying to speed this up, what would be the best way to process these messages, bearing in mind sometimes a shop can lose comms and send down a batch of sales when it reconnects.|||

I'm using triggers to cause an update of a seperate database via service broker, in our activated stored procedure we are seeing similiar situations, Could you give an example of a script that a trigger might use to take advantage of the RELATED_CONVERSATION clause? I looked at the documentation and didn't see how I could use this in our situation. (trigger on update sends inserted table with for xml clause as message to queue)

Thanks,

Bill