[アーティクル]
06/08/2016

移行スクリプト 1 を実行して Project Server 2003 データを確認する

適用先: Project Server 2010

トピックの最終更新日: 2013-12-18

移行スクリプト 1 は、Microsoft Office Project Server 2003 データの移行を支援する、Project Server 仮想移行環境 (VME) に含まれているいくつかの移行スクリプトのうちの 1 つです。Project Server 2003 データベースに対してこのスクリプトを実行すると、移行プロセスの正常終了を妨げる可能性があるエラーが識別されます。この SQL Server スクリプトは、Project Server 2003 データベースからの読み取りのみ行います (Project Server 2003 データベースへの書き込みは行いません)。

警告

このスクリプトは、Project Server 仮想移行環境 (VME) に含まれているいくつかの移行前スクリプトのうちの 1 つです。このスクリプトの実行は必須ではありませんが、正常なデータの移行を妨げる可能性のある問題の検出に役立ちますので、実行することを強く推奨します。利用可能な移行前スクリプトの詳細については、「Project Server VME: 移行前スクリプトを実行する (オプション)」を参照してください。

このスクリプトは、以下の問題を識別します。

Project Server 2003 データベースのバージョン (移行前は SP3 である必要があります)
プロジェクトがチェックアウトされている
プロジェクトが外部で編集されている
プロジェクトの進捗の更新が保留中
重複したエンタープライズリソースがある
プロジェクト内に重複したエンタープライズリソースがある
エンタープライズグローバルテンプレートが外部で編集されている
エンタープライズグローバルテンプレートがチェックアウトされている
エンタープライズグローバルテンプレートがロックされている
既定の言語が Project テーブルと Web テーブルで異なる
リソースの名前にコンマが含まれている
必須エンタープライズリソースユーザー設定フィールドに値がない
必須エンタープライズリソースユーザー設定フィールドが、参照テーブルの定義に含まれていない値を持つ
エンタープライズリソースが外部で編集されている
エンタープライズリソース期間ユーザー設定フィールドが値のリストを持つ
エンタープライズリソース期間ユーザー設定フィールドの値が無効

スクリプト 1 を実行するには

VME デスクトップで [移行プロセスの開始] をクリックします。エクスプローラーのウィンドウが開いてドライブ E の内容が表示されます。
Windows エクスプローラーで、次のフォルダーをダブルクリックします。
- Project Server 2003 データベースが 1 つの場合は、Migrate_Proj_2003_Single_DB フォルダーを開きます。
- Project Server 2003 データベースが分割されている場合は、Migrate_Proj_2003_Split_DB を開きます。
Verification Scripts フォルダーを開き、[VME Script 1.sql] をクリックします。SQL Server Management Studio が開いてスクリプト 1 が表示されます。
[実行] をクリックしてスクリプトを実行します。
スクリプトの結果を確認します。必要に応じて修正を行います。

注意

Project Server 2003 データの検証手順および修正の詳細については、「移行前スクリプト A1 で特定されたデータ問題を修正する (Project Server 2010)」を参照してください。

Project Server 2003 データベースの修正は、Project Server 2003 環境の Microsoft Office Project Professional 2003 を通じて行う必要があります。修正後、Project Server 2003 データベースのバックアップコピーを作成し、「Project Server VME にデータを読み込む」の手順を使用して、更新されたデータベースを VME 仮想マシンに復元します。更新されたデータベースでスクリプト 1 を再実行して、すべての問題が修正されたことを確認します。

スクリプト 1

スクリプト 1 に含まれているコードは、以下のとおりです。

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

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

次の方法で共有

移行スクリプト 1 を実行して Project Server 2003 データを確認する

スクリプト 1

その他のリソース