Select the product you need help with

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

Article ID: 971217 - View products that this article applies to.

SYMPTOMS

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\

Back to the top | Give Feedback

CAUSE

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.

Back to the top | Give Feedback

RESOLUTION

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.

Notes

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.

Back to the top | Give Feedback

STATUS

Microsoft has confirmed that this is a problem in the Microsoft products that are listed in the "Applies to" section.

Back to the top | Give Feedback

MORE INFORMATION

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.

Back to the top | Give Feedback