Post reply

Error:Maximum stored procedure, function, trigger, or view nesting level exceeded (limit 32) in SQL 2005

  • January 7, 2008 at 9:50 pm

    #180237

    Hi all,

    Please inform me how can I avoid getting the error message as "Maximum stored procedure, function, trigger, or view nesting level exceeded (limit 32)" in SQL 2005.This is thrown from the production environment while accessing some of the aspx pages in the application.But now major issues has been reported because of getting this error.But to mantain quality work we want to stop this error message getting displayed.

    Please provide me with a permanent fix or work around to solve this issue.Thanks in advance.

  • January 8, 2008 at 1:37 am

    #765611

    Just as the error message says, you exceeded the maximum nesting level of 32. And from my point of view this means there's a serious flaw in your database design.

    Unfortunately you don't provide any more details, but my first guess would be that two or more triggers are creating a loop. Check your database for triggers and if the database option "Recursive triggers" is enabled you might want to disable that.

    If the error is not caused by triggers then you neesd to find out which stored procedure or function is the culprit. You might be able to trace it using profiler.

    [font="Verdana"]Markus Bohse[/font]

  • January 8, 2008 at 6:12 am

    #765683

    You really shouldn't cross-post questions.

    I gave a possible answer over here: http://qa.sqlservercentral.com/Forums/Topic439862-360-1.aspx

    ----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software

  • January 9, 2008 at 2:17 am

    #766054

    I have noticed this error in nesting views. If you have for example 5 views (each of 8 tables) and you create view with joins to all of them, you can simply exceed the limit (even if they use the same tables)

  • September 10, 2010 at 10:35 am

    #1219345

    This can happen when you recursively inadverly call the same object through some of your database calls.

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply