Always two queues?

I'm just getting around to understanding notification services in sql 2005 and I have been working through some examples. I'm curious as to why there are always two queues and two corresponding services being set up even when both queues/services exist in the same database. Here is my ultimate goal. I want to have triggers put messages on the queue for various stored procedures to handle asynchronously - i.e. table xyz is updated, and I need a stored proc to take the updated values and possibly generate or update rows in another table. When I tried to set up one queue and one service, nothing ever seems to get put on the queue:

Create Message Type TestMessageType Validation = Well_Formed_XML;

Create Contract TestContract (TestMessageType Sent By Any);

Create Queue TestQueue;

Create Service TestService ON Queue TestQueue(TestContract);

Begin Tran;

Declare @.DialogHandle UNIQUEIDENTIFIER;

Begin Dialog Conversation @.DialogHandle

From Service TestService

To Service 'TestService'

On Contract TestContract

Send On Conversation @.DialogHandle

Message Type TestMessageType (N'<message>Hello World</message>');

End Conversation @.DialogHandle;

Commit Tran;

Select * From dbo.TestQueue;

(nothing is ever returned)

If you perform BEGIN, SEND and END in one transaction, you lose any opportunity to receive response messages (including errors). Once you END, you cannot send or receive more messages. Hence we do not recommend trying the fire-and-forget pattern of doing BEGIN, SEND and END in one shot. It is likely in your case that there is a security misconfiguration that is resulting in the dialog being errored out. An error message should be delivered to the initiator queue.

Regaring your question of why two queues? The primitive of communication in Service Broker is a persistent session called conversation. Dialogs are types of conversations between two services -- the initiator and the target. Given that there are two services involved in any message exchange, each will have its own storage -- resulting in two queues. This differs from MSMQ or MQSeries which use atomic messaging as their primitive and are more queue-centric than service-oriented.

I would recommend that you use separate initiator and target services even though you do not forsee ever sending replies back to the initiator. While loopback dialogs are supported, they are tricky to implement in practice and not recommended if you are a newbie.

Finally Service Broker is a component of the SQL Server database engine (sqlservr.exe process). It is unrelated to SQL Notification Services (which run as external processes) used for developing and deploying apps that send notifications to users.

Rushi

|||Thanks - by the way no error messages or anything in the queue. Just FYI. And I'm happy to use two queues if that's what it takes to make things work. Just seems a little odd. I take it that the initiator queue doesn't have to be manually cleaned out - ie the messages don't pile up in the initiator queue while the other services retrieves messages from the target queue?|||

If the target service ends the conversation first, it will send a end dialog message to the initiator service which is delivered to the initiator queue. The initiator queue needs to process that message and end the conversation on its end. If you don't want to block and wait for this message to be received, you could set up an internally activated stored proc just to consume these messages and end the conversations.

|||Unfortunately I am struggling with clearing the initiator queue. Do I have to set up another contract on the initiator queue? I am just trying to use the helloworld sample included with sql server 2005. I created a stored proc and assigned it to the initiator queue. Its supposed to consume messages from the initiator queue and end the conversation, but the message is still in the queue. If I take the stored proc code and run it in a query window, it executes without error, but the message is still in the queue.|||Can you paste your code?|||

Here is the stored procedure. The code to create the initiator queue is below it. Everything else is taken straight from the hello world example (and it was working)

CREATE procedure [dbo].[MessageDisposal] as

WHILE (1 = 1)

BEGIN

DECLARE @.conversation_handle UNIQUEIDENTIFIER,

@.conversation_group_id UNIQUEIDENTIFIER,

@.message_body XML,

@.message_type_name NVARCHAR(128);

BEGIN TRANSACTION ;

-- Get next conversation group.

WAITFOR(

GET CONVERSATION GROUP @.conversation_group_id FROM [dbo].[InitiatorQueue]),

TIMEOUT 500 ;

-- If there are no more conversation groups, roll back the

-- transaction and break out of the outermost WHILE loop.

IF @.conversation_group_id IS NULL

BEGIN

ROLLBACK TRANSACTION ;

BREAK ;

END ;

-- Process all messages in the conversation group. Notice

-- that all processing occurs in the same transaction.

WHILE 1 = 1

BEGIN

-- Receive the next message for the conversation group.

-- Notice that the receive statement includes a WHERE

-- clause to ensure that the messages recieved belong to

-- the same conversation group.

RECEIVE

TOP(1)

@.conversation_handle = conversation_handle,

@.message_type_name = message_type_name,

@.message_body =

CASE

WHEN validation = 'X' THEN CAST(message_body AS XML)

ELSE CAST(N'<none/>' AS XML)

END

FROM [dbo].[TargetQueue]

WHERE conversation_group_id = @.conversation_group_id ;

-- End the conversation.

END CONVERSATION @.conversation_handle ;

END; -- Process all messages in conversation group.

-- Commit the receive statements and the end conversation statement.

COMMIT TRANSACTION ;

END ; -- Process all conversation groups.

CREATE MESSAGE TYPE HelloWorldMessage

VALIDATION = WELL_FORMED_XML ;

GO

CREATE CONTRACT HelloWorldContract

( HelloWorldMessage SENT BY INITIATOR);

GO

CREATE QUEUE [dbo].[TargetQueue] ;

GO

CREATE QUEUE [dbo].[InitiatorQueue]

with status=on,

activation(

procedure_name=testproc,

max_queue_readers=2,

execute as owner) ;

GO

CREATE SERVICE InitiatorService

ON QUEUE [dbo].[InitiatorQueue];

GO

CREATE SERVICE TargetService

ON QUEUE [dbo].[TargetQueue]

(HelloWorldContract);

GO

|||

You are setting the activation procedure name to 'testproc' instead of MessageDisposal.

Rushi

|||Sorry that was a typo. I was setting it to the correct procedure (I double checked)|||I found the error. The stored procedure was referencing the initiator queue when getting the conversation group ID, but then it was trying to receive messages from the target queue. Thanks for your help.