Search code examples
sql-serversql-server-2012service-brokerfire-and-forget

inconsistent activation settings

I have a service that uses the infamous fire and forget pattern (don't judge me, I didn't create it) that was working fine on SQL Server 2005. I recently upgraded to 2012 and now ssbdiagnose is complaining about inconsistent activation settings:

The activation settings for queue dbo.ScanSendQueue are inconsistent: Activation is configured but disabled
The activation settings for queue dbo.ScanSendQueue are inconsistent: Activation is configured with 0 max_queue_readers
The activation settings for queue dbo.ScanSendQueue are inconsistent: Activation is configured but no procedure is specified

I tried to figure out how to solve this but couldn't figure it out short of adding a stored procedure to the "Send" queue, but I can't see why that's necessary.

The basic setup is:

  1. Insert to "Scan in" table

  2. Trigger calls "Send" service like so:

    BEGIN DIALOG CONVERSATION @SBDialog FROM SERVICE ScanSendService TO SERVICE 'ScanReceiveService', 'CURRENT DATABASE' ON CONTRACT ScanContract WITH ENCRYPTION = OFF

  3. "Receive" service calls a stored procedure which then does irrelevant stuff. Queue detail below:

    ALTER QUEUE [dbo].[ScanReceiveQueue] WITH STATUS = ON , RETENTION = OFF , ACTIVATION ( STATUS = ON , PROCEDURE_NAME = [dbo].[usp_Process_ScanReceiveQueue] , MAX_QUEUE_READERS = 1 , EXECUTE AS OWNER ), POISON_MESSAGE_HANDLING (STATUS = OFF)

Messages make it into "Send" queue just fine, but not the "Receive" queue. Why is ssbdiagnose complaining about this, and how can I fix it?

EDIT: More info, since no one seems to have any ideas :(

I took a look at Microsoft.SqlServer.ServiceBroker.Diagnostics.dll and it seems that all these checks are being run on every queue regardless of whether "Activation is configured" or not, but I don't even know how to not configure activation....

Source if anyone is interested: https://gist.github.com/Mansfield7/5766457#file-gistfile1-cs-L12

EDIT 2: I turned on activation for that queue and specified a stored procedure that did nothing. The ssbdiagnose errors went away, but the queue is still broken (receive queue still empty).

Solution

  • After adding activation properly for that queue, the ssbdiagnose errors went away, but the services still weren't working.

    I attached the SQL Server profiler and saw that the database was not in trusted mode, so I ran the following command, and it started working.

    ALTER DATABASE [DBNAME] SET TRUSTWORTHY ON;

    I'd still be curious to know more about the ssbdiagnose warnings about activation, but for now my problem is solved.