When upgrading, sorry updating, a Dynamics 365 for Operations environment the runbook will fail somewhere around step 57 (I am not sure if it will be 57 every time). I have had this in every environment I have run… it might have to do with the fact that we are using the Project module
The error message will look like this:
The step 23 failed with the following error:
The step started
04/26/2019 13:16:35: Managed Sync Table Worker encountered an exception, but is continuing because ContinueOnError is true. Table Sync Failed for Table: ProjHierarchySorting. Exception: System.InvalidOperationException: 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). The statement has been terminated. CREATE UNIQUE INDEX I_19940NODEREFERENCE ON DBO.PROJHIERARCHYSORTING(PARTITION,DATAAREAID,TYPE,REFID,REFNODEID); —> System.Data.SqlClient.SqlException: 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). The statement has been terminated. at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction) at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose) at System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady) at System.Data.SqlClient.SqlCommand.RunExecuteNonQueryTds(String methodName, Boolean async, Int32 timeout, Boolean asyncWrite) at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(TaskCompletionSource`1 completion, String methodName, Boolean sendToPipe, Int32 timeout, Boolean& usedCache, Boolean asyncWrite, Boolean inRetry) at System.Data.SqlClient.SqlCommand.ExecuteNonQuery() at Microsoft.Practices.EnterpriseLibrary.TransientFaultHandling.SqlCommandExtensions.<>c__DisplayClass1.<ExecuteNonQueryWithRetry>b__0() at Microsoft.Practices.EnterpriseLibrary.TransientFaultHandling.RetryPolicy.ExecuteAction[TResult](Func`1 func) at Microsoft.Dynamics.AX.Data.Sql.SqlDataAccessManager.<ExecuteNonQuery>b__27_0(SqlCommand c) at Microsoft.Dynamics.AX.Data.Sql.SqlDataAccessManager.ExecuteSql[T](SqlCommand cmd, Func`2 sqlFunc) — End of stack trace from previous location where exception was thrown — at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw() at Microsoft.Dynamics.AX.Data.Sql.SqlDataAccessManager.HandleException(ExceptionDispatchInfo edi, SqlExecutionInfo execInfo) at Microsoft.Dynamics.AX.Data.Sql.SqlDataAccessManager.ExecuteSql[T](SqlCommand cmd, Func`2 sqlFunc) at Microsoft.Dynamics.AX.Data.Management.DBExecute.ExecuteDDLWithNewTransaction(String commandText) — End of inner exception stack trace — at Microsoft.Dynamics.AX.Data.Management.DBExecute.ThrowOnSqlExecuteException(SqlException e, String commandText) at Microsoft.Dynamics.AX.Data.Management.DBExecute.ExecuteDDLWithNewTransaction(String commandText) at Microsoft.Dynamics.AX.Data.Management.DBExecute.ExecuteSqlBatch(TableSyncSqlBatch sqlBatch) at Microsoft.Dynamics.AX.Data.Management.ManagedSyncTableWorker.ExecuteTableModificationDdl(TableSyncSqlBatch sqlBatch, String tableName, Boolean disableChangeTracking) at Microsoft.Dynamics.AX.Data.Management.ManagedSyncTableWorker.ExecuteModifyTableViaAlterTable(AxTable newTableSchema, IndexListCache ignoreIndexListCache, IncrementalSyncTableSchemaBuilder tableSchemaBuilder) at Microsoft.Dynamics.AX.Data.Management.ManagedSyncTableWorker.ExecuteModifyTableViaAlterViewAndHandlePossibleExceptionsFromSchemaBoundViews(AxTable newTableSchema, IndexListCache ignoreIndexListCache, IncrementalSyncTableSchemaBuilder tableSchemaBuilder) at Microsoft.Dynamics.AX.Data.Management.ManagedSyncTableWorker.ExecuteModifyTable(AxTable newTableSchema, AxTable oldTableSchema, Int32 tableId) at Microsoft.Dynamics.AX.Data.Management.ManagedSyncTableWorker.ModifyTable(AxTable newTableSchema, Dictionary`2 oldTableSchemaDictionary) at Microsoft.Dynamics.AX.Data.Management.ManagedSyncTableWorker.TableSyncAction(AxTable axTable, Dictionary`2 oldTableSchemaDictionary, ConcurrentBag`1 exceptions) 04/26/2019 13:20:47: AOS database sync failed. Microsoft.Dynamics.AX.Framework.Database.TableSyncException: Full sync did not complete successfully. Error: AggregateException:One or more errors occurred. at Microsoft.Dynamics.AX.Data.Management.ManagedSyncWorkerManager.SyncAll() at Microsoft.Dynamics.AX.Data.Management.ManagedSync.<SyncAll>b__21_0() at Microsoft.Dynamics.AX.Data.Management.ManagedSync.EnsureMetadataProviderCacheCleared(Action action) at Microsoft.Dynamics.AX.Data.Management.ManagedSync.SyncAll() at Microsoft.Dynamics.AX.Framework.Database.Tools.LegacyCodepath.RunFullManagedSync(SyncOptions options, String sqlConnectionString, IMetadataProvider metadataProvider) at Microsoft.Dynamics.AX.Framework.Database.Tools.LegacyCodepath.<>c__DisplayClass15_0.<RunFullTableSync>b__0() at Microsoft.Dynamics.AX.Framework.Database.Tools.LegacyCodepath.ExecuteWithinAOS(SyncOptions syncOptions, String sqlConnectionString, IMetadataProvider metadataProvider, Func`1 func, Action`1 errorHandler) Inner exceptions: 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). The statement has been terminated. CREATE UNIQUE INDEX I_19940NODEREFERENCE ON DBO.PROJHIERARCHYSORTING(PARTITION,DATAAREAID,TYPE,REFID,REFNODEID); at Microsoft.Dynamics.AX.Data.Management.DBExecute.ThrowOnSqlExecuteException(SqlException e, String commandText) at Microsoft.Dynamics.AX.Data.Management.DBExecute.ExecuteDDLWithNewTransaction(String commandText) at Microsoft.Dynamics.AX.Data.Management.DBExecute.ExecuteSqlBatch(TableSyncSqlBatch sqlBatch) at Microsoft.Dynamics.AX.Data.Management.ManagedSyncTableWorker.ExecuteTableModificationDdl(TableSyncSqlBatch sqlBatch, String tableName, Boolean disableChangeTracking) at Microsoft.Dynamics.AX.Data.Management.ManagedSyncTableWorker.ExecuteModifyTableViaAlterTable(AxTable newTableSchema, IndexListCache ignoreIndexListCache, IncrementalSyncTableSchemaBuilder tableSchemaBuilder) at Microsoft.Dynamics.AX.Data.Management.ManagedSyncTableWorker.ExecuteModifyTableViaAlterViewAndHandlePossibleExceptionsFromSchemaBoundViews(AxTable newTableSchema, IndexListCache ignoreIndexListCache, IncrementalSyncTableSchemaBuilder tableSchemaBuilder) at Microsoft.Dynamics.AX.Data.Management.ManagedSyncTableWorker.ExecuteModifyTable(AxTable newTableSchema, AxTable oldTableSchema, Int32 tableId) at Microsoft.Dynamics.AX.Data.Management.ManagedSyncTableWorker.ModifyTable(AxTable newTableSchema, Dictionary`2 oldTableSchemaDictionary) at Microsoft.Dynamics.AX.Data.Management.ManagedSyncTableWorker.TableSyncAction(AxTable axTable, Dictionary`2 oldTableSchemaDictionary, ConcurrentBag`1 exceptions). at Microsoft.Dynamics.AX.Framework.Database.Tools.LegacyCodepath.<>c.<RunFullTableSync>b__15_1(Tuple`2 result) at Microsoft.Dynamics.AX.Framework.Database.Tools.LegacyCodepath.ExecuteWithinAOS(SyncOptions syncOptions, String sqlConnectionString, IMetadataProvider metadataProvider, Func`1 func, Action`1 errorHandler) at Microsoft.Dynamics.AX.Framework.Database.Tools.LegacyCodepath.RunFullTableSync(SyncOptions syncOptions, String sqlConnectionString, IMetadataProvider metadataProvider) at Microsoft.Dynamics.AX.Framework.Database.Tools.SyncEngine.FullTableViewSync() at Microsoft.Dynamics.AX.Framework.Database.Tools.SyncEngine.FullSync() at Microsoft.Dynamics.AX.Framework.Database.Tools.SyncEngine.RunSync(SyncOptions options) at Microsoft.Dynamics.AX.Framework.Database.Tools.SyncEngine.Run(String metadataDirectory, String sqlConnectionString, SyncOptions options) 04/26/2019 13:20:47: The operation failed.
The step failed
at Microsoft.Dynamics.AX.AXUpdateInstallerBase.AXUpdateInstallerBase.executeParallel(String runbookID, Boolean silent, String updatePackageFilePath, IRunbookExecutor runbookExecutor, Boolean versionCheck, Parameters param)
at Microsoft.Dynamics.AX.AXUpdateInstaller.Program.InstallUpdate(String[] args)
at Microsoft.Dynamics.AX.AXUpdateInstaller.Program.Main(String[] args
The issue is that the update adds an index to the table ProjHierarchySorting and if there are records in this table it will most likely fail since there will me posts that the index considers duplicates.
The solution is to empty this table before the update (this can be done safely since the table is auto populated). If you have already started the update you can empty the table and then resume the update. Simply log into the server, start Management Studio and run:
delete from ProjHierarchySorting
In production you will need to add a message in the service request telling the technician to run the above SQL line.