Pre-migration verification and cleanup

This Office product will reach end of support on October 10, 2017. To stay supported, you will need to upgrade. For more information, see , Resources to help you upgrade your Office 2007 servers and clients.

 

Topic Last Modified: 2016-11-14

In this article:

• Run the migration tool with the -verify option

• Perform manual tests for migration problems

This article describes how to verify that the Microsoft Office Project Server 2003 source data that you plan to migrate is in a valid state to migrate to Microsoft Office Project Server 2007. The article describes useful tools for verifying that the data you plan to migrate is not invalid or corrupt. These include the migration tool's -verify option and SQL queries to run on your Project Server 2003 databases. It also describes general checks you should do in your Project Server 2003 environment prior to migrating.

Run the migration tool with the -verify option

The migration tool's -verify option examines your Project Server 2003 data for the following:

• Projects that are checked out

• Projects that have been modified externally

• Projects with pending status updates

• Duplicate enterprise resources

The migration tool determines whether changes need to be made to the data to make it suitable for migration. It writes those changes to the migration log file. It does not fix the data for you.

Note that the -verify option is not the only set of checks you need to perform on your data. You need to run through the remaining verification procedures in this article to ensure that your data is in a valid state to migrate.

You should read through the generated log file and make any necessary changes to your Project Server 2003 data to eliminate problems that can occur when migrating data.

Determining whether projects are checked out

A project cannot be migrated if it is checked-out. It is a best practice before any migration to get the source data to a stable state—consciously checking in projects ensures that a project is in a stable state for being migrated. Migration coordinators could set a deadline for Project Managers to check in their projects by a certain date, and beyond that date can force a check-in of all projects.

How to check for the problem

If a project to be migrated is checked out when you run the migration tool with the -verify option, you will see the following message on the console and in the log:

The following projects are checked out. These projects will not be migrated by the migration tool (the migration tool will not stop, it will just skip these projects). Please check them in.

Successful test result	Unsuccessful test result
The preceding message will not appear if all projects are checked in.	The preceding message will appear, followed by project names. Each project name will appear on a separate line.

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

Determining whether projects have been edited externally

A project cannot be migrated if it has been externally edited.

How to check for the problem

When you run the migration tool with the -verify option, if a project to be migrated has been externally edited, you will see the following message on the console and in the log:

The following projects have been externally modified. These projects will not be migrated by the migration tool (the migration tool will not stop, it will just skip these projects). Please open them using Project Professional 2003 and save them back to Project Server 2003.

Successful test result	Unsuccessful test result
The preceding message will not appear if all projects have not been externally edited.	The preceding message will appear, followed by project names. Each project name will appear on a separate line.

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.

If any rows results are returned, the projects referred to have been externally edited.

Determining whether projects have status updates pending

By default, if Project Server 2003 projects have pending updates, they will not migrate to Office Project Server 2007. Before migrating, we recommend that your users accept or reject status updates to ensure that projects are in a stable state to migrate.

How to check for the problem

If a project has any pending status updates when you run the migration tool with the -verify option, you will see the following message on the console and in the log:

The following projects have pending status updates. These projects will not be migrated by the migration tool (the migration tool will not stop, it will just skip these projects). Please apply the pending updates.

Successful test result	Unsuccessful test result
The preceding message will not appear if all projects do not have pending status updates.	The preceding message will appear, followed by project names. Each project name will appear on a separate line.

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 Configuring the migration tool for 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).

Determining whether there are duplicate Enterprise Resources

Having multiple resources in the enterprise resource pool with the same name is not supported in Project 2003 or Project 2007. If this situation occurs, it might be the result of direct database edits. Note that the duplicate names may not appear in Project Professional when you are editing the Enterprise Resource Pool. This occurs because there might be just one entry in the MSP_WEB_RESOURCES table for an enterprise resource with this name. There is no simple way to resolve this situation. Work with your Project Server expert to resolve it. Following are some guidelines to help you check for and fix issues that occur with duplicate enterprise resources.

