Cannot drop SqlQueryNotificationService queue

Question

While trying to drop the SQL service broker object, I come accross this error

The queue 'SqlDependencyStarter.SqlQueryNotificationService-e1304de0-fb30-4009-b3fb-478c34acc40a' cannot be dropped because it is bound to one or more service.

But before dropping the queue i am dropping all of the servies bound to it.

SET @sql = 'ALTER QUEUE [SqlDependencyStarter].[' + @service_queue_name + '] WITH STATUS = OFF, RETENTION = OFF, ACTIVATION (STATUS = ON, PROCEDURE_NAME = ' + @activation_procedure + ', MAX_QUEUE_READERS = 1, EXECUTE AS OWNER)';
 EXEC sp_executesql @sql
SET @sql = 'DROP SERVICE [' + @service_name + ']';
 EXEC sp_executesql @sql

Then i am trying to drop the queue

SET @sql = 'DROP QUEUE [SqlDependencyStarter].[' + @service_queue_name + ']';
EXEC sp_executesql @sql

Answer 1

You need to drop the event notification as well:

set @query = N'DROP EVENT NOTIFICATION [' + @eventName + N'] ON SERVER';

exec sp_executesql @query

Answer 2

You need to first drop the service, then the queue.