DR204104 asked a question.
Data warehouse export failed
Hi Team, I'm searching help on this one, it's started recently. Does anyone know what's caused this?
- 2024-11-07 02:25:32,154 [ERROR] System.Reflection.TargetInvocationException: Exception has been thrown by the target of an invocation. ---> DevExpress.Xpo.DB.Exceptions.ConstraintViolationException: Executing Sql '-- Merge only rows in table that have changedDECLARE @MergeChanges TABLE(Change nvarchar(10));MERGE Application_Measurement_FACT AS targetUSING (SELECT * FROM Application_Measurement_FACT) AS source ON (source.ApplicationID = target.ApplicationID OR (source.ApplicationID IS NULL AND target.ApplicationID IS NULL)) AND (source.SoftwareLicenseID = target.SoftwareLicenseID OR (source.SoftwareLicenseID IS NULL AND target.SoftwareLicenseID IS NULL)) AND (source.ComplianceOperatorID = target.ComplianceOperatorID OR (source.ComplianceOperatorID IS NULL AND target.ComplianceOperatorID IS NULL))WHEN MATCHED AND NOT EXISTS(SELECT 1 WHERE (source.ComplianceOperatorID = target.ComplianceOperatorID OR (source.ComplianceOperatorID IS NULL AND target.ComplianceOperatorID IS NULL)) AND (source.ApplicationID = target.ApplicationID OR (source.ApplicationID IS NULL AND target.ApplicationID IS NULL)) AND (source.SoftwareLicenseID = target.SoftwareLicenseID OR (source.SoftwareLicenseID IS NULL AND target.SoftwareLicenseID IS NULL)) AND (source.InstallCount = target.InstallCount OR (source.InstallCount IS NULL AND target.InstallCount IS NULL)) AND (source.UnlicensedInstallCount = target.UnlicensedInstallCount OR (source.UnlicensedInstallCount IS NULL AND target.UnlicensedInstallCount IS NULL))) THENUPDATE SET ComplianceOperatorID = source.ComplianceOperatorID, ApplicationID = source.ApplicationID, SoftwareLicenseID = source.SoftwareLicenseID, InstallCount = source.InstallCount, UnlicensedInstallCount = source.UnlicensedInstallCountWHEN NOT MATCHED BY TARGET THENINSERT (ComplianceOperatorID, ApplicationID, SoftwareLicenseID, InstallCount, UnlicensedInstallCount) VALUES (ComplianceOperatorID, ApplicationID, SoftwareLicenseID, InstallCount, UnlicensedInstallCount)WHEN NOT MATCHED BY SOURCE THENDELETEOUTPUT $action INTO @MergeChanges;SELECT Change, COUNT(*) AS CountPerChange FROM @MergeChanges GROUP BY Change' with parameters '' exception 'System.Data.SqlClient.SqlException (0x80131904): Violation of UNIQUE KEY constraint 'UQ_Application_Measurement'. Cannot insert duplicate key in object 'dbo.Application_Measurement_FACT_MT'. The duplicate key value is (1, -505664, <NULL>).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.SqlDataReader.TryConsumeMetaData() at System.Data.SqlClient.SqlDataReader.get_MetaData() at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString, Boolean isInternal, Boolean forDescribeParameterEncryption, Boolean shouldCacheForAlwaysEncrypted) at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async, Int32 timeout, Task& task, Boolean asyncWrite, Boolean inRetry, SqlDataReader ds, Boolean describeParameterEncryptionRequest) at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, TaskCompletionSource'1 completion, Int32 timeout, Task& task, Boolean& usedCache, Boolean asyncWrite, Boolean inRetry) at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method) at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior, String method) at DevExpress.Xpo.DB.ConnectionProviderSql.<>c__DisplayClass6.<InternalGetData>b__4() at DevExpress.Xpo.Logger.LogManager.Log[T](String category, LogHandler'1 handler, MessageHandler'1 createMessageHandler) at DevExpress.Xpo.DB.ConnectionProviderSql.SelectDataSimple(Query query, CriteriaOperatorCollection targets, Boolean includeMetadata)ClientConnectionId:5e16e695-522a-47c4-ae21-4f0e04556c52Error Number:2627,State:1,Class:14' ---> System.Data.SqlClient.SqlException: Violation of UNIQUE KEY constraint 'UQ_Application_Measurement'. Cannot insert duplicate key in object 'dbo.Application_Measurement_FACT_MT'. The duplicate key value is (1, -505664, <NULL>).
I have find one report of a similar error occurring in an FNMS installation several years ago, but no root cause was identified and the issue stopped occurring after a few days in that instance.
The error suggests that there are some duplicate records of data somewhere in the system that can't be handled for some reason.
What output do you get from running the following query against the compliance database?
In theory this should return no more than 1 row; but based on the error being received I am wondering whether you may see multiple rows being returned.
Hi Chris,
Thank you for a quick response.
When I try to run your script against FNMSCompliance DB I get
I got ahead of this I tried
then SQL returns 0 rows
If I'm looking just for ApplicationID = -505664, SQL returns 1 row.
ComplianceOperatorID ApplicationID SoftwareLicenseID InstallCount UnlicensedInstallCount
2269 -505664 NULL 25 25