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

Question

Post reply

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

Charlottecb

Hall of Fame

Points: 3932
More actions
August 25, 2022 at 11:12 am

#4080441

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

October 2, 2024 at 8:15 pm

This was removed by the editor as SPAM

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

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

Charlottecb Hall of Fame Points: 3932 More actions · Answer 1

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 2 years, 1 month ago by Charlottecb.

Charlottecb Hall of Fame Points: 3932 More actions · Answer 2

Solved...

Needed

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

GO