GP AA error: creating a Transaction Dimension

(0) Share

Report

Posted on by l-thomas

1,325

Help please!!!

This error message is driving me nuts and there is nothing on Customersource to point me in the right direction to fix it. When attempting to create a new Transaction Dimension for AA I keep getting the following error messages:

Violation of PRIMARY KEY constraint PKAAG00600. Cannot insert duplicate key in object dbo.AAG00660 and The stored procedure aagCreateTree returned the following results: DBMS 2627, Microsoft Dynamics GP.

I'm willing to wipe out my records of AA and start again

*This post is locked for comments

I have the same question (0)

All responses (10)

Answers (0)

Community Member on at

Like (0)

Report

Re: GP AA error: creating a Transaction Dimension

Thomas,

When a dimension is added tree record is saved in table AAG00600. This table has already records for master records

ACCOUNT

CUSTOMER

EMPLOYEE

ITEM

SITE

VENDOR

Therefore, if you try to add dimension with dimension ID same as any of the above mentioned reserved keywords, you will get this error.

If you are not adding transaction dimension ID same as above mentioned keywords, still try varying your dimension ID. Test by appending 1 at end of dimension

Rubal

Smith and Allen Consulting Inc.

Was this reply helpful? Yes No
jakelaux on at

Like (0)

Report

Re: GP AA error: creating a Transaction Dimension

Hello Thomas,

Another possibility is that the DYNAMICS..AAG00102 table has a bad entry in it. If you look at the linked KB there is an example on how to fix this problem for the AAG30000 table:

897280 Error message when you try to post Analytical Accounting transactions in Microsoft Dynamics GP: "Cannot insert duplicate key in object"

mbs.microsoft.com/.../KBDisplay.aspx

The error message in the KB says the AAG30000 table but you should be able to replace that with AAG00600 table and fix the issue.

Thanks,

Jake Laux

Was this reply helpful? Yes No
l-thomas 1,325 on at

Like (0)

Report

Re: GP AA error: creating a Transaction Dimension

Jake, why does Microsoft make this so hard!!!

I had a look at the TK and dont understand what I need to do in Step 3. My error is definitely coming from AAG00600. What exactly do I need to replace because the column (aaGLHdrID) does not exist in this table?

I know my CMPANYID so this is not an issue either. What exactly is this TK attempting to do?

----------------------------------------------------------------------------------------------------------------------------------

1. Open SQL Server Management Studio. To do this, click Start, point to Programs, point to Microsoft SQL Server 2005 or 2008, and then click SQL Server Management Studio.

2. In the Connect to SQL Server window, log in to SQL Server Management Studio by using your sa password.

3. Type the following query in the query window.

select MAX (aaGLHdrID) from AAG30000

*/Insert in the appropriate column/table for the aaGLHdrID and AAG30000 placeholders in the script.

4. Note the query results to view the highest value last used. (If the table has sub-tables such as listed in the Cause section above, make sure to check the highest last used value in those as well.)

5. Type the following script in the query window against the Dynamics database:

select * from AAG00102 where CMPANYID = 'nnn'

Note In this query, replace the nnn placeholder with the company ID. To find the company ID, type the following query in the query window.

select * from SY01500

Select the DYNAMICS database in the list at the top of the window, and then press F5.

6. Verify that the value in the aaRowID field for the aaTableID value of 30000 (or appropriate table) is equal to or greater than the value that you noted in step 4.

7. Use the following script to update the value. To do this, type the script in the query window, and then press F5.

update aag00102 set aaROWID = 'yyyy' where aaTableID = 30000 and CMPANYID = 'zzz'

Note In this script, replace the yyyy placeholder with the value that you noted in step 4. Replace the zzz placeholder with the company ID. Also replace the aaTableID with the appropriate table.

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

Re: GP AA error: creating a Transaction Dimension

This article is general article applicable to lots of Analytical Accounting tables. aaGLHdrID is primary key in table AAG30000. For table AAG00600 key field is aaTreeID

For table AAG00600, you will need to run the following query against your COMPANY DATABASE

update aag00102 set aaROWID = (select max(aaTreeID) from AAG00600)

where aatableID=600 and CMPANYID=-1 --Replace -1 with Company ID

Was this reply helpful? Yes No
l-thomas 1,325 on at

Like (0)

Report

Re: GP AA error: creating a Transaction Dimension

Rubal

How is it possible to update the AAG00102 table and run the script you've recommed against the company DB when the AAG00102 table exists in the DYNAMICS DB?!?!?!?!?!

THIS SCRIPT IS NOT CORRECT!

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

Re: GP AA error: creating a Transaction Dimension

The update script will work against Dynamics database. But, the part to get aaTreeID should be executed against company database. I don't know your database name. So I can't add name qualifier for it in query (select max(aaTreeID) from AAG00600) like I did for table aag00102 i.e dynamics.aag00102. I forgot to add qualifier in periviosi query. Below is the updated query. It should work against company database

