SQL Errors after upgrade from 5.8 to 6.0

Expand / Collapse
 

SQL Errors after upgrade from 5.8 to 6.0


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

PROBLEM

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. 



SYMPTOMS

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 ---




CAUSE

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



RESOLUTION

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.



APPLIES TO

CCM 6.0.X.X 

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



Rate this Article:
     

Add Your Comments


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

Details
Last Modified:Saturday, November 16, 2013
Last Modified By: andrew_montpetit
Type: FIX
Article not rated yet.
Article has been viewed 9,951 times.
Options