CODEWITHSUNDEEP
azure-sql-databaseazure-data-factory
Osceria
Osceria

Reputation: 353

How to fix this error in ADF : "The maximum recursion 100 has been exhausted before statement completion."?

When I try to run a query and load the resultset into a table in Azure SQL database, I keep getting this error after the pipeline runs for some minutes.

Query:

  WITH RecursiveCTE AS (
    SELECT  PARENT,CHILD , 1 AS Level
    FROM P_C

    UNION ALL

    SELECT  t.PARENT, t.CHILD , c.Level + 1
    FROM   P_C t
    INNER JOIN RecursiveCTE c ON t.PARENT = c.CHILD 
)
SELECT distinct PARENT,CHILD ,Level FROM RecursiveCTE;

Error Details: "Azure Data Factory: Failure happened on 'Source' side. ErrorCode=SqlOperationFailed,'Type=Microsoft.DataTransfer.Common.Shared.HybridDeliveryException,Message=A database operation failed with the following error: 'The statement terminated. The maximum recursion 100 has been exhausted before statement completion.',Source=,''Type=System.Data.SqlClient.SqlException,Message=The statement terminated. The maximum recursion 100 has been exhausted before statement completion.,Source=.Net SqlClient Data Provider,SqlErrorNumber=530,Class=16,ErrorCode=-2146232060,State=1,Errors=[{Class=16,Number=530,State=1,Message=The statement terminated. The maximum recursion 100 has been exhausted before statement completion.,},],'"

How to fix this error? Please advise.

I increased the timeout minutes but no luck.

Upvotes: 0

Views: 226

Answers (1)

Nandan
Nandan

Reputation: 4945

Can you please provide the query that you are executing because of which you are seeing this error? Based on the error message, it seems you are using recursive CTE. So you might need to explicitly specify max recursion:

MSFT Doc:

An incorrectly composed recursive CTE may cause an infinite loop. For example, if the recursive member query definition returns the same values for both the parent and child columns, an infinite loop is created. To prevent an infinite loop, you can limit the number of recursion levels allowed for a particular statement by using the MAXRECURSION hint and a value between 0 and 32,767 in the OPTION clause of the INSERT, UPDATE, DELETE, or SELECT statement. This lets you control the execution of the statement until you resolve the code problem that is creating the loop. The server-wide default is 100. When 0 is specified, no limit is applied. Only one MAXRECURSION value can be specified per statement.

Similar thread : The maximum recursion 100 has been exhausted before statement completion

Upvotes: 0

Related Questions