SQL Synchronization failed on one table

Finance and Operations/AX Technical Forum
1

I have remove the liscense for certain module. After that, I synchronize the database again but it return an error like this: Cannot execute a data definition language command on ().
The SQL database has issued an error.
Problems during SQL data dictionary synchronization.
The operation failed.
Synchronize failed on 1 table(s)

I suspect that it is cause by the InventDim table. There is one index I_698DIMIDX cannot be located. I notice that it does not exist in the database index when compare with AOT plus one column Wmspalletid is missing also. Because of this, it cause problem in generating report under inventory management.

I have try to create the index at the sql and also delete the wmspalletid field from AOT. Both solutions doesn’t work.

Does anyone know why and how to solve this problem? Please advice.
Thank you

Regards,
Mandy

2

Hi Mandy,

To resolve the issue, please try the following -

  1. Go to administration → Periodic → SQL administration form

  2. Select the table and click Table actions → Check/Synchronise

  3. First click ‘start’ with the default options.

If the issue is not resolved, then select ‘remove unknown indexes’ in this form.

Hope this helps,

3

Still problect exist even after following your step Harish. and I didnot get the select ‘remove unknown indexes’ option in that form.

I’m using Ax 2009.

Thanx,

Ambanna Yatnal

4

Hi Ambanna,

Check in Event viewer (Both SQL and AOS servers if they are running separately). Most likely this error is due to index violation. If this is the case, this would be recorded in Event viewer.

Regards,

5

Thanx Harish,

Error message in Eventviewer is saying “The maximum limit for index or statistics key column list is 16”. But this is system table I dont want to alter it. so any other solution you know please let me know.

Regards,

Ambanna Yatnal

6

Hi Ambanna,

Can you let us know following -

  1. Ax application / kernel version
  2. List of Ax tables mentioned in the error log

Regards,

7

Hi Harish,

ya sure , these are the following details,

  1. AX 2009, SP1 and KR3

  2. Tables:

i)InventCostTransSum ii) LedgerBalancesDimTrans iii) LedgerBalancesTransDelta.

Thanx,

Ambanna Yatnal

8

Hi Ambanna,

As the error message indicates, the maximum columns for index is 16 in SQL Server. In your case, this limit has been breached. I checked these tables in standard Ax 2009 KR2 and they are well within the limit.

Therefore I guess yours must have been modified. In your system, have you increased financial dimensions?

Regards,

9

Yes Harish, Dimensions are increased. I’ve asked my clients to clarify on that if they said not required all the dimensions issue will be solved but if they said they need all the dimensions, I’m not getting what to do?

Thanx,

Ambanna Yatnal

10

Hi Ambanna,

As I mentioned earlier, the limit is set by SQL server. There is nothing you can do other than reducing number of dimensions.

I say this from past experience. In a previous project, we ran into same situation. When escalated, MS support told us the same.

Regards,

11

OK Harish.

Thank you very much. Just I’ll check with our clients and convience them regarding this.

Thank you,

Ambanna Yatnal

12

Dear Harish,

I am also facing the same issue… Do we have any other solution now or is it the same one???

your help is appreciated!!!

13

Hi,

Welcome to DUG [:)]

If you are referring to Dimension error, then to my knowledge there is no solution in DAX 2009 other than to reduce the dimensions.

14

Thank you for the reply!!!..

I checked with the event viewer and found that it says there are duplicate entries with respect to InventDim in CEU company when I query the table to find out duplicate records I dont find any and then when change the property of index DIMIDX in AX as allow duplicates the table synchronizes and then again when I revert the condition and synchronize it throws up an error.

Any Idea how can we resolve this.

Thank you in advance !!

Your help is appreciated!!

Thanks and Regards

Nikhil Pujar

15

Dear Harish,

Below is the message in event viewer

Object Server 01:
[Microsoft][SQL Native Client][SQL Server]The CREATE UNIQUE INDEX statement terminated because a
duplicate key was found for the object name ‘dbo.INVENTDIM’ and the index name ‘I_698DIMIDX’. The
duplicate key value is (ceu, , , , , 22, , 01-01-01-1, ).
CREATE UNIQUE INDEX I_698DIMIDX ON INVENTDIM (DATAAREAID,CONFIGID,INVENTSIZEID,INVENTCOLORID,INVENTSITEID,INVENTLOCATIONID,INVENTBATCHID,WMSLOCATIONID,INVENTSERIALID)
session 3 (nikhi)

Hope you can help me with this!!

Thank you!!

16

go to inventory

then periodic

clean up

clean diementions

17

if problem persists

then use code to find duplicate records in 2009 tables

select field1,field2,field3, count (*) from table

group by field1,field2,field3

having count(*) >1

query return duplicate records then you can treat them as per your scenario