Ausführen des Skripts vor der Migration (A1) (Project Server 2010)
Gilt für: Project Server 2010
Letztes Änderungsdatum des Themas: 2010-12-15
Durch die Ausführung dieses Skripts für die Microsoft Office Project Server 2003-Datenbank werden mögliche Fehler identifiziert, die den erfolgreichen Abschluss des Migrationsvorgangs verhindern. Mit diesem SQL-Skript werden nur Datenbanken aus Project Server 2003 Service Pack 3 gelesen.
Es werden die folgenden Probleme vom Skript identifiziert:
Version der Project Server 2003-Datenbank (muss vor der Migration SP3 sein)
Ausgecheckte Projekte
Extern bearbeitete Projekte
Ausstehende Statusaktualisierungen für Projekte
Doppelte Enterprise-Ressourcen
Doppelte Enterprise-Ressourcen in Projekten
Extern bearbeitete Enterprise-Global-Vorlage
Ausgecheckte Enterprise-Global-Vorlage
Gesperrte Enterprise-Global-Vorlage
Standardsprache ist unterschiedlich in Projekttabellen und Webtabellen
Ressource weist ein Komma im Namen auf
Erforderliche benutzerdefinierte Felder für Enterprise-Ressourcen enthalten keine Werte
Erforderliche benutzerdefinierte Felder für Enterprise-Ressourcen enthalten Werte, die nicht in der Definition der Nachschlagetabelle enthalten sind
Extern bearbeitete Enterprise-Ressourcen
Benutzerdefinierte Enterprise-Ressourcendauerfelder weisen Wertelisten auf
Benutzerdefinierte Enterprise-Ressourcendauerfelder weisen ungültige Werte auf
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