Event notification on disabled queue: the conversation must end?

Question

 Hello,

I have configured an Event Notification for BROKER_QUEUE_DISABLED.

 

I created a Queue different from the one the Receive my normal messages and on this queue I define an activation.

 

The Activated SP takes the [EventNotification] message and execute a

RAISEERROR('Message',0,1) WITH LOG.

 

The error is properly logged in the SQL server event log but I gave a look the Initiator and target sys.conversation_endpoints catalog view and I have see the the conversations are in the status CONVERSING or SI ...

 Do I have to call the END CONVERSATION after the commit or these statuses are caused by some mistakes on the message process workflow?

 

Thank you

Marina B.

Answer 1

The conversations used by the event notifications infrastructure should not be ended. They will be ended by initiator when the event notification object is dropped (DROP EVENT NOTIFICATION ...). You procedure should react to the EndDialog message by ending it's side. If you end the conversations prematurely from the procedure side you are in effect disabling the event notification (it will have to be dropped and created again).

This applies for all event notifications, it is not specific to BROKER_QUEUE_DISABLED.

Answer 2

I'm doing something similar with a server event notification (AUDIT_LOGOUT), except in my case I want the activation stored procedure to drop the event notification once I get the specific event that I'm looking for.  If I try to call DROP EVENT NOTIFICATION inside the procedure, I get an SQL Error logged that it does not exist or the user does not have permissions (even if it was created and owned by the SA and being executed with that user's permissions).  Whatsmore, it seems that server event notifications names are not written into the sys.event_notifications table, even in the master or msdb databases.  Its gotten be defined somewhere!  Last, why does the database user trying to create the event notification need to be a user in the MSDB database even though they've been granted the CREATE DDL EVENT NOTIFICATION permission in the server?

Your advice about ending the conversation prematurely on the procedure side is helpful and does achieve my goal, however it also causes SQL Server to error log "Event notification 'Notification_Audit_LogOut' in database 'master' dropped due to send time service broker errors. Check to ensure the conversation handle, service broker contract, and service specified in the event notification are active."  Is this really the only way to do what I want to do?  (I note that a stored procedure can in fact drop itself while running, but for some reason a service broker queue's activation stored procedure can't drop a server event notification during its activation).

Answer 3

Chris,

If I understand correctly, you're trying to drop event notification created by SA from an activated procedure and running into permission problem. Please keep in mind that activated procedures run under special activation context that may have less permissions than you expect it to. Please refer to this article for more information.