How to fix Error 9704 on Windows Server 2025 / Windows 11

by

Troubleshooting Guide: SQL Server Error 9704 – XML Validation Failure During Service Broker Message Delivery

Why This Error Happens

Error 9704 occurs when SQL Server Service Broker attempts to deliver a message to a target service, but the message body does not conform to the XML schema defined for that message type. This typically happens due to malformed XML generated by the sending application, a mismatch between the message type’s validation setting (WELLFORMEDXML or VALID_XML) and the actual payload, or a corrupted/truncated message during transit. In some cases, encoding mismatches (such as UTF-8 vs UTF-16) between the sender and the XML schema collection can also trigger this validation failure.

Step-by-Step Resolution Guide

Step 1: Identify the Failing Message and Conversation

  • Query the sys.transmission_queue system view to locate the specific message causing the failure.
  • Run: SELECT * FROM sys.transmission_queue WHERE transmission_status LIKE '%9704%';
  • Note the conversation_handle and message_body for further inspection.

Step 2: Validate the Message Body Against the Expected Schema

  • Extract the message_body column and cast it to XML to inspect its structure.
  • Run: SELECT CAST(message_body AS XML) FROM sys.transmission_queue WHERE conversation_handle = '<handle>';
  • Compare the output against the XML Schema Collection bound to the message type using sys.xml_schema_collections.
  • Check for unclosed tags, invalid namespaces, or missing required elements.

Step 3: Correct the Message Type Validation Setting (If Needed)

  • Open SQL Server Management Studio (SSMS) and navigate to Service Broker > Message Types under your target database.
  • Right-click the relevant message type and review its Validation property.
  • If the sending application is not producing strict XML, consider changing validation from VALID_XML to WELL_FORMED_XML temporarily using: ALTER MESSAGE TYPE [YourMessageType] VALIDATION = WELL_FORMED_XML;
  • Alternatively, fix the application code generating the XML to match the required schema.

Step 4: Clear the Poisoned Message and Reset the Queue

  • If the malformed message is stuck and causing queue poisoning, end the affected conversation.
  • Run: END CONVERSATION '<conversation_handle>' WITH CLEANUP;
  • Restart the Service Broker queue by disabling and re-enabling it: ALTER QUEUE [YourQueueName] WITH STATUS = OFF; then ALTER QUEUE [YourQueueName] WITH STATUS = ON;

Step 5: Restart the SQL Server Service Broker Endpoint

  • On Windows Server 2025, open Services.msc and restart the SQL Server (MSSQLSERVER) service to force Service Broker to reinitialize its internal queues.
  • Alternatively, run ALTER ENDPOINT commands to toggle the Service Broker endpoint state without a full server restart.

Alternative or Verification Steps

  • Verify the fix by sending a test message through the same service and confirming it appears in the target queue without generating a new entry in sys.transmission_queue.
  • If the error persists, drop and recreate the XML Schema Collection associated with the message type, ensuring it matches the exact namespace and structure used by the sending application.