Ausführen des Skripts vor der Migration (A1) (Project Server 2010)

Project Server 2007-Skript vor der Migration 1

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

------------------------------------------------------------------------------
/* 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