Run Migration Script 2 to capture statistics about the data
Applies to: Project Server 2010
Topic Last Modified: 2013-12-18
Migration Script 2 is one of several migration scripts included in the Project Server virtual migration environment (VME) to assist in migrating your Microsoft Office Project Server 2003 data. Run Script 2 to capture statistics about the project data contained in your Project Server 2003 database that you plan to migrate.
Warning
This script is one of several pre-migration scripts included in the Microsoft Office Project Server 2003 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's output provides information about the following:
Size of the databases (the Project Tables and Web Tables databases)
Number of projects
Number of inserted projects
Number of cross-project links
Number of tasks
Number of assignments
Number of Enterprise resources
Number of custom fields in use
Number of Project-authenticated users
Number of Windows-authenticated users
Number of security groups
Number of security categories
Whether there is Windows SharePoint Services integration
Number of linked issues
Number of linked risks
Number of linked documents
Number of custom Office Project Web Access views
This script's output provides information about the Project Server 2003 data that you are migrating. You can make a general estimate of how long the migration process will take from some of this data. Generally, the migration process will take about 30 seconds to one minute for each project that you are migrating, varying by a number of factors, such as number of assignments, tasks, computer speed, and so on. Therefore, if your Project Server 2003 database contained 600 projects, it would take about 10 hours to migrate, based on the one minute per project estimate.
To run Script 2
On the VME desktop, click Start Migration Process. This opens a Windows Explorer window that displays the contents of drive E.
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.
Open the Verification Scripts folder, and then click VME Script 2.sql. This opens SQL Server Management Studio and displays Script 2.
Click Execute to run the script.
Check the results of the script for information about your data.
Script 2
Script 2 contains the following code:
/*-----------------------------------------------------------------------------------------------------------
Script A2: Project Server 2003 Database Profile
This script reads the Project Server 2003 database (Project Tables and View Tables may be in the same or separate database)
-------------------------------------------------------------------------------------------------------------*/
use <Enter "Project Tables" database name>
--Total size of DB
exec sp_spaceused
--Number of projects in the database
select count(*) as 'Total Number of Projects in 2003' from msp_projects
--Number of inserted projects
select count(*) as 'Number of Inserted Projects in 2003'
from MSP_TEXT_FIELDS where TEXT_FIELD_ID = 188743706
--Number of cross-project links
select count(*) as 'Number of cross-project links in 2003'
from MSP_TEXT_FIELDS
where TEXT_FIELD_ID = 239075346 or TEXT_FIELD_ID = 239075347
--Number of total tasks, assignments in the system
select count(*) as 'Number of tasks in 2003' from msp_tasks
select count(*) as 'Number of assignments in 2003' from msp_assignments
--Number of Enterprise resources (in ResGlobal)
select count(*) as 'Number of Enterprise Resources in 2003'
from msp_resources where proj_id = 1 and res_name is not null
--Number of custom fields in use
select count(*) as 'Number of Custom Fields in use in 2003'
from msp_field_attributes
where attrib_id = 206 and proj_id in (select proj_id
from msp_projects where proj_type =2 )
/*-----------------------------------------------------------------------------------------------------------
This script reads the Project Server 2003 database (Project Tables and View Tables may be in the same or separate database)
-------------------------------------------------------------------------------------------------------------*/
use <Enter "Web Tables" database name>
--Total size of DB
exec sp_spaceused
--Number of Project Authenticated users, Windows authenticated users
select count(*) as 'Number of Project Authenticated users in 2003'
from MSP_WEB_RESOURCES
where WRES_USE_NT_LOGON = 0
and WRES_CAN_LOGIN <>0 and WRES_COUNT_LICENSE <> 0
select count(*) as 'Number of Windows Authenticated users in 2003'
from MSP_WEB_RESOURCES
where WRES_USE_NT_LOGON <> 0
and WRES_CAN_LOGIN <>0 and WRES_COUNT_LICENSE <> 0
--Number of security groups,security categories
select count(*) as 'Number of Security Groups in 2003'
from MSP_WEB_SECURITY_GROUPS
select count(*) as 'Number of Security Categories in 2003'
from MSP_WEB_SECURITY_CATEGORIES
--Is there any Windows SharePoint Services integration ?
select count(*) as 'Is there any Windows SharePoint Services Integration in 2003?'
from msp_web_admin where WADMIN_CURRENT_STS_SERVER_ID <> -1
--Number of linked issues, risks, documents
-- (indicates how much Windows SharePoint Services integration is used)
select count(*) as
'Number of linked Windows SharePoint Services issues/risks/documents in 2003'
from MSP_WEB_OBJECT_LINKS
--Number of custom Project Web Access views
select count(*) as 'Number of customer Projet Web Access views in 2003'
from MSP_WEB_VIEW_REPORTS where WVIEW_ID > 100