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