Fixing “Maximum stored procedure nesting level exceeded” in Power Query.

The error “Maximum stored procedure, function, trigger, or view nesting level exceeded (limit 32)” (SQL Server Error 217) occurs when a stored procedure or nested database pipeline called by Excel or Power Query exceeds Microsoft SQL Server’s hard limit of 32 execution stack levels. This happens when a stored procedure calls another procedure recursively without a valid termination condition, when a database trigger fires an infinite loop, or when Power Query’s internal metadata-discovery mechanism repeatedly calls a procedural wrapper.

Quick Answer

To fix “Maximum stored procedure nesting level exceeded” in Power Query, inspect the stored procedure for uncontrolled recursion and add a depth guard using IF @@NESTLEVEL > 30 RETURN;. In Power Query, call the procedure using Value.NativeQuery with [EnableFolding = false] to prevent the Mashup Engine from repeatedly executing procedural wrappers during preview generation.

---

Why Nesting Limits Break in Excel

SQL Server maintains an internal execution counter called @@NESTLEVEL. Each time a stored procedure calls another procedure, enters a user-defined function, or invokes a trigger, this counter increases by 1. Once @@NESTLEVEL reaches 33, SQL Server terminates the query to prevent a server thread stack overflow.

In Excel and Power Query, this problem is triggered by three primary scenarios:

1. Uncontrolled Recursive Procedures: A stored procedure designed to traverse hierarchical data (such as parent-child employee rosters, bill-of-materials assemblies, or account rollups) encounters circular references, looping indefinitely until hitting level 32.
2. Recursive Database Triggers: A table update initiated by an automated process fires an UPDATE trigger that modifies another table, which in turn updates the original table.
3. Power Query Schema-Discovery Loops: When Power Query loads a stored procedure, it executes internal routines (such as sp_describe_first_result_set or legacy SET FMTONLY ON) to identify column names and data types. If the stored procedure calls nested dynamic SQL blocks that depend on session state, the schema inspection can trigger infinite evaluation loops.

To maintain overall query engine stability, see Managing SQL Query Stability: Fixing Timeouts and Resource Exhaustion..

---

Practical Diagnostic and Repair Steps

Step 1: Add a Nesting Guard to the Stored Procedure

If you maintain or can edit the stored procedure code, inspect the recursive logic and install an explicit safety exit.

1. Open the stored procedure in SQL Server Management Studio (SSMS).
2. Locate the recursive execution block.
3. Add a check using the system variable @@NESTLEVEL: ALTER PROCEDURE dbo.GetHierarchyData @ParentID INT AS BEGIN SET NOCOUNT ON; -- Safety guard against infinite execution loops IF @@NESTLEVEL > 30 BEGIN RAISERROR('Hierarchy processing exceeded safe nesting limit.', 16, 1); RETURN; END; -- Recursive procedure logic follows... END;
4. If the procedure traverses trees or networks, check the underlying source tables for circular records (for example, where Record A lists Record B as parent, and Record B lists Record A as parent).

Step 2: Disable Schema-Folding Loops in Power Query

Power Query’s Mashup Engine will repeatedly invoke stored procedures if it attempts to fold transformation steps back to the server. You can disable this evaluation loop by declaring [EnableFolding = false].

1. Open your workbook and launch the Power Query Editor.
2. Click Advanced Editor on the Home tab.
3. Locate your stored procedure call and wrap it inside Value.NativeQuery: let Source = Sql.Database("db-server.corp.local", "WarehouseDB"), RunProcedure = Value.NativeQuery( Source, "EXEC dbo.GetHierarchyData @ParentID = 101", null, [EnableFolding = false] ) in RunProcedure
4. By setting [EnableFolding = false], you tell Power Query to accept the tabular output directly without re-evaluating the procedure’s internal schema during UI rendering.

If your stored procedure requires non-standard parameters or returns formatting errors, see Fixing crashes when calling Stored Procedures with complex parameters..

Step 3: Check for Recursive Database Triggers

If your query triggers data updates that crash at nesting level 32, verify whether database triggers are firing cyclically.

Run the following query in SSMS to inspect trigger recursion settings:

SELECT name, is_recursive_triggers_on
FROM sys.databases
WHERE name = 'YourDatabaseName';

If recursive triggers are enabled unexpectedly, disable them:

ALTER DATABASE YourDatabaseName
SET RECURSIVE_TRIGGERS OFF;

---

Diagnostic Next Steps

If the nesting limit persists after applying safety guards:

• Multiple Output Sets: Stored procedures that execute complex control-flow loops often return multiple tables or return codes before the primary data set, confusing the Power Query Mashup Engine. Review Troubleshooting “The resultset of a stored procedure must be a single table.”.
• Automation Security Dialogs: When you update stored procedure calls to use Value.NativeQuery, Excel may present interactive permission prompts that stall scheduled updates. Learn how to manage them in Handling “Native Query” security warnings that block automation..