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_DBDB_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:
ALTER trigger [dbo].[tr_UpdateAddressShipTaxtoDebtorMain] on [dbo].[RM00101]after insert, update as
update RM00102set SHIPMTHD = RM00101.SHIPMTHD , TAXSCHID = RM00101.TAXSCHIDfrom RM00101where RM00101.CUSTNMBR = RM00102.CUSTNMBRand 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:
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.
update a set SHIPMTHD = c.SHIPMTHD, TAXSCHID = c.TAXSCHIDfrom RM00102 ajoin inserted i on i.CUSTNMBR = a.CUSTNMBRjoin RM00101 c on c.CUSTNMBR = a.CUSTNMBRwhere (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.
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.
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.
// 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.)
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.
Posting from Janakiram at DynamicsBlogger
Posting from Mark Polino at DynamicAccounting.net
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
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.
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 :
Take care !
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.
Please let me know if I have to use same resolutions or it will have something different.
Sounds like you have a trigger on the table that is causing an error.
Exactly what I was looking for!
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.
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.
Just to the database property and say "Allow Triggers to Fire Others" make equal to False
Changing that option might break other functionality. Best to fix the root cause of the issue rather than just hide it.
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.