Dynamics AX Cannot drop the index 'WMSSHIPMENT _dta_index_WMSSHIPMENT

Cannot drop the index 'WMSSHIPMENT _dta_index_WMSSHIPMENT Dynamics AX

Many times happens while running the forms or Synchronization error Dynamics AX.

The reason behind this is index created which is not found while the synchronization run. during synchronise it drop the index and recreate. the hypothetical index (_dta) created by SQL for analysing tuning.

In the most cases, this error comes when data base restored. Run below script for the data base from SQL

DROP INDEX WMSSHIPMENT._dta_index_WMSSHIPMENT

WHILE EXISTS (SELECT * FROM sys.indexes WHERE is_hypothetical = 1) 
BEGIN 
 DECLARE @sql varchar(max) 
 SELECT @sql = 'drop index ' + indexname + ' on [' + tablename + ']' 
 FROM (
SELECT TOP 1 indexname = i.name, tablename = o.name 
FROM sys.indexes i 
INNER JOIN sys.objects o 
ON i.object_id = o.object_id 
WHERE i.is_hypothetical = 1 and o.type = 'u'
) x 
 SELECT @sql 
 EXEC (@sql) 
END 
GO 

WHILE EXISTS (SELECT * FROM sys.stats i WHERE OBJECTPROPERTY(i.[object_id],'IsUserTable') = 1 AND i.[name] LIKE '_dta%' and user_created = 0) 
BEGIN 
 DECLARE @sql varchar(max) 
 SELECT @sql = 'drop statistics [' + object_name(i.[object_id]) + '].['+ i.[name] + ']' 
 FROM sys.stats i 
 WHERE 
OBJECTPROPERTY(i.[object_id],'IsUserTable') = 1 AND 
i.[name] LIKE '_dta%' and user_created = 0 
 SELECT @sql 
 EXEC (@sql) 
END