Management Reporter CU14 Datamart Error – Violation of PRIMARY KEY
If you have run into this error the source of it maybe in the sequence that you created the Datamart for multiple Dynamics GP DBs. In the case I encountered the users had 3 Dynamics GP Databases and the Functional and ISO Current were created at different times than all at once before the creation of the Datamart in its entirety. The solution though a broad stroke was to simply regenerate the entire Datamart now that the require Functional and ISO Current data had been entered.
Hope this helps others that might have received this error.
System.Data.SqlClient.SqlException (0x80131904): Violation of PRIMARY KEY constraint ‘PK_Scenario’. Cannot insert duplicate key in object ‘Datamart.Scenario’. The duplicate key value is (1).
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
Beginning transaction.
Committing transaction.
0 staged scenarios updated with organization id
2016-02-11 20:40:16.2519490
0 staged scenarios updated with id
2016-02-11 20:40:16.2519490
0 staged scenarios found requiring recalculation
2016-02-11 20:40:16.2539490
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.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString)
at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async, Int32 timeout, Task& task, Boolean asyncWrite, SqlDataReader ds)
at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, TaskCompletionSource`1 completion, Int32 timeout, Task& task, Boolean asyncWrite)
at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(TaskCompletionSource`1 completion, String methodName, Boolean sendToPipe, Int32 timeout, Boolean asyncWrite)
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.Performance.Core.SqlCommandWrapper.RunCommand[TResult](SqlCommand command, Func`1 execute)
at Microsoft.Dynamics.Performance.DDM.FactLoader.ProcessStagedData(String connectionString)
at Microsoft.Dynamics.Integration.Adapters.DimensionalDataMart.DataMartAdapter.RunMaintenance()
at Microsoft.Dynamics.Integration.Service.Tasks.MaintenanceTask.RunImplementation()
ClientConnectionId:1b476910-58d9-4daa-b33e-899da1d3074b