sql server - Remove an orphaned sys.service_queue -


i have orphaned entry in sys.service_queues table.

when do

select * sys.service_queues name '%sqlquerynotificationservice-%' select * sys.objects name '%sqlquerynotificationservice-%' 

i see matching results:

sqlquerynotificationservice-1d50f684-cb16-4feb-a2e1-d53872cd1e23    627637429   null    6   0   sq  service_queue   2013-07-10 14:11:14.520 2013-07-10 14:11:14.520 0   0   0   1   [cqs_web].[sqlquerynotificationstoredprocedure-1d50f684-cb16-4feb-a2e1-d53872cd1e23]    -2  1   1   1   0   1 sqlquerynotificationservice-1d50f684-cb16-4feb-a2e1-d53872cd1e23    627637429   null    6   0   sq  service_queue   2013-07-10 14:11:14.520 2013-07-10 14:11:14.520 0   0   0 

so i'm trying remove orphans with:

declare @objname nvarchar(max)  declare @msg nvarchar(max)  select @objname = name sys.objects name 'sqlquerynotificationservice%'  if (@objname 'sqlquerynotificationservice-%')  begin    print 'found orphan ' + @objname + ', checking conversations'   declare @convhandle uniqueidentifier    declare conv cursor    select conversation_handle sys.conversation_endpoints (nolock) far_service = @objname;    open conv;      fetch next conv @convhandle;      while (@@fetch_status = 0) begin        print n'    closing conversation ' + cast(@convhandle nvarchar)       end conversation @convhandle cleanup;        fetch next conv @convhandle;      end    close conv;    deallocate conv;    declare @count int;   select @count=count(*) sys.services name = @objname   if (@count > 0) begin     print '    dropping service ' + @objname     declare @query nvarchar(200);     set @query = n'drop service ' + @objname;     exec sp_executesql @query   end end go  kill query notification subscription  go 

but that's not clearing them out. how can clear out orphans?


Comments

Popular posts from this blog

javascript - Count length of each class -

What design pattern is this code in Javascript? -

hadoop - Restrict secondarynamenode to be installed and run on any other node in the cluster -