How do I change the destination service name of a queue / service

Question

I have a SQL Server 2005 Service Broker queue "ProductChangeMessages" and a Service Broker service "ProductChangeNotifications". The pair are the backing to an SqlDependency I'm trying to get working but the OnChanged of the dependency doesn't fire. When the table being watched changes the subscription (visible through the results of select * from sys.dm_qn_subscriptions) is removed.

There are messages on the queue shown by SELECT * FROM sys.transmission_queue which have the queue name as their to_service_name and in the transmission_status they have

The target service name could not be found. Ensure that the service name is specified correctly and/or the routing information has been supplied.

So it appears that whatever is generating the message is trying to use the queue name rather than the service name as the to_service_name.

How can I change this so that it is using the right service name?

Answer 1

You cannot. You need to subscribe with the proper service name from your dependency infrastructure to begin with.

To clean up the existing messages, you can use the big nuke option: alter database <dbname> set new_broker with rollback immediate; which will wipe out every single existing dialog/message, but will leave in place all services/queues. The fine grained option is to terminate the bad dialogs one by one via end dialog <handle> with cleanup.