update dynamics.dbo.aag00102 set aaROWID = (select max(aaTreeID) from AAG00600)

where aatableID=600 and CMPANYID=-1 --Replace -1 with Company ID

Was this reply helpful? Yes No
l-thomas 1,325 on at

Like (0)

Report

Re: GP AA error: creating a Transaction Dimension

You dont need to know my company DB name. You can use the the default GP test database called TWO in your script.

Either way the script did fix the error message. I still have the issue.

Was this reply helpful? Yes No
Suggested answer

veeyeskay on at

Like (0)

Report

Re: GP AA error: creating a Transaction Dimension

Hi Louelle

Based on our discussion, please run the script on my blog URL

msdynamicstips.com/.../updated-sql-script-for-fixing-primary-key-errors-in-analytical-accounting

which updates the index record if it exists and create it if it does not. This will resolve your issue. :)

Regards

Siva

Was this reply helpful? Yes No
Brian Burke 150 on at

Like (0)

Report

RE: GP AA error: creating a Transaction Dimension

Siva, I am not able to access the link in your post above regarding fixing primary key errors in Analytical Accounting. Can you please reply and include the script in your reply?

Thanks,

Brian

Was this reply helpful? Yes No
Suggested answer

Community Member on at

Like (0)

Report

RE: GP AA error: creating a Transaction Dimension

Brian,

I am able to pull the script in my inbox.

here is the script for fixing Primary Key Errors in Analytical Accounting. Make sure that all the users have logged out of GP before this script is executed.

DECLARE @aaRowID INT

DECLARE @CMPANYID INT

SELECT @CMPANYID = CMPANYID

FROM DYNAMICS.dbo.SY01500

WHERE INTERID = DB_NAME()

--Update the keys for AAG00201 table

SELECT @aaRowID = ISNULL(MAX(aaAcctClassID), 0)

FROM dbo.AAG00201

IF EXISTS ( SELECT *

FROM DYNAMICS.dbo.AAG00102

WHERE aaTableID = 201

AND CMPANYID = @CMPANYID )

BEGIN

UPDATE DYNAMICS.dbo.AAG00102

SET aaRowID = @aaRowID

WHERE aaTableID = 201

AND CMPANYID = @CMPANYID

END

ELSE

BEGIN

IF @aaRowID > 0

BEGIN

INSERT INTO DYNAMICS.dbo.AAG00102

( aaTableID, CMPANYID, aaRowID )

VALUES ( 201, @CMPANYID, @aaRowID )

END

END

--Update the keys for AAG00300 table

SELECT @aaRowID = ISNULL(MAX(aaDistrQueryID), 0)

FROM dbo.AAG00300

IF EXISTS ( SELECT *

FROM DYNAMICS.dbo.AAG00102

WHERE aaTableID = 300

AND CMPANYID = @CMPANYID )

BEGIN

UPDATE DYNAMICS.dbo.AAG00102

SET aaRowID = @aaRowID

WHERE aaTableID = 300

AND CMPANYID = @CMPANYID

END

ELSE

BEGIN

IF @aaRowID > 0

BEGIN

INSERT INTO DYNAMICS.dbo.AAG00102

( aaTableID, CMPANYID, aaRowID )

VALUES ( 301, @CMPANYID, @aaRowID )

END

END

--Update the keys for AAG00310 table

SELECT @aaRowID = ISNULL(MAX(aaMLQueryID), 0)

FROM dbo.AAG00310

IF EXISTS ( SELECT *

FROM DYNAMICS.dbo.AAG00102

WHERE aaTableID = 310

AND CMPANYID = @CMPANYID )

BEGIN

UPDATE DYNAMICS.dbo.AAG00102

SET aaRowID = @aaRowID

WHERE aaTableID = 310

AND CMPANYID = @CMPANYID

END

ELSE

BEGIN

IF @aaRowID > 0

BEGIN

INSERT INTO DYNAMICS.dbo.AAG00102

( aaTableID, CMPANYID, aaRowID )

VALUES ( 310, @CMPANYID, @aaRowID )

END

END

--Update the keys for AAG00400 table

SELECT @aaRowID = ISNULL(MAX(aaTrxDimID), 0)

FROM dbo.AAG00400

IF EXISTS ( SELECT *

FROM DYNAMICS.dbo.AAG00102

WHERE aaTableID = 400

AND CMPANYID = @CMPANYID )

BEGIN

UPDATE DYNAMICS.dbo.AAG00102

SET aaRowID = @aaRowID

WHERE aaTableID = 400

AND CMPANYID = @CMPANYID

END

ELSE

BEGIN

IF @aaRowID > 0

BEGIN

INSERT INTO DYNAMICS.dbo.AAG00102

