Activating the Queue

Hi,

I have created Queue with the following syntax. But it is not getting activated itself. What I have to do to get it activated itself, and what could be the frequency by default.

CREATE QUEUE NewCustomerQueue
WITH ACTIVATION
(PROCEDURE_NAME = prProcessNewCustomers,
STATUS = ON,
MAX_QUEUE_READERS = 1,
EXECUTE AS SELF)
GO

If I execute the prProcessNewCustomers procedure manually it is showing that the Queue has been activated. What change I have to make in the syntax to get it activated itself.

Actually I have two scenarios in my requirement,

1. One Queue processing immediately when it receives data (Order Processing)

2. Another Queue, Process when the server is idle i.e., off-peak time (for mailing)

What syntax I have to use for these.

Please help.

Thanks in advance

Babu

Activation can be turned on and off using ALTER QUEUE

ALTER QUEUE NewCustomerQueue WITH ACTIVATION (STATUS = ON|OFF);

So in your first scenario you can keep activation on always. In your second scenario, you can keep it off by default and turn it on whenever desired.

Activation launches the specified stored procedure in the background upon receipt of incoming messages. If the procedure cannot keep with the rate of incoming messages, it can launch concurrent instances of the stored procedure (you need to set max_queue_readers > 1 for that).

Hope that helps,

Rushi

|||

Hi Rushi,

Thanks for your reply.

I have created the Queue with the ACTIVATION (STATUS = ON) only.

Currently I'm trying with a typical example what I got from some books. When a new customer is added, the trigger in the Customer table will "BEGIN DIALOG CONVERSATION" and send a message. I think when a message is sent, the Queue should get activated. It is not happening in this case. What could be the reason.

Please help.

Regards

Babu

|||

Hi Babu:

First make sure the trigger runs by checking in query analyser. Check the sql profiler to see whats the errror your getting.

Pramod

|||

Do you see the message in the target queue?

select * from [target queue]

(where [target queue] is the name of your target queue)

Rushi

|||

Hi Rushi, Pramod,

Thank you both for the responses.

The problem was occured because the 'Idle CPU Condition' was not defined.

I'll come back with new doubts . Thank you once again.

Regards

Babu