Service broker Activation outputing "The message body may not be null", when switch off XML validation on the queue

Question

Running Service Broker on a 2014 MS SQL Server the switch from test to live is generating a new error:

The activated proc '[dbo].[usp_CalcPrice]' running on queue 'dbo.CalcPriceDataQueue' output the following: 'The message body may not be NULL. A zero-length UNICODE or binary string is allowed.'

The difference from test to Live version is that the message type used in [dbo].[usp_CalcPrice] no longer has VALIDATION = XML SCHEMA XXXXX but for performance is changes to VALIDATION = NONE.

I added a table to dump what the Stored Procedure [dbo].[usp_CalcPrice] RECEIVE from the queue, and it looks fine, done this way:

  WHILE (1 = 1) 
  BEGIN -- Receive the next available message from the queue 
    WAITFOR ( 
             RECEIVE TOP(1)     
             @message_body = CAST(message_body AS XML),     
             @dialog = conversation_handle
        FROM [dbo].[CalcPriceDataQueue]), TIMEOUT 50    
        IF (@@ROWCOUNT = 0 OR @message_body IS NULL) 
        BEGIN 
            break
        END 
        ELSE 
        BEGIN 
            SET @XmlBody = CAST(@message_body AS Nvarchar(max))
            INSERT INTO dbo.SB_Tester (XmlBody)
            VALUES(@XmlBody)
            ...

On a simple test the queue get 6-8k entries, on the test server they get processed in 'no time', with no errors, but on the live it takes 2 - 5 sec per entry and the windows Event Viewer gets an error per entry (The one above), in the dbo.SB_Tester i get a nice looking XML entry for each entry in the Queue both on test and live.

Additional info:

@@Version: Microsoft SQL Server 2014 - 12.0.2254.0 (X64) Jul 25 2014 18:52:51 Copyright (c) Microsoft Corporation Enterprise Edition (64-bit) on Windows NT 6.3 (Build 9600: )

They are maintained so the version is always the same.

Code that send to the queue:

DECLARE @MessageBody XML
SET @MessageBody = N'<Calc 
                        PartId="'           + @PartId +'"
                        CustomerId="'       + @CustId + '"
                        PriceRuleId="'      + @RuleId + '"
                        BaseSalesPrice="'   + @BaseSalesPrice + '"
                        CostPrice="'        + @CostPrice + '"
                        MinDB="'            + @MinDB + '"
                        IgnoreMinDB="'      + @IgnoreMinDB + '"
                        NoPriceRaising="'   + @NoPriceRaising + '"
                        Priority="'         + @Priority + '"
                        TargetPrice="'      + @TargetPrice + '"
                        ChangeType="'       + @ChangeType + '"
                        Change="'           + @Change + '"
                    />'

--PRINT CONVERT(Nvarchar(max), @MessageBody)

IF (@MessageBody IS NOT NULL)  
    BEGIN 
        DECLARE @Handle UNIQUEIDENTIFIER;   
        BEGIN DIALOG CONVERSATION @Handle
        FROM SERVICE [PriceCalcServiceSource]
        TO SERVICE   'PriceCalcServiceSink'
        ON CONTRACT [CalcPriceDataContract]
        WITH ENCRYPTION = OFF;   
        SEND ON CONVERSATION @Handle   
        MESSAGE TYPE [PriceData](@MessageBody);
    END

Queue and msg create:

CREATE MESSAGE TYPE [PriceData] VALIDATION = NONE
GO

CREATE CONTRACT [CalcPriceDataContract] ([PriceData] SENT BY INITIATOR)
GO

CREATE QUEUE [dbo].[CalcPriceDataQueue] WITH STATUS = ON ,
    RETENTION = OFF , 
    ACTIVATION (STATUS = ON , 
                PROCEDURE_NAME = [dbo].[usp_CalcPrice] , 
                MAX_QUEUE_READERS = 40 , 
                EXECUTE AS OWNER  ), 
    POISON_MESSAGE_HANDLING (STATUS = ON)  
    ON [PRIMARY] 
GO

Message Create on test server:

CREATE MESSAGE TYPE [PriceData]
VALIDATION = VALID_XML WITH SCHEMA COLLECTION dbo.PriceData

Anyone know the cause of this problem? Any help is much appreciated :)

/Grue

Answer 1

I'm a bit late for the party, but I had the same problem with Service Broker. When our mainframe software tried to insert the data into our SQL Server database, the insert got cancelled and the log file had this same message. The recommendation I received from the enterprise responsible for the mainframe was to modify the trigger on the target table, in order to check wether the inserted information existed before beginning the transaction. Here's what my trigger looks like now:

ALTER TRIGGER [dbo].[OnPedidoCompraInserted] ON [dbo].[PEDIDO_COMPRA] FOR INSERT
AS
BEGIN
    IF EXISTS (SELECT * FROM inserted) -- this is the verification he recommended me to add
    begin
        BEGIN TRANSACTION;
        DECLARE @ch UNIQUEIDENTIFIER
        DECLARE @messageBody NVARCHAR(MAX);
 
        BEGIN DIALOG CONVERSATION @ch
        FROM SERVICE [InitPedidoCompraService]
        TO SERVICE 'TargetPedidoCompraService'
        ON CONTRACT [http://ssb.csharp.at/PedidoCompraContract]
        WITH ENCRYPTION = OFF;
 
        -- Construct the request message
        SET @messageBody = (SELECT * from inserted FOR XML AUTO, ELEMENTS);
 
        -- Send the message to the TargetService
        ;SEND ON CONVERSATION @ch
        MESSAGE TYPE [http://ssb.csharp.at/RequestPedidoCompra] (@messageBody);
        COMMIT;
    END
end

I know I'm late, but I hope I can help somebody in the future.