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

by

Troubleshooting Guide: SQL Server Error 5321

Why This Error Happens

This error occurs because SQL Server 2025 does not permit certain functions to be referenced in the OUTPUT clause (represented by %S_MSG) when the statement’s FROM clause contains a nested data-modification operation, such as an INSERT wrapped inside a composable DML query. This restriction exists because the query optimizer cannot guarantee deterministic row-ordering or side-effect consistency when volatile functions are evaluated against a result set that is simultaneously being modified by a nested writer. Essentially, the engine is blocking a syntax pattern where you are trying to read “in-flight” data changes using a function that isn’t safe for that context.

Step-by-Step Resolution Guide

Step 1: Identify the Offending Function in the Query

  • Open the specific stored procedure or ad-hoc query referenced in the SQL Server error log that triggered Event ID 5321.
  • Look specifically at the OUTPUT or RETURNING clause of the outer query.
  • Check if you are using Ranking functions (e.g., ROW_NUMBER(), RANK()) or aggregate functions directly inside this clause while the FROM source is a nested INSERT.

Step 2: Refactor the Nested INSERT into a Temporary Table

  • Instead of nesting the INSERT ... OUTPUT directly inside the FROM clause of the outer query, break the transaction into two distinct steps.
  • First, execute the nested INSERT statement and capture its OUTPUT into a temporary table or table variable (e.g., INSERT INTO #TempResults...).
  • Second, run your outer SELECT statement—including the restricted function—against the #TempResults table instead of the live nested insert.

Step 3: Update Compatibility Level and Apply Cumulative Updates

  • Navigate to SQL Server Management Studio (SSMS) and right-click the Database > Properties > Options.
  • Verify the “Compatibility Level” is set to SQL Server 2025 (170) to ensure the query processor uses the latest execution rules.
  • On your Windows Server 2025 host, check Windows Update or the SQL Server Installation Center for the latest Cumulative Update (CU), as early builds of SQL Server 2025 had stricter parsing bugs regarding this specific clause that were patched in subsequent updates.

Step 4: Validate T-SQL Syntax Using sqlcmd

  • Open a Command Prompt or PowerShell terminal on the Windows Server 2025 machine.
  • Run the modified query via the command line utility to isolate it from SSMS caching issues:
    sqlcmd -S localhost -d YourDatabaseName -Q "EXEC YourStoredProcedureName"
  • Check the terminal output directly for the removal of the 5321 error code.

Alternative or Verification Steps

  • Verification: Re-run the specific transaction via SSMS and check the “Messages” tab; if the fix was successful, the query will return a “Command(s) completed successfully” message instead of the %S_MSG clause error.
  • Alternative: If refactoring the query is not immediately feasible, wrap the restricted function logic in a Common Table Expression (CTE) that sits outside the nested insert logic, effectively separating the data modification from the data read-back operation.