SQL Server 2005 Asnyc stored procedure

Question

Hi all,

I am currently having problems calling a stored procedure async from within a insert-update-trigger. For this I m using the service broker.

--message type
CREATE MESSAGE TYPE [TheMessage] VALIDATION = NONE

--contract
CREATE CONTRACT [TheContract] ([TheMessage] SENT BY ANY);

--queue
CREATE QUEUE [TheQueue] WITH ACTIVATION
(STATUS = ON, MAX_QUEUE_READERS = 1,
PROCEDURE_NAME = TheStoreProcedure,
EXECUTE AS OWNER);

--service
CREATE SERVICE [TheService] ON QUEUE [TheQueue] ([TheContract]); 

Within the trigger:

DECLARE @Handle UNIQUEIDENTIFIER;

BEGIN DIALOG CONVERSATION @Handle
FROM SERVICE [TheService]
TO SERVICE 'TheService'
ON CONTRACT [TheContract]
WITH ENCRYPTION = OFF;

SEND ON CONVERSATION @Handle 
MESSAGE TYPE [TheMessage](N'some data');

Within the stored procedure:

DECLARE @Handle UNIQUEIDENTIFIER;
DECLARE @MessageType SYSNAME;

RECEIVE TOP (1)
@Handle = conversation_handle,
@MessageType = message_type_name
FROM [TheQueue];

IF(@Handle IS NOT NULL)

BEGIN

-- some statements

END

This setup doesn't seem to work. The trigger does not throw any errors so I assume the message is queued. But the receive within the stored doesn't seem to work. None of my statements are being executed.

Answer 1

• Check if the message isn't retained in sys.transmission_queue. Transmission_status column should explain why the message isn't delivered.
• Check if the message is in the queue: SELECT ... FROM [TheQueue]. If the message is there and the procedure didn't activate check the queue's is_receive_enabled status in sys.service_queues. If the queue is disabled, you probably rolled back 5 receives in a row during testing and triggered the poison message mechanism.
• If the queue is enabled, check the queue monitors status, see Understanding Queue Monitors.
• If the message is neither in the queue nor in transmission queue, it must been consumed by the activated procedure. Verify your ERRORLOG for any error output. Disable activation, send a message again, then run the procedure manually from an SSMS query window see if you get any error message.
• Mae sure your activated procedure does not fall into the traps of the EXECUTE AS context. See Why does feature … not work under activation? and Call a procedure in another database from an activated procedure

Answer 2

Ok, thanks for your answers, I fixed it.

The problem was that the service broker was disabled..

USE AdventureWorks
GO

ALTER DATABASE AdventureWorks SET SINGLE_USER WITH ROLLBACK IMMEDIATE
ALTER DATABASE AdventureWorks SET ENABLE_BROKER
ALTER DATABASE AdventureWorks SET MULTI_USER
GO