KB47731: In MicroStrategy Intelligence Server, error ".....Cannot insert duplicate key in object IS_XXX_STATS.." is received when running SQL from the StatisticsFailedSQL.log against the Statistics database

SYMPTOM
In Strategy version 9.x, error similar to the following is received when executing the SQL from the StatisticsFailedSQL.log against the Statistics database:
 

Violation of PRIMARY KEY constraint 'PK__IS_REPOR__67501E390425A276'. Cannot insert duplicate key in object 'dbo.IS_REPORT_STATS'. The duplicate key value is (C31E8F2111E3EBB64B21008059250000, 936, Jun  4 2014  9:07AM). The statement has been terminated.

 
Note: The error message shown above is against the IS_REPORT_STATS table, but can be received against any Statistics table.
 
 
CAUSE
As the error indicates, the same record already exists in the Statistics table. This situation can arise in cases where the initial insert of the statistics data does not complete within the specified statistics INSERT timeout (default is 10 seconds). In such a case, Strategy Intelligence Server 9.x issues a SQLCancel to cancel the INSERT statement and writes this entry to the StatisticsFailedSQL.log. This situation can be detected based on an error similar to the following in the DSSErrors log.
 
 

2014-06-04 09:08:29.934+01:00 Execute Query failed. Error type: Odbc error. Odbc operation attempted: SQLExecDirect. Operation canceled Timeout expired Connection String: DSN=Stats;DESCRIPTION=Stats DSN;UID=SA;WSID=SA;DATABASE=STATS;ADDRESS=TS_SQL,2737;. SQL Statement: insert into IS_REPORT_STATS (JOBID,SESSIONID,SERVERID,SERVERNAME,SERVERMACHINE,PROJECTID,USERID,REPORTID,REPORTTYPE,...,RECORDTIME) values (936, 'C31E8F2111E3EBB64B21008059250000', '5CD2A4C431DC48AA85AB00806A5610F4', ......,GETDATE())

  
 
However, just issuing a SQLCancel does not guarantee that the SQL statement will be canceled on the database side. Depending on the load, the database server can or cannot cancel the insert statement. In cases where the database server cannot actually cancel the insert statement, the data is inserted into the database and when the same insert statement is again executed based on the entry in the StatisticsFailedSQL.log, the error is received.
 
Refer to the following Strategy Knowledge Base document for information on how query cancellation is facilitated by Strategy.
 
KB9868 : How is query cancellation facilitated by the Strategy Query Engine? 
 
 
 
ACTION 
Change the timeout for Statistics insert using Strategy Command Manager 9.x. The value to be set depends on individual environments and it is suggested that users try which value works best for their environments. The timeout can be increased to values of 20 or 30 seconds to start with. Refer to the following Strategy Knowledge Base document for information on how to change the timeout:
 
KB32897 : New feature in Strategy Intelligence Server 9.0.1: Ability to change the SQL execution timeout for inserts into Statistics tables.
 
Administrators can ignore the entries in the StatisticsFailedSQL.log(s) if these already exist in the Statistics database.