SQL Errors after upgrade from 5.8 to 6.0

Article ID: 52063 - Last Review: November 16, 2013


After upgrading from 5.8.X.X to 6.0.X.X you find the Event Viewer and SQL Writer logs showing a number of errors. 


Realtime filing is not working, and MSMQ is showing requests backing up and not being processed.

In the SQL Writer log you see an error that looks like the following: 

Error     10/25/2013 9:35:27 AM    .\Private$\pf6110CCMEnterpriseRealTimeFiling: Database Unavailable or Unknown Error: SqlException: 547 SqlException Error Index # 0 | Message: The INSERT statement conflicted with the FOREIGN KEY constraint "FK_tblData_QueuePerformanceByPeriod_DimDate". The conflict occurred in database "CCMData", table "dbo.DimDate", column 'DateKey'. | LineNumber: 72 Source: .Net SqlClient Data Provider | Procedure: proc_InsertUpdateData_QueuePerformanceByPeriodSqlException Error Index # 1 | Message: The statement has been terminated. | LineNumber: 72 Source: .Net SqlClient Data Provider | Procedure: proc_InsertUpdateData_QueuePerformanceByPeriod
--- Start Exception Stack ---
System.Data.SqlClient.SqlException (0x80131904): The INSERT statement conflicted with the FOREIGN KEY constraint "FK_tblData_QueuePerformanceByPeriod_DimDate". The conflict occurred in database "CCMData", table "dbo.DimDate", column 'DateKey'.
The statement has been terminated.
   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()
   at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)
   at System.Data.SqlClient.SqlCommand.RunExecuteNonQueryTds(String methodName, Boolean async)
   at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(DbAsyncResult result, String methodName, Boolean sendToPipe)
   at System.Data.SqlClient.SqlCommand.ExecuteNonQuery()
   at prairieFyre.Framework.Sql.AsyncSqlCommandProcessor.ProcessSqlCommand(String sqlCommand, Boolean executeAsync)
   at prairieFyre.Framework.Sql.AsyncSqlCommandProcessor.ProcessMessage(String message, String& syntaxFixedMessage)
--- End Exception Stack ---


During the upgrade, the dbo.DimDate table did not update correctly.


This can be corrected by running an included SQL script.

1. Ensure that SQL Management Studio is available on the Enterprise Server, and can connect to the database.
2. Go to [InstallDir]\CCM\Services\MaintenanceAlarmDispatcher\.
3. Double click the InsertDimDate.sql file.
4. This will launch the script in SQL Management Studio.  Click the Execute button (sometimes just a red ! ).

This will insert the missing date entries, and realtime filing will start up again.


CCM 6.0.X.X 

Keywords: SQL error errors upgrade realtime real-time real time filing

Posted Saturday, November 16, 2013 by andrew_montpetit