Share via

  • Article

SQL script for known Project Server 2010 upgrade issues

 

Applies to: Project Server 2010

Topic Last Modified: 2011-02-16

The SQL script for Known Upgrade Issues runs a data validation check on your Office Project Server 2007 databases to look for known conditions that may cause an upgrade to Project Server 2010 to fail. This script is run on Microsoft Office Project Server 2007 databases (Draft, Archive, Published, and Reporting) that you plan to upgrade to Project Server 2010. Running the script is optional, but it is highly recommended to detect issues that may prevent a successful upgrade of your data.

This script checks for the following conditions:

  • Office Project Server 2007 not updated to Service Pack 2 or a later version

  • Projects not saved and published since the installation of Office Project Server 2007 Service Pack 2

  • Lookup tables that are using names that are reserved in Project Server 2010

  • Custom field names that are reserved in Project Server 2010

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

  • User accounts that use forms-authenticated logon names

  • Team names that have no associated lookup table or value

Note

Although this script does not make any changes to your Office Project Server 2007 databases, we recommend that you run it on a restored copy of your Office Project Server 2007 databases. Another option is to have the script run by a SQL Server account that only has read permissions to the databases.

To run the Known Upgrade Issues script

  1. On the computer that contains the Office Project Server 2007 databases, open the SQL Server Management Studio Database Engine.

  2. In Management Studio, click New Query.

  3. Copy and paste the script text into the Query Editor window in Management Studio.

    Note

    You can copy the script file directly from this article to your clipboard by clicking the Copy Code link located at the top of the script.

  4. After pasting the script text in the Query Editor, you must make the following changes to it:

    • Edit the USE <ProjectServer_Draft> line to specify the name of your Office Project Server 2007 Draft database. (For example, USE Litware_Draft)

    • Edit the two lines that read USE <ProjectServer_Published> to specify the name of your Office Project Server 2007 Published database. (For example, USE Litware_Published)

  5. Click Execute to run the script.

After you have run the script, you can make necessary changes to your Office Project Server 2007 data before you try to upgrade:

  • If the results show that Office Project Server 2007 is not updated to Service Pack 2 or a later version, update your Office Project Server 2007 environment with the latest updates. For more information, see Deploy Project Server 2007 updates.

  • If the results display projects that have not been saved and published since the installation of Office Project Server 2007 SP2, verify that Service Pack 2 is installed, and then open and republish the projects to Office Project Server 2007.

  • If the results include lookup table names that are reserved in Project Server 2010, rename the lookup tables. For a complete list of lookup table names reserved in Project Server 2010, see Project Server 2010 upgrade overview.

  • If the results include custom field names that are reserved in Project Server 2010, rename the custom fields. For a complete list of custom field names reserved in Project Server 2010, see the Project Server 2010 upgrade overview.

  • If the results include views that use Office Web Components that you want to keep in Project Server 2010, re-create these views in Excel Services (OWC is not used in Project Server 2010).

  • If the results include team names that have no lookup table, create a lookup table for these teams.

After making changes, create new backup copies of your Office Project Server 2007 databases, and then re-run the script on these databases to verify that the changes have been corrected.

Script

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

-----------------------------------------------------------------------------*/

------------------------------------------------------------------------------
/* Check Project 2007 Version: Must be SP2 or higher
-----------------------------------------------------------------------------*/
USE <ProjectServer_Draft>
select Version 
as 'Project Server 2007 SP2 Version Must Be 12.0.6422.1000 or Higher. If not, upgrade to SP2' 
, timestamp
from dbo.Versions
go
----------------------------------------------------------------------------------------------
/* Display the projects not saved or published since the installation of SP2 
----------------------------------------------------------------------------------------------*/
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