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

by

Troubleshooting Guide: Error 8768 — Recursive CTEs Unsupported in Synapse SQL

Why This Error Happens

Error 8768 occurs when a T-SQL query containing a recursive Common Table Expression (CTE) is executed against a database or compute pool operating under Synapse SQL compatibility mode, which does not support recursive query plans. This typically happens when a database was migrated to or created under a Synapse-targeted compatibility level, or when a client application mistakenly routes a query to a Serverless/Dedicated SQL pool endpoint instead of a standard SQL Server 2025 engine instance. It can also surface if the database compatibility level was set incorrectly during an upgrade from an older SQL Server version to SQL Server 2025.

Step-by-Step Resolution Guide

Step 1: Verify the Target Engine and Connection Endpoint

  • Confirm you are connecting to a genuine SQL Server 2025 instance and not a Synapse SQL pool endpoint.
  • Check the connection string or SSMS “Server name” field — Synapse endpoints typically resemble yourworkspace.sql.azuresynapse.net.
  • Run SELECT SERVERPROPERTY('Edition'), SERVERPROPERTY('ProductVersion'); to confirm the engine type.

Step 2: Check and Correct Database Compatibility Level

  • Open SQL Server Management Studio (SSMS) and connect to the instance.
  • Run the following to check current compatibility level:
  • SELECT name, compatibility_level FROM sys.databases WHERE name = 'YourDatabaseName';
  • If the compatibility level is incorrectly set to a Synapse-mapped value, update it:
  • ALTER DATABASE YourDatabaseName SET COMPATIBILITY_LEVEL = 170;

Step 3: Review and Rewrite the Query Execution Context

  • Inspect the query or stored procedure for the recursive CTE syntax (WITH cte AS (... UNION ALL ...)).
  • Ensure the query is not being executed inside a context forwarded to a linked Synapse resource (check linked servers via sp_linkedservers).
  • If using Polybase or external tables pointing to Synapse, isolate the recursive CTE logic to run locally against the SQL Server 2025 engine instead.

Step 4: Update SQL Server 2025 Cumulative Updates

  • Navigate to Control Panel > Programs > Programs and Features on Windows Server 2025 / Windows 11 to confirm the installed SQL Server build.
  • Download and apply the latest Cumulative Update (CU) from the Microsoft Update Catalog, as early SQL Server 2025 builds had misclassified engine-mode flags affecting recursive CTE support.
  • Restart the SQL Server service via services.msc after patching.

Step 5: Restart SQL Server Services

  • Open services.msc on Windows Server 2025 / Windows 11.
  • Restart SQL Server (MSSQLSERVER) and SQL Server Agent services to ensure configuration changes take effect.

Alternative or Verification Steps

  • Re-run the original recursive CTE query after applying the compatibility level fix; a successful result set confirms resolution.
  • If the error persists, temporarily rewrite the recursive CTE using a WHILE loop with a temporary table as a functional workaround while further diagnosing the engine routing issue.