Getting errors on 'The INSERT statement conflicted with the FOREIGN KEY constraint "FK_EventQueueEntryMetaData_EventQueueEntry" '

Products

• IT Management Suite

Issue/Introduction

Customer started noticing errors when certain NSEs were processed.
The INSERT statement conflicted with the FOREIGN KEY constraint "FK_EventQueueEntryMetaData_EventQueueEntry". The conflict occurred in database "Symantec_CMDB", table "dbo.EventQueueEntry", column 'Id'.
The statement has been terminated.
  Event not processed. IP: 192.168.43.110; Error: Failed to register NSE: <inline>, source=43d23a1a-0219-43fa-be5e-37ab967a97ee, length=1.25 KB
Failed to register NSE: <inline>, source=43d23a1a-0219-43fa-be5e-37ab967a97ee, length=1.25 KB
   [Altiris.NS.NseRegistrationFailedException @ Altiris.NS]
   at Altiris.NS.EventRouter.DeliverStream(Stream input, Guid source, Int32 priority, Guid metaDataType, String metaData)
   at Altiris.NS.EventRouter.DeliverPostedDataImpl(HttpContext ctx, EHttpHandleOptions options)
   at Altiris.NS.EventRouter.ProcessAgentHttpPostEvent(HttpContext ctx, EHttpHandleOptions options)
 
The INSERT statement conflicted with the FOREIGN KEY constraint "FK_EventQueueEntryMetaData_EventQueueEntry". The conflict occurred in database "Symantec_CMDB", table "dbo.EventQueueEntry", column 'Id'.
The statement has been terminated.
   [System.Data.SqlClient.SqlException @ .Net SqlClient Data Provider]
   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 Altiris.NS.DataAccessLayer.Implementation.Altiris_NS_EventRouterDataAccessLayerEventRouterDal.spRegisterEventWithMetaData(Boolean dataInLine, String file, Int32 priority, Int64 size, Guid source, Int32 queueId, Int64 maxSizeSetting, Guid metaDataType, String metaData)
   at Altiris.NS.EventRouter.RegisterDeliveryTry(Boolean fileInLine, String tempFile, Int64 fileSize, Guid source, Int32 priority, Guid metaDataType, String metaData, Int32 queueId, Int64 max)
   at Altiris.NS.EventRouter.<>c__DisplayClassa.<RegisterDelivery>b__9(IDatabaseContext ac)
   at Altiris.Database.DatabaseContext`1.RetryAction(Int32 retries, Boolean transacted, Getter`1 getContext, Action`1 action, Action`1 retry, DeadlockRetryArgs& re, Exception& exception, Boolean inTransaction)
   at Altiris.Database.DatabaseContext`1.PerformWithDeadlockRetryHelper(Int32 retries, Boolean inTransaction, Getter`1 getContext, Action`1 action, Action`1 retry, Boolean transacted)
   at Altiris.Database.DatabaseContext`1.PerformTransactedDeadlockRetry(Int32 retries, Getter`1 getContext, Action`1 action, Action`1 retry)
   at Altiris.NS.EventRouter.RegisterDelivery(Boolean fileInLine, String tempFile, Int64 fileSize, Guid source, Int32 priority, Guid metaDataType, String metaData)
   at Altiris.NS.EventRouter.DeliverStream(Stream input, Guid source, Int32 priority, Guid metaDataType, String metaData)
 
SQL Exception details: code=547, line=54, procedure=spRegisterEventWithMetaData
 
Exception logged from:
   at Altiris.Diagnostics.Logging.EventLog.ReportException(Int32 severity, String strMessage, String category, Exception exception, String footer)
   at Altiris.NS.EventRouter.ProcessAbstractException(HttpContext ctx, Exception ex, EHttpHandleOptions options)
   at Altiris.NS.EventRouter.ProcessAgentHttpPostEvent(HttpContext ctx, EHttpHandleOptions options)
   at Altiris.Web.NS.Agent.PostEventHandler.ProcessRequest(HttpContext context)
   at System.Web.HttpApplication.CallHandlerExecutionStep.System.Web.HttpApplication.IExecutionStep.Execute()
   at System.Web.HttpApplication.ExecuteStep(IExecutionStep step, Boolean& completedSynchronously)
   at System.Web.HttpApplication.PipelineStepManager.ResumeSteps(Exception error)
   at System.Web.HttpApplication.BeginProcessRequestNotification(HttpContext context, AsyncCallback cb)
   at System.Web.HttpRuntime.ProcessRequestNotificationPrivate(IIS7WorkerRequest wr, HttpContext context)
   at System.Web.Hosting.PipelineRuntime.ProcessRequestNotificationHelper(IntPtr rootedObjectsPointer, IntPtr nativeRequestContext, IntPtr moduleData, Int32 flags)
   at System.Web.Hosting.PipelineRuntime.ProcessRequestNotification(IntPtr rootedObjectsPointer, IntPtr nativeRequestContext, IntPtr moduleData, Int32 flags)
   at System.Web.Hosting.UnsafeIISMethods.MgdIndicateCompletion(IntPtr pHandler, RequestNotificationStatus& notificationStatus)
   at System.Web.Hosting.UnsafeIISMethods.MgdIndicateCompletion(IntPtr pHandler, RequestNotificationStatus& notificationStatus)
   at System.Web.Hosting.PipelineRuntime.ProcessRequestNotificationHelper(IntPtr rootedObjectsPointer, IntPtr nativeRequestContext, IntPtr moduleData, Int32 flags)
   at System.Web.Hosting.PipelineRuntime.ProcessRequestNotification(IntPtr rootedObjectsPointer, IntPtr nativeRequestContext, IntPtr moduleData, Int32 flags)
 
**CEDUrlStart** :http://entced.symantec.com/entt?product=SMP&version=7.6.1383.0&language=en&module=NC4rzHuqNsqbazgbQO/1Bk0bIOIxRY2RtqOC1icsXmnT1KD8HfWL6em5As29YpdOAC7HHMT5xlwpZNjMyFsdsf1NuHfMUnEcAqmeHWerf0g=&error=1500136771&build=**CEDUrlEnd**
 
-----------------------------------------------------------------------------------------------------
Date: 8/10/2015 1:15:05.9273060 PM, Tick Count: 351819663 (4.01:43:39.6630000), Size: 5.89 KB
Process: w3wp (21964), Thread ID: 81, Module: w3wp.exe
Priority: 1, Source: EventRouter
 

Cause

Known issue. Unexpected results were present while using  “IDENT_CURRENT('EventQueueEntry')” in the original "spRegisterEventWithMetaData" stored procedure.
This issue can be present on releases before SMP 7.6 HF4.

Resolution

This issue has been addressed with the ITMS 7.6 HF4 release (See DOC8949).


A workaround is available for previous versions. See attached modified stored procedure.

Basically changed this line from  this:
VALUES ( IDENT_CURRENT('EventQueueEntry'), @metaDataType, @metaData )

to:
VALUES ( SCOPE_IDENTITY(), @metaDataType, @metaData )