SQL : Maximum Recursion Limit

Recently at work, I got back in touch with dealing with Microsoft .NET technology.

Something that I haven’t working on for awhile, long story short the requirements was simple generate report to webpart (Sharepoint) using their custom application database.

Seems fairly simple, but with a fairly complex database schema, I adopted the view approach and leave all complex data manipulation to the SQL Server.

Until I hit the following error,

MAXIMUM RECUSION LIMIT 100 have been reach.

This normally means that you are not able to progress further, after much figuring with the problem. I have found 2 solutions.

1. Data Issue, in my case this was the problem, due to the quality of the  sample data. I had to generate my own data sample and it was all good.

2. You will have to add “option (maxrecusion 1000)” after your select statement.

A little bit more on this, the SQL Server maximum recursion is 32767.

By default this value is set as 100, therefore you can set the number anywhere from 0 to 32767.

To bypass this error, you can simply set this to ZERO, but this doesnt mean that the problem goes away.
The reason this error appears means that the sql query that was written have some issue and its best to find the root cause of it.