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

by

Troubleshooting Guide: Error 9717 – Cannot Enable Stored Procedure Activation on Service Broker Queue

Why This Error Happens

This error occurs because SQL Server Service Broker does not allow a single queue to have both stored procedure activation and event notification for queue_activation configured simultaneously. The queue is designed to use only one activation mechanism at a time to signal that messages have arrived, since both methods are fundamentally different approaches to the same notification task. When an administrator or deployment script attempts to enable ACTIVATION with a stored procedure while an existing CREATE EVENT NOTIFICATION ... ON QUEUE definition is still active on that same queue, SQL Server rejects the change to prevent conflicting activation logic.

Step-by-Step Resolution Guide

Step 1: Identify the Conflicting Event Notification

  • Open SQL Server Management Studio (SSMS) or Azure Data Studio and connect to the target instance running on Windows Server 2025 / Windows 11.
  • Run the following query to locate the event notification tied to the queue:
SELECT 
    en.name AS EventNotificationName,
    en.event_type_desc,
    o.name AS QueueName
FROM sys.event_notifications en
JOIN sys.objects o ON en.parent_id = o.object_id
WHERE o.name = 'YourQueueName';
  • Note the EventNotificationName returned; you will need it to drop the object in the next step.

Step 2: Drop the Existing Event Notification

  • Execute a DROP EVENT NOTIFICATION statement using the name retrieved in Step 1.
  • Use the exact syntax below in a New Query window in SSMS:
DROP EVENT NOTIFICATION YourEventNotificationName 
ON QUEUE YourSchema.YourQueueName;
  • Verify there are no dependent triggers or external applications relying on this specific notification before deletion, as dropping it will stop the “push” based alerts to the Broker service.

Step 3: Enable Stored Procedure Activation

  • Once the event notification is removed, re-run the ALTER QUEUE command to attach the stored procedure activation.
  • Use the following T-SQL syntax, ensuring the procedure schema is fully qualified:
ALTER QUEUE YourSchema.YourQueueName
WITH ACTIVATION (
    STATUS = ON,
    PROCEDURE_NAME = YourSchema.YourStoredProcedure,
    MAX_QUEUE_READERS = 5,
    EXECUTE AS SELF
);
  • Check the Windows Server 2025 Event Viewer (under Applications and Services Logs > MSSQLSERVER) to confirm no errors were logged during the ALTER execution.

Step 4: Restart the SQL Server Service Broker (If Needed)

  • If the queue remains in a disabled state after the alteration, toggle the Service Broker status to force a refresh.
  • Run ALTER DATABASE YourDatabaseName SET DISABLE_BROKER; followed immediately by ALTER DATABASE YourDatabaseName SET ENABLE_BROKER;.
  • Note: This step can cause a brief delay in message processing, so schedule it during a maintenance window.

Alternative or Verification Steps

  • Verification: Query sys.service_queues and check the is_activation_enabled column; it should now return 1, confirming the stored procedure activation is active and the conflict is resolved.
  • Alternative: If you need to retain the event-driven architecture instead of the stored procedure, simply leave the event notification in place and do not attempt the ALTER QUEUE ... WITH ACTIVATION command, as the two features are mutually exclusive by design.