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

by

Troubleshooting Guide: SQL Server Error 253 – Multiple Recursive References in CTE

Why This Error Happens

This error occurs when the recursive member of a Common Table Expression (CTE) references the CTE name more than once in its query definition. SQL Server’s recursive query engine only supports a single recursive self-reference per anchor/recursive member combination, as multiple references create ambiguity in how the recursion should iterate. This is almost always caused by a logic error in the T-SQL query itself, such as a self-join or subquery within the recursive member that inadvertently calls the CTE twice, rather than a server configuration, permissions, or port issue.

Step-by-Step Resolution Guide

Step 1: Identify the Offending CTE in the Query

  • Open the query in SQL Server Management Studio (SSMS) or Azure Data Studio and locate the exact CTE name mentioned in the error message (the ‘%.*ls’ placeholder will show the real name at runtime).
  • Review the WITH cte_name AS (...) block and separate the anchor member (before UNION ALL) from the recursive member (after UNION ALL).
  • Confirm the recursive member is the section causing the issue by checking for the CTE name appearing more than once in that block.

Step 2: Refactor the Recursive Member to Use a Single Reference

  • Rewrite the recursive member so the CTE name appears only once, typically in a single JOIN or FROM clause.
  • If the original logic requires comparing against multiple prior levels of recursion, restructure the logic using a temporary table, a WHILE loop, or a helper column (e.g., a level/depth counter) instead of a second self-reference.
  • Example fix pattern:
  WITH cte_name AS (
      SELECT col1, col2, 1 AS Lvl
      FROM SourceTable
      WHERE ParentID IS NULL
      UNION ALL
      SELECT s.col1, s.col2, c.Lvl + 1
      FROM SourceTable s
      INNER JOIN cte_name c ON s.ParentID = c.col1  -- single reference only
  )
  SELECT * FROM cte_name;

Step 3: Validate Query Syntax Using SQL Server’s Parser

  • In SSMS, use Ctrl+F5 (Parse) to validate the corrected query without executing it.
  • Check the “Messages” tab for confirmation that the command completed successfully with no syntax errors.
  • If using SQL Server 2025’s enhanced Query Store diagnostics, review the Query Store recommendations panel for any additional flagged anti-patterns in recursive CTEs.

Step 4: Test Execution with a Limited Recursion Depth

  • Add OPTION (MAXRECURSION 10) temporarily to the end of the query to prevent runaway recursion during testing.
  • Execute the query against a development or staging instance of SQL Server 2025 first.
  • Confirm the result set returns expected hierarchical/recursive data (e.g., org charts, bill-of-materials, folder trees) without triggering Error 253.

Alternative or Verification Steps

  • Run the corrected query in a test environment and confirm it executes without Error 253, then check the actual execution plan in SSMS to ensure only one recursive operator appears for the CTE.
  • If refactoring the JOIN doesn’t resolve the issue, consider replacing the recursive CTE entirely with a WHILE loop and a temporary table (#TempHierarchy) as an alternative approach, especially for complex multi-path recursive logic that SQL Server’s CTE engine cannot express with a single self-reference.