locked
Maximum stored procedure, function, trigger, or view nesting level exceeded (limit 32). RRS feed

  • Question

  • Hi All,

    I have transactional replication with up-datable subscriber. When i try to update the data from Publisher side i am getting below error. in the same way when i update from subscriber side, it is updating fine. There is no problem with select statement the problem is happening in updating the data. And i am able to update the tables which are not participating in replication

    Msg 217, Level 16, State 1, Procedure uDocument, Line 7
    Maximum stored procedure, function, trigger, or view nesting level exceeded (limit 32).

    Kindly help.

    Thanks in  Advance

    Regards

    Khushi


    • Edited by Khushi N Thursday, December 20, 2012 7:25 AM
    Thursday, December 20, 2012 7:23 AM

Answers

  • I think the repl trigger is firing the user trigger.The user trigger is updating the same table and hence cause the firing of the triggers in loop.

    You may look at the below article on how this issue can be reproduced irrespective of replication

    http://www.mssqltips.com/sqlservertip/1713/maximum-stored-procedure-function-trigger-or-view-nesting-level-exceeded-limit-32/

    • Marked as answer by Khushi N Thursday, December 20, 2012 7:34 AM
    Thursday, December 20, 2012 7:29 AM
  • i resolved the error with thissolution

    SP_CONFIGURE 'nested_triggers',0
    GO
    RECONFIGURE
    GO

    http://blog.lekevin.com/computer/database/maximum-stored-procedure-function-trigger-or-view-nesting-level-exceeded-limit-32-error/

    • Marked as answer by Khushi N Thursday, December 20, 2012 7:34 AM
    Thursday, December 20, 2012 7:34 AM

All replies

  • I think the repl trigger is firing the user trigger.The user trigger is updating the same table and hence cause the firing of the triggers in loop.

    You may look at the below article on how this issue can be reproduced irrespective of replication

    http://www.mssqltips.com/sqlservertip/1713/maximum-stored-procedure-function-trigger-or-view-nesting-level-exceeded-limit-32/

    • Marked as answer by Khushi N Thursday, December 20, 2012 7:34 AM
    Thursday, December 20, 2012 7:29 AM
  • i resolved the error with thissolution

    SP_CONFIGURE 'nested_triggers',0
    GO
    RECONFIGURE
    GO

    http://blog.lekevin.com/computer/database/maximum-stored-procedure-function-trigger-or-view-nesting-level-exceeded-limit-32-error/

    • Marked as answer by Khushi N Thursday, December 20, 2012 7:34 AM
    Thursday, December 20, 2012 7:34 AM
  • You can also refer : 'nested_triggers'

    Thanks Saurabh Sinha http://saurabhsinhainblogs.blogspot.in/ Please click the Mark as answer button and vote as helpful if this reply solves your problem

    Saturday, February 8, 2014 1:08 AM