Dynamic queue receive sql ?

Hi There

My activation sp must be able to read of various queues.

I load a variable with the queue name that activated the sp btu i cannot get the syntax working to receive or get a conversation group of a queue name that is a variable.

I have tried:

WAITFOR

(

RECEIVE message_body, conversation_handle, message_type_name, message_sequence_number, conversation_group_id FROM @.callingQueue INTO @.msgTable WHERE conversation_group_id = @.conversationGroup

), TIMEOUT 2000;

But i get this error:

Incorrect syntax near '@.callingQueue'.

Looks like you cannot use a variable.

So i tried the following:

SELECT @.SQL = N' WAITFOR

(

RECEIVE message_body, conversation_handle, message_type_name, message_sequence_number, conversation_group_id FROM @.callingQueue INTO @.msgTable WHERE conversation_group_id = @.conversationGroup

), TIMEOUT 2000'

EXEC sp_executesql @.SQL, N'@.msgTable table output'

But i get the same error.

How do i receive of a queue using a vriable holding the queue name ?

Thanx

Hi,

Just have a look at the following code sample. This sample implements an actviated stored procedure that gets the queue name from the sys.dm_broker_activated_tasks DMV. Maybe you must modify the code a little bit to target your scenario, but the basic structure should meet your requirements.

HTH

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

CREATE PROCEDURE ProcessRequestMessages
AS
DECLARE @.ch UNIQUEIDENTIFIER
DECLARE @.messagetypename NVARCHAR(256)
DECLARE @.messagebody XML
DECLARE @.responsemessage XML
DECLARE @.queue_id INT
DECLARE @.queue_name NVARCHAR(MAX)
DECLARE @.sql NVARCHAR(MAX)
DECLARE @.param_def NVARCHAR(MAX);

-- Determining the queue for that the stored procedure was activated
SELECT @.queue_id = queue_id FROM sys.dm_broker_activated_tasks
WHERE spid = @.@.SPID

SELECT @.queue_name = [name] FROM sys.service_queues
WHERE object_id = @.queue_id

-- Creating the parameter substitution
SET @.param_def = '
@.ch UNIQUEIDENTIFIER OUTPUT,
@.messagetypename NVARCHAR(MAX) OUTPUT,
@.messagebody XML OUTPUT'

-- Creating the dynamic T-SQL statement, which does a query on the actual queue
SET @.sql = '
WAITFOR (
RECEIVE TOP(1)
@.ch = conversation_handle,
@.messagetypename = message_type_name,
@.messagebody = CAST(message_body AS XML)
FROM '
+ @.queue_name + '
), TIMEOUT 1000'

WHILE (1=1)
BEGIN
BEGIN TRANSACTION

-- Executing the dynamic T-SQL statement that contains the actual queue
EXEC sp_executesql
@.sql,
@.param_def,
@.ch = @.ch OUTPUT,
@.messagetypename = @.messagetypename OUTPUT,
@.messagebody = @.messagebody OUTPUT

IF (@.@.ROWCOUNT = 0)
BEGIN
ROLLBACK TRANSACTION
BREAK
END
END

|||

Hi Klaus

I use exactly the same technique, i can see straight away where i went wrong, Thank You

|||

Hi Klaus

Ok next problem.

This does not work:

SELECT @.SQL = N'WAITFOR

(RECEIVE message_body, conversation_handle, message_type_name, message_sequence_number, conversation_group_id FROM ' + @.callingQueue + ' INTO @.msgTable WHERE conversation_group_id = '

+ CAST(@.conversationGroup AS char) + '), TIMEOUT 2000'

EXEC sp_executesql @.SQL, N'@.msgTable TABLE output', @.msgTable out

The reason for this is becuase a table variable cannot be an output.

Now i have to read into a tabel variable because i am expecting many messages. Originally i kept looping to get the next message but i kept getting the first message of the queue , i then saw that it was a good practice to read all message into a table variable and then process them.

So i am a bit stuck i have to use sp_executesql to have a dynamic queue, and i need a table variable becuase i want to process multipel message, but i cannot use a table variable with sp_executesql.?

Please help

|||

Hi Klaus

Just so you can get a better idea, this is what i am trying to do. Not sure how to make it work without a table variable. As mentioned i used to simply do a receive top 1, i would then process the message commit and do another read of the queue but i would keep getting the first message. This was resolved using a table variable which is now a problem for sp_executesql.

BEGIN

SELECT @.SQL = N'WAITFOR

(RECEIVE message_body, conversation_handle, message_type_name, message_sequence_number, conversation_group_id FROM ' + @.callingQueue + ' INTO @.msgTable WHERE conversation_group_id = '

+ CAST(@.conversationGroup AS char) + '), TIMEOUT 2000'

EXEC sp_executesql @.SQL, N'@.msgTable TABLE output', @.msgTable out

IF @.@.ROWCOUNT = 0

BEGIN

ROLLBACK TRAN

BREAK

END

DECLARE message_cursor CURSOR FOR

SELECT message_body , conversation_handle , message_type_name, message_seq_number, conversation_group_id from @.msgTable order by message_seq_number asc

OPEN message_cursor

FETCH NEXT from message_cursor INTO @.msgBody, @.ConvHandle, @.msgType, @.message_seq_number, @.conversationGroup

WHILE @.@.FETCH_STATUS = 0

BEGIN

IF @.msgType = 'http://schemas.microsoft.com/SQL/ServiceBroker/EndDialog'

FETCH NEXT from message_cursor INTO @.msgBody, @.ConvHandle, @.msgType, @.message_seq_number, @.conversationGroup

END

END CONVERSATION @.ConvHandle;

CLOSE message_cursor

DEALLOCATE message_cursor

return

END

|||Hi Kluas please note i dont actually do an end conversation in the if, i have cut the logic out, i just wanted you to get an idea of the recieve and how i use the table variable. Thanx|||

Hi Dietz!

Yes, that's a restriction with the sp_executesql statement: you can't use table variables.
Another option would be to use a temp table instead a table variable - would this make sense to you?

Thanks

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

|||

Dietz wrote:

SELECT @.SQL = N'WAITFOR

SELECT @.SQL = N'WAITFOR

(RECEIVE message_body, conversation_handle, message_type_name, message_sequence_number, conversation_group_id FROM ' + @.callingQueue + ' INTO @.msgTable WHERE conversation_group_id = '

+ CAST(@.conversationGroup AS char) + '), TIMEOUT 2000'

EXEC sp_executesql @.SQL, N'@.msgTable TABLE output', @.msgTable out

Use QUOTENAME around the @.callingQueue. Use INSERT ... EXEC instead of INTO @.msgTable:

SELECT @.SQL = N'WAITFOR (RECEIVE message_body, conversation_handle, message_type_name, message_sequence_number, conversation_group_id FROM ' + QUOTENAME(@.callingQueue)

+ ' WHERE conversation_group_id = '

+ CAST(@.conversationGroup AS char) + '), TIMEOUT 2000'

INSERT INTO @.msgTable EXEC (@.SQL);

Dietz wrote:

DECLARE message_cursor CURSOR FOR

SELECT message_body , conversation_handle , message_type_name, message_seq_number, conversation_group_id from @.msgTable order by message_seq_number asc

The correct order by is conversation_handle, message_seq_number, to process one conversation at a time:

DECLARE message_cursor CURSOR FOR

SELECT message_body , conversation_handle , message_type_name, message_seq_number, conversation_group_id from @.msgTable order by conversation_handle, message_seq_number asc

HTH,

~ Remus

|||

Hi Remus

That looks great, thank you very much, i will try it today.

There is only ever 1 conversation handle, the converation continues for years so i dont think it is required in the order by. Also is it necessary to order by message_seq_number to ensure messages are processed in the right order? Is this not "built into" service broker, i added it just in case.

|||

The RECEIVE returns the messages in order, but the SELECT ... FROM @.table does not offer any quarantee unless an explicit ORDER BY is provided.

HTH,
~ Remus

|||Yes of course, sorry stupid question. Dont know why i thought you where reffering to the recieve :)