The SharePoint Products and Technologies Configuration Wizard fails after you install any cumulative update or service pack for Project Server 2007 that was released in October 2008 or later

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.
Secure Data
You will also notice the Versions table in the reporting database will look similar to this:
VersionId Version 00000000-0000-0000-0000-000000000000 12.0.4518.1016 00000000-0000-0000-0000-000000000000 12.0.6218.1000 00000000-0000-0000-0000-000000000000 12.0.6300.5000 00000000-0000-0000-0000-000000000000 12.0.6318.5000 00000000-0000-0000-0000-000000000000 12.0.6327.5000 A1EF27C5-A62C-44A2-BEA8-D244C622A092
The first few lines will depend on the various upgrades previously loaded, but the final line signifies that the last successful upgrade step was 152, and the upgrade action for 12_1_153 failed.

Article ID: 971217 - Last Review: 01/15/2015 19:53:50 - Revision: 5.0

  • Microsoft Office Project Server 2007
  • kbnosurvey kbarchive kbexpertiseinter kbtshoot kbsurveynew kbprb KB971217