SQL Server administration and T-SQL development, Web Programming with ASP.NET, HTML5 and Javascript, Windows Phone 8 app development, SAP Smartforms and ABAP Programming, Windows 7, Visual Studio and MS Office software Kodyaz Development Resources
Development resources, articles, tutorials, samples, codes and tools for .Net, SQL Server, Vista, etc.

The statement terminated. The maximum recursion 100 has been exhausted before statement completion.

Last post 06-02-2008, 5:19 AM by eralper. 0 replies.
Sort Posts:
  •  06-02-2008, 5:19 AM 1761

    The statement terminated. The maximum recursion 100 has been exhausted before statement completion.

    Hello all,

    If you have read my article about Recursive CTE Split function MS SQL Server Recursive T-SQL Split Function and if you have used it with a large number of concatenated values waiting to be seperated, you might have get the following error message :


    The statement terminated. The maximum recursion 100 has been exhausted before statement completion.

    This is because by default CTE (Common Table Expression) functions are preconfigured for a maximum recursive number of 100. (MAXRECURSION )
    So you can easily configure this maximum recursion number MAXRECURSION from 100 to 32767 which is maximum value which can be defined except setting the value of MAXRECURSION to 0 which causes no limit to be applied on the recursive CTE function

    You can easily overcome this maxrecursion problem by adding the "OPTION ( MAXRECURSION 0 )" query hint to the end of the recursive select statement as I have updated the function at my MS SQL Server Recursive T-SQL Split Function article.


View as RSS news feed in XML
Copyright © 2004 - 2014 Eralper Yilmaz. All rights reserved.
Community Server by Telligent Systems