Service Broker with Sql Server 2005 - Messages stuck in queue

Question

Hi!

I'm configuring a simple service broker implementation. Locally, we use SQL Server 2008 Express and everything works fine. Once the code runs on our production server (SQL SERVER 2005), messages are stuck in the receiver queue. The followind code is incomplete but states how queues and services are basically configured:

-- Create message type to validate the content of a message
CREATE MESSAGE TYPE MyMessageType VALIDATION = NONE;

-- Create contract to validate what direction messages can be sent in a conversation.
CREATE CONTRACT MyContract 
(
    MyMessageType SENT BY INITIATOR
) 

-- The receiver queue will process each message using the 'spProcessMessage' stored procedure
CREATE QUEUE [MyReceiverQueue];
ALTER QUEUE [MyReceiverQueue] WITH ACTIVATION 
(
      STATUS = ON,
      MAX_QUEUE_READERS = 1,
      PROCEDURE_NAME = spProcessMessage,
      EXECUTE AS SELF
);

-- The receiver service processes the incoming messages and passes them to the ReceiverQueue.
CREATE SERVICE [MyReceiverService] ON QUEUE [MyReceiverQueue]([MyContract]);

-- Queue and service to send the message from
CREATE QUEUE [MySenderQueue];
CREATE SERVICE [MySenderService] ON QUEUE [MySenderQueue];

Once the service is installed, a message is fired this way:

-- Send a message to the receiver queue
DECLARE @MessageBody XML
SET @MessageBody = ''; 
DECLARE @Handle UNIQUEIDENTIFIER;
BEGIN DIALOG CONVERSATION @Handle
FROM SERVICE [MySenderService]
TO SERVICE 'MyReceiverQueue'
ON CONTRACT [MyContract]
WITH ENCRYPTION = OFF;
SEND ON CONVERSATION @Handle 
MESSAGE TYPE [MyMessageType](@MessageBody);

All message are stuck in the 'MyReceiverQueue'. If I execute manually 'spProcessMessage', messages are processed normally.

A few more details:
- *sys.transmission_queue* is empty
- *sys.conversation_endpoints* state that both service are 'CONVERSING'
- Broker is enabled and DB is configured as SQL 2005 compatible

Any ideas why those messages aren't processed automatically?

Thanks a lot for your time.

--

OK, after playing around for a bit I got ssbdiagnose working. As Remus commentented, it is located here: C:\Program Files\Microsoft SQL Server\100\Tools\Binn. I made it work locally this way:

ssbdiagnose -E -S "JDECUPER\SQLEXPRESS" -d mydb CONFIGURATION FROM SERVICE MySenderService TO SERVICE MyReceiverService ON CONTRACT MyContract

Then, I tried it on our production server:

ssbdiagnose -XML -U loginID -P pwd -S "machine's IP" -d mydb CONFIGURATION FROM SERVICE MySenderService TO SERVICE MyReceiverService ON CONTRACT MyContract > C:\testBrokerService.xml

First error detected:

Service Broker GUID is identical to that of database Pandilla on server 200.57.141.193

Another database on the server had an identical GUID for the service broker. I only had to regenerate the GUID:

ALTER DATABASE [myotherdb] SET NEW_BROKER;

The second error had to do with the user's permissions to execute the stored procedure:

The EXECUTE AS for the user dbo specified for activation on queue dbo.AlbumGanadoresQueue cannot be impersonated due to an exception

After upgrading those permissions, everything started to work correctly.

Many thanks to Remus for me pointing into the right direction. I was a bit long but I hope the details will help other developers.

Answer 1

First thing read up the Troubleshooting Dialogs guide on my site and follow the step by step until you find the possible issue.
Second, if you have access to a SQL 2008 deployment, run the ssbdiagnose tool from it. Although is part of SQL 2008, is perfectly capable of diagnosing SQL 2005 as well. This is the preferred method, again, if you have access to a SQL 2k8 deployment.