Export (0) Print
Expand All
This topic has not yet been rated - Rate this topic

Run Migration Script 9 to test your Project Server 2007 data for known migration issues

Project Server 2010

Published: May 15, 2010

Run Script 9 to run a data validation check on your Microsoft Office Project Server 2007 databases to look for known conditions that may cause an upgrade to Microsoft Project Server 2010 to fail. Script 9 checks for the following conditions:

  1. Office Project Server 2007 is updated to Service Pack 2 and the October 2009 Cumulative Update

  2. Projects not saved and published since the installation to Office Project Server 2007 of both Service Pack 2 and the October 2009 Cumulative Update

  3. Lookup tables that are using names that are reserved in Project Server 2010

  4. Custom field names that are reserved in Project Server 2010

  5. Office Web Components (OWC) views in Office Project Server 2007 that need to be migrated to Excel Services for use in Project Server 2010

  6. User accounts with forms-authenticated login names

  7. Team names that have no associated lookup table or a value

  8. Office Project Web Access Portfolio Analyzer OWC views in Project Server 2003 that may need to be migrated to Office Project Server 2007

note Note:

This script is one of several post-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 post-migration scripts that are available, see Project Server VME: Run post-migration scripts (optional).

To run Script 9

  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 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 9.sql. This opens SQL Server Management Studio and displays Script 9.

  4. Click Execute to run the script.

  5. The results will appear. Try to resolve any issues that are found before migrating your data to Project Server 2010.

You can use Microsoft Office Project Professional 2007 (included in the VME) to try to resolve some of data issues that are detected in this script.

Script 9

Script 9 includes the following code:

------------------------------------------------------------------------------
/* Pre-Migration Steps from Project 2007 SP2 to Project 2010
VME Script 12
-----------------------------------------------------------------------------*/

------------------------------------------------------------------------------
/* Check Project 2007 Version: Must be SP2 + October CU = ??
-----------------------------------------------------------------------------*/
USE ProjectServer_Draft
select Version 
as 'Project Server 2007 SP2 + Oct09CU Version Must Be 12.0.6520.5000 or Higher. If not, upgrade to SP2 + Oct2009 CU' 
, timestamp
from dbo.Versions
go
----------------------------------------------------------------------------------------------
/* Display the projects not saved or published since the installation of SP2 + December 09 CU?
----------------------------------------------------------------------------------------------*/
select Distinct PROJ_NAME AS 'List of Projects Not Saved and Published Since the Installation of Service Pack2'
,PROJ_PROP_AUTHOR, PROJ_LAST_SAVED, WPROJ_LAST_PUB
from dbo.MSP_PROJECTS 
where PROJ_TYPE = 0 
and ((dbo.MSP_PROJECTS.PROJ_LAST_SAVED < (SELECT MAX(TimeStamp) FROM dbo.Versions))
OR (dbo.MSP_PROJECTS.WPROJ_LAST_PUB < (SELECT MAX(TimeStamp) FROM dbo.Versions))) 
GROUP BY PROJ_NAME,PROJ_PROP_AUTHOR, PROJ_LAST_SAVED, WPROJ_LAST_PUB
Order by PROJ_NAME
go
------------------------------------------------------------------------------
/* Display the Lookup tables using reserved names in 2010
This query is to be run against the Published database of Project Server 2007
-----------------------------------------------------------------------------*/
USE ProjectServer_Published
select LT_NAME 
AS 'List of Lookup Table names with Reserved Names. These names must be changed before proceeding with the Upgrade'
from dbo.MSP_LOOKUP_TABLES 
WHERE LT_NAME IN
('Department',
'Project Impact',
'Relative Importance',
'Sample Areas Impacted',
'Sample Primary Objective')
-- Cost Type, Health, RBS and State are standard in 2007
go
------------------------------------------------------------------------------
/* Display the Custom Fields using reserved names in 2010
This query is to be run against the Published database of Project Server 2007
-----------------------------------------------------------------------------*/
USE ProjectServer_Published
select MD_PROP_NAME
AS 'List of Custom Fields names with Reserved Names. These names must be changed before proceeding with the Upgrade'
from dbo.MSP_CUSTOM_FIELDS 
WHERE MD_PROP_NAME IN 
('Flag Status',
'Project Departments',
'Project Impact',
'Relative Importance',
'Resource Departments',
'Sample Approved Finish Date',
'Sample Approved Start Date',
'Sample Areas Impacted',
'Sample Assumptions',
'Sample Business Need',
'Sample Compliance Proposal',
'Sample Goals',
'Sample Post Implementation Review Date',
'Sample Post Implementation Review Notes',
'Sample Primary Objectives',
'Sample Proposal Cost',
'Sample Proposed Finish Date',
'Sample Proposed Start Date')
go
---------------------------------------------------------------------------------------------------------
/* Displays Existing OWC Views in 2007 that need to be migrated to Excel Services in Project Server 2010
--------------------------------------------------------------------------------------------------------*/
select WVIEW_NAME AS 'List of PWA Data Analysis OWC Views to Migrate to Excel Services in Project Server 2010' 
FROM dbo.MSP_WEB_VIEW_REPORTS 
WHERE WVIEW_OWC_PIVOT_XML is not null
OR WVIEW_OWC_CHART_XML is not null

------------------------------------------------------------------------------
/* Displays User Accounts with Forms Auth Login Names > 249 Ch. -- on 2007
-----------------------------------------------------------------------------*/
select RES_NAME AS 'List of FBA accounts with Names > 249 Ch. Shorten the Resource Names below'
FROM dbo.MSP_RESOURCES
WHERE RES_IS_WINDOWS_USER = 0
AND (Select LEN(RES_NAME)) > 249
go
------------------------------------------------------------------------------
/* Display Team Names with no Lookup Tables and a Value -- on 2007
-----------------------------------------------------------------------------*/
select TEXT_VALUE AS 'List of Team Names with values and NO Lookup Tables. Create a LT for these Teams'
FROM dbo.MSP_RES_CUSTOM_FIELD_VALUES, dbo.MSP_CUSTOM_FIELDS, dbo.MSP_LOOKUP_TABLES
WHERE dbo.MSP_RES_CUSTOM_FIELD_VALUES.CUSTOM_FIELD_UID = dbo.MSP_CUSTOM_FIELDS.MD_PROP_UID
AND MD_PROP_NAME = 'Team Name'
AND dbo.MSP_CUSTOM_FIELDS. MD_LOOKUP_TABLE_UID IS NULL
go

------------------------------------------------------------------------------------------------------------------
/* Displays Existing PWA Portfolio Analyzer OWC Views in 2003 that may need to be migrated to Project Server 2007
-----------------------------------------------------------------------------------------------------------------*/
USE Project2003SourceDB  
select WVIEW_NAME AS 'List of PWA Portfolio Analyzer OWC Views to Migrate to Project Server 2007' 
FROM dbo.MSP_WEB_VIEW_REPORTS 
WHERE WVIEW_OWC_PIVOT_XML is not null
OR WVIEW_OWC_CHART_XML is not null

Did you find this helpful?
(1500 characters remaining)
Thank you for your feedback
Show:
© 2014 Microsoft. All rights reserved.