Fix data issues identified in pre-migration script A1 (Project Server 2010)

How to fix the problem

Check in all projects that are checked out.

Check in projects that are checked out in Project Server 2003

1. In Microsoft Office Project Server 2003 Project Web Access, click Admin.

2. Click Manage Enterprise Features.

3. Click Check in Enterprise Projects and see which projects are checked out. Coordinate its check-in or do a forced check-in operation from Project Web Access.

How to fix the problem

Open the externally edited project in Project Professional 2003, save it, and check it back in to the server. This action should set the Externally edited flag to false.

How to fix the problem

The StopProjectMigrationIfStatusUpdatesPending parameter in the migration configuration file can be configured to allow the migration of projects with pending updates. For more information about this parameter, see Configure the migration tool (Project Server).

As a pre-migration best practice, you could set a deadline for project managers to accept or reject pending status updates by a certain date. Beyond that date you could then force-migrate all projects (even if status updates are not up-to-date).

Identifying duplicate enterprise resources

The pre-migration script A1 identifies duplicate enterprise resources if they are contained in your data. If the script finds any duplicate enterprise resources, the data will be displayed similarly to the following example of two duplicate resources:

Identify duplicate enterprise resources used in your projects

The pre-migration script A1 identifies duplicate enterprise resources if they are contained in your data. If the script finds any duplicate enterprise resources, the data will be displayed similarly to the following example of two duplicate resources:

Fix projects that use duplicate enterprise resources

Running the following query (query 1) returns project IDs that are associated with duplicate enterprise resources. You need to eliminate this kind of duplication such that only one "res_name, res_euid" combination is ever used in your projects. If duplicates exist, you must designate one of the duplicates as the "correct" resource and make sure all projects use this resource. (Update the MSP_RESOURCES.RES_EUID column for that project to point it to the enterprise resource you have designated as the correct one.) After doing this, run script A1 to repeat your verification test for duplicate enterprise resources used in projects.

Query 1: 

select res_name,  res_euid, proj_id from msp_resources where res_name in (select distinct r1.RES_NAME from dbo.MSP_RESOURCES r1 inner join dbo.MSP_RESOURCES r2 on (r1.RES_NAME = r2.RES_NAME and r1.PROJ_ID = r2.PROJ_ID) where r1.PROJ_ID = 1 and r1.RES_UID != r2.RES_UID) and proj_id <> 1 and res_euid is not null order by res_name, res_euid asc

For example, let's say that running Query 1 returns the following results:

To fix the situation, you could choose ‘Brad Sutton’ with RES_EUID=125 as the "correct" enterprise resource and fix the rows as follows:

Fix the duplicate enterprise resources to point to the correct one

In the previous section you designated a correct resource among the duplicates and fixed the projects to point to it. Now, you must change the MSP_RESOURCES table in accordance with the resource you have designated. Run Query 4 (which is the same as Query 1) to get a list of the duplicate enterprise resources. Then update the RES_EUID column for the duplicate resource to point it to the resource you have designated as the correct one.

Query 4: 

select res_uid, res_name, res_euid from msp_resources where res_name in (select distinct r1.RES_NAME from dbo.MSP_RESOURCES r1 inner join dbo.MSP_RESOURCES r2 on (r1.RES_NAME = r2.RES_NAME and r1.PROJ_ID = r2.PROJ_ID) where r1.PROJ_ID = 1 and r1.RES_UID != r2.RES_UID) and proj_id = 1 order by res_name asc

For example, let's say that running Query 4 returns the following results. The ‘Peter Krebbs’ row is correct. ‘Brad Sutton’ needs to be corrected.

To fix the situation, you could choose ‘Brad Sutton’ with RES_UID=125 as the "correct" resource and update the other row to point to that item. For example:

Remove the duplicate resource from the Enterprise Resource Pool

You can remove a duplicate resource from the Enterprise Resource Pool by pointing the duplicate resource to a bogus project (PROJ_ID = maximum integer value). In addition, we also need to clean up the related enterprise calendars. Run Query 5 to do this.

Query 5:

update msp_resources set proj_id=2147483647 WHERE res_uid != res_euid and res_euid is not null and res_uid > 0 and proj_id = 1

update msp_calendars set proj_id=2147483647 WHERE res_uid not in (select res_uid from msp_resources where proj_id=1) and proj_id = 1 and cal_uid > 0 and res_uid is not null

After completing the preceding steps, attempt to run the migration tool with the –verify option again to verify that the duplicate resource problem is resolved.

How to fix the problem

