Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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:

CREATE PROCEDURE QueueReceive
  @myTargetQueue SYSNAME
  @cg UNIQUEIDENTIFIER OUTPUT
  @ch UNIQUEIDENTIFIER OUTPUT
  @msg XML OUTPUT
as
    BEGIN TRANSACTION;
    WAITFOR
            ( RECEIVE TOP(1)
                @cg = conversation_group_id,
                @msg = cast(message_body as XML),
                @ch= conversation_handle  
              FROM @myTargetQueue
            ), TIMEOUT 3000;
    COMMIT
...

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...

share|improve this question

2 Answers 2

up vote 5 down vote accepted

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.

share|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

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

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

                DECLARE @h UNIQUEIDENTIFIER;
                DECLARE @messageType int;
                DECLARE @Packet VARCHAR(50);
                DECLARE @MyId UNIQUEIDENTIFIER;
                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
                                FROM                                
                                                sys.service_queues q (nolock)
                                JOIN                
                                                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
                                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)
                                BEGIN
                                                COMMIT;
                                              BREAK;
                                END
                                ELSE
                                BEGIN

                                                IF  @messageType <= 2  
                                                BEGIN
                                                                END CONVERSATION @h;
                                                END
                                            ELSE 
                                                BEGIN

                                                                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;
                                            END                             

                                            COMMIT;
                                END
            END
END
share|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 at 16:21

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.