CREATE PROC [dbo].[procEnableBroker]
ALTER DATABASE MyDb SET ENABLE_BROKER WITH ROLLBACK IMMEDIATE ;
ALTER DATABASE MyDb SET NEW_BROKER WITH ROLLBACK IMMEDIATE ;
I needed the second bit in the catch which was some weird thing about needing a new service broker ID? The rollback boots out any uncommitted transactions and does the alter.
This all seemed OK so the messages were now being put on the queue but the queue was not calling the activation proc. If I ran the proc manually, it worked OK so I ended up thinking it was something to do with permissions. I followed some MS advice which as usual is buried amongst a lot of smoke and mirrors and did the following:
EXECUTE AS USER='dbo';
in order to match the user that it would normally run as (EXECUTE AS OWNER on the queue) and then I got the old, "the remote server could not be accessed because the current security context is not trusted". All I had to do then was to call:
ALTER DATABASE MyDb SET TRUSTWORTHY ON
And then after a little while and poking the queue again, it all came into life. I quite like the idea of Asynchronous calls for some things, this is a proc that updates a load of readonly fields and which takes up to about 10 seconds, something that we needn't wait for.