Share
Report
Hello All,
Here are the system details
- Windows 2000 SP4
- SQL Server 2005 Express
- Microsoft Dynamics GP Professional: 9.00.0352 (Service Pack 4 )
Dexterity: 9.00.0086.0
SmartList: 9.00.0280
Database: SQL Server
System: Windows 2000 Service Pack 4 (2195)
ODBC Driver Manager: 03.52.0000
ODBC Driver: 03.85.1128
Microsoft SQL Server 2005 – 9.00.4053.00 (Intel X86)
we’ve been trying for the past month to solve an issue of a Primary Key violation on a SQL level and am running into a wall and have not been able to find any additional information on line.
Basically our client is having the following problem:
While attempting to do a series posting through GP Dexterity (Transactions -> Sales -> Series Posting), they check off a specific entry and then click “Post” they receive the following error:
**********************
Microsoft Dynamics GP
[Microsoft][ODBC SQL Server Driver][SQL Server]Violation of PRIMARY KEY constraint 'PKAAG10001'. Cannot insert duplicate key in object 'dbo.AAG10001'. [Microsoft][ODBC SQL Server Driver][SQL Server]Violation of PRIMARY KEY constraint 'PKAAG10002'. Cannot…<rest is cut-off>
**********************
And upon closing that message box, this message box appears…
**********************
The stored procedure aagCreateGLWorkSLRealTimePost returned the following results: DBMS 2627, Microsoft Dynamics GP: 2627.
**********************
This is repeated throughout the entire set of items in the series posting but once it is completed (100%) the posting is displayed and appears to be correct in all aspects.
Now I’ve done some digging and in a dev environment (basically a VM reproduction of the Live production server) and have gone through the following experiments:
1) Ran standard “Check Links” process on the entire database. Corrected some issues and re-ran the Series Post. No joy.
2) Used SDK information to check what tables/ processes I’m dealing with. Basically the General Ledger and Analytical Accounting tables.
3) Ran check to confirm that the associated existing ID in the [DYNAMICS].[dbo].[AAG00102] table are ok regarding the IDs used within the GLXXXXX and AAGXXXXX tables. The IDs (max) for the associated tables did match. Is this wrong? Should the “watch” tables ([DYNAMICS].[dbo].[AAG00102]) ids be one greater than those MAX id’s that are active (contained in the AGGXXXXX and GLXXXXX tables)?
4) Attempted various experimental methods of clearing out the Analytical Accounting tables (AAG10000, AAG10001, AAG10002 and AAG10003) and rerunning the process to see if it was bad data sitting in those tables that were causing the issue, no joy.
5) Ran duplicate checks. All OK
6) Ran DEXSQL logging and got reports. Other than the additional error shown in “DEXSQL.LOG Excerpt 2”, the violations correspond with that which is displayed in the interface. No help
7) checked out GP stored proc’s aagCreateGLWorkSLRealTimePost and aagGetNextID to get idea of what happens during processes. Tried samples to see what happens for process.
8) Created query to report on relations between the GL tables and AA tables. Found that the majority appeared to have correct relationships BUT there were cases where a given GL would only have entries in the AAG10000 table but not AAG10001, AAG10002 or AAG10003.
9) Tried removing specific entries that looked “suspicious” and re-ran post. No joy.
10) Upgrading to rollup 14. No joy
11) no batches are hung up in the sys00800 table.
This is really frustrating so if anyone can shed some light or any additional path I could take, would be extremely grateful.
I can provide full DEXSQL log if required.
Thanks
DEXSQL.LOG Excerpt 1
*************************
…
/* Date: 01/19/2011 Time: 16:51:20
stmt(20416560):*/
BEGIN DECLARE @stored_proc_name char(39) DECLARE @retstat int DECLARE @param3 tinyint set nocount on SELECT @param3 = 0 SELECT @stored_proc_name = 'SBM01.dbo.aagCreateGLWorkSLRealTimePost' EXEC @retstat = @stored_proc_name 1, 30915, @param3 OUT SELECT @retstat, @param3 set nocount on END
/* Date: 01/19/2011 Time: 16:51:20
stmt(20416560):*/
BEGIN DECLARE @stored_proc_name char(39) DECLARE @retstat int DECLARE @param3 tinyint set nocount on SELECT @param3 = 0 SELECT @stored_proc_name = 'SBM01.dbo.aagCreateGLWorkSLRealTimePost' EXEC @retstat = @stored_proc_name 1, 30915, @param3 OUT SELECT @retstat, @param3 set nocount on END
/* Date: 01/19/2011 Time: 16:51:20
stmt(20416560):*/
BEGIN DECLARE @stored_proc_name char(39) DECLARE @retstat int DECLARE @param3 tinyint set nocount on SELECT @param3 = 0 SELECT @stored_proc_name = 'SBM01.dbo.aagCreateGLWorkSLRealTimePost' EXEC @retstat = @stored_proc_name 1, 30915, @param3 OUT SELECT @retstat, @param3 set nocount on END
/*
/* Date: 01/19/2011 Time: 16:51:20
SQLSTATE:(23000) Native Err:(2627) stmt(20416560):*/
[Microsoft][ODBC SQL Server Driver][SQL Server]Violation of PRIMARY KEY constraint 'PKAAG10001'. Cannot insert duplicate key in object 'dbo.AAG10001'.*/
/*
/* Date: 01/19/2011 Time: 16:51:20
SQLSTATE:(01000) Native Err:(3621) stmt(20416560):*/
[Microsoft][ODBC SQL Server Driver][SQL Server]The statement has been terminated.*/
/*
/* Date: 01/19/2011 Time: 16:51:20
SQLSTATE:(23000) Native Err:(2627) stmt(20416560):*/
[Microsoft][ODBC SQL Server Driver][SQL Server]Violation of PRIMARY KEY constraint 'PKAAG10002'. Cannot insert duplicate key in object 'dbo.AAG10002'.*/
/*
/* Date: 01/19/2011 Time: 16:51:20
SQLSTATE:(01000) Native Err:(3621) stmt(20416560):*/
[Microsoft][ODBC SQL Server Driver][SQL Server]The statement has been terminated.*/
/*
/* Date: 01/19/2011 Time: 16:51:20
SQLSTATE:(23000) Native Err:(2627) stmt(20416560):*/
[Microsoft][ODBC SQL Server Driver][SQL Server]Violation of PRIMARY KEY constraint 'PKAAG10001'. Cannot insert duplicate key in object 'dbo.AAG10001'.*/
/*
/* Date: 01/19/2011 Time: 16:51:20
SQLSTATE:(01000) Native Err:(3621) stmt(20416560):*/
[Microsoft][ODBC SQL Server Driver][SQL Server]The statement has been terminated.*/
/*
/* Date: 01/19/2011 Time: 16:51:21
SQLSTATE:(23000) Native Err:(2627) stmt(20416560):*/
[Microsoft][ODBC SQL Server Driver][SQL Server]Violation of PRIMARY KEY constraint 'PKAAG10002'. Cannot insert duplicate key in object 'dbo.AAG10002'.*/
/*
/* Date: 01/19/2011 Time: 16:51:21
SQLSTATE:(01000) Native Err:(3621) stmt(20416560):*/
[Microsoft][ODBC SQL Server Driver][SQL Server]The statement has been terminated.*/
/*
/* Date: 01/19/2011 Time: 16:51:21
SQLSTATE:(00000) Native Err:(3621) stmt(20416560):*/
*/
/*
/* Date: 01/19/2011 Time: 16:51:21
SQLSTATE:(00000) Native Err:(3621) status(54):*/
SWSTATUS DUMP*/
/* Date: 01/19/2011 Time: 16:51:30
stmt(20416560):*/
EXEC DYNAMICS.dbo.zDP_SY01700SS_1 2, 35100
…
*************************
DEXSQL.LOG Excerpt 2
*************************
…
/* Date: 01/19/2011 Time: 16:44:59
stmt(20341864):*/
use SBM01
/*
/* Date: 01/19/2011 Time: 16:44:59
SQLSTATE:(01000) Native Err:(5701) stmt(20341864):*/
[Microsoft][ODBC SQL Server Driver][SQL Server]Changed database context to 'SBM01'.*/
/*
/* Date: 01/19/2011 Time: 16:44:59
SQLSTATE:(00000) Native Err:(5701) stmt(20341864):*/
*/
/*
/* Date: 01/19/2011 Time: 16:44:59
SQLSTATE:(00000) Native Err:(5701) status(0):*/
SWSTATUS DUMP*/
/* Date: 01/19/2011 Time: 16:45:00
stmt(20341864):*/
select count(*) from dbo.sysobjects where id = object_id(N'[dbo].[SCPItemMap]' ) and OBJECTPROPERTY(id, N'IsUserTable') = 1
/* Date: 01/19/2011 Time: 16:45:00
stmt(20349024):*/
SELECT TOP 25 CMPANYID,EXPRINST,PRICEOPT,PALOPT,DEX_ROW_ID FROM DYNAMICS.dbo.SY05501 WHERE CMPANYID = 1 ORDER BY CMPANYID ASC
/* Date: 01/19/2011 Time: 16:45:00
stmt(20351488):*/
BEGIN DECLARE @num int EXEC DYNAMICS.dbo.zDP_POA40001SI 1, 0, @num OUT SELECT @num END
/*
/* Date: 01/19/2011 Time: 16:45:01
SQLSTATE:(23000) Native Err:(2627) stmt(20351488):*/
[Microsoft][ODBC SQL Server Driver][SQL Server]Violation of PRIMARY KEY constraint 'PKPOA40001'. Cannot insert duplicate key in object 'dbo.POA40001'.*/
/*
/* Date: 01/19/2011 Time: 16:45:01
SQLSTATE:(01000) Native Err:(3621) stmt(20351488):*/
[Microsoft][ODBC SQL Server Driver][SQL Server]The statement has been terminated.*/
/*
/* Date: 01/19/2011 Time: 16:45:01
SQLSTATE:(00000) Native Err:(3621) stmt(20351488):*/
*/
/*
/* Date: 01/19/2011 Time: 16:45:01
SQLSTATE:(00000) Native Err:(3621) status(54):*/
SWSTATUS DUMP*/
/* Date: 01/19/2011 Time: 16:45:01
stmt(20350408):*/
EXEC DYNAMICS.dbo.zDP_POA40001SS_1 1
/* Date: 01/19/2011 Time: 16:45:02
stmt(20351488):*/
EXEC SBM01.dbo.zDP_CPO40001F_1 1,1
/* Date: 01/19/2011 Time: 16:45:02
stmt(20352568):*/
EXEC DYNAMICS.dbo.zDP_SY02000SS_1 1, '<user>', 3180, 2, 22061
/* Date: 01/19/2011 Time: 16:45:03
stmt(20352568):*/
EXEC DYNAMICS.dbo.zDP_SY00302SS_1 0
/* Date: 01/19/2011 Time: 16:45:03
stmt(20353648):*/
EXEC DYNAMICS.dbo.zDP_SY02000SS_1 1, '<user>', 3180, 2, 22126
…
*************************
*This post is locked for comments
All responses (
Answers (
291,280
