Export (0) Print
Expand All

Run Migration Script 1 to check the Project Server 2003 data

 

Applies to: Project Server 2010

Topic Last Modified: 2013-12-18

Migration Script 1 is one of several migration scripts included in the Project Server virtual migration environment (VME) to assist in migrating Microsoft Office Project Server 2003 data. Running this script against the Project Server 2003 database identifies possible errors that will prevent the migration process from completing successfully. This SQL Server script only reads from your Project Server 2003 database. (it will not write to the Project Server 2003 database.)

warningWarning
This script is one of several pre-migration scripts included in the Project Server virtual migration environment (VME). Running the scripts is optional, but highly recommended for helping to detect issues that may prevent a successful migration of your data. For more information about the pre-migration scripts that are available, see Project Server VME: Run pre-migration scripts (optional).

This script identifies the following problems:

  • Version of Project Server 2003 database (which must be SP3 before the migration)

  • Projects are checked out

  • Projects have been externally edited

  • Projects have status updates pending

  • Duplicate enterprise resources exist

  • Duplicate enterprise resources exist in projects

  • Enterprise Global template have been externally edited

  • Enterprise Global template is checked out

  • Enterprise Global template is locked

  • Default language is different between the Project tables and the Web tables

  • Resource has a comma in its name

  • Required enterprise resource custom fields have no values

  • Required enterprise resource custom fields have values which are not in the lookup table definition

  • Enterprise resources have been externally edited

  • Enterprise resource duration custom fields have value lists

  • Enterprise resource duration custom fields have invalid values

To run Script 1
  1. On the VME desktop, click Start Migration Process. This opens a Windows Explorer window that displays the contents of drive E.

  2. In Windows Explorer, double-click the following folder:

    • If you have one Project Server 2003 database, open the Migrate_Proj_2003_Single_DB folder.

    • If you have split Project Server 2003 databases, open Migrate_Proj_2003_Split_DB.

  3. Open the Verification Scripts folder, and then click VME Script 1.sql. This opens SQL Server Management Studio and displays Script 1.

  4. Click Execute to run the script.

  5. Check the results of the script. Take corrective action as necessary.

noteNote
For additional information about Project Server 2003 data validation steps and corrective actions, see Fix data issues identified in pre-migration script A1 (Project Server 2010).

You have to make corrections to the Project Server 2003 database through Microsoft Office Project Professional 2003 in your Project Server 2003 environment. After making corrections, make a backup copy of the Project Server 2003 database, and then use the procedures in Load data to the Project Server VME to restore the updated database to the VME virtual machine. You can then re-run Script 1 on the updated database to verify that all issues have been fixed.

Script 1 contains the following code:

------------------------------------------------------------------------------
/* Pre-Migration Steps from Project 2003 SP3 to Project 2007 SP2
-----------------------------------------------------------------------------*/
USE Project2003SourceDB

------------------------------------------------------------------------------
/* Check Project 2003 Version: Must be SP3 = 11.3
-----------------------------------------------------------------------------*/
select replace(str(WADMIN_VERSION_MAJOR)+'.'+str(WADMIN_VERSION_MINOR),' ','') 
as 'Project Server Version SP3 Must Be 11.3 or Higher. If not, upgrade your 2003 database to SP3' 
from dbo.MSP_WEB_ADMIN
go
------------------------------------------------------------------------------
/* Display the projects checked out?
-----------------------------------------------------------------------------*/
select PROJ_NAME AS 'List of Projects Checked-out: Must be Fixed'
from dbo.MSP_PROJECTS where PROJ_CHECKEDOUT = 1 and PROJ_TYPE in (0, 1)
go
------------------------------------------------------------------------------
/* Display the projects Externally Edited?
-----------------------------------------------------------------------------*/
select PROJ_NAME AS 'List of Projects Externally Edited: Must be Fixed' 
from dbo.MSP_PROJECTS where (PROJ_EXT_EDITED = 1 or RESERVED_BINARY_DATA is null) and PROJ_TYPE in (0, 1)
go
------------------------------------------------------------------------------
/* Determining whether projects have status updates pending
-----------------------------------------------------------------------------*/
select distinct PROJ_NAME AS 'List of Projects with Status Updates Pending: Must be Fixed' 
from dbo.MSP_WEB_ASSIGNMENTS wa, dbo.MSP_WEB_TRANSACTIONS trans, 
dbo.MSP_WEB_PROJECTS wp where wa.WPROJ_ID = wp.WPROJ_ID 
and trans.WASSN_ID = wa.WASSN_ID and trans.WTRANS_STATE in (0, 1, 2)
go
------------------------------------------------------------------------------
/* Determining whether there are duplicate Enterprise Resources
-----------------------------------------------------------------------------*/
select res_uid, res_name AS 'Duplicate Enterprise Resources: Must be Fixed' 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

