Schedule service broker to receive messages automatically

Question

I am new to Sql Server Service Broker and experimenting with it.

I was able to send messages from one DB and receive those messages in another DB (of the same SQL server) and I am inserting those messages into a table in the receiving DB.

Everything is working so far, but everytime I send a message from the source DB, I have to go the destination DB and run the RECEIVE query manually to fetch the message from the receiving queue and insert into the table.

I would like to automatically receive the messages from the receive queue as soon as they arrive (or in a schedule, say every 10 minutes) and insert them into my destination table, without me manually doing it.

One option is to create a SP and schedule that to run every 10 minutes. I am not sure if that is the recommended way or if there is any other better way to listen to the receiving queue and automatically retrieve the messages as soon as they arrive.

Any help would be appreciated.

Answer 1

What you're looking for is what's called broker activation (specifically, internal activation). In essence, you can "attach" a stored procedure to a service broker queue that will be called when a message shows up on the queue. Read all about it in BOL.