Can't receive Message from Queue (Async Trigger)

Hi Folks,

I've found a pretty good code example on http://www.dotnetfun.com for a Asynchronous Trigger.

I've parsed through the Code, to understand how to wirte my own Async Trigger with a Service Broker, but the Code isn't working! It seems that the stored procedure don't receive the messages in the queue, but the queue get's filled.

MessageType

CREATE MESSAGE TYPE myMsgXML
VALIDATION = WELL_FORMED_XML;

Contract

CREATE CONTRACT myContractANY
(myMsgXML SENT BY ANY)

Queue

CREATE QUEUE myQueue
WITH STATUS = ON,
RETENTION = ON,
ACTIVATION
(
STATUS = ON,
PROCEDURE_NAME = sp_myServiceProgram,
MAX_QUEUE_READERS = 5,
EXECUTE AS SELF
)

Service

CREATE SERVICE myService ON QUEUE myQueue (myContractANY)

Procedure (greped from http://www.dotnetfun.com/)

CREATE PROC sp_myServiceProgram
AS
-- This procedure will get triggered automatically
-- when a message arrives at the
-- Let's retrieve any messages sent to us here:
DECLARE @.XML XML,
@.MessageBody VARBINARY(MAX),
@.MessageTypeName SYSNAME,
@.ID INT,
@.COL2 VARCHAR(MAX);
DECLARE @.Queue TABLE (
MessageBody VARBINARY(MAX),
MessageTypeName SYSNAME);
WHILE (1 = 1)
BEGIN
WAITFOR (
RECEIVE message_body, message_type_name
FROM myQueue INTO @.Queue
), TIMEOUT 5000;
-- If no messages exist, then break out of the loop:
IF NOT EXISTS(SELECT * FROM @.Queue) BREAK;
DECLARE c_Test CURSOR FAST_FORWARD
FOR SELECT * FROM @.Queue;
OPEN c_Test;
FETCH NEXT FROM c_Test
INTO @.MessageBody, @.MessageTypeName;
WHILE @.@.FETCH_STATUS = 0
BEGIN
-- Let's only deal with messages of Message Type
-- myMsgXML:
IF @.MessageTypeName = 'myMsgXML'
BEGIN
SET @.XML = CAST(@.MessageBody AS XML);
-- Now let's save the XML records into the
-- historical table:
INSERT INTO tblDotNetFunTriggerTestHistory
SELECT tbl.rows.value('@.ID', 'INT') AS ID,
tbl.rows.value('@.COL2', 'VARCHAR(MAX)') AS COL2,
GETDATE() AS UPDATED
FROM @.XML.nodes('/inserted') tbl(rows);
END
FETCH NEXT FROM c_Test
INTO @.MessageBody, @.MessageTypeName;
END
CLOSE c_Test;
DEALLOCATE c_Test;
-- Purge the temporary in-proc table:
DELETE FROM @.Queue;
END

Send Message in a Update Trigger

SELECT @.XML = (SELECT * FROM inserted FOR XML AUTO);
-- Send the XML records to the Service Broker queue:
DECLARE @.DialogHandle UNIQUEIDENTIFIER,
@.ConversationID UNIQUEIDENTIFIER;
/*
The target Service Broker service is the same
service as the initiating service; however, you
can set up this type of trigger to send messages
to a remote server or another database.
*/
BEGIN DIALOG CONVERSATION @.DialogHandle
FROM SERVICE myService
TO SERVICE 'myService'
ON CONTRACT myContractANY;
SEND ON CONVERSATION @.DialogHandle
MESSAGE TYPE myMsgXML
(@.XML);
-- Let's detect an error state for this dialog
-- and rollback the entire transaction if one is
-- detected:
IF EXISTS(SELECT * FROM sys.conversation_endpoints
WHERE conversation_handle = @.DialogHandle
AND state = 'ER')
RAISERROR('Dialog in error state.', 18, 127);
ELSE
BEGIN

--I want to list the queue after the trigger so I disabled

--END CONVERSATION @.DialogHandle;
COMMIT TRAN;
END

The Problem is, that the Procedure doesn't even get started! So I tried to receive the Queues manually

WAITFOR (
RECEIVE message_body, message_type_name
FROM myQueue INTO @.Queue
), TIMEOUT 5000;

and I run always into the timeout and get nothing back. A Select * FROM myQueue gives me some results back. Why I can't recevie?

Would be grateful for help, or at least a good tutorial, I haven't found one yet....

thx and greez

Karsten

Since you have retention enabled on the queue, when you do select * from myQueue, you are probably seeing the sent messages instead of the delivered messages. Look at the 'status' column on the queue in the above query.

If the messages are not being delivered, it is likely that they are pending in the transmission queue:
select * from sys.transmission_queue

The transmission status column of that view should tell you why the messages could not be delivered. If it is related to security, you probably need to create a database master key in the database. You can do that as follows:
create master key encryption by password='YourP@.$$word';