Clever Geek Handbook

Disable Queue Application Broker event

I am trying to use the option BROKER_QUEUE_DISABLED

in EVENT NOTIFICATION, it looks like it does not generate a Broker Queue disabled event. Can someone please validate the script I created? (see below)

Script:

CREATE QUEUE NotifyQueue

GO

CREATE

SERVICE NotifyService

ON

QUEUE NotifyQueue

[http://schemas.microsoft.com/SQL/Notifications/PostEventNotification]);


GO

CREATE ROUTE NotifyRoute
 WITH SERVICE_NAME = 'NotifyService',
 ADDRESS = 'LOCAL';
 GO

CREATE

EVENT NOTIFICATION [CHS_QueueDisabledNotif] 

       ON QUEUE [CHS_Change_Queue] WITH FAN_IN 

       FOR BROKER_QUEUE_DISABLED

       TO SERVICE 'NotifyService', 'current database'



--Testing

--Try Disabling Application Queue

ALTER QUEUE dbo.CHS_Change_Queue WITH STATUS = OFF

SELECT * FROM NotifyQueue
+2


source to share


1 answer


I found the answer to my question :)



The SQL engine will not generate a disconnect event if we manually disconnect the queue. It will only be generated if any error occurs during the activation process. I added custom error in proc activation, something like SELECT 1/0

then SQL can fire Disable event.

+1


source






More articles:


All Articles