none
Cannot get the Service Broker work

    Question

  • Some months ago I was playing with Service Broker and everything was ok. But now I just can't get it work. I opened a sample "helloworld" project and still cannot get a message in a target queue. No errors, just empty queues. We have two SQL servers sept CTP on different computers and both give the same issue. It looks like it should have a very simple solution that I cannot come up with.

    The following script initializes service broker and sends a message, then selects both target and initiator queues. On my environment it just returns two empty selects.

    --Initializing service broker objects

    use master

    GO

     

    SET NOCOUNT ON

    GO

     

    IF NOT EXISTS

      (SELECT * FROM sys.databases

       WHERE name = 'AdventureWorks'

       AND is_broker_enabled = 1)

    BEGIN

      ALTER DATABASE AdventureWorks SET ENABLE_BROKER

    END

    GO

     

    USE AdventureWorks

    GO

     

    IF EXISTS (SELECT *

               FROM sys.services

               WHERE name = 'InitiatorService')

    BEGIN

        DROP SERVICE InitiatorService

    END

    GO

     

    IF EXISTS (SELECT *

               FROM sys.services

               WHERE name = 'TargetService')

    BEGIN

        DROP SERVICE TargetService

    END

    GO

     

    IF EXISTS (SELECT *

               FROM sys.service_contracts

               WHERE name = 'HelloWorldContract')

    BEGIN

        DROP CONTRACT HelloWorldContract

    END

    GO

     

    IF EXISTS (SELECT *

               FROM sys.service_message_types

               WHERE name = 'HelloWorldMessage')

    BEGIN

        DROP MESSAGE TYPE HelloWorldMessage

    END

    GO

     

    IF OBJECT_ID('[dbo].[InitiatorQueue]') IS NOT NULL AND

       EXISTS(SELECT *

              FROM sys.objects

              WHERE object_id = OBJECT_ID('[dbo].[InitiatorQueue]')

                AND type = 'SQ')

    BEGIN

        DROP QUEUE [dbo].[InitiatorQueue]

    END

    GO

     

    IF OBJECT_ID('[dbo].[TargetQueue]') IS NOT NULL AND

       EXISTS(SELECT *

              FROM sys.objects

              WHERE object_id = OBJECT_ID('[dbo].[TargetQueue]')

                AND type = 'SQ')

    BEGIN

        DROP QUEUE [dbo].[TargetQueue]

    END

    GO

     

    CREATE MESSAGE TYPE HelloWorldMessage

        VALIDATION = WELL_FORMED_XML

    GO

     

    CREATE CONTRACT HelloWorldContract

        ( HelloWorldMessage SENT BY INITIATOR)

    GO

     

    CREATE QUEUE [dbo].[TargetQueue]

    GO

     

    CREATE QUEUE [dbo].[InitiatorQueue]

    GO

     

    CREATE SERVICE InitiatorService

        ON QUEUE [dbo].[InitiatorQueue]

    GO

     

    CREATE SERVICE TargetService

        ON QUEUE [dbo].[TargetQueue]

        (HelloWorldContract)

    GO

     

    -- Starting conversation

     

    USE AdventureWorks

    GO

     

    --BEGIN TRANSACTION

    GO

    DECLARE @message XML

    SET @message = N'<message>Hello, World!</message>'

     

    -- Declare a variable to hold the conversation

    -- handle.

     

    DECLARE @conversationHandle UNIQUEIDENTIFIER

     

    -- Begin the dialog.

     

    BEGIN DIALOG CONVERSATION @conversationHandle

        FROM SERVICE InitiatorService

        TO SERVICE 'TargetService'

        ON CONTRACT HelloWorldContract;

     

    -- Send the message on the dialog.

     

    SEND ON CONVERSATION @conversationHandle

      MESSAGE TYPE HelloWorldMessage

      (@message)

     

    print @conversationHandle

     

    GO

     

    --COMMIT TRANSACTION

     

    GO

     

    SELECT * FROM [dbo].[TargetQueue]

    SELECT * FROM [dbo].[InitiatorQueue]

     

    Monday, November 14, 2005 3:58 PM

Answers

  • Can you run this query in AdventureWorks: 

    SELECT * FROM sys.transmission_queue

    and look at the content of the transmission_status column? It should indicate the reason for which the message cannot be delivered.

    HTH,
    ~ Remus

    Monday, November 14, 2005 6:01 PM
    Moderator
  • As I thought it is a very simple problem Smile. I just included

    WITH ENCRYPTION = OFF

    to BEGIN DIALOG CONVERSATION statement and everything started working as expected (thanks Tito Jermaine for comment).
    Monday, November 14, 2005 6:06 PM

All replies

  • Can you run this query in AdventureWorks: 

    SELECT * FROM sys.transmission_queue

    and look at the content of the transmission_status column? It should indicate the reason for which the message cannot be delivered.

    HTH,
    ~ Remus

    Monday, November 14, 2005 6:01 PM
    Moderator
  • As I thought it is a very simple problem Smile. I just included

    WITH ENCRYPTION = OFF

    to BEGIN DIALOG CONVERSATION statement and everything started working as expected (thanks Tito Jermaine for comment).
    Monday, November 14, 2005 6:06 PM
  • The other way around it is to mark your target database as trustworthy: alter database db_name set trustworthy on, and create a master key in both databases: create master key encryption by password = 'some_pwd'

    Niels
    Tuesday, November 15, 2005 9:30 AM
  • I have "WITH ENCRYPTION = OFF" and it's still not working.  The status in the transmission queue reads:

    An exception occurred while enqueueing a message in the target queue. Error: 15517, State: 1. Cannot execute as the database principal because the principal "dbo" does not exist, this type of principal cannot be impersonated, or you do not have permission.

    Any idea?

    Thx

    Thursday, December 29, 2005 11:37 PM
  • The original Windows user that created the database is no longer available. Change the database ownership to a valid login, so that user 'dbo' can be impersonated:

    ALTER AUTHORIZATION ON DATABASE::[yourdatabase] TO [sa];

    HTH,
    ~ Remus

     

    Friday, December 30, 2005 1:42 AM
    Moderator
  • For those of you reading this and asking what statement the "WITH ENCRYPTION = OFF" clause applies to, it's the BEGIN DIALOG CONVERSATION statement.
    Wednesday, April 18, 2007 7:05 PM
  • The original Windows user that created the database is no longer available. Change the database ownership to a valid login, so that user 'dbo' can be impersonated:

    ALTER AUTHORIZATION ON DATABASE::[yourdatabase] TO [sa];

    HTH,
    ~ Remus

     


    This works
    • Proposed as answer by manzyron Wednesday, May 16, 2012 8:13 PM
    Monday, April 30, 2012 2:16 PM
  • Hi All,

    was running into the same problem because I attached the AdventureWorks database from CodePlex. Like Fran mentioned that the user that created the database doesn't exist and you have to change the authorization. 

    This is what fixed the issue in my case.

    Thanks

    channa

    Wednesday, May 16, 2012 8:16 PM