Service broker works on one DB but not another on the same server

  • Hi all,

    So, I'm learning about Service Broker. I've got 2 different DBs on my server and have switched on Service Broker on each by going to each DBs Properties and have turned it on then run a query to verify it is on.

    When I run the script, the messages, queues etc on each DB, one DB works correctly and I can see the messages in the queue and the non-working DB other has nothing going into the queue. I'm thinking this must be a config issue somewhere but no idea what.

    My script (attached) is one I created following on-line samples - the working one fires the SP at the end 3 times which puts rows into a table and the queue which the last query picks up and displays. The non-working one does the same - 3 rows go into the table, but the last query shows an empty queue. Script runs on both DBs without error...

    Any ideas or suggestions?

    Many thanks

  • Code here...

    CREATE MESSAGE TYPE ReceivedOrders
    AUTHORIZATION dbo
    VALIDATION = NONE
    GO

    CREATE CONTRACT postmessages (ReceivedOrders SENT BY ANY)
    GO

    CREATE QUEUE OrderQueue WITH STATUS = ON, RETENTION = OFF
    GO

    CREATE SERVICE OrderService
    AUTHORIZATION dbo
    ON QUEUE OrderQueue (postmessages)
    GO

    CREATE TABLE [dbo].[Orders](
    [OrderID] [int] NOT NULL,
    [OrderDate] [date] NULL,
    [ProductCode] [varchar](50) NOT NULL,
    [Quantity] [numeric](9, 2) NULL,
    [UnitPrice] [numeric](9, 2) NULL,
    CONSTRAINT [PK__Orders] PRIMARY KEY CLUSTERED
    (
    [OrderID] ASC,
    [ProductCode] ASC
    )
    ON [PRIMARY]
    ) ON [PRIMARY]
    GO

    CREATE PROCEDURE usp_CreateOrders (
    @OrderID INT
    ,@ProductCode VARCHAR(50)
    ,@Quantity NUMERIC(9, 2)
    ,@UnitPrice NUMERIC(9, 2)
    )
    AS
    BEGIN
    DECLARE @OrderDate AS SMALLDATETIME
    SET @OrderDate = GETDATE()
    DECLARE @XMLMessage XML

    CREATE TABLE #Message (
    OrderID INT PRIMARY KEY
    ,OrderDate DATE
    ,ProductCode VARCHAR(50)
    ,Quantity NUMERIC(9, 2)
    ,UnitPrice NUMERIC(9, 2)
    )

    INSERT INTO #Message (
    OrderID
    ,OrderDate
    ,ProductCode
    ,Quantity
    ,UnitPrice
    )
    VALUES (
    @OrderID
    ,@OrderDate
    ,@ProductCode
    ,@Quantity
    ,@UnitPrice
    )

    --Insert to Orders Table
    INSERT INTO Orders (
    OrderID
    ,OrderDate
    ,ProductCode
    ,Quantity
    ,UnitPrice
    )
    VALUES (
    @OrderID
    ,@OrderDate
    ,@ProductCode
    ,@Quantity
    ,@UnitPrice
    )
    --Creating the XML Message
    SELECT @XMLMessage = (
    SELECT *
    FROM #Message
    FOR XML PATH('Order')
    ,TYPE
    );

    DECLARE @Handle UNIQUEIDENTIFIER;
    --Sending the Message to the Queue
    BEGIN
    DIALOG CONVERSATION @Handle
    FROM SERVICE OrderService TO SERVICE 'OrderService' ON CONTRACT [postmessages]
    WITH ENCRYPTION = OFF;

    SEND ON CONVERSATION @Handle MESSAGE TYPE ReceivedOrders(@XMLMessage);
    END
    GO


    usp_CreateOrders 202003,'PD0001',1,10.50
    GO
    usp_CreateOrders 202003,'PD0002',2,100.75
    GO
    usp_CreateOrders 202003,'PD0010',1.5,20.00
    GO

    SELECT service_name,
    priority,
    queuing_order,
    service_contract_name,
    message_type_name,
    validation,
    message_body,
    message_enqueue_time,
    status
    FROM dbo.OrderQueue
    GO

    • This reply was modified 1 month, 1 week ago by  Charlottecb.
  • Solved...

    Needed

    ALTER AUTHORIZATION ON DATABASE::[DB_Test1] TO [sa]

    GO

Viewing 3 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply