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

by

Troubleshooting Guide: Error 5310 — Aggregates Not Allowed in VALUES List

Why This Error Happens

Error 5310 occurs when a T-SQL INSERT statement contains an aggregate function (such as SUM(), COUNT(), AVG(), MAX(), or MIN()) directly inside the VALUES clause, which SQL Server’s query parser does not permit by design. This is a syntax-level restriction rather than a permissions or configuration issue — the VALUES clause is meant for literal values, variables, or scalar expressions, not set-based aggregate calculations. It typically surfaces after a code migration, an ORM-generated query, or a manually written script where a developer intended to insert a computed summary value but used the wrong INSERT syntax pattern.

Step-by-Step Resolution Guide

Step 1: Identify the Offending Statement

  • Open SQL Server Management Studio (SSMS) or Azure Data Studio and locate the exact query from the application log or SQL Profiler/Extended Events trace that raised Error 5310.
  • Look specifically for a pattern like: INSERT INTO TableName (Col1) VALUES (SUM(Col2)).
  • Confirm the aggregate function is positioned inside the VALUES(...) parentheses rather than in a SELECT clause.

Step 2: Rewrite the INSERT Using INSERT…SELECT Syntax

  • Replace the VALUES clause with a SELECT statement that performs the aggregation, since aggregates are valid in SELECT but not in VALUES.
  • Example corrected syntax:
  INSERT INTO TableName (Col1)
  SELECT SUM(Col2) FROM SourceTable WHERE Condition = 1;
  • If you need a single static aggregate result inserted, calculate it first into a variable, then insert the variable:
  DECLARE @Total INT;
  SELECT @Total = SUM(Col2) FROM SourceTable;
  INSERT INTO TableName (Col1) VALUES (@Total);

Step 3: Audit Application Code and Stored Procedures

  • Search stored procedures, triggers, and application data-access layers (Entity Framework, Dapper, or custom ADO.NET code) for dynamically generated INSERT statements.
  • On Windows Server 2025, check SQL Server Agent job steps under SQL Server Management Studio > SQL Server Agent > Jobs for scripts that may contain the same flawed pattern.
  • Update any ORM mapping logic that incorrectly concatenates aggregate functions into insert value lists.

Step 4: Test and Deploy the Fix

  • Run the corrected query in a non-production/staging SQL Server 2025 instance first.
  • Validate that the INSERT...SELECT statement returns the expected row(s) without raising Error 5310.
  • Deploy the corrected script to production once validated, using your standard change-management process.

Alternative or Verification Steps

  • Re-run the original failing transaction or application workflow and confirm Error 5310 no longer appears in the SQL Server Error Log (Management > SQL Server Logs in SSMS) or Windows Event Viewer.
  • If the error persists, enable SQL Server Extended Events to capture the exact query text in real time, as the error may be originating from a different, undocumented stored procedure or dynamic SQL string than initially suspected.