Activation/Security/Dynamic SQL Question

I will have a variety of different types of work that will come into my Service Broker queue and I'll likely have a stored procedure or two for each of the different types of work (ie. move order header, move items, move payment, etc.) What is required to be done in each of these steps may vary by the subsidiary and type of order coming in. My plan is to use exclusively stored procedures but to execute them dynamically using sp_executesql. I think I should use sp_executesql because that way I can have a config file (in xml) that I can store what stored procedures need to be called for which unit of work/order type/subsidiary. If I do this I should be able to easily configure each type of work to be done in a config file and let Service Broker handle the execution dynamically. As long as I keep the parameters the same for each of the stored procedures (I'm thinking maybe 4 or 5 parameters) and passing them to each of the stored procedures, this approach will allow me to dynamically configure Service Broker to do what it is supposed to do. I can pull what needs to be done out of the message that comes in with an XQuery expression on the config file. I know that I will have to configure my user (activation user) to be able to run sp_executesql and the security may be complex (especially since I'm using certificates). I can not use trusted databases. Are there any other considerations I should think about?

Gary

You should be able to execute dynamic SQL as well as impersonate database principals (using the 'EXECUTE AS' statement or clause) to call message type handlers from the activation stored procedure. If you stay within the database, your database does not have to be trustworthy.

Hope that helps,
Rushi

Activation procedure design

I've been experimenting with Service Broker and was surprised at one aspect of the design: the interface to Activation stored procedures.

I would have expected the queue to be a parameted passed to the procedure rather than having to hard code the queue query into the SP.

In a system with lots of queues it seems plausible that the same activation procedure might want to be used with several queues.

Any comments?

David.

Stored procedures are compiled into execution plans that bind strictly the rowsets being involved. That means that a stored procedure cannot be compiled to issue a RECEIVE (or SELECT for the matter) against a generic 'queue', but only agains a very specific <queue_name>. This is the same reason why one cannot write a SELECT where the table name is a @.variable. The only workaround, both in SELECT and in activation case, is to use dynamic SQL, with the likely cost of having to compile the dynamic SQL when the procedure executes.

Given this it would not make sense to have the queue name passed as a parameter, it is not a performant pattern. However, if you must, there is the trick to get the queue you were activated for from sys.dm_broker_activated_tasks and build dynamic SQL to RECEIVE the messages.

HTH,

~ Remus

Activation

I was testing around with a sample service broker app using activation, and came across an interesting question. The little app sends a series of four messages to a queue, either on the same conversation or on seperate ones. Each message invokes one stored procedure in my activation procedure. All the procedure does is enter a record into a test table and then wait for an allotted amount of time. In my example, the first message called a proc that waited 20 sec, the 2nd one that waited 10 seconds, the third 5 seconds, and the 4th 1 second. I am using internal activation on the queue. It seemed that in both scenarios (sending on 4 separate conversations and on one conversation) the procedures executed "almost" sequentially. "Almost" meaning that the first procedure was done before the last one started executing. It makes sense to me that this would happen where I sent them on the same conversation, but not really when I sent them on 4 seperate ones. Is it because when I call a procedure from my activation procedure it locks the queue so that another message cannot be processed (I'm processing a message at a time)? How could I make it so that the 4th procedure (the one that only waits 1 second) returns before the 1st procedure (the one that waits 20 seconds)?MAX_QUEUE_READERS = ?|||

Sorry, my max_queue_readers where 5 in both situations.

Tim

|||

With one message per conversation then new instances of the stored procedure may be launched, so a message that arrived later may be processed sooner by a new instance activated procedure.

Also, even locally, the message order is only guaranteed within a conversation, so 5 messages sent on 5 conversation may arrive on the target queue in any order (unlikely to arive in different order, but one should not assume order between conversations)

|||You reference that new instances of a sproc may be launched for one message per convo, but what determines if it will or not? Will it only happen if some threshold is exceeded on the queue? The reason I was doing the little test was because I haven't seen any articles out there really detailing good ways to run large processing queries asynchronously for one application, so I was wanting to write one. My main curiosity was if there was any way to modify any of the settings for the procedure activation (which I don't think that there is).|||I just read your blog entry on Parallel Activation...neat stuff. Although, I doubt that it is something I would ever need to implement. I changed my procedures a bit so that they delay for a longer time period, and it appears that a new activation occurs ~ 5 seconds.