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

Today, 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:

• Error message when you work in Microsoft Dynamics GP, apply an update, or install an additional module: ”Maximum stored procedure, function, trigger, or view nesting level exceeded (limit 32)” (KB 2102990)

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, https://opensource.org/licenses/ms-pl.html.)

Comments

• Anonymous
  May 02, 2012
  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

• Anonymous
  May 07, 2012
  Posting from Janakiram at DynamicsBlogger dynamicsblogger.com/sql-error-maximum-stored-procedure-function-trigger-or-view-nesting-level-exceeded-limit-32

• Anonymous
  May 07, 2012
  Posting from Mark Polino at DynamicAccounting.net msdynamicsgp.blogspot.com.au/.../sql-error-maximum-stored-procedure.html

• Anonymous
  August 08, 2012
  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

• Anonymous
  August 08, 2012
  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

• Anonymous
  July 29, 2013
  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

• Anonymous
  January 06, 2015
  Hi David, I am also facing same issue while I am entering new  customer in dynamic gp 2013 sp1. I am getting following error : save operation on table 'RM_Customer_MSTR' failed accessing SQL data. [Microsoft][SQL Server Native Client 10.0][SQL Server]The server principal "sgxxxx" is not able to access the database "DYNAMICS" under the current security context. EXCEPTION_CLASS_DBA DB_ERR_SQL_DATA_ACCESS_ERR Please let me know if I  have to use same resolutions or it will  have something different. Regards, Sanjay

• Anonymous
  January 30, 2015
  Hi Sanjay Sounds like you have a trigger on the table that is causing an error. David

• Anonymous
  March 17, 2015
  Exactly what I was looking for!

• Anonymous
  June 23, 2015
  I just had this error : A save operation on table 'RM_Customer_MSTR'   I got this because the audit trail database was not available. As soon as I restored it from the production company, the error went away.

• Anonymous
  July 19, 2015
  Hi Paul The Audit Trail functionality uses triggers on the tables. Without the audit trail database, the trigger would have failed. As the update statement and all triggers are handled as a transaction, it would cause the save to fail as well... hence the error. It is important that triggered code works without errors (or infinite nesting) to avoid breaking Dynamics GP. David

• Anonymous
  August 14, 2015
  Just to the database property and say  "Allow Triggers to Fire Others" make equal to False

• Anonymous
  August 20, 2015
  Hi Ysidro Changing that option might break other functionality. Best to fix the root cause of the issue rather than just hide it. David