Dynamics AX2012 SQL Dictionary challenges when restoring database to another environment

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 **

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

Case 1 – Custom table – 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 – Custom & Standard tables – Name and IDs match but 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 positives

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 AX2012_Test

GO

— Backup the existing SQL dictionary as precaution

select  * into    AX2012_Test.dbo.SQLDICTIONARYBKP1

from    AX2012_Test.dbo.SQLDICTIONARY

USE AX2012_Test_Model

GO

–Step 1 Find tables in SQLDICTIONARY that have same name as in AOT but different ID and then update SystemSequences.TabID = TableID in Modelstore

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 AX2012_Test..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 AX2012_Test.dbo.SYSTEMSEQUENCES

set TABID = t.axid  from t

join AX2012_Test.dbo.SYSTEMSEQUENCES x

on t.tableid = x.tabid

Go

–Step 2 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 AX2012_Test..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 AX2012_Test.dbo.SQLDICTIONARY

set TABLEID = (t.axid * -1)

from t

join AX2012_Test.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 AX2012_Test..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 AX2012_Test.dbo.SQLDICTIONARY set TABLEID = (TABLEID * -1) where AX2012_Test.dbo.SQLDICTIONARY.TABLEID < 0

go

— Step 3 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 AX2012_Test..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 AX2012_Test.dbo.SQLDICTIONARY

set FIELDID = (t.axid * -1)

from t join AX2012_Test.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 AX2012_Test.dbo.SQLDICTIONARY

set FIELDID = (FIELDID * -1)

where FIELDID < 0

go

Reference :http://daxjohan.blogspot.com.au/2015/01/ax-2012-r2-fix-sqldictionary.html

 

4 thoughts on “Dynamics AX2012 SQL Dictionary challenges when restoring database to another environment”

  1. How about handling a situation where a field with the same ID exists in both environments but their names and data type differ. we have tried this script . it did not give any issue during sync but caused a field name change but not the field data type . seems like a bug in synchronisation

Leave a Reply

Your email address will not be published. Required fields are marked *