Developing for Dynamics GP

by Microsoft Dynamics GP Developer Support Team (Fargo, ND, USA)

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

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

Rate This
  • Comments 6

David Meego - Click for blog homepageToday, I resolved a very interesting case. We had a customer who had just upgraded from an early build of Microsoft Dynamics GP 10.0 to Microsoft Dynamics GP 2010 (11.0) and after the upgrade they were unable to save from the Customer Maintenance window.

Every time they would try to add a new record or update an existing Customer Master record, the system throw an unhandled exception error with the following details:

Unhandled database exception:
A save operation on table 'RM_Customer_MSTR'  (45).
[Microsoft][SQL Server Native Client 10.0][SQL Server]Maximum stored procedure, function, trigger, or view nesting level exceeded (limit 32).
EXCEPTION_CLASS_DB
DB_ERR_SQL_OBJ_NOT_FOUND

Capturing a DEXSQL.LOG for the problem showed the call to the zDP_RM00101SI stored procedure to insert the record followed by the reporting of the error. As the stored procedure failed, Dexterity tries again with an actual SQL INSERT statement which also failed with the same error message.

/*  Date: 05/02/2012  Time: 15:38:48
SQLSTATE:(37000) Native Err:(217) stmt(456758368):*/
[Microsoft][SQL Server Native Client 10.0][SQL Server]Maximum stored procedure, function, trigger, or view nesting level exceeded (limit 32).*/
/*

There is a Knowledge Base (KB) article which discusses this error:

The article explains how the miscellaneous Database Property option for Recursive Triggers Enable should be set to false. This will prevent a trigger on a table from triggering itself again when it makes a change to table it is registered against. In our case this setting was already correctly set to false.

As this problem does not seem to affect most Dynamics sites, I felt that it must be related to some sort of customisation. As the error was being generated at the SQL level, the customisation would need to be at the SQL level....I suspected a custom trigger on the RM_Customer_MSTR (RM00101) table.

So, I asked the partner to check for triggers on the RM00101 table, and try disabling them to see if that allows the record to be saved.

On a standard GP 2010 system there is a zDT_RM00101U trigger. This is a new trigger (added for v10.0 SP2 and GP2010) which writes a UTC time stamp to the DEX_ROW_TS column of the table every time the table is updated. The DEX_ROW_TS field is used for synchronization with external systems, such as Dynamics CRM. Also see Understanding how Microsoft Dynamics GP works with Microsoft SQL Server.

When the partner checked the triggers on the RM00101 table, they found a custom trigger. Below is the code for that trigger:

Original SQL Trigger on RM00101 table

ALTER trigger [dbo].[tr_UpdateAddressShipTaxtoDebtorMain] on [dbo].[RM00101]
after insert, update as

update RM00102
set SHIPMTHD = RM00101.SHIPMTHD , TAXSCHID = RM00101.TAXSCHID
from RM00101
where RM00101.CUSTNMBR = RM00102.CUSTNMBR
and RM00101.CUSTNMBR in
(select CUSTNMBR from inserted)

The code updates the Shipping Method and Tax Schedule ID from the RM_Customer_MSTR (RM00101) table to the RM_Customer_MSTR_ADDR (RM00102) table for the saved customer. Once the trigger was disabled, the error went away.

NOTE: The scripts use the ALTER TRIGGER command as we were editing an existing trigger.

Why would this trigger which worked previously for the customer fail after the upgrade?


The answer becomes clearer when you check the RM_Customer_MSTR_ADDR (RM00102) table for triggers.

The RM00102 table also has zDT triggers for the Create, Delete and Update events. Reading the trigger scripts you can see that they also write back to the RM00101 table to update the time stamp in the DEX_ROW_TS column.

Can you see what was happening yet? .... Let me break it down:

  1. User Saves data from Microsoft Dynamics GP application.
     
  2. Data written to RM00101 table.
  3. Trigger zDT_RM00101U on RM00101 table executes and updates time stamp.
  4. Custom Trigger executes and updates RM00102 table.
  5. Data written to RM00102 table.
  6. Trigger zDT_RM00102U on RM00102 table executes and writes back to RM00101 table.
  7. Go back to step 2, and repeat until nesting level error generated.
     
  8. When error generated, all changes captured in the transaction are rolled back.
  9. Unhandled Exception reported back at the application.

So now we understand what is happening; How can we modify the custom trigger code to stop it causing an infinite loop?


My first thought was that we could change the custom trigger so that it would not update records that did not need to be changed.

So we rewrote the trigger to use joins and added statements into the where clause so that the update would only only change a record if the Shipping Method or Tax Schedule ID were not the desired final values and the Shipping Method and Tax Schedule ID from the Customer Master table were not blank.

First Revision of SQL Trigger on RM00101 table

ALTER trigger [dbo].[tr_UpdateAddressShipTaxtoDebtorMain] on [dbo].[RM00101]
after insert, update as

update a set SHIPMTHD = c.SHIPMTHD, TAXSCHID = c.TAXSCHID
from RM00102 a
join inserted i on i.CUSTNMBR = a.CUSTNMBR
join RM00101 c on c.CUSTNMBR = a.CUSTNMBR
where (a.SHIPMTHD <> c.SHIPMTHD or a.TAXSCHID <> c.TAXSCHID)
and (c.SHIPMTHD <> '' and c.TAXSCHID <> '')

This code does work correctly by itself, but it did not prevent the nesting level exceeded error. The problem was that the update statement itself causes the triggers to fire even if the update does not actually change any records. So while the inserted data set returned to the trigger was empty, the update event still happens.

So we needed a way to prevent the update statement from running when it was not needed.

The second revision (below) included an if exists statement with a select query to check if there were any records that needed updating and so only execute the update statement when it is really needed.

Second Revision of SQL Trigger on RM00101 table

ALTER trigger [dbo].[tr_UpdateAddressShipTaxtoDebtorMain] on [dbo].[RM00101]
after insert, update as

if exists(
select a.CUSTNMBR, a.ADRSCODE, a.SHIPMTHD, a.TAXSCHID, c.CUSTNMBR, c.ADRSCODE, c.SHIPMTHD, c.TAXSCHID
from RM00102 a
join inserted i on i.CUSTNMBR = a.CUSTNMBR
join RM00101 c on c.CUSTNMBR = a.CUSTNMBR
where (a.SHIPMTHD <> c.SHIPMTHD or a.TAXSCHID <> c.TAXSCHID)
  and (c.SHIPMTHD <> '' and c.TAXSCHID <> '')
)

begin
update a set SHIPMTHD = c.SHIPMTHD, TAXSCHID = c.TAXSCHID
from RM00102 a
join inserted i on i.CUSTNMBR = a.CUSTNMBR
join RM00101 c on c.CUSTNMBR = a.CUSTNMBR
where (a.SHIPMTHD <> c.SHIPMTHD or a.TAXSCHID <> c.TAXSCHID)
  and (c.SHIPMTHD <> '' and c.TAXSCHID <> '')
end

This version of the code fixed the problem by breaking the infinite loop. 

However, while testing the code we noticed that if you entered only one of the Shipping Method or Tax Schedule ID fields, the code did not roll down that one field change to the Address (RM00102) table.

So the last change was to separate the code into two sections, one to handle the Shipping Method and one to handle the Tax Schedule ID.

Final Revision of SQL Trigger on RM00101 table

ALTER trigger [dbo].[tr_UpdateAddressShipTaxtoDebtorMain] on [dbo].[RM00101]
after insert, update as

if exists(
select a.CUSTNMBR, a.ADRSCODE, a.SHIPMTHD, c.CUSTNMBR, c.ADRSCODE, c.SHIPMTHD
from RM00102 a
join inserted i on i.CUSTNMBR = a.CUSTNMBR
join RM00101 c on c.CUSTNMBR = a.CUSTNMBR
where (a.SHIPMTHD <> c.SHIPMTHD)
  and (c.SHIPMTHD <> '')
)

begin
update a set SHIPMTHD = c.SHIPMTHD
from RM00102 a
join inserted i on i.CUSTNMBR = a.CUSTNMBR
join RM00101 c on c.CUSTNMBR = a.CUSTNMBR
where (a.SHIPMTHD <> c.SHIPMTHD)
  and (c.SHIPMTHD <> '')
end

if exists(
select a.CUSTNMBR, a.ADRSCODE, a.TAXSCHID, c.CUSTNMBR, c.ADRSCODE, c.TAXSCHID
from RM00102 a
join inserted i on i.CUSTNMBR = a.CUSTNMBR
join RM00101 c on c.CUSTNMBR = a.CUSTNMBR
where (a.TAXSCHID <> c.TAXSCHID)
  and (c.TAXSCHID <> '')
)

begin
update a set TAXSCHID = c.TAXSCHID
from RM00102 a
join inserted i on i.CUSTNMBR = a.CUSTNMBR
join RM00101 c on c.CUSTNMBR = a.CUSTNMBR
where (a.TAXSCHID <> c.TAXSCHID)
  and (c.TAXSCHID <> '')
end

With this final code, we had fixed the error and made the code behave much better than the original design.

Hope you enjoyed reading post, I know it was "fun" to solve the problem.

David

// Copyright © Microsoft Corporation.  All Rights Reserved.
// This code released under the terms of the
// Microsoft Public License (MS-PL, http://opensource.org/licenses/ms-pl.html.)

  • David,

    We had the same problem (quite some time ago now, on our V8 to V10 upgrade, SOP10100 table).  Our solution was to check and see if the only thing being updated was the DEX_ROW_TS column - if it was then the custom trigger would just exit.  I have code if you're interested.

    Tim

  • Posting from Janakiram at DynamicsBlogger

    dynamicsblogger.com/sql-error-maximum-stored-procedure-function-trigger-or-view-nesting-level-exceeded-limit-32

  • Posting from Mark Polino at DynamicAccounting.net

    msdynamicsgp.blogspot.com.au/.../sql-error-maximum-stored-procedure.html

  • I had almost the same problem, only thing is my GP is 2010 straight no update, and it was because specific users did not had access to another database where i save like the audit trail, when i gave access to the users it all came good

    Hope helps someone

  • Hi John

    Thanks for the feedback.

    The Audit Trails module uses SQL triggers to watch watch for table changes.

    So, in some ways you did have "custom" triggers in place.

    David

  • Hi David,

    Thanks for you post, gave me some hints about another floating trigger that was not working as expected :-)... This time in the POP module ... have a look at this community discussion :

    community.dynamics.com/.../102749.aspx

    Take care !

    Beat

Page 1 of 1 (6 items)
Comments Information

PLEASE READ BEFORE POSTING

Please only post comments relating to the topic of this page.

If you wish to ask a technical question, please use the links in the links section (scroll down, on right hand side) to ask on the Newsgroups or Forums. If you ask on the Newsgroups or Forums, others in the community can respond and the answers are available for everyone in the future.

Leave a Comment
  • Please add 1 and 4 and type the answer here:
  • Post