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

by

Troubleshooting Guide: Error 9233 – “The option ‘Service’ Must Be Specified in the Notification Options Identifier”

Why This Error Happens

This error occurs when SQL Server’s Service Broker attempts to process a notification request (commonly tied to Query Notifications or Event Notifications) but the notification options string is malformed or incomplete. Typically, this happens because the SERVICE clause—which specifies the target service name and broker instance GUID—was omitted or incorrectly formatted when constructing the notification options in application code or a SUBSCRIBE statement. This can also surface after a SQL Server 2025 upgrade if legacy connection strings or cached notification templates from older client libraries (like older SqlDependency implementations) don’t conform to the updated Service Broker syntax validation.

Step-by-Step Resolution Guide

Step 1: Review the Notification Options Syntax in Application Code

  • Locate the code generating the SqlNotificationRequest or SUBSCRIBE statement (often in C#/.NET applications using SqlDependency or SqlCommand.Notification).
  • Confirm the options string follows the required format: service=ServiceName;local database=DatabaseName.
  • Check for typos, missing semicolons, or omitted service= key-value pairs.

Step 2: Verify Service Broker Configuration on the Database

  • Open SQL Server Management Studio (SSMS) and connect to the instance.
  • Run the following query to confirm Service Broker is enabled:
SELECT name, is_broker_enabled FROM sys.databases WHERE name = 'YourDatabaseName';
  • If is_broker_enabled returns 0, enable it with:
ALTER DATABASE YourDatabaseName SET ENABLE_BROKER;
  • Ensure the required Service Broker service (e.g., SqlQueryNotificationService-<GUID>) exists under sys.services.

Step 3: Recreate the Notification Service and Queue

  • Drop and recreate the notification service tied to the queue if it appears corrupted or missing the correct name binding:
DROP SERVICE [SqlQueryNotificationService];
CREATE SERVICE [SqlQueryNotificationService] ON QUEUE [SqlQueryNotificationQueue]
([http://schemas.microsoft.com/SQL/Notifications/PostQueryNotification]);
  • Restart the SQL Server service via Services.msc (Windows Server 2025/Windows 11) or run:
Restart-Service -Name 'MSSQLSERVER' -Force

Step 4: Update Client Libraries and Connection Strings

  • Ensure the application is using an updated System.Data.SqlClient or Microsoft.Data.SqlClient NuGet package compatible with SQL Server 2025.
  • Rebuild the notification options string explicitly in code rather than relying on cached or default values:
SqlDependency.Start(connectionString, "service=MyNotifyService;local database=MyDB");

Alternative or Verification Steps

  • To verify the fix, re-run the application’s subscription logic and monitor sys.dm_qn_subscriptions in SSMS—an active row with no errors confirms the notification registered successfully.
  • If the error persists, enable Service Broker trace flags (DBCC TRACEON(933, -1)) temporarily to capture detailed diagnostic output for the malformed notification request.