Dynamics AX Database Synchronization Issue

Caution: Don't try to apply this, if you don't possess any idea of technical knowledge of Dynamics AX and/or SQL server. 

Before I say something, all of us know that it’s a broad chapter the database synchronization in ax, often we do face various situations and based on situation we do apply solution to fix the problem.

Although I wrote earlier too on this topic, and will write more, but for time being let me take you to a special situation of database synchronization. So here goes the story

Sometimes after updating AX e.g. R2 to CU7 or after moving customizations from one server to other, we do face serious kind of issues on few of tables in whole application.

Our purpose here becomes to save data and at the same time preserve/ save the actual table, which is being rejected by ax during synchronization.

So let’s talk how to deal effectively with this kind of situation. In order to demonstrate I would take a table for sake of example. Let’s assume I am facing synchronization issue on table SalesQuotataionTable.

Step#1: Save you data and preserve it [this step will be done on sql server]

·         Make sure if you have any data in the table by writing following query on sql server

select * from SalesQuotationTable

·         Run the following query to create a copy of existing table along with data

SELECT * INTO SalesQuotationTableCopy1 FROM SalesQuotationTable

·         Make sure, has new table with same data created and whether it took data long with itself, from original table, for this purpose run following query

select * from SalesQuotationTableCopy1

·         After you are sure, that there is copy of original table,  and also data of old table is avilble in thi newly created copy table ; now drop the original table by following query

DROP TABLE SalesQuotationTable

·         Clear the synchronization cache of Dynamics AX, by running following query on sql server.

DELETE FROM SQLSYNCINFO

                You can also run the entire step 1 in a single cycle as under:

                select * from SalesQuotationTable

SELECT * INTO SalesQuotationTableCopy1 FROM SalesQuotationTable

select * from SalesQuotationTableCopy1

DROP TABLE SalesQuotationTable

DELETE FROM SQLSYNCINFO

[Note: when you runmultiple quries at one time, make sure the table names are correct. Do not delete any wrong obkect/table.]

Step#2: Synch from AX

·         Close SQL Server

·         Open Dynamics AX

·         Go to table [For our example the table is SalesQuotationTable]

·         Right click the table and click save. Again right click and click on synchronize.

Step#3 Restore data back

·         Open SQL Server again

·         Write a new with query with code as under

INSERT INTO [DatabaseName].[dbo].[TableName]

           ([Field1]

           ,[Field2]

             …

             …

           ,[Field N])

          

    SELECT [Field1]

      ,[ Field2]

             …

             …

      ,[ Field N]

  FROM [DatabaseName].[dbo].[CopiedTableName] 

·         So for our example table (SalesQuotataionTable) we will write query as under

INSERT INTO [MicrosoftDynamicsAX].[dbo].[ SalesQuotationTable]

           ([Field1]

           ,[Field2]

             …

             …

           ,[Field N])

          

    SELECT [Field1]

      ,[ Field2]

             …

             …

      ,[ Field N]

·           FROM [MicrosoftDynamicsAX].[dbo].[ SalesQuotationTableCopy1]

·         For list of fields from 1 to N, it’s better to be taken from Old/Copied/Preserved table; the simplest reason to this is that there may be few new fields in newly saved / synched table.  

·         Also list of field could be generated by sql server automatically using wizard in management studio as shown in the following figure