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
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.
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.
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?
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?
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.
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)