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

by

Troubleshooting Guide: SQL Server Error 5322

Why This Error Happens

This error occurs when a T-SQL statement attempts to use an aggregate function (such as SUM(), COUNT(), or AVG()) directly within a clause that references a nested INSERT statement inside a FROM clause, typically via the OUTPUT INTO or data-consuming INSERT ... EXEC pattern. SQL Server’s query optimizer cannot resolve aggregation logic against a nested DML statement because the nested INSERT has not fully materialized its result set at the point the aggregate is evaluated. This is a query construction limitation rather than a server configuration or permissions issue, meaning the fix lies entirely in rewriting the offending query.

Step-by-Step Resolution Guide

Step 1: Identify the Offending Query

  • Check the SQL Server error log or Extended Events session for the exact statement that triggered Error 5322.
  • Look specifically for a FROM clause containing a nested INSERT (commonly seen with INSERT ... OUTPUT combined with SELECT subqueries).
  • Confirm the aggregate function (SUM, COUNT, MAX, MIN, AVG) is being applied in the same statement block as the nested insert.

Step 2: Refactor the Query Using a Temporary Table or Table Variable

  • Instead of nesting the INSERT directly inside the FROM clause, redirect the OUTPUT results into a temporary table first.
  • Example pattern:
  DECLARE @Results TABLE (ID INT, Amount DECIMAL(18,2));

  INSERT INTO TargetTable (ID, Amount)
  OUTPUT inserted.ID, inserted.Amount INTO @Results
  SELECT ID, Amount FROM SourceTable;

  SELECT SUM(Amount) FROM @Results;
  • This separates the DML operation from the aggregation step, avoiding the parser conflict entirely.

Step 3: Update Stored Procedures and ORM-Generated Queries

  • If the query originates from an ORM (Entity Framework, Dapper, etc.), inspect the generated SQL in SQL Server Profiler or Extended Events.
  • Manually override the generated query with a stored procedure that uses the temp table pattern from Step 2.
  • Deploy the corrected stored procedure via SQL Server Management Studio (SSMS) 21 or Azure Data Studio, ensuring it replaces the inline query call.

Step 4: Validate Compatibility Level Settings

  • Open SSMS and run:
  SELECT compatibility_level FROM sys.databases WHERE name = 'YourDatabaseName';
  • Ensure the database is running at compatibility level 160 or higher (SQL Server 2022/2025 engine behavior) since older compatibility levels can sometimes mask or alter how nested DML errors surface.
  • Adjust if needed:
  ALTER DATABASE YourDatabaseName SET COMPATIBILITY_LEVEL = 170;

Alternative or Verification Steps

  • Re-run the corrected query in SSMS or Azure Data Studio and confirm no Error 5322 appears in the Messages tab; check sys.messages to confirm error ID 5322 is no longer logged in the SQL Server error log.
  • As an alternative, wrap the aggregate logic in a separate SELECT statement that queries the final target table after the INSERT transaction commits, rather than attempting to aggregate in-flight data from the nested insert.