Export (0) Print
Expand All

List the Project Server 2003 database profile

 

Applies to: Project Server 2010

Topic Last Modified: 2010-06-10

You can run the Microsoft Office Project Server 2003 profile script A2 to capture statistics about the database that you plan on migrating. This script's output will provide information about:

  • Size of the databases (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

  • If there is Windows SharePoint Services integration

  • Number of linked issues

  • Number of linked risks

  • Number of linked documents

  • Number of custom Project Web Access views

You must enter the name of your Project Tables database and of your Web Tables database in the USE statements in the script.

/*-----------------------------------------------------------------------------------------------------------
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 Project Web Access views in 2003' 
  from MSP_WEB_VIEW_REPORTS where WVIEW_ID > 100

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