Apply Project Server 2003 SP3 to your Project Server 2003 installation. For more information about Project Server 2003 SP3, see the knowledge base article Description of Project Server 2003 Service Pack 3 (https://go.microsoft.com/fwlink/p/?LinkId=188720).

How to fix the problem

Open the Enterprise Global in Project Professional and save it back to the server.

How to fix the problem

Make sure that the Enterprise Global is checked in.

1. In Project Server 2003 Project Web Access, click Admin.

2. Click Manage Enterprise Features.

3. Click Check in Enterprise Projects, and see who has checked out the Enterprise Global. Coordinate its check-in or do a forced check-in operation from Project Web Access.

How to fix the problem

The following SQL query unlocks the Enterprise Global Template. Run the following SQL query in the "Project Server 2003 Project tables" database .

Update MSP_PROJECTS set PROJ_LOCKED = 1 where PROJ_TYPE = 2

How to fix the problem

Work with your administrator to correct the inconsistent state between the two databases.

How to fix the problem

Run the following SQL query in the "Project Server 2003 Project tables" and the "Project Server 2003 Web tables" databases. It replaces the comma character with an underscore character. If you want to use a different valid character, you can edit the query.

-- run this against the project tables db

declare @replacement_char char

declare @char_to_replace char

set @replacement_char = '_'

set @char_to_replace = ','

update dbo.MSP_RESOURCES set RES_NAME = replace(RES_NAME, @char_to_replace, @replacement_char)

where RES_NAME is not null and charindex(@char_to_replace, RES_NAME) > 0

-- run this against the web tables db

declare @replacement_char char

declare @char_to_replace char

set @replacement_char = '_'

set @char_to_replace = ','

update dbo.MSP_WEB_RESOURCES set RES_NAME = replace(RES_NAME, @char_to_replace, @replacement_char)

where RES_NAME is not null and charindex(@char_to_replace, RES_NAME) > 0

How to fix the problem

In Project Server 2003, open the enterprise resource pool and set values for the required custom fields or set the affected custom fields as ‘Not Required’. You can then change them back manually in Office Project Server 2007 after they are migrated.

Resource custom field has a value which is not in the lookup table definition

A resource custom field's values must all be in the lookup definition table.

How to fix the problem

To fix the problem, you need to first set all the Enterprise Resources as externally edited. This can be done through an SQL query. You can then make a change to each Enterprise Resource so that all relevant refreshes are done by Project Professional when the Enterprise Resource Pool is saved. This action removes the "externally edited" flag for each Enterprise resource. If an enterprise resource is not changed, no recalculations are done by Project Professional to that resource. That specific enterprise resource continues to be flagged as externally edited, and the Enterprise Resource Pool will not migrate.

You can use the following general workaround to remove all externally edited flags from your Enterprise Resources.

1. Set all enterprise resources to be externally edited. You can do this by running the following SQL query in the "Project Server 2003 Project tables" databases.

   Update dbo.MSP_RESOURCES set EXT_EDIT_REF_DATA = 1 where PROJ_ID = 1

   Update dbo.MSP_PROJECTS set PROJ_EXT_EDITED = 1, PROJ_EXT_EDITED_DATE = 1, PROJ_EXT_EDITED_DUR = 1, PROJ_EXT_EDITED_NUM = 1, PROJ_EXT_EDITED_FLAG = 1, PROJ_EXT_EDITED_CODE = 1, PROJ_EXT_EDITED_TEXT = 1 where PROJ_ID = 1

2. Temporarily add an inconsequential resource custom field to each Enterprise Resource to force recalculations to be done by Project Professional 2003. The forced recalculations will mark each Enterprise Resource as no longer externally edited. As an example, in Project Professional 2003 you can create a 'test' resource custom field that will be added to each Enterprise Resource. You can use any custom field type. For example, you can use a Resource Duration custom field, which is seldom used.

   Note

   If you use an Enterprise Resource Duration custom field, do not create a value list for it. Enterprise Resource Duration custom fields with value lists will not migrate. When creating this temporary custom field, use a numeric value.

   You can name the new custom field "Test Resource CF". After creating the "Test Resource CF" custom field, open the Enterprise Resource Pool and add the "Test Resource CF" column. Set a valid value for this custom field for all resources. Save the Enterprise Resource Pool and perform the migration. After migration, remove the "Test Resource CF" custom field in Microsoft Office Project Professional 2007.

How to fix the problem

To fix the problem, you must manually remove all values lists from each Enterprise Resource Duration custom field found in the SQL query. You can do this using the following procedure.

1. Open Project Professional 2003 connected to the Project Server 2003 server.

2. On the Tools menu, point to Enterprise Options, and then click Open Enterprise Global.

3. On the Microsoft Project - Checked-out Enterprise Global page, on the Tools menu, point to Customize, and then click Enterprise Fields.

4. On the Custom Enterprise Fields page, in the Custom Fields tab, in the Fields section, click Resource.

5. In the Type drop-down list, click Duration. All Enterprise Duration custom fields will appear in the Field list.

6. In the Field list, select the first Enterprise Resource Duration custom field that matches the ones returned by the SQL query you ran previously. In the Custom Attributes section, click the Value List button. In the value list for the specific Enterprise Duration custom field, delete any values in the Value column, and then click OK. On the Microsoft Office Project dialog box that appears, click OK.

7. On the Customize Enterprise Fields page, in the Custom Attributes section, verify that Value List is not selected for the Enterprise Resource Duration custom field selected in the Field list. If Value List is selected, select None.

8. In the Field list, select all remaining Enterprise Resource Duration custom fields that correspond to the SQL query and repeat steps 6 and 7.

9. Click OK.

How to fix the problem

To fix the problem, you must manually edit the Enterprise Resource Duration custom field value for each of the custom fields identified. You need to ensure that the field values are valid (between 0 and 34689600).

If the field identified by the query is a simple custom field (not a formula field), you can change the field value to fall within the allowed range. If the field identified by the query is a formula field, you will have to alter the formula to make sure that the results fall within the valid range.