All Dynamics AX customers restore transaction database from one environment ( e.g. Production environment ) to another ( e.g. Test environment)
There are situations when the object IDs in the Model database of the two environments do not match resulting in issues such as data loss during the synchronisation.
I stumbled on the Johan’s blog post (reference at the end of this post) to find a light on how to address these challenges quickly. It provided a good start but we required to change the script to suite the special situation when transnational database is restored from a production to non production environment and objects IDs do not match between the two applications
/*
Only to update the SQL dictionary records before the synchronisation after the database restore . No new record in SQL dictionaty will be created through this job.
This will only update existing Ids in the SQL dictionary to match the Model store Ids
Objects that are new in AOT will get created in SQL dictionary when synchonisation happens
**USE AT YOUR OWN RISK**
** Please Change database Name to your own DB names when testing the following script and DB collation **
Case 1 – Table Name matches but table ID is different – Need to fix table ID in SQL dictionary where fields ID is 0 and !0 for such table names
Case 2 – Field Ids name matches but field Id(s) are different – Need to Fix Field IDs in SQL dictionary to match AOT
step 1 – update tableid on tables and fields. Update SQL dictionary mismatched IDs = (ID in AOT)*(-1). Then change the negatives to Postives
step 2 – fieldid on fields Updated negative entry from 1 and 2 to positive. Update SQL dictionary mismatched IDs = (ID in AOT)*(-1).
Then change the negatives to Postives
*/
—
USE AX2012TestDB
GO
— Backup the existing SQL dictionary as precaution
select *
into AX2012TestDB.dbo.SQLDICTIONARYBKP
from AX2012TestDB.dbo.SQLDICTIONARY
USE AX2012TestDB_Model
GO
–Step 1 Find tables in SQLDICTIONARY that have same name as in AOT but different ID and then update = ModelstoreID in SQLDICTIONARY for Table and fields records
With t as (SELECT m.ElementHandle,m.NAME mName,m.AxId,md.LegacyId,s.TABLEID,s.NAME sName,s.SQLNAME
FROM modelelementdata md,ModelElement m
LEFT OUTER JOIN AX2012TestDB..SQLDictionary s
ON upper(m.NAME) collate Latin1_General_CI_AS = s.NAME
WHERE m.ElementType = 44
AND m.elementhandle = md.elementhandle
AND s.ARRAY = 0
AND s.FIELDID = 0
AND s.TABLEID != m.AxId
)
–Update to correct * -1
update AX2012TestDB.dbo.SQLDICTIONARY
set TABLEID = (t.axid * -1)
from t
join AX2012TestDB.dbo.SQLDICTIONARY s
on t.tableid = s.tableid
go
–verify SQLDICTIONARY that have negative IDs for change to positive
with t as
(SELECT m.ElementHandle,m.NAME mName,m.AxId,md.LegacyId,s.TABLEID,s.NAME sName,s.SQLNAME
FROM modelelementdata md ,ModelElement m
LEFT OUTER JOIN AX2012TestDB..SQLDictionary s
ON (s.TABLEID * -1) = m.AxId
WHERE m.ElementType = 44
AND m.elementhandle = md.elementhandle
AND s.ARRAY = 0
AND s.FIELDID = 0
AND upper(m.NAME) collate Latin1_General_CI_AS = s.NAME
)
— Update to positive
update AX2012TestDB.dbo.SQLDICTIONARY set TABLEID = (TABLEID * -1) where AX2012TestDB.dbo.SQLDICTIONARY.TABLEID < 0
go
— Step 2 Fix the field ids in SQLDictionary which do not match with Model
with t as (SELECT (SELECT m1.NAME
FROM ModelElement m1
WHERE m1.ElementHandle = m.ParentHandle
) AS ‘Table Name’,m.NAME AS ‘mName’,m.AXid,s.RECID,M.ParentId,s.TableId,s.FIELDID,S.NAME,s.SQLNAME
FROM ModelElement m
LEFT OUTER JOIN AX2012TestDB..SQLDICTIONARY s ON m.ParentId = s.TABLEID
AND s.NAME = upper(m.NAME) collate Latin1_General_CI_AS
WHERE m.ElementType = 42
AND (s.ARRAY = 1 OR s.ARRAY IS NULL)
AND (s.FIELDID > 0 OR s.FIELDID IS NULL)
AND s.FIELDID != m.AxId
)
— First set to a negative number but correct ID
update AX2012TestDB.dbo.SQLDICTIONARY
set FIELDID = (t.axid * -1)
from t join AX2012TestDB.dbo.SQLDICTIONARY s
on upper(t.mName) collate Latin1_General_CI_AS = s.NAME
and s.FIELDID <> 0
and s.TABLEID = t.ParentId
go
–Reverse the negative to positive
update AX2012TestDB.dbo.SQLDICTIONARY
set FIELDID = (FIELDID * -1)
where FIELDID < 0
go
Reference :http://daxjohan.blogspot.com.au/2015/01/ax-2012-r2-fix-sqldictionary.html
Dynamics AX 2012 Resolve SQL dictionary mismatch when refreshing Database
Shekhar Diptiman's space 