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?