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

by

Troubleshooting Guide: SQL Server Error 531 – “Cannot Set NOCOUNT to OFF Inside Trigger Execution”

Why This Error Happens

This error occurs because SQL Server 2025 has the server-level option disallow_results_from_triggers enabled, which explicitly blocks any trigger from returning result sets to the calling application. It commonly surfaces when a SET NOCOUNT OFF statement exists inside a DML or LOGON trigger, causing the trigger to attempt to send row-count messages or output back to the client. Additionally, if the error occurs specifically during a LOGON trigger, it is a hard-coded restriction, as LOGON triggers are never permitted to return results regardless of server configuration.

Step-by-Step Resolution Guide

Step 1: Identify the Offending Trigger

  • Open SQL Server Management Studio (SSMS) and connect to your Windows Server 2025 instance.
  • Run the following query to check the current server configuration:
  EXEC sp_configure 'disallow_results_from_triggers';
  • Check the run_value column; if it returns 1, the option is currently enabled.
  • Review the error log (via %ProgramFiles%\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\Log\ERRORLOG) to identify which specific trigger (DML or LOGON) triggered the event.

Step 2: Remove NOCOUNT OFF from the Trigger Body

  • Open the trigger definition in SSMS via Object Explorer > Databases > [Your DB] > Programmability > Triggers.
  • Locate and delete or comment out any line containing SET NOCOUNT OFF;.
  • Ensure the trigger includes SET NOCOUNT ON; at the very beginning of the trigger body to explicitly suppress row-count messages.
  • Save and redeploy the trigger using an ALTER TRIGGER script.

Step 3: Adjust the Server Configuration (If Business Logic Requires Results)

  • If the application logic genuinely requires results from triggers (not recommended for LOGON triggers), open a query window and run:
  EXEC sp_configure 'show advanced options', 1;
  RECONFIGURE;
  EXEC sp_configure 'disallow_results_from_triggers', 0;
  RECONFIGURE;
  • Restart the SQL Server (MSSQLSERVER) service via the Services app (services.msc) on Windows Server 2025 for the change to take full effect.
  • Note: This step will not resolve errors originating strictly from a LOGON trigger, as those are restricted by the engine regardless of this setting.

Step 4: Refactor LOGON Triggers (If Applicable)

  • If the error is tied to a LOGON trigger, open the trigger using sp_helptext [TriggerName].
  • Remove any PRINT, SELECT, or RAISERROR statements with the WITH LOG option that might return output to the client.
  • Replace direct output methods with logging to a dedicated audit table instead of returning data to the login session.

Alternative or Verification Steps

  • Verification: Attempt to log in or trigger the DML event again; monitor the SQL Server Error Log and Windows Event Viewer (Application Log) to confirm Error 531 no longer appears.
  • Alternative: If modifying the trigger is not immediately possible, temporarily disable the specific trigger using DISABLE TRIGGER [TriggerName] ON DATABASE; (for LOGON triggers) or ON [TableName] (for DML triggers) until the code can be properly refactored.