none
Trying to follow a SQL Service Broker Step By Step configuration, what am I missing? RRS feed

  • Question

  • I've never used SSB before and is looking to implement this due to an issue we recently had with long running queries executing from a trigger.

    I'm following the guide below. Looks OK when I run each code and I'm kind of following what is happening.

    https://sqlperformance.com/2014/03/sql-performance/configuring-service-broker

    Anyway, I wanted to test an email notification. So on the [ProcessingQueueActivation] SPROC, I inserted the lines below (send mail part):

    IF @message_type_name = N'AsyncRequest' BEGIN -- Handle complex long processing here -- For demonstration we'll pull the account number and send a reply back only DECLARE @AccountNumber INT = @message_body.value('(AsyncRequest/AccountNumber)[1]', 'INT');

    -- ADDED PART START

    EXEC msdb.dbo.sp_send_dbmail @profile_name = 'MYPRODILE', @recipients = 'myname@mycompany.org', @subject = 'BROKER TEST', @body = @AccountNumber; -- ADDED PART END -- Build reply message and send back DECLARE @reply_message_body XML = N' ' + CAST(@AccountNumber AS NVARCHAR(11)) + ' ';

    I tested it and I got the email when I run the script below.

    EXECUTE dbo.SendBrokerMessage
      @FromService = N'RequestService',
      @ToService   = N'ProcessingService',
      @Contract    = N'AsyncContract',
      @MessageType = N'AsyncRequest',
      @MessageBody = N'<AsyncRequest><AccountNumber>04</AccountNumber></AsyncRequest>';
     
     EXECUTE dbo.ProcessingQueueActivation;
     EXECUTE dbo.RequestQueueActivation;
    

    However, when I turn on the AUTOMATE PROCESS (below), I don't get any email at all. I don't see any records on the [ProcessingQueue] and [RequestQueue] so I'm assuming it got processed.

    -- Alter the processing queue to specify internal activation
    ALTER QUEUE ProcessingQueue
        WITH ACTIVATION
        ( 
          STATUS = ON,
          PROCEDURE_NAME = dbo.ProcessingQueueActivation,
          MAX_QUEUE_READERS = 10,
          EXECUTE AS SELF
        );
    GO
     
    -- Alter the request queue to specify internal activation
    ALTER QUEUE RequestQueue
        WITH ACTIVATION
        ( 
          STATUS = ON,
          PROCEDURE_NAME = dbo.RequestQueueActivation,
          MAX_QUEUE_READERS = 10,
          EXECUTE AS SELF
        );
    GO

    Any ideas?

    TIA

    Tuesday, December 24, 2019 5:20 PM

All replies

  • Have you looked in the SQL Server errorlog? This is where any output from the activation procedure turns up.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    Tuesday, December 24, 2019 6:22 PM
  • Thank you that helped me a lot!

    The activated proc '[dbo].[ProcessingQueueActivation]' running on queue 'AsyncProcessingDemo.dbo.ProcessingQueue' output the following:

    'The EXECUTE permission was denied on the object 'sp_send_dbmail', database 'msdb', schema 'dbo'.'

    It looks like I need to add a user to the DatabaseMailUserRole . 

    So trying to figure out WHO this user I need to add. It works just fine when I run it by itself. Only when it is running on the the broker service that it does not execute and I get the error.

    Tuesday, December 24, 2019 7:35 PM
  • Ok, so this is not going to be that simple. Because you are impersonating a database user, you are sandboxed into the current database, and cannot access things outside of the database. So that user cannot easily reach into msdb.

    As it happens I have written quite a bit about this, if not about this exact problem. But you will find that there is quite much of text to get through. The article is called Packaging Permissions in Stored Procedure is on http://www.sommarskog.se/grantperm.html. But that article is only the basics. To learn how to use this with Service Broker and msdb, you also need to read some chapters in the appendix. And then you will finally have to fill in the gaps to get it working with your case.

    You have my understanding and reconsider and aim for a different solution.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    Tuesday, December 24, 2019 7:53 PM