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

by

Troubleshooting Guide: SQL Server Error 116 — “Only One Expression Can Be Specified in the Select List”

Why This Error Happens

This error occurs when a subquery used in a comparison expression (such as with =, <, >, or in a WHERE clause) returns multiple columns instead of a single scalar value. SQL Server 2025’s query optimizer strictly enforces that non-EXISTS subqueries must resolve to exactly one column per row, since the engine cannot map multiple return values to a single comparison operator. This typically stems from a poorly structured SELECT statement inside the subquery, an accidental SELECT *, or a missing column-specific filter that was intended to narrow the result set.

Step-by-Step Resolution Guide

Step 1: Identify the Offending Subquery

  • Open the query in SQL Server Management Studio (SSMS 21) or Azure Data Studio connected to your SQL Server 2025 instance.
  • Locate the exact line number referenced in the error output (SSMS will highlight it in the Messages pane).
  • Check if the subquery uses SELECT * or SELECT col1, col2 instead of a single column.

Step 2: Rewrite the Subquery to Return a Single Column

  • Modify the SELECT clause inside the subquery to explicitly reference only one column, e.g., SELECT CustomerID FROM Orders WHERE ... instead of SELECT CustomerID, OrderDate FROM Orders.
  • If you need to compare against multiple columns, refactor the query to use EXISTS or IN with a correlated subquery instead of a direct equality comparison.
  • Example fix:
  -- Incorrect
  SELECT * FROM Employees WHERE (Salary, DeptID) = (SELECT Salary, DeptID FROM Managers WHERE ManagerID = 5)

  -- Corrected
  SELECT * FROM Employees WHERE Salary = (SELECT Salary FROM Managers WHERE ManagerID = 5)

Step 3: Validate Data Model and Joins

  • Confirm the subquery’s underlying table doesn’t have duplicate logical columns due to a bad JOIN condition that inflates the column count.
  • Check the file path C:\Program Files\Microsoft SQL Server\160\Tools\Binn\ (adjust version folder for SQL Server 2025) to ensure your SSMS/query tool build is current, as outdated parsers occasionally mis-flag valid syntax.
  • Update SSMS via Help > Check for Updates if using an older build against the 2025 engine.

Step 4: Test Query Execution Plan

  • Run SET SHOWPLAN_XML ON before executing the corrected query to confirm SQL Server accepts the single-column subquery.
  • Review the execution plan for any residual multi-column references that may cascade from nested subqueries.

Alternative or Verification Steps

  • Re-run the corrected query in SSMS and confirm it returns results without triggering Error 116; check the Messages tab for a clean execution with zero errors.
  • If the issue persists, replace the subquery with a JOIN or CROSS APPLY construct, which often resolves ambiguity in multi-column comparisons more reliably than scalar subqueries.