This article has been archived. It is offered "as is" and will no longer be updated.
After you install any cumulative update or service pack for Microsoft Office Project Server 2007 that was released in October 2008 or later, the SharePoint Products and Technologies Configuration Wizard fails. Additionally, the following error message logged in the Upgrade.log file:
“Microsoft.Office.Project.Server.Upgrade.ReportingDatabaseUpgrader_12_1_153_0.Upgrade()” The DELETE statement conflicted with the REFERENCE constraint "FK_MSP_TimesheetLine_TaskNameUID". The conflict occurred in database "TaskConsistency_<instance_name>_Reporting", table "dbo.MSP_TimesheetLine", column 'TaskNameUID'. The statement has been terminated. at System.Data.SqlClient.SqlConnection.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.RunExecuteNonQueryTds(String methodName, Boolean async) at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(DbAsyncResult result, String methodName, Boolean sendToPipe) at System.Data.SqlClient.SqlCommand.ExecuteNonQuery() at Microsoft.Office.Server.Data.SqlSession.ExecuteScript(TextReader textReader, Int32 commandTimeout) at Microsoft.SharePoint.Upgrade.SPDatabaseAction.ExecuteSql(String sql, Int32 commandTimeout) at Microsoft.SharePoint.Upgrade.SPDatabaseAction.ExecuteSql(String sql)at Microsoft.Office.Project.Server.Upgrade.ReportingDatabaseUpgrader_12_1_153_0.Upgrade() at Microsoft.SharePoint.Upgrade.SPActionSequence.Upgrade()
Note By default, the Upgrade.log file is in the following folder: %ProgramFiles%\Common Files\Microsoft Shared\Web Server Extensions\Logs\
This issue occurs because of a very specific condition in the reporting database. In a very small number of cases, there may be duplicate records in the reporting database. These duplicate records can cause the SharePoint Products and Technologies Configuration Wizard to fail.
Before you install any Project Server 2007 cumulative update or service pack that is released in October 2008 or later, use the following script to check for the condition in the reporting database.
Only run this detection script one time. If you receive a positive then run the recovery script. Do not run the detection script a second time because it will show a positive again even though there is no longer a problem. If you run the detection script a second time and still get a positive result, it's a false positive and this is by design. The detection script was written to be very sensitive to any indication of the problem that causes the failure. The fix script, however, is pinpointed at fixing the exact problem that causes the failure. If you re-run the detection script, it is possible that there is still some little detail in your database that it's picking, but the fix script will have already fixed the root of the failure.
If you have a default installation of Project Server, the name of the database is named ProjectServer_Reporting. You can also verify the name of the reporting database that is associated with your Project Server site through the SharePoint Central Administration Web site.
IF( SELECT DuplicatesCount = COUNT(*) FROM ( -- Returns the TaskUID and the Latest date that are duplicates, along with the number of times they are duplicated SELECT TSTask.TaskUID, TSTask.LatestDate, DuplicatesCount = COUNT(TSTask.TaskNameUID) FROM ( -- This will return a 'copy' of the MSP_TimesheetTask table, but with the TaskUID fixed (by looking at the Assignments table) SELECT distinct(MSP_TimesheetTask.TaskNameUID), --Distinct, because when joining with lines we will have one row for each line, and so on MSP_TimesheetTask.LatestDate, TaskUID = ISNULL(MSP_EpmAssignment.TaskUID, MSP_TimesheetTask.TaskUID) FROM dbo.MSP_TimesheetTask LEFT OUTER JOIN MSP_TimesheetLine ON MSP_TimesheetLine.TaskNameUID = dbo.MSP_TimesheetTask.TaskNameUID LEFT OUTER JOIN dbo.MSP_TimesheetProject ON MSP_TimesheetLine.ProjectNameUID = MSP_TimesheetProject.ProjectNameUID LEFT OUTER JOIN dbo.MSP_EpmAssignment ON MSP_EpmAssignment.ProjectUID = MSP_TimesheetProject.ProjectUID AND MSP_EpmAssignment.AssignmentUID = MSP_TimesheetLine.AssignmentUID ) AS TSTask GROUP BY TSTask.TaskUID, TSTask.LatestDate --Define groups: all the rows that have the same TaskUID and LatestDate HAVING COUNT(TSTask.TaskNameUID) > 1 --See if we have any groups that have more than one row in them. If there are this means the old upgrade will fail. ) AS Duplicates) > 0 PRINT 'A correctable data issue has been detected. Please run the data correction script before upgrading your installation.'ELSE PRINT 'No data issues were detected. You can continue with your upgrade.'
If this script provides a positive result, contact Microsoft Customer Support for help resolve this issue.
Microsoft has confirmed that this is a problem in the Microsoft products that are listed in the "Applies to" section.
If this is an issue for your deployment, it will only be an issue one time. Therefore, you do not have to run this script if either of the following conditions is true:
Any Project Server 2007 cumulative update that is released in October 2008 or later is successfully deployed.
The 2007 Office Servers Service Pack 2 is successfully deployed.
You will also notice the Versions table in the reporting database will look similar to this: