D365FO Sync failed RouteOperationPropertiesResourceRequirementStaging duplicate record

While restoring data from a newer version 'Version 10 Update24 (7.0.5179.35389)' of AX to older version I faced the following error:

"

Table Sync Failed for Table: RouteOperationPropertiesResourceRequirementStaging. Exception: System.InvalidOperationException: Database execution failed: The CREATE UNIQUE INDEX statement terminated because a duplicate key was found for the object name 'dbo.ROUTEOPERATIONPROPERTIESRESOURCEREQUIREMENTSTAGING' and the index name 'I_17244STAGINGIDX'. The duplicate key value is (5637144576, 6_Resource requirements for route operation properties, 6_Resource requirements for route operation properties-2019-08-20T12:21:09-B9A8188398E6401, 0).

"

Solution:

The error is due to the index allow duplicate property. In earlier version it didn't allow duplicate records in new version the problem is fixed and it does allow duplicate records. Because in my case I was taking data from newer version to older version I had to face this issue. Do following steps:

1. On target VM (older version one), open SQL server and take backup of table 'RouteOperationPropertiesResourceRequirementStaging' data i.e. Generate script and in advanced property select Type of data script to 'Data only'.
   
2. Delete truncate RouteOperationPropertiesResourceRequirementStaging table in target VM.
   
3. On source VM (newer version one) go to 'AOSService > PackagesLocalDirectory > ApplicationSuite > Foundation > AxTables' and copy RouteOperationPropertiesResourceRequirementStaging. Paste and replace this element on target VM in same location.
4. Build Application Suite and Application Foundation models and Synchronize database.
5. After synchronization runs successfully copy and run the SQL insert query you took backup in step 1.

Comment if you need to need any help. Thanks.

D365fo deploy reports through powershell

To deploy report use powershell following commands:

1. All reports in all modules
   
   K:\AosService\PackagesLocalDirectory\Plugins\AxReportVmRoleStartupTask\DeployAllReportsToSSRS.ps1 -PackageInstallLocation "K:\AosService\PackagesLocalDirectory"
   
   Here the AOS resides in K drive.
   
2. All reports in specific module
   
   K:\AosService\PackagesLocalDirectory\Plugins\AxReportVmRoleStartupTask\DeployAllReportsToSSRS.ps1 -PackageInstallLocation "K:\AosService\PackagesLocalDirectory" -Module ApplicationSuite
   
   Here all reports in Application Suite module will be deployed
   
3. Specific report in specific module
   
   K:\AosService\PackagesLocalDirectory\Plugins\AxReportVmRoleStartupTask\DeployAllReportsToSSRS.ps1 -PackageInstallLocation "K:\AosService\PackagesLocalDirectory" -Module ApplicationSuite -ReportName MyCustomReport.Report
   
   Here MyCustomReport.Report inside the Application Suite module is deployed.

D365FO Database sync failed duplicate key on ProjHierarchySorting table

While taking my one server data to other, I faced the following error on Synchronization of database:

"Database execution failed: The CREATE UNIQUE INDEX statement terminated because a duplicate key was found for the object name 'dbo.PROJHIERARCHYSORTING' and the index name 'I_19940NODEREFERENCE'. The duplicate key value is (5637144576, usmf, 0, , 0)."

I opened Sql server and found there were 22 records with many level columns having 0 value. I took backup of table data and deleted all the records with delete query.

Then I ran DB sync again from VS and it ran successfully.