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

Question

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

mailgokul

Old Hand

Points: 300
More actions
January 7, 2008 at 9:56 pm

#180238

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.

Viewing 8 posts - 1 through 7 (of 7 total)

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

Grant Fritchey SSC Guru Points: 398690 More actions · Answer 1

You have nesting occuring in triggers on your database. You need to examine the code that is being called and examine the data structure to see what triggers you might have. You have a trigger that fires a trigger, that fires a trigger, etc. and there is a limit of 32 deep in which this can occur. You can turn this off by changing the "nested triggers" option in the server configuration. It only turns affects "AFTER" triggers and not "INSTEAD OF" triggers. These will still nest and could still lead to your problem.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

Michal Mokros Hall of Fame Points: 3259 More actions · Answer 2

Not only triggers cause this error, the same problem is with nesting views...

Koenraad Dendievel SSC Veteran Points: 289 More actions · Answer 3

This also happens a lot while executing cross database procedures

E.g. Trigger calls procedure in other db to update lookuptable

possible solution: run the procedure asynchronously (in a new connection)

jadhavsonali5 Valued Member Points: 57 More actions · Answer 4

Please check foreign key reference that have been not being used.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 5

Please note: 3 year old thread.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Isabella.Sokolovskaya Valued Member Points: 56 More actions · Answer 6

I just had the same.

Check if you have inside of your procedure/script un-commented sample how to execute the procedure with different parameters or something a like.

Good Luck,

Isabella

The Dixie Flatline SSC Guru Points: 53354 More actions · Answer 7

:crazy:

__________________________________________________

Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills