Dynamics 365 for operations - DB sync failed, table FinancialReports (deleting duplicate rows)

DB sync failed - table financialreports

DB sync failed due to error creating a unique index

CREATE UNIQUE INDEX I_4361FOLDERREPORTNAME ON DBO.FINANCIALREPORTS(PARTITION,DATAAREAID,FOLDER,REPORTNAME)

The duplicate key value is (5637144576, 0987, Default Report Definitions, 12 Month Rolling Single Column Income Statement - Default).

When applying application packages, DB sync failures typically occur during creation of unique index:

• New column (unique values) - if table has existing data, sync will try to create blank values in existing rows which will throw error

In my case, I got error in table finacialreports. However, data is correct. data is unique on columns "PARTITION,DATAAREAID,FOLDER,REPORTNAME, designID". But for some reason in our package, the clusteredindex doesn't have designname and hence, it is throwing errors for rows with different design name but same values for "PARTITION,DATAAREAID,FOLDER,REPORTNAME"

To resolve issue temporarily, I used below query to delete duplicate rows.

delete FROM financialreports

WHERE  recID NOT IN (SELECT MAX(recID) as recid

                  FROM   financialreports

                  GROUP  BY REPORTNAME,DATAAREAID,FOLDER,PARTITION

                  /*Even if ID is not null-able SQL Server treats MAX(ID) as potentially

                    nullable. Because of semantics of NOT IN (NULL) including the clause

                    below can simplify the plan*/

HAVING   COUNT(REPORTNAME) > 1

                 -- HAVING MAX(recID) IS NOT NULL

 ) 

Note: Before doing this, make sure you backup data for that table so that it can be used later.

Dynamics 365 for Operations: Use test VM for development VM

We have lot of environments in our project but due to some issues, we needed  to use one of our test VM for development purpose as it has the correct data.

We tried to export and import data to one of the dev boxes but the code base Test and DEV machine are different (which is another story). We were having lot of sync issues especially on views. In AX2012, we can just go and install VS tools. But in AX7, i dont know 

So, here is the approach we followed:

1. Installed VS2015 update 3. VS 2017 is not supported
2. Download binary update from LCS and extract it to a folder. In my case, I used update 4 from MS. In that folder, go to DevToolsService\Scripts, find Microsoft.Dynamics.Framework.Tools.Installer.vsix, double-click it to start the installation and walk through the installation wizard.
3. If you want to create projects, copy MSBuild target files (which are referenced by projects) to the the below location. You can do it using below Powershell script (run as admin and go to DevToolsService\Scripts to run below commands).
1. $dir = New-Item "C:\Program Files (x86)\MSBuild\Microsoft\Dynamics\AX" -Type Directory
2. cp *.targets $dir

After doing this, your VS will have correct settings w.r.t, AX7 database etc.

Now, you can connect to TFS and get latest code. You should also map your workspace properly and start using.

Note: If you want to browse thru the code of AX7 without having the full AX7 setup, please follow this wonderful link: https://community.dynamics.com/ax/b/goshoom/archive/2017/02/03/ax7-development-tools-without-vm

This post is based on the above URL. I thank Martin for his invaluable contribution to AX7 community