How to check for the problem

If duplicate enterprise resources exist when you run the migration tool with the -verify option, you will see the following message on the console and in the log:

Your enterprise resource pool contains duplicate entries for the following resources. This is an unsupported scenario in Project 2003 and could cause enterprise resource migration to fail. Please make sure there are no duplicate resources before trying migration.

Successful test result	Unsuccessful test result
The preceding message will not appear if there are no duplicate enterprise resources.	The preceding message will appear, followed by resource names. Each resource name will appear on a separate line.

RES_UID	RES_NAME	RES_EUID
123	Peter Krebbs	123
124	Peter Krebbs	123
125	Brad Sutton	125
126	Brad Sutton	126

Check for duplicate enterprise resources used in your projects

The following SQL query checks for duplicate enterprise resources that are used in your projects. Because we will refer back to this query in the sections that follow, we will call it "query 2."

Query 2:

select  distinct 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 and res_euid is not null  order by res_name, res_euid asc

Running the query gives you a list of duplicate enterprise resources that are used in projects. If you see only one row per resource name, run query 4, located in the "Fix the duplicate enterprise resources to point to the correct one" section. If you see more than one row per resource name, run query 3, located in the "Fix projects that use duplicate enterprise resources" section.

In the following example, note that duplicate instances of ‘Brad Sutton’ are used in projects. This needs to be fixed.

RES_NAME	RES_EUID
Peter Krebbs	123
Brad Sutton	125
Brad Sutton	126

Fix projects that use duplicate enterprise resources

Running the following query (query 3) 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 need to 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 query 2 to repeat your verification test for duplicate enterprise resources used in projects.

Query 3: 

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 Script 3 returns the following results:

RES_NAME	RES_EUID	PROJ_ID
Brad Sutton	125	12
Brad Sutton	126	13
Brad Sutton	125	14

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

RES_NAME	RES_EUID	PROJ_ID
Brad Sutton	125	12
Brad Sutton	125	13
Brad Sutton	125	14

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.

RES_UID	RES_NAME	RES_EUID
123	Peter Krebbs	123
124	Peter Krebbs	123
125	Brad Sutton	125
126	Brad Sutton	126

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:

RES_UID	RES_NAME	RES_EUID
123	Peter Krebbs	123
124	Peter Krebbs	123
125	Brad Sutton	125
126	Brad Sutton	125

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.

Perform manual tests for migration problems

Running the migration tool with the -verify option allows you to automatically check your Project Server 2003 data for problems that can cause your migration to fail. You can also run these checks (as well as others that are not available through the verify option) manually. The first half of this article describes the automatic checks you can perform. The remainder of the article describes how to run manual checks on your Project Server 2003 data to prepare it for migration.

Project version should be at least 'Project Server 2003 SP2a'

