KB223: Primary key constraint error encountered while performing...

Expand / Collapse
 

KB223: Primary key constraint error encountered while performing schedule adherence summarize


Problem

When performing a schedule adherence summarize on a schedule that was 2 shifts for the same employee that are really close to each other (within the tolerance thresholds of each other) and the second shift starts with an embedded time event (job or break), then it is possible to get the following error in the DataProcessor.log file

DateFileWriter::WriteDataToSQL: Violation of PRIMARY KEY constraint 'PK_tbl6125_ActualTimeEventData'. Cannot insert duplicate key in object 'tbl6125_ActualTimeEventData'.

The statement has been terminated.-SqlException

    STACK:    at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection)

   at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection)

   at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj)

   at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)

   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)

   at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result)

   at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(DbAsyncResult result, String methodName, Boolean sendToPipe)

   at System.Data.SqlClient.SqlCommand.ExecuteNonQuery()

   at prairieFyre.ccs.RTSA.DataFileWriter.WriteDataToSQL(SqlConnection conn, Int32 iSqlTimeout, DateTime dtFrom, DateTime dtUntil)

 

This will then cause the adherence summarize data for that particular day, not to be written to the database.

Cause

The cause of this problem is that the adherence summarize process will incorrectly build the list of scheduled events for that employee in this scenario, which, given the right circumstances, can cause the adherence summarize to report 2 different sets of start time-end time for the same shift, which will cause a primary key violation when trying to insert this data into the database

Fix

The fix consists in changing the schedule adherence summarize to correctly build the list of schedule events in this situation. The affected file is the prairieFyre.BAL.DataAnalysis.dll file in the DataProcessor service

Fix location

The hotfix is available at KB223

 
How to apply the fix

Double-click the KB223.exe file and follow the instructions

Applies to

version 5.1, SP1, SP2, SP3, SP4 (this hotfix is to be applied on top an SP3 or SP4)

 



Rate this Article:
     

Add Your Comments


Name: *
Email Address:
Web Address:
Verification Code:
*
 

Details
Last Modified:Saturday, January 22, 2011
Last Modified By: kmiddlemiss
Type: HOTFIX
Article not rated yet.
Article has been viewed 3,630 times.
Options