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

with ` EXEC sp_executesql @sql` there is no need for seperate `go` — Deepanshu Kalra, Sep 14 '17 at 06:18

score 0 · Answer 1 · answered Sep 14 '17 at 08:36

0

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

answered Sep 14 '17 at 08:36

Remus Rusanu

288,378
40
442
569

that is what i have done. First i deactivate the queue, Drop its services and then drop the queue – Deepanshu Kalra Sep 14 '17 at 08:46

score 0 · Answer 2 · answered Sep 14 '17 at 13:14

0

You need to drop the event notification as well:

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

exec sp_executesql @query

answered Sep 14 '17 at 13:14

Richard Cupples

186
1
6

there are no Event notifications to drop at any time. we have not created any event notifications. – Deepanshu Kalra Sep 15 '17 at 06:22

Cannot drop SqlQueryNotificationService queue

2 Answers2