1

I have to receive messages from a queue continuously.

My current code looks like this:

    DECLARE @status INT=1
    WHILE @status IS NOT NULL
    BEGIN
    WAITFOR (RECEIVE  @status=status,
            @message = message_body 
    
            FROM dbo.MyQueue) 
...

Of course this code never ends, I have to stop it manually, but it does what's needed.

I'm thinking on put this code in a SP invoked by a job, scheduled at a time, in order to have the call resuming in case of error.

What I would like to ask is:

  • Is a job the correct way of starting the "infinite" listener?
  • If a job is scheduled, let's say, every ten seconds, does it create "overlapped" processes?
  • If I kill the job, is the invoked SP killed as well?
  • If not, how can I kill running SP?
1
  • Why not just give it control commands in the same Q? With a little luck you can priotize them in such a way that the control commands are received first so you can always make an orderly exit.
    – ik_zelf
    Aug 12 at 8:39
2

While the answer by Trubs does answer your questions and it might work great for you, I'd say the "correct" way to continuously receive from a queue is by using Service Broker Activation, either internal or external.

When using internal activation you attach a Stored Procedure to a queue and tell Service Broker to activate it when there are messages to be processed. The SP can terminate when there is no work left and it will be reactivated as needed. Also, you have the choice of telling the system to run multiple activations of your SP if there is demand. You configure this type of activation like this:

ALTER QUEUE dbo.MyQueue
WITH ACTIVATION (
    PROCEDURE_NAME = dbo.MyQueueReaderSP
    , MAX_QUEUE_READERS = 1
    , EXECUTE AS OWNER
    , STATUS = ON);

An example of using this feature can be seen on this article by Remus Rusanu. His site has many articles on Service Broker, definitely worth checking out. The code above was adapted from his site as well.

External activation is more complicated and has more moving pieces but it allows you to activate external processes. It's great in the fact you can run code written in any language and also take the processing load out of the database server itself.

3
  • An agent job is a good "SQL Server" way to start an infinite job. as for your code, you could simply use

       While 1=1
    
  • An agent job will not start another session while the existing one is still running

  • Some SQL statements will be killed with the job, some not (see link)

  • find the spid of the running command and kill it.

1
  • Have to say response from @kirchner better for my case. Sep 6 at 12:15

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.