a while ago I asked a question about how to make begin dialog and send more flexible so that it can be embedded in a procedure that takes the to, from, parameters as sysname variables.

Service Broker Procedure that will be used by multiple initiators to begin dialog conversation.. how to pass a parameter into it

however as Rusanu mentioned in the answer, this same technique cannot be used for the From clause of the Receive.

Actually it will work. Most SSB verbs accept parameters for they arguments (except queue name for RECEIVE, of course). The parameters are of type sysname ...

actually the send side is done and I am now trying to make the RECEIVE flexible in the same way, something like:

  @myTargetQueue SYSNAME
            ( RECEIVE TOP(1)
                @cg = conversation_group_id,
                @msg = cast(message_body as XML),
                @ch= conversation_handle  
              FROM @myTargetQueue
            ), TIMEOUT 3000;

It seems variables of type sysname cannot be used in the from clause of the RECEIVE? If I have to do it in dynamic SQL, how would I return all the variables, conversation_group_id, conversation_handle, etc. from the dynamic sql execute of a receive function? is there a better performing technique to accomplish the same thing?

Thank you.

FIX/Update SO FAR: I am creating a bunch of IF clauses depending on what the passed parameter is, it will just execute a different whole receive statement. It is not efficient because I have to change procedure code any time I add a new QUEUE, but I guess it will do for now...


Because RECEIVE is basically a DELETE and as such has a query plan, it must obey the same restrictions SELECT/INSERT/DELETE/UPDATE statements have, specifically the restrictions that the object it acts on must be known at compile time, not at execution time.

The only option is to use dynamic SQL, with all the blessings and pitfalls that follow.

You could also generate the procedure body during project deployment, have a single template and generate a specific procedure for each queue, specialized for the specific queue name. Whether this is feasible depends on many factors, first and foremost on the organization of your project and how you deploy.

On a side note I'm surprised to hear that you have many queues. In general the tendency is to have a single queue and several queue readers (activated procedures). Since SSB programing is event driven (wait for for message, process message, wait for message, process message, wait for message...) having more than one queue to wait for messages on it becomes harder, as the application now has to wait on multiple sources (eg. one thread per queue at least). Even with SSB activation, which alleviates the need for explicit wait wince it launches the code to process the message on-demand, multiple queues are harder to manage (max_queue_readers per queue adds up to perhaps too many internal activated procedures launched). Consider using a single service and queue on the RECEIVE side. Even when multiple services are needed (for whatever reason) they can be consolidated on a single queue.

| improve this answer | |
  • I see. Thanks. A one-queue design would require some rethinking, but if it is recommended to comply with limitations on internal activated procedures, and to avoid having problems with receiving data from any of a number of queues, then I'll go along with best practices... – fa1c0n3r Jun 13 '12 at 6:24
  • 1
    Is not necessarily a bad practice. Is just easier to write the code, you'll see. – Remus Rusanu Jun 13 '12 at 6:46
  • @RemusRusanu - do you mean to say multiple instances of activation procedures or multiple, different activation procedures? I was under the impression that a queue could only have one activation procedure. – Jeff Sacksteder Mar 20 '15 at 23:07

This is a working sample receive procedure that is re-usable over different queues.

ALTER PROCEDURE [dbo].[zBroker_OnReceive_WFItemsUpdated_Queue]
                SET NOCOUNT ON;

                DECLARE @messageType int;
                DECLARE @Packet VARCHAR(50);
                DECLARE @QueueName NVARCHAR(200);
                DECLARE @Sql NVARCHAR(2000);

                SET  @MyId = NEWID(); -- Just an Id for testing that get's written to the log table

                SET @QueueName = (
                                SELECT q.NAME
                                                sys.service_queues q (nolock)
                                                sys.dm_broker_activated_tasks t (nolock) ON t.spid = @@SPID AND q.object_id = t.queue_id
                SET @Sql = 'WAITFOR( RECEIVE TOP(1) 
                                                @h = conversation_handle,
                                                @messageType = message_type_id,
                                                @Packet = message_body
                                                FROM ' + @QueueName + '), TIMEOUT 1000;'

                --Clear the queue
                WHILE (1=1)
                                BEGIN TRANSACTION;

                             EXECUTE sp_executesql @Sql
                                ,N'@h UNIQUEIDENTIFIER OUTPUT,@messageType int OUTPUT,@Packet VARCHAR(50) OUTPUT'
                                ,@h = @h OUTPUT
                                ,@messageType = @messageType OUTPUT
                                ,@Packet = @Packet OUTPUT

                                IF (@@ROWCOUNT = 0)

                                                IF  @messageType <= 2  
                                                                END CONVERSATION @h;

                                                                INSERT  INTO t_log (logdata,msgdata,ProcId,StartTime)
                                                                VALUES  ( @QueueName, @Packet ,@MyId,GETDATE());

                                                                WAITFOR DELAY '00:00:10';
                                                                UPDATE t_log SET EndTime = GETDATE()
                                                                WHERE logid = @@identity

                                                                END CONVERSATION @h;

| improve this answer | |
  • Thanks, this helped me, but it's worth pointing out that 1) Querying sys.dm_broker_activated_tasks requires VIEW SERVER STATE permissions, 2) you probably want to use QUOTENAME(@QueueName) because the queue name might have special characters, and 3) unless you put everything in dbo (which is generally bad practice), you should qualify the queue name with with its schema name, too. – p.s.w.g Sep 6 '14 at 16:21

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.