go
------------------------------------------------------------------------------
/* Determining whether there are duplicate Enterprise Resources
Check for duplicate enterprise resources used in your projects
-----------------------------------------------------------------------------*/
select  distinct res_name AS 'Duplicate Enterprise Resources Used in Projects: Must be Fixed', 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
go

------------------------------------------------------------------------------
/* Enterprise Global template should not be externally edited
-----------------------------------------------------------------------------*/
select PROJ_NAME 'Enterprise Global Template Externally Edited: Must be Fixed' from dbo.MSP_PROJECTS 
where (PROJ_EXT_EDITED = 1 or RESERVED_BINARY_DATA is null) 
and PROJ_TYPE = 2

go
------------------------------------------------------------------------------
/* Determining whether the Enterprise Global template is checked out
-----------------------------------------------------------------------------*/
select count(*) AS 'Enterprise Global Template Checked Out: Must be Fixed' from dbo.MSP_PROJECTS 
where PROJ_CHECKEDOUT = 1 and PROJ_TYPE = 2

go
------------------------------------------------------------------------------
/* Determining whether the Enterprise Global template is locked
-----------------------------------------------------------------------------*/
select cast(isnull(PROJ_LOCKED, '0') as int) AS 'Enterprise Global Template Is Locked: Must be Fixed'
 from dbo.MSP_PROJECTS where PROJ_TYPE = 2

go
---------------------------------------------------------------------------------------------------------------
/* Determining whether the default language on the Web tables database and Project tables database should match
--------------------------------------------------------------------------------------------------------------*/
select WADMIN_DEFAULT_LANGUAGE As 'The Deafault Language on Web and Project Tables should Match: Must be Fixed' 
from dbo.MSP_WEB_ADMIN

go
---------------------------------------------------------------------------------------------------------------
/* Determining whether a resource has a comma in its name
--------------------------------------------------------------------------------------------------------------*/
select RES_NAME 'List of Resources With a Comma in the Name: Not Allowed: Must be Fixed' 
from MSP_RESOURCES where RES_NAME is not null and charindex(',', RES_NAME) > 0

go
---------------------------------------------------------------------------------------------------------------
/* Determining whether required enterprise resource custom fields do not have values
--------------------------------------------------------------------------------------------------------------*/
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 with NO VALUE: Must be Fixed'
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

go
---------------------------------------------------------------------------------------------------------------
/* Determining whether a resource custom field has a value which is not in the lookup table definition
--------------------------------------------------------------------------------------------------------------*/
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 with Invalid Value: Must be Fixed'
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

go
---------------------------------------------------------------------------------------------------------------
/* Determining whether Enterprise resources are externally edited
--------------------------------------------------------------------------------------------------------------*/
select count(*) AS 'Number of Enterprise Resources Externally Edited: Must be Fixed' 
from dbo.MSP_RESOURCES  
where PROJ_ID = 1 and  cast(EXT_EDIT_REF_DATA as varchar(1)) = '1'

go
---------------------------------------------------------------------------------------------------------------
/* Determining whether There are Enterprise Resource Duration Custom Fields with Value Lists
--------------------------------------------------------------------------------------------------------------*/
declare @proj_id int
set @proj_id = (select proj_id from msp_projects where proj_type = 2)
select ats.as_value as 'Enterprise Resource Duration Custom Field with Value Lists: Must be Fixed' 
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)

go
---------------------------------------------------------------------------------------------------------------
/* Determining whether Enterprise Resource Duration custom fields contain valid values
--------------------------------------------------------------------------------------------------------------*/
select r.res_name AS 'Resource Name'
, mas.as_value AS 'Enterprise Resource Duration Custom Field with Invalid Value: Must be Fixed'
 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

go


Was this page helpful?
(1500 characters remaining)
Thank you for your feedback
Show:
© 2014 Microsoft