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

by

Troubleshooting Guide: SQL Server Error 4112 — “The function ‘%.*ls’ must have an OVER clause with ORDER BY”

Why This Error Happens

This error occurs when a ranking or analytic window function—such as ROW_NUMBER(), RANK(), DENSE_RANK(), or NTILE()—is called without a mandatory ORDER BY clause inside its OVER() clause. Unlike aggregate window functions (e.g., SUM() OVER()), these specific functions require a defined logical order to calculate sequential values correctly, so SQL Server 2025 rejects the query at the parsing stage. This is typically a T-SQL syntax issue introduced by a code migration, an ORM-generated query, or a copy-pasted script rather than a server configuration or OS-level fault.

Step-by-Step Resolution Guide

Step 1: Identify the Offending Query

  • Locate the exact statement referenced in the error using SQL Server Management Studio (SSMS) 21 or Azure Data Studio.
  • Check the Messages tab output or the application’s exception log for the full query text, since %.*ls is a placeholder for the function name (e.g., ROW_NUMBER).
  • If running on a stored procedure, use sp_helptext '<ProcedureName>' to pull the full definition for review.

Step 2: Insert a Valid ORDER BY Clause into OVER()

  • Modify the function call to include an ORDER BY inside the parentheses of OVER().
  • Example of broken syntax: SELECT ROW_NUMBER() OVER() AS RowNum FROM Sales.Orders;
  • Corrected syntax: SELECT ROW_NUMBER() OVER (ORDER BY OrderDate DESC) AS RowNum FROM Sales.Orders;
  • Ensure the column referenced in ORDER BY actually exists in the table/view and is not ambiguous in multi-join queries.

Step 3: Check for Dynamic SQL and ORM Auto-Generated Scripts

  • If the query is generated dynamically (via Entity Framework, Dapper, or a reporting tool), inspect the application’s data access layer on the Windows Server 2025 host.
  • Search configuration files or stored logic (usually under C:\inetpub\wwwroot\<AppName>\ or the app’s installation directory) for template strings building OVER() clauses.
  • Patch the query template to always append a default sort column (e.g., a primary key) if no explicit ORDER BY is provided by the end-user filter.

Step 4: Recompile and Redeploy the Object

  • Once corrected, drop and recreate the stored procedure or view using CREATE OR ALTER PROCEDURE.
  • Clear the plan cache for the specific object to ensure SQL Server 2025 doesn’t attempt to reuse a cached (invalid) execution plan: DBCC FREEPROCCACHE (<plan_handle>);
  • Restart the associated application pool in IIS (if applicable) on the Windows Server 2025 instance to clear any cached connection strings or prepared statements.

Alternative or Verification Steps

  • Verification: Re-run the query directly in SSMS against the production or staging database; a successful execution with no Error 4112 confirms the fix. Additionally, monitor the SQL Server Error Log (via EXEC sp_readerrorlog) for 24 hours to ensure the error does not recur from other calling applications.
  • Alternative: If you cannot determine a logical sort order for the business requirement, use ORDER BY (SELECT NULL) as a syntactically valid placeholder, though this is only recommended for SUM()/COUNT() aggregate window functions and will not work for ROW_NUMBER(), which requires a genuine, deterministic order to function as intended.