How can I debug a stored procedure running from a Service Broker queue in SQL Server 2008?

Question

I have a table with a complex trigger that eventually calls the Service Broker to run a stored procedure with a parameter that is a table (custom type).

I can "step into" the INSERT and the trigger, but I can't step into the service broker stored procedure, as it runs async as far as I understand.

Is there any way to tap into the stored procedure that the Service Broker runs and debug it?

Thanks.

Answer 1

To debug it (using the management studio debugger) you could alter the queue to set activation off.

ALTER QUEUE queue_name 
WITH ACTIVATION (STATUS = OFF); 

run the insert statement then run & debug the activation procedure manually just like you would with any stored procedure. This way there will be something in the queue while you debug. Then when finished use STATUS = ON to restore activation.