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

by

Troubleshooting Guide: SQL Server Error 9706

Why This Error Happens

This error occurs when a Service Broker queue references a stored procedure (set via the ACTIVATION clause) that no longer exists, has been renamed, or was dropped after the queue was created. It can also happen if the procedure exists but under a different schema than what the queue’s activation metadata expects. Additionally, corruption in the system catalog views (sys.objects, sys.service_queues) can cause SQL Server to flag a valid procedure as invalid.

Step-by-Step Resolution Guide

Step 1: Identify the Affected Queue and Procedure IDs

  • Run the following query to resolve the IDs mentioned in the error message:
SELECT object_id, name, schema_id 
FROM sys.service_queues 
WHERE object_id = <queue_id>;
  • Cross-reference the procedure ID:
SELECT object_id, name 
FROM sys.objects 
WHERE object_id = <procedure_id>;
  • If the second query returns no rows, the procedure has been dropped or never existed.

Step 2: Recreate or Restore the Missing Stored Procedure

  • Check source control, DACPAC backups, or .sql deployment scripts for the original procedure definition.
  • Recreate the procedure using the exact original name and schema:
CREATE PROCEDURE [dbo].[YourProcedureName]
AS
BEGIN
    -- original logic
END;
  • Ensure the schema matches what the queue expects (check via sys.service_queues.schema_id).

Step 3: Reassign the Activation Procedure on the Queue

  • If the procedure was renamed or moved, update the queue’s activation settings to point to the correct procedure:
ALTER QUEUE [YourQueueName]
WITH ACTIVATION (
    STATUS = ON,
    PROCEDURE_NAME = dbo.NewProcedureName,
    MAX_QUEUE_READERS = 5,
    EXECUTE AS OWNER
);
  • Confirm the EXECUTE AS principal has EXECUTE permission on the procedure to avoid a secondary permissions error.

Step 4: Validate Service Broker Configuration

  • Open SQL Server Configuration Manager (Windows Server 2025 / Windows 11) and confirm the SQL Server service account has not changed, as this can affect EXECUTE AS context resolution.
  • Restart the SQL Server service if activation still fails to trigger:
Restart-Service -Name 'MSSQLSERVER' -Force

Alternative or Verification Steps

  • Verify the fix by sending a test message to the queue and checking sys.transmission_queue and SQL Server Error Log for renewed 9706 errors.
  • If the error persists despite a valid procedure, disable and re-enable activation (ALTER QUEUE ... WITH ACTIVATION (STATUS = OFF) then STATUS = ON) to force SQL Server to refresh its internal metadata cache for the queue.