Wednesday 29 April 2009

Problems enabling service broker

If you restore a database with service broker enabled on the same sql server instance, it may happen that broker doesn't enables and when try to manually enable it you may get back the following error message:

 

The Service Broker in database "??????" cannot be enabled because there is already an enabled Service Broker with the same ID

 

This statement fixed the situation:

 

alter database ?????? set NEW_BROKER