( aaTableID, CMPANYID, aaRowID )

VALUES ( 400, @CMPANYID, @aaRowID )

END

END

--Update the keys for AAG00401 table

SELECT @aaRowID = ISNULL(MAX(aaTrxDimCodeID), 0)

FROM dbo.AAG00401

IF EXISTS ( SELECT *

FROM DYNAMICS.dbo.AAG00102

WHERE aaTableID = 401

AND CMPANYID = @CMPANYID )

BEGIN

UPDATE DYNAMICS.dbo.AAG00102

SET aaRowID = @aaRowID

WHERE aaTableID = 401

AND CMPANYID = @CMPANYID

END

ELSE

BEGIN

IF @aaRowID > 0

BEGIN

INSERT INTO DYNAMICS.dbo.AAG00102

( aaTableID, CMPANYID, aaRowID )

VALUES ( 401, @CMPANYID, @aaRowID )

END

END

--Update the keys for AAG00402 table

SELECT @aaRowID = ISNULL(MAX(aaTrxDimCodeNumID), 0)

FROM dbo.AAG00402

IF EXISTS ( SELECT *

FROM DYNAMICS.dbo.AAG00102

WHERE aaTableID = 402

AND CMPANYID = @CMPANYID )

BEGIN

UPDATE DYNAMICS.dbo.AAG00102

SET aaRowID = @aaRowID

WHERE aaTableID = 402

AND CMPANYID = @CMPANYID

END

ELSE

BEGIN

IF @aaRowID > 0

BEGIN

INSERT INTO DYNAMICS.dbo.AAG00102

( aaTableID, CMPANYID, aaRowID )

VALUES ( 402, @CMPANYID, @aaRowID )

END

END

--Update the keys for AAG00403 table

SELECT @aaRowID = ISNULL(MAX(aaTrxDimCodeBoolID), 0)

FROM dbo.AAG00403

IF EXISTS ( SELECT *

FROM DYNAMICS.dbo.AAG00102

WHERE aaTableID = 403

AND CMPANYID = @CMPANYID )

BEGIN

UPDATE DYNAMICS.dbo.AAG00102

SET aaRowID = @aaRowID

WHERE aaTableID = 403

AND CMPANYID = @CMPANYID

END

ELSE

BEGIN

IF @aaRowID > 0

BEGIN

INSERT INTO DYNAMICS.dbo.AAG00102

( aaTableID, CMPANYID, aaRowID )

VALUES ( 403, @CMPANYID, @aaRowID )

END

END

--Update the keys for AAG00404 table

SELECT @aaRowID = ISNULL(MAX(aaTrxDimCodeDateID), 0)

FROM dbo.AAG00404

IF EXISTS ( SELECT *

FROM DYNAMICS.dbo.AAG00102

WHERE aaTableID = 404

AND CMPANYID = @CMPANYID )

BEGIN

UPDATE DYNAMICS.dbo.AAG00102

SET aaRowID = @aaRowID

WHERE aaTableID = 404

AND CMPANYID = @CMPANYID

END

ELSE

BEGIN

IF @aaRowID > 0

BEGIN

INSERT INTO DYNAMICS.dbo.AAG00102

( aaTableID, CMPANYID, aaRowID )

VALUES ( 404, @CMPANYID, @aaRowID )

END

END

--Update the keys for AAG00500 table

SELECT @aaRowID = ISNULL(MAX(aaDateID), 0)

FROM dbo.AAG00500

IF EXISTS ( SELECT *

FROM DYNAMICS.dbo.AAG00102

WHERE aaTableID = 500

AND CMPANYID = @CMPANYID )

BEGIN

UPDATE DYNAMICS.dbo.AAG00102

SET aaRowID = @aaRowID

WHERE aaTableID = 500

AND CMPANYID = @CMPANYID

END

ELSE

BEGIN

IF @aaRowID > 0

BEGIN

INSERT INTO DYNAMICS.dbo.AAG00102

( aaTableID, CMPANYID, aaRowID )

VALUES ( 500, @CMPANYID, @aaRowID )

END

END

--Update the keys for AAG00600 table

SELECT @aaRowID = ISNULL(MAX(aaTreeID), 0)

FROM dbo.AAG00600

IF EXISTS ( SELECT *

FROM DYNAMICS.dbo.AAG00102

WHERE aaTableID = 600

AND CMPANYID = @CMPANYID )

BEGIN

UPDATE DYNAMICS.dbo.AAG00102

SET aaRowID = @aaRowID

WHERE aaTableID = 600

AND CMPANYID = @CMPANYID

END

ELSE

BEGIN

IF @aaRowID > 0

BEGIN

INSERT INTO DYNAMICS.dbo.AAG00102

( aaTableID, CMPANYID, aaRowID )

VALUES ( 600, @CMPANYID, @aaRowID )

END

END

