Hi
I'm having problems with activation. I have a CLR stored procedure that runs fine when run directly (and consumes messages from the queue). When I try to enable activation nothing happens. I've tried different execute as options and looked in the sql server logs and I don't see anything indicating why.
I've checked sys.service_queues and all options look correct including activation options. The sys.dm_broker_activated_tasks view is empty.
Without any errors I'm having difficulty tracking down the problem. I've seen references to service broker shutting down the procedure if it doesn't consume any messages but I haven't seen where this is indicated.
Any help appreciated.
Duncan Godwin
Service Broker Activation
JedrekMedrek
1. Are your messages actually being delivered to the target queue. Verify this by SELECTing from the target queue.
2. Is activation enabled for the target queue. Verify this by looking at sys.service_queues. While you are looking at this view, also check who the 'execute as' user for activation is.
3. If you have access to ERRORLOG, check the ERRORLOG for unhandled exceptions thrown by your CLR stored proc. It could well be that the stored proc doesn't raise any unhandled exceptions when you invoke it in management studio, however, due to security issues or something else, it doesn't work in the background.
Warren Jans
When running activated, the procedure is under an EXECUTE AS context and therefore it has restricted access to server level views (like sys.dm_...). See the 'Extending Database Impersonation by Using EXECUTE AS' topic in BOL for all the details.
Also see this post at http://blogs.msdn.com/remusrusanu/archive/2006/01/12/512085.aspx
To fix the issue you have to either properly sign the procedure or mark the database as trustworthy.
HTH,
~ Remus
Andrei Kuzmenkov