Microsoft Dynamics AX (Archived)

Synchronize database Error during upgrade

(0) Share

Report

Posted on by w.sallam

2,259

I'm making data upgrade from AX 2009 to AX 2012 R3.

I went through the 'Data Upgrade Checklist' in AX 2012 R3,

the last step is to ' Synchronize database (Required) ', but I got the flowing errors description

SQL error description: [Microsoft][SQL Server Native Client 11.0][SQL Server]The CREATE UNIQUE INDEX statement terminated because a duplicate key was found for the object name 'dbo.VENDPACKINGSLIPJOUR' and the index name 'I_500SDHEADERIDX'. The duplicate key value is (5637144576, 0, maef).

SQL error description: [Microsoft][SQL Server Native Client 11.0][SQL Server]The CREATE UNIQUE INDEX statement terminated because a duplicate key was found for the object name 'dbo.DIRPARTYTABLE' and the index name 'I_041KEY'. The duplicate key value is (5637144576, <NULL>, 41).

can any one help me with that?

thanks a lot.

*This post is locked for comments

I have the same question (0)

All responses (9)

Answers (0)

Suggested answer

Community Member on at

Like (0)

Report

Perform the following steps:

1. Truncate the table

Run the below command

truncate table tablename

2. Restart AOS

3. Perform DB Synchronization.

Was this reply helpful? Yes No
Sohaib Cheema 49,438 User Group Leader on at

Like (1)

Report

Truncation of table means you are deleting data also for that table.

In case of upgrade I believe data is important.

By the way, your error message seems because of data duplication, which is violating primary index.

Sometimes it happens in tables like batch table, where system has records of old system+new system creates its own records and result is what as you are receiving.

There is something wrong with your data of VENDPACKINGSLIPJOUR

Was this reply helpful? Yes No
André Arnaud de Cal... 301,109 Super User 2025 Season 2 on at

Like (1)

Report

Hi Wasim,

I would not start truncating any table like Shaik mentioned. This could cripple your current database upgrade process.

Did you had some customizations in AX2009 that are now different in AX2012?

You have to find which records causes the duplicates. The error details provides you the table names and the record id's.

The first issue is related to a SourceDocumentHeader relation in this table. Probably by accident AX issued a duplicate ID or the value is 0 for some records.

Maybe the best is have a technical consultant onsite to help you out.

Was this reply helpful? Yes No
Suggested answer

dekaasboer 1,530 on at

Like (0)

Report

I know it is april fools day but truncating the data you want to upgrade is not really a solution.

It seems that you have duplicate recid's. This can be corrected manually by assigning the next recid value from the SYSTEMSEQUENCES table. (is sorted by table identifier, not name)

Please note that many things are joined together by recid. Carefully map the relationships of the recid you want to change in order to keep the database integrity.

If you just want to complete the checklist and worry about solving your data integrity problems later you could temporarily disable the offending indexes in the AOT.

Was this reply helpful? Yes No
w.sallam 2,259 on at

Like (0)

Report

thanks all for reply.

I tried solutions biased on the ideas you gave.

but still can't synchronize.

the indexes 'I_500SDHEADERIDX' and 'I_041KEY' are not appearing in the 'VENDPACKINGSLIPJOUR' and 'DIRPARTYTABLE' tables.

should I create it manually?

Was this reply helpful? Yes No
Sohaib Cheema 49,438 User Group Leader on at

Like (0)

Report

you should check your data inside SQL Server Management studio using query. Analyze it with respect to Current Unique key. You will find current key in AOT>>Tables>>YourTable>>Indexes. take index which is defined as primary or alternative in table properties

Was this reply helpful? Yes No
w.sallam 2,259 on at

Like (0)

Report

as I'm in the synchronize phase I can't open AOT in AX,

I check the tables in SQL Server

Was this reply helpful? Yes No
Sohaib Cheema 49,438 User Group Leader on at

Like (0)

Report

On AX2012R3 the Primary key for VENDPACKINGSLIPJOUR (Table) is a composite primary key which is based on following two columns

SourceDocumentHeader

RecId

Whereas on AX2009 the primary index for this table is RecId

In AX 2009, SourceDocumentHeader and SourceDocumnetLine tables do not exist.

So I doubt it a case where system is trying to synch before, where data is same as of AX2009

Was this reply helpful? Yes No
Sohaib Cheema 49,438 User Group Leader on at

Like (0)

Report

following URL is almost very clear indication of your issue.

msdn.microsoft.com/.../jj719039.aspx

it's the issue of synchronization prior to Preprocessing Data Upgrade Scripts for Microsoft Dynamics AX 2012

Was this reply helpful? Yes No