--Update the keys for AAG00900 table

SELECT @aaRowID = ISNULL(MAX(aaBudgetTreeID), 0)

FROM dbo.AAG00900

IF EXISTS ( SELECT *

FROM DYNAMICS.dbo.AAG00102

WHERE aaTableID = 900

AND CMPANYID = @CMPANYID )

BEGIN

UPDATE DYNAMICS.dbo.AAG00102

SET aaRowID = @aaRowID

WHERE aaTableID = 900

AND CMPANYID = @CMPANYID

END

ELSE

BEGIN

IF @aaRowID > 0

BEGIN

INSERT INTO DYNAMICS.dbo.AAG00102

( aaTableID, CMPANYID, aaRowID )

VALUES ( 900, @CMPANYID, @aaRowID )

END

END

--Update the keys for AAG10000 table

SELECT @aaRowID = ISNULL(MAX(aaGLWorkHdrID), 0)

FROM dbo.AAG10000

IF EXISTS ( SELECT *

FROM DYNAMICS.dbo.AAG00102

WHERE aaTableID = 10000

AND CMPANYID = @CMPANYID )

BEGIN

UPDATE DYNAMICS.dbo.AAG00102

SET aaRowID = @aaRowID

WHERE aaTableID = 10000

AND CMPANYID = @CMPANYID

END

ELSE

BEGIN

IF @aaRowID > 0

BEGIN

INSERT INTO DYNAMICS.dbo.AAG00102

( aaTableID, CMPANYID, aaRowID )

VALUES ( 10000, @CMPANYID, @aaRowID )

END

END

--Update the keys for AAG20000 table

SELECT @aaRowID = ISNULL(MAX(aaSubLedgerHdrID), 0)

FROM dbo.AAG20000

IF EXISTS ( SELECT *

FROM DYNAMICS.dbo.AAG00102

WHERE aaTableID = 20000

AND CMPANYID = @CMPANYID )

BEGIN

UPDATE DYNAMICS.dbo.AAG00102

SET aaRowID = @aaRowID

WHERE aaTableID = 20000

AND CMPANYID = @CMPANYID

END

ELSE

BEGIN

IF @aaRowID > 0

BEGIN

INSERT INTO DYNAMICS.dbo.AAG00102

( aaTableID, CMPANYID, aaRowID )

VALUES ( 20000, @CMPANYID, @aaRowID )

END

END

--Update the keys for AAG30000 table

SELECT @aaRowID = ISNULL(MAX(aaGLHdrID), 0)

FROM dbo.AAG30000

IF EXISTS ( SELECT *

FROM DYNAMICS.dbo.AAG00102

WHERE aaTableID = 30000

AND CMPANYID = @CMPANYID )

BEGIN

UPDATE DYNAMICS.dbo.AAG00102

SET aaRowID = @aaRowID

WHERE aaTableID = 30000

AND CMPANYID = @CMPANYID

END

ELSE

BEGIN

IF @aaRowID > 0

BEGIN

INSERT INTO DYNAMICS.dbo.AAG00102

( aaTableID, CMPANYID, aaRowID )

VALUES ( 30000, @CMPANYID, @aaRowID )

END

END

--Update the keys for AAG40000 table

SELECT @aaRowID = ISNULL(MAX(aaGLHdrID), 0)

FROM dbo.AAG40000

IF EXISTS ( SELECT *

FROM DYNAMICS.dbo.AAG00102

WHERE aaTableID = 40000

AND CMPANYID = @CMPANYID )

BEGIN

UPDATE DYNAMICS.dbo.AAG00102

SET aaRowID = @aaRowID

WHERE aaTableID = 40000

AND CMPANYID = @CMPANYID

END

ELSE

BEGIN

IF @aaRowID > 0

BEGIN

INSERT INTO DYNAMICS.dbo.AAG00102

( aaTableID, CMPANYID, aaRowID )

VALUES ( 40000, @CMPANYID, @aaRowID )

END

END

--

@dyngp_warren

Was this reply helpful? Yes No

Helpful resources

News and Announcements

Join experts in person to explore low code and AI agents at the Power Platform Community Conference sponsored by Microsoft

Community site session details

GP AA error: creating a Transaction Dimension

Helpful resources

News and Announcements

Quick Links

Responsible AI policies

Abhilash Warrier – Community Spotlight

Congratulations to the September Top 10 Community Leaders!

Leaderboard > 🔒一 Microsoft Dynamics GP (Archived)

Featured topics

Product updates

Community site session details

GP AA error: creating a Transaction Dimension

Helpful resources

News and Announcements

Quick Links

Responsible AI policies

Abhilash Warrier – Community Spotlight

Congratulations to the September Top 10 Community Leaders!

Subscribe to this forum!

Select categories

Leaderboard > 🔒一 Microsoft Dynamics GP (Archived)

Featured topics

Product updates