Skip to content
+1-888-319-3663

COMMUNITY FORUM

Duplicate Key error in DexSQL log when Opening Smart list after upgraded from GP10 to 2013

May asked 7 years ago
Hi,
We are testing GP 2013 and found out that it is really slow to open the windows for a user that have quite a few smart list reminders.

By looking at the DexSQL log, there are lots of records that saying duplicate key row in a table. Not sure if that is related to the speed issue?  How to fix the erros, please?  The log detail as below:

/*  Date: 05/15/2014  Time: 14:25:48
stmt(99999024):*/
select count(*) from DYNAMICS..ASIEXP81 where ASI_Favorite_Dict_ID = 3107 and ASI_Favorite_Type = 3 and ASI_Favorite_Save_Level = 0
/*
/*  Date: 05/15/2014  Time: 14:25:48
SQLSTATE:(01000) Native Err:(5701) stmt(0):*/
[Microsoft][SQL Server Native Client 11.0][SQL Server]Changed database context to 'master'.*/
/*
/*  Date: 05/15/2014  Time: 14:25:48
SQLSTATE:(01000) Native Err:(5703) stmt(0):*/
[Microsoft][SQL Server Native Client 11.0][SQL Server]Changed language setting to us_english.*/
/*
/*  Date: 05/15/2014  Time: 14:25:48
SQLSTATE:(00000) Native Err:(5703) stmt(0):*/
*/
/*
/*  Date: 05/15/2014  Time: 14:25:48
SQLSTATE:(00000) Native Err:(5703) status(0):*/
SWSTATUS DUMP*/

/*  Date: 05/15/2014  Time: 14:25:48
stmt(99987712):*/
BEGIN  SET DATEFORMAT ymd
SET NOCOUNT ON
 SET CURSOR_CLOSE_ON_COMMIT OFF
 SET ANSI_NULLS ON
 SET ANSI_WARNINGS OFF
 SET ANSI_NULL_DFLT_ON ON
 SET ANSI_PADDING ON
 SET NUMERIC_ROUNDABORT OFF
END

/*  Date: 05/15/2014  Time: 14:25:48
stmt(99999024):*/
update DYNAMICS..ASIEXP81 set ASI_Favorite_Name = 'RMA Tracking and Processing Header' where ASI_Favorite_Dict_ID = 3107 and ASI_Favorite_Type = 3 and ASI_Favorite_Save_Level = 0

/*  Date: 05/15/2014  Time: 14:25:48
stmt(99986096):*/
{ CALL PROD.dbo.zDP_EXT20200SS_1 ( 'CK RMA' ) }

/*  Date: 05/15/2014  Time: 14:25:48
stmt(99986096):*/
{ CALL PROD.dbo.zDP_EXT20200SS_1 ( 'CK RMA' ) }

/*  Date: 05/15/2014  Time: 14:25:48
stmt(99964640):*/
{CALL DYNAMICS.dbo.zDP_ASIEXP86L_1(3107,3,0,0,'','','',-32768,3107,3,0,0,'','','',32767)}

/*  Date: 05/15/2014  Time: 14:25:48
stmt(99999024):*/
BEGIN DECLARE @num int EXEC DYNAMICS.dbo.zDP_ASIEXP86SI 3107, 3, 0, 0, '', '', '', 9, 1, 1, 3107, 2, 'RMA Number', 0, @num OUT SELECT @num END
/*
/*  Date: 05/15/2014  Time: 14:25:48
SQLSTATE:(23000) Native Err:(2601) stmt(99999024):*/
[Microsoft][SQL Server Native Client 11.0][SQL Server]Cannot insert duplicate key row in object 'dbo.ASIEXP86' with unique index 'AK2ASIEXP86'. The duplicate key value is (3107, 3, 0, 0,                ,                ,                                  */
/*
/*  Date: 05/15/2014  Time: 14:25:48
SQLSTATE:(01000) Native Err:(3621) stmt(99999024):*/
[Microsoft][SQL Server Native Client 11.0][SQL Server]The statement has been terminated.*/
/*
/*  Date: 05/15/2014  Time: 14:25:48
SQLSTATE:(00000) Native Err:(3621) stmt(99999024):*/
*/
/*
/*  Date: 05/15/2014  Time: 14:25:48
SQLSTATE:(00000) Native Err:(3621) status(54):*/
SWSTATUS DUMP*/


Answers
Best Answer
Nicole answered 7 years ago
It appears that the duplicate key errors are when it is trying to make sure that the Extender tables set to publish to SmartLists are there. It doesn’t appear though to be a performance issue. It is just finding that the record is already there and moving on. Generally with the reminders in SmartList, you will see a time delay in the log somewhere as it tries to do a calculation or figure out if the reminder needs to be displayed or not. My recommendation would be to look at the log for points where the time takes a while on a statement. That would be more likely where the issue is. You may also want to refer to Knowledge Base 898982 from Microsoft. There is a section in it titled “Performance issues that occur when you log on to Microsoft Dynamics GP” that talks about the issues with reminders.
May answered 7 years ago
Thanks for your quick reply Nicole!
We've followed the instructions in Knowledge Base 898982 before we found out that the smart list reminders slowing down everything.

To find out if there is a time delay, is there any key words you would suggest to look for in the log,please?
Nicole answered 7 years ago
There really isn’t a key word, you have to look at all the time values before each call and see where there is a gap in time to figure out which call is taking a while. Otherwise, you can note what the reminder does and one by one remove them to see if there is a particular one that is taking all the time.
May replied 7 years ago

Thanks Nicole.

Finally we found out that there are smart lists that using UMBC0001 table that slows down  parts of the system (e.g.: user login/log out/opening windows in the navigation pane).

So the issue now is that we don't understand why the smart list will affect the windows in the navigation pane (for example: User Preferences window). The first time when it is opened, it's pretty fast, and after that it slows down. Comparing the logs show that, the second time the window is open, it shows  some logs that checking the smart lists.

Any idea why that is happening,please?

Thanks!

Nicole answered 7 years ago
I am not sure what the UMBC0001 is and what data it holds as it appears it may be a part of a 3rd party product? As to why calls to it are slowing things down, this is hard to say without looking at the logs, what the SmartList calls and setup is, and knowing that table and its purpose. If it was just reminders, I would say remove those reminders and see if other things are still slow. If it is, then the shortcut bar in general would have to be looked at to see what is going on. This might be something that is going to require more than the support we can offer and would need to go to Microsoft of the ISV that the UMBC0001 table belongs to, but we can certainly look at it if you would like. You can work with our support team by calling 888-319-3663 or e-mailing support@eonesolutions.com.
May replied 7 years ago

Yes, that table is a 3rd part table (and it's a realy big table), we know that the smart list using that table is slow. But didn't expect that it will slow down other window's performance.

We'll contact our 3rd party provider to see if they have any clue.

Thanks agin for all your help!


If you would like to submit an answer or comment, please sign in to the eOne portal.