Does anyone used recently the PSTL utility to apply changes to GL accounts in GP2010?
I have the error coming on screen when trying to use the PSTL tool to change GL Accounts from one segment to another... Renaming accounts (non-existant destination) seems to work fine, but Accounts that need to be merged with an existing account poses a serious issue and can't be converted. I've tried both avenues (file import or direct input) and none of them work.... I looked at the GL00100 table and can't find custom triggers aside of the one put in place by the PSTL (starting with ta... ). I also see 3 triggers starting with glp... and that covers the inset, delete and update actions. They do look like coming from GP, but I couldn't say for sure. They have a date of creation quite recent (when the last YE Tax update was applied)... but they seem to have been there for a while.
I did run also a DEX trace log, but the last statement that is called before the error is a stored procs... : zDP_SY02100SS_1 and then right after an SP declare : BEGIN DECLARE @stored_proc_name char(28) DECLARE @retstat int DECLARE @param5 int set nocount on SELECT @stored_proc_name = 'FTI.dbo.smChangeAccountIndex' EXEC @retstat = @stored_proc_name '2274', '2435', 2274, 2435, @param5 OUT SELECT @retstat, @param5 set nocount on END
after which I get the SQL nested error message :
/* Date: 03/06/2013 Time: 13:39:42SQLSTATE:(37000) Native Err:(217) stmt(13379536):*/[Microsoft][SQL Server Native Client 10.0][SQL Server]Maximum stored procedure, function, trigger, or view nesting level exceeded (limit 32).*/
Any ideas where to start look for ?
-- Enjoy the Life+-+-+-+-+-+-+-+ Beat BUCHER Dynamics GP 2010R2 Business Analystwww.fti-ibis.com +-+-+-+-+-+-+-+
A few things:
1. You can't change the server configuration Nesting Level Limit - 32 is it.
2. Many of the PSTL stored procedures are stored in SQL Server as encrypted. The details of what they do can't even be seen in SQL Profiler. Your DEX trace might actually be returning more detail than SQL would. I suspect decrypting the Stored Procedure violates licencing - so don't do it.
3. In your DEX trace, can you see the broader pattern where one stored procedure calls the next stored procedure calls the next...(32 times, like a cascade)? How many accounts are you trying to merge at once? Have you tried one (1) account at a time (even if it is painfully slow)? The Dynamics GP code usually doesn't use recursive SQL calls (I think Btreive and C-tree couldn't even do it). However, I have seen loops caused by triggers and cursors.
In a test environment, you could add a bit of code to a stored procedure to help you trace. In SMSS, script the stored procedure as a drop and replace. Use SQL formatter to reformat the code. Add some SQL Print statements to help you see where you are. The DEX log will show you the Printed statements. @@NESTLEVEL in SQL will return the current nesting level.
Other Microsoft Sites
I'm a Customer
I'm a Partner
Use the official Twitter tags:
#MSDYNCOMM | #CONV13