Many Queues and one Activation Procedure for that queues.

Hi, All!

Could you please help me in my problem?

I develop Service Broker applcation, SB has 20 queues, also I have one Activation Procedure which carries out following actions:

CREATE PROCEDURE proc_ms_Activation

AS

BEGIN

WAITFOR (RECEIVE TOP(1) message_type_name, message_body, conversation_handle, message_id FROM Queue1)

-- process message, save statisctisc and etc

END

Problem in that: when I execute command WAITFOR (RECEIVE TOP (1) message_type_name, message_body, conversation_handle, message_id FROM Queue1) I should specify a name concrete Queue, for example "... FROM Queue1 ". I would like to use one procedure for processing all Queue.

How I can design my application that one Activation Procedure processed messages from all Queues?

Thank a lot for help.

You can figure out what queue you were activated for by looking in sys.dm_broker_activated_tasks and filter for the current session (spid = @.@.SPID). Then use database_id and queue_id to lookup the queue name is sys.service_queues and execute the WAITFOR (RECEIVE... ) as dynamic SQL.

BTW, If you run into EXECUTE AS context restrictions (like inability to see the records in servel level views) see this article: http://blogs.msdn.com/remusrusanu/archive/2006/03/01/541882.aspx Simples fix is to mark the database trustworthy.

HTH,
~ Remus

|||

Hi Svitlana!

What's the reason that you have one activated stored procedure for the whole 20 queues? Because when you get different message types on each of the 20 queues, then it makes (for me) no sense to use just one activated stored procedure that handles all message types. Why you don't use one activated stored procedure for each queue?

Thanks

Klaus Aschenbrenner
www.sqljunkies.com/weblog/klaus.aschenbrenner
www.csharp.at

|||

Thanks for the shown interest, excuse that long did not answer.

Remus, thanks a lot for help! you post is very useful.

Klaus, allow me to explain what's the reason that I have one activated stored procedure for the 20 queues.

In my application only one custom message type. Stored Procedures identical and also differ only Queue name. It would be convenient to have one stored procedure for all queues.