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

by

Troubleshooting Guide: SQL Server Error 5329

Why This Error Happens

This error occurs because SQL Server’s query optimizer cannot process windowed functions (such as ROW_NUMBER(), RANK(), OVER(), or aggregate windowing clauses) when the statement’s FROM clause references a nested INSERT (commonly via the INSERT ... OUTPUT pattern used as a data source). The engine restricts this combination because the execution plan cannot guarantee a stable row ordering or partition context when DML operations are composed inline with windowing logic. This typically surfaces in Microsoft SQL Server 2025 when developers attempt to use OUTPUT INTO or composable DML in combination with analytic functions in the same statement tree, which is an unsupported T-SQL construct rather than a bug or corrupted installation.

Step-by-Step Resolution Guide

Step 1: Identify the Offending Query

  • Review the SQL statement referenced in the error log or SSMS output pane.
  • Look specifically for a nested INSERT statement (e.g., INSERT INTO ... OUTPUT ... INTO) being used inside a FROM clause.
  • Check if a windowed function like OVER (PARTITION BY ... ORDER BY ...) appears in the SELECT, WHERE, or ORDER BY clause of the same outer query.

Step 2: Refactor the Query to Separate DML and Windowing Logic

  • Break the composable DML (INSERT ... OUTPUT) into its own standalone statement, writing results into a temporary table or table variable first.
  • Example pattern:
  • INSERT INTO #TempResults OUTPUT inserted.* INTO #StagingTable SELECT * FROM SourceTable;
  • Apply the windowed function in a separate, subsequent SELECT statement that reads from #StagingTable instead of nesting it directly.

Step 3: Update Application or ORM-Generated Queries

  • If the query is auto-generated by an ORM (Entity Framework, Dapper, etc.), check the generated SQL via SQL Server Profiler or Extended Events.
  • Modify the ORM mapping or raw SQL override to avoid combining OUTPUT clauses with windowed analytic functions in a single composed statement.
  • Redeploy the updated stored procedure or query to C:\Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\Binn\ dependent application logic if stored procedures are version-controlled externally.

Step 4: Validate T-SQL Compatibility Level

  • Open SSMS and run: SELECT compatibility_level FROM sys.databases WHERE name = 'YourDatabaseName';
  • Ensure the database is running under SQL Server 2025 compatibility level (160 or higher) so that any newer workarounds or syntax improvements are available.
  • If running a lower compatibility level, update it via: ALTER DATABASE YourDatabaseName SET COMPATIBILITY_LEVEL = 160;

Alternative or Verification Steps

  • Re-run the refactored query in SSMS or Azure Data Studio and confirm Error 5329 no longer appears in the Messages tab.
  • If the issue persists, use a Common Table Expression (CTE) to isolate the INSERT OUTPUT operation from the windowed SELECT, ensuring no windowed function shares a query plan with the nested DML statement.