Service Broker Queue not calling Activation Procedure
More fun with the Service Broker today on SQL Server 2005. I had got everything working on a test database and wanted to move the changes into the live database, both on the same server. I scripted all the stuff up and then realise service broker wasn't running. I found this because when I selected from sys.transmission_queue (which should be empty if the message is delivered to a queue), the exception column told me this. I tried to alter database to enable service broker but it wouldn't work with users logged in and I didn't want to boot all few hundred of them off! I wrote a SQL server job that ran the enable code at 5:50am with the following code:
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:
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:
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.
CREATE PROC [dbo].[procEnableBroker]
AS
BEGIN TRY
ALTER DATABASE MyDb SET ENABLE_BROKER WITH ROLLBACK IMMEDIATE ;
END TRY
BEGIN CATCH
ALTER DATABASE MyDb SET NEW_BROKER WITH ROLLBACK IMMEDIATE ;
END CATCH
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';
EXEC [dbo].[procQueueActiviationProc]
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.