Microsoft supports migration from Project Server 2003 SP2a. If you do not have this service pack installed, you will need to apply it. For more information about the service pack, see the Knowledge Base article named Description of Project Server 2003 Service Pack 2a (https://go.microsoft.com/fwlink/?LinkId=78242\&clcid=0x409).

How to check for the problem

Run the following SQL query in the "Project Server 2003 Web tables" database. This query tells you the specific version of Project Server, including the number of the service pack.

select replace(str(WADMIN_VERSION_MAJOR)+'.'+str(WADMIN_VERSION_MINOR),' ','') as 'Project Server Version' from dbo.MSP_WEB_ADMIN

Successful test result	Unsuccessful test result
11.2	11.1

How to fix the problem

Apply Project Server 2003 SP2a to your Project Server 2003 installation. For more information about applying Project Server 2003 SP2a, see Prepare for migration to Project Server 2007.

The Enterprise Global template should not be externally edited

The ‘Externally Edited’ flag is set to ‘true’ when the Enterprise Global template is edited outside of Project (for example, by a third-party application). Project Professional will check for this flag: If it is set to true, Project Professional recalculates all data in the Enterprise Global template to ensure consistency. Set this flag to ‘false’ to enable migration.

How to check for the problem

Run the following SQL query in the "Project Server 2003 Project tables" database. The following query checks the Project Server 2003 database for projects that have been externally edited. If any rows are returned, the Enterprise Global template cannot be migrated in its current state.

select PROJ_NAME from dbo.MSP_PROJECTS where (PROJ_EXT_EDITED = 1 or RESERVED_BINARY_DATA is null) and PROJ_TYPE = 2

Successful test result	Unsuccessful test result
No rows are returned.	One or more rows are returned.

How to fix the problem

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

Determining whether the Enterprise Global template is checked out

The Enterprise Global template should not be checked out when you are migrating your projects.

How to check for the problem

The following SQL query verifies whether the enterprise global is checked out. Run the following SQL query in the "Project Server 2003 Project tables" database.

select count(*) from dbo.MSP_PROJECTS where PROJ_CHECKEDOUT = 1 and PROJ_TYPE = 2

Successful test result	Unsuccessful test result
0	A value greater than "0" is returned.

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.

Determining whether the Enterprise Global template is locked

A project becomes locked if the computer shuts down abnormally, or if Project Professional exits abnormally. If the Enterprise Global template is locked, the migration tool cannot run.

How to check for the problem

The following SQL Query checks whether the Enterprise Global template file is locked. Run the following SQL query in the "Project Server 2003 Project tables" database.

select cast(isnull(PROJ_LOCKED, '0') as int) from dbo.MSP_PROJECTS where PROJ_TYPE = 2

Successful test result	Unsuccessful test result
0	1

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

Determining whether the default language on the Web tables database and Project tables database should match

The default language on the Project Server 2003 Web Tables database and the Project Server 2003 project tables database must match prior to migration.

How to check for the problem

This query determines the default language for each database. If the query results do not match, the migration fails. Run the following SQL query in the "Project Server 2003 Project tables" and the "Project Server 2003 Web Tables" databases.

select WADMIN_DEFAULT_LANGUAGE from dbo.MSP_WEB_ADMIN.

Successful test result	Unsuccessful test result
Results match when run on the two databases.	Results do not match when run on the two databases.

How to fix the problem

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

Determining whether a resource has a comma in its name

It is a limitation of Office Project Server 2007 that an Enterprise Resource (of any language) cannot have a comma character (,) in its name. The character must be replaced with a valid character.

How to check for the problem

This query checks the Enterprise Resource Pool for any resource names that contain a comma character. Run the following SQL query in the "Project Server 2003 Project tables" database.

select RES_NAME from MSP_RESOURCES where RES_NAME is not null and charindex(',', RES_NAME) > 0

Successful test result	Unsuccessful test result
No rows returned.	One or more rows returned.

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

Determining whether required enterprise resource custom fields do not have values

Any enterprise resource that does not have a value for a required enterprise custom field (like the RBS), will fail to migrate. It will post an error in the migration log during migration. We recommend that you check for this scenario before migration. If your enterprise resources are very old (created before the custom field was made ‘Required’) or inactive, they might not have values for the required custom field.

One way to fix this situation is to make sure all your Office Project Server 2007 resource custom fields are set to Not Required. You can then change them back manually in Office Project Server 2007.

There are two verification methods you can use to find out which custom fields caused the migration problem.

• Verification method 1 uses a SQL query on the "Project Server 2003 Project tables" database.

• Verification method 2 uses the Project Professional 2003 user interface.

How to check for the problem using verification method 1

This SQL query returns enterprise resources that have required enterprise custom fields that do not have a value. Run the following SQL query in the "Project Server 2003 Project tables" database.

declare @eglobal_proj_id int

set @eglobal_proj_id = (select PROJ_ID from dbo.MSP_PROJECTS where PROJ_TYPE = 2)

select

   r1.RES_NAME as 'Resource Name',

   ast1.AS_VALUE as 'Custom Field Name'

from

   dbo.MSP_RESOURCES r1

   inner join dbo.MSP_CODE_FIELDS cf1 on (r1.RES_UID = cf1.CODE_REF_UID)

   inner join dbo.MSP_FIELD_ATTRIBUTES fa1 on (cf1.CODE_FIELD_ID = fa1.ATTRIB_FIELD_ID)

   inner join dbo.MSP_ATTRIBUTE_STRINGS ast1 on (fa1.AS_ID = ast1.AS_ID)

   inner join dbo.MSP_OUTLINE_CODES oc3 on (cf1.CODE_UID = oc3.CODE_UID and oc3.PROJ_ID = @eglobal_proj_id)

   left join dbo.MSP_OUTLINE_CODES oc1 on (oc1.CODE_UID = cf1.CODE_UID and cf1.CODE_FIELD_ID = oc1.OC_FIELD_ID and oc1.PROJ_ID = @eglobal_proj_id)

   left join (

      select

         oc.CODE_UID,

         fa.ATTRIB_FIELD_ID as OC_FIELD_ID,

         @eglobal_proj_id as PROJ_ID

      from

         dbo.MSP_OUTLINE_CODES oc

         inner join dbo.MSP_FIELD_ATTRIBUTES fa on (fa.PROJ_ID = oc.PROJ_ID and fa.ATTRIB_VALUE = oc.OC_FIELD_ID and fa.ATTRIB_ID = 212)

      where

         oc.PROJ_ID = @eglobal_proj_id

         and fa.PROJ_ID = @eglobal_proj_id

         and fa.ATTRIB_ID = 212

   ) as oc2 on (oc2.CODE_UID = cf1.CODE_UID and cf1.CODE_FIELD_ID = oc2.OC_FIELD_ID and oc2.PROJ_ID = @eglobal_proj_id)

where

   r1.PROJ_ID = 1

   and cf1.proj_id = 1

   and fa1.PROJ_ID = @eglobal_proj_id

   and fa1.ATTRIB_ID = 206

   and ast1.PROJ_ID = @eglobal_proj_id

   and oc3.PROJ_ID = @eglobal_proj_id

   and oc1.CODE_UID is null

   and oc2.CODE_UID is null

order by

   r1.RES_NAME,

   ast1.AS_VALUE

Successful test result	Unsuccessful test result
No rows are returned.	One or more are rows returned.

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

How to check for the problem using verification method 2

This method requires the use of Project Professional 2003 to open the Enterprise Resource Pool. By attempting to save the Enterprise Resource Pool, you will verify whether all selected enterprise resources have values for their required custom fields.

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

2. On the Tools menu, click Enterprise Options, and then click Open Enterprise Resource Pool.

3. In the Open Enterprise Resources dialog box that appears, select the Include inactive resources check box. Verify that Read/write to check out is selected. Click the Apply filter button.

4. Click the Select/Deselect all button to make sure all resources are selected.

5. Click the Open/Add button. Once the resources are opened for editing, click Save. Project Professional then validates whether each enterprise resource (active or inactive) has values for the required custom fields.

   Successful test result	Unsuccessful test result
   After you save, no error messages appear.	An error message appears, listing errors for resources that do not have values for required enterprise custom fields.

How to fix the problem

In Project Server 2003, set values for the required custom fields, or set the affected custom fields as Not Required.

Determining whether a 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 check for the problem

This SQL query checks for invalid resource custom fields by checking whether the custom field value is in the corresponding lookup table.

Run the following SQL query in the "Project Server 2003 Project tables" and the "Project Server 2003 Web Tables" databases.

declare @eglobal_proj_id int

set @eglobal_proj_id = (select PROJ_ID from dbo.MSP_PROJECTS where PROJ_TYPE = 2)

select

   r1.RES_NAME,

   ast1.AS_VALUE

from

   dbo.MSP_RESOURCES r1

   inner join dbo.MSP_CODE_FIELDS cf1 on (r1.RES_UID = cf1.CODE_REF_UID)

   inner join dbo.MSP_FIELD_ATTRIBUTES fa1 on (cf1.CODE_FIELD_ID = fa1.ATTRIB_FIELD_ID)

   inner join dbo.MSP_ATTRIBUTE_STRINGS ast1 on (fa1.AS_ID = ast1.AS_ID)

   left join dbo.MSP_OUTLINE_CODES oc1 on (oc1.CODE_UID = cf1.CODE_UID and cf1.CODE_FIELD_ID = oc1.OC_FIELD_ID and oc1.PROJ_ID = @eglobal_proj_id)

   left join (

      select

         oc.CODE_UID,

         fa.ATTRIB_FIELD_ID as OC_FIELD_ID,

         @eglobal_proj_id as PROJ_ID

      from

         dbo.MSP_OUTLINE_CODES oc

         inner join dbo.MSP_FIELD_ATTRIBUTES fa on (fa.PROJ_ID = oc.PROJ_ID and fa.ATTRIB_VALUE = oc.OC_FIELD_ID and fa.ATTRIB_ID = 212)

      where

         oc.PROJ_ID = @eglobal_proj_id

         and fa.PROJ_ID = @eglobal_proj_id

         and fa.ATTRIB_ID = 212

   ) as oc2 on (oc2.CODE_UID = cf1.CODE_UID and cf1.CODE_FIELD_ID = oc2.OC_FIELD_ID and oc2.PROJ_ID = @eglobal_proj_id)

where

   r1.PROJ_ID = 1

   and cf1.proj_id = 1

   and cf1.code_uid is not null

   and fa1.PROJ_ID = @eglobal_proj_id

   and fa1.ATTRIB_ID = 206

   and ast1.PROJ_ID = @eglobal_proj_id

   and oc1.CODE_UID is null

   and oc2.CODE_UID is null

union

select

   r1.RES_NAME,

   ast1.AS_VALUE

from

   dbo.MSP_RESOURCES r1

   inner join dbo.MSP_MV_FIELDS cf1 on (r1.RES_UID = cf1.CODE_REF_UID)

   inner join dbo.MSP_FIELD_ATTRIBUTES fa1 on (cf1.CODE_FIELD_ID = fa1.ATTRIB_FIELD_ID)

   inner join dbo.MSP_ATTRIBUTE_STRINGS ast1 on (fa1.AS_ID = ast1.AS_ID)

   left join dbo.MSP_OUTLINE_CODES oc1 on (oc1.CODE_UID = cf1.CODE_UID and (cf1.CODE_FIELD_ID - 76) = oc1.OC_FIELD_ID and oc1.PROJ_ID = @eglobal_proj_id)

   left join (

      select

         oc.CODE_UID,

         fa.ATTRIB_FIELD_ID as OC_FIELD_ID,

         @eglobal_proj_id as PROJ_ID

      from

         dbo.MSP_OUTLINE_CODES oc

         inner join dbo.MSP_FIELD_ATTRIBUTES fa on (fa.PROJ_ID = oc.PROJ_ID and fa.ATTRIB_VALUE = oc.OC_FIELD_ID and fa.ATTRIB_ID = 212)

      where

         oc.PROJ_ID = @eglobal_proj_id

         and fa.PROJ_ID = @eglobal_proj_id

         and fa.ATTRIB_ID = 212

   ) as oc2 on (oc2.CODE_UID = cf1.CODE_UID and (cf1.CODE_FIELD_ID - 76) = oc2.OC_FIELD_ID and oc2.PROJ_ID = @eglobal_proj_id)

where

   r1.PROJ_ID = 1

   and cf1.proj_id = 1

   and cf1.code_uid is not null

   and fa1.PROJ_ID = @eglobal_proj_id

   and fa1.ATTRIB_ID = 206

   and ast1.PROJ_ID = @eglobal_proj_id

   and oc1.CODE_UID is null

   and oc2.CODE_UID is null

order by

   r1.RES_NAME,

   ast1.AS_VALUE

Successful test result	Unsuccessful test result
No rows are returned.	One or more rows are returned.

How to fix the problem

The following SQL query sets invalid resource custom field values to null. Run the following SQL query in the "Project Server 2003 Project tables" databases.

declare @eglobal_proj_id int

set @eglobal_proj_id = (select PROJ_ID from dbo.MSP_PROJECTS where PROJ_TYPE = 2)

update dbo.MSP_MV_FIELDS

set

   CODE_UID = NULL

from dbo.MSP_MV_FIELDS cf1

   left join dbo.MSP_OUTLINE_CODES oc1 on (oc1.CODE_UID = cf1.CODE_UID and (cf1.CODE_FIELD_ID - 76) = oc1.OC_FIELD_ID and oc1.PROJ_ID = @eglobal_proj_id)

   left join (

      select

         oc.CODE_UID,

         fa.ATTRIB_FIELD_ID as OC_FIELD_ID,

         @eglobal_proj_id as PROJ_ID

      from

         dbo.MSP_OUTLINE_CODES oc

         inner join dbo.MSP_FIELD_ATTRIBUTES fa on (fa.PROJ_ID = oc.PROJ_ID and fa.ATTRIB_VALUE = oc.OC_FIELD_ID and fa.ATTRIB_ID = 212)

      where

         oc.PROJ_ID = @eglobal_proj_id

         and fa.PROJ_ID = @eglobal_proj_id

         and fa.ATTRIB_ID = 212

   ) as oc2 on (oc2.CODE_UID = cf1.CODE_UID and (cf1.CODE_FIELD_ID - 76) = oc2.OC_FIELD_ID and oc2.PROJ_ID = @eglobal_proj_id)

where

   cf1.proj_id = 1

   and oc1.CODE_UID is null

   and oc2.CODE_UID is null

update dbo.MSP_CODE_FIELDS

set

   CODE_UID = NULL

from dbo.MSP_CODE_FIELDS cf1

   left join dbo.MSP_OUTLINE_CODES oc1 on (oc1.CODE_UID = cf1.CODE_UID and cf1.CODE_FIELD_ID = oc1.OC_FIELD_ID and oc1.PROJ_ID = @eglobal_proj_id)

   left join (

      select

         oc.CODE_UID,

         fa.ATTRIB_FIELD_ID as OC_FIELD_ID,

         @eglobal_proj_id as PROJ_ID

         from

         dbo.MSP_OUTLINE_CODES oc

         inner join dbo.MSP_FIELD_ATTRIBUTES fa on (fa.PROJ_ID = oc.PROJ_ID and fa.ATTRIB_VALUE = oc.OC_FIELD_ID and fa.ATTRIB_ID = 212)

      where

         oc.PROJ_ID = @eglobal_proj_id

         and fa.PROJ_ID = @eglobal_proj_id

         and fa.ATTRIB_ID = 212

   ) as oc2 on (oc2.CODE_UID = cf1.CODE_UID and cf1.CODE_FIELD_ID = oc2.OC_FIELD_ID and oc2.PROJ_ID = @eglobal_proj_id)

where

   cf1.proj_id = 1

   and oc1.CODE_UID is null

   and oc2.CODE_UID is null

update dbo.MSP_PROJECTS set PROJ_EXT_EDITED = 1, PROJ_EXT_EDITED_CODE = 1 where PROJ_ID = 1

Determining whether Enterprise resources are externally edited

In order for enterprise resources to be migrated from Project Server 2003 to Project Server 2007, they must not be externally edited.

How to check for the problem

The following query checks the Enterprise Resource Pool for enterprise resources that have been externally edited. If the test results are unsuccessful, the Enterprise Resource Pool cannot be migrated in its current state. Run the following SQL query in the "Project Server 2003 Project tables".

select count(*) from dbo.MSP_RESOURCES  where PROJ_ID = 1 and  cast(EXT_EDIT_REF_DATA as varchar(1)) = '1'

Successful test result	Unsuccessful test result
0	A value greater than "0" is returned.

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.

Determining whether there are Enterprise Resource Duration custom fields with value lists

Enterprise Resource Duration custom fields with associated value lists will cause migration to fail.

How to check for the problem

This SQL query checks for Enterprise Resource Duration custom fields with associated value lists. Run the following SQL query in the "Project Server 2003 Project tables" database.

declare @proj_id int

set @proj_id = (select proj_id from msp_projects where proj_type = 2)

select ats.as_value as CustomFieldName from msp_attribute_strings ats

inner join msp_field_attributes fa on (fa.proj_id = ats.proj_id and fa.as_id = ats.as_id)

where fa.attrib_id = 206 and fa.proj_id = @proj_id and fa.attrib_field_id >= 205521382 and fa.attrib_field_id <= 205521391

and exists (select * from msp_field_attributes fa2 where fa2.proj_id = fa.proj_id and fa2.attrib_field_id = fa.attrib_field_id and fa2.attrib_id = 210)

Successful test result	Unsuccessful test result
No rows are returned.	One or more rows are returned. These Enterprise Resource Duration custom fields have value lists associated with them. The value list must be manually removed for each.

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.

Determining whether Enterprise Resource Duration custom fields contain valid values

Office Project Server 2007 does not allow negative duration values or duration values greater than 34689600. If you attempt to migrate Project Server 2003 Enterprise Resource Duration custom fields which contain either, your migration will fail.

How to check for the problem

To check for the problem, run the following SQL query on the "Project Server 2003 Project tables" database. This query will return the Enterprise Resource Duration custom fields with invalid values.

select r.res_name, mas.as_value from msp_resources r

inner join msp_duration_fields df on (df.dur_ref_uid = r.res_euid and df.proj_id = r.proj_id)

inner join msp_projects p on (p.proj_type = 2)

inner join msp_field_attributes fa on (fa.proj_id = p.proj_id and fa.attrib_field_id = df.dur_field_id)

inner join msp_attribute_strings mas on (mas.proj_id = p.proj_id and fa.as_id = mas.as_id)

where (dur_value < 0 or dur_value > 34689600) and df.proj_id = 1 and p.proj_type = 2 and fa.attrib_id = 206

Successful test result	Unsuccessful test result
No rows are returned.	One or more rows are returned.

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.

Determining whether Enterprise Custom Field names do not contain leading or trailing spaces

Verify that your Office Project Server 2007 custom field names do not contain any leading or trailing spaces (for example, __Custom Field_ (where the underscore character represents a space character). If you attempt to migrate custom fields with leading or trailing space characters, migration will fail with an error.

Determining whether the Migration Administrator Account exists in Project Server 2003

If the ‘Migration Administrator Account’ exists in Project Server 2003 (either by name or Windows Account)—those user permissions, category mappings, and user properties will not migrate to Office Project Server 2007. In essence, the Office Project Server 2007 user wins. Note that if the ‘Migration Administrator Account’ is used in projects, those resources will be replaced with the ‘Migration Administrator Account’.

Replacing comma characters in enterprise resource names

If the Project Server 2003 databases have enterprise resource names containing a comma character, migration of the resource will fail. This may be a problem for customers in certain locales where using the comma character in resource names is commonly practiced.

How to fix the problem

Use the following SQL queries to replace each comma character with a neutral underscore character.

Run this query on the Project tables database:

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 query on the Web tables database:

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

Download this book

This topic is included in the following downloadable book for easier reading and printing:

• Migration for Office Project Server 2007

See the full list of available books at Downloadable content for Project Server 2007.