Reporting database refresh fails - PWA 2007

[Adrienne]

This is a rather urgent issue, so any help would be greatly appreciated.
The environment contains MSPS 2007, patched up to and including the December
Cumulative Update, and SQL Server 2005 SP3.

First, this error showed up in the Application server event log:
Event Type: Error
Event Source: Office SharePoint Server
Event Category: Project Server Reporting
Event ID: 7797
Date: 1/22/2009
Time: 5:53:44 PM
User: N/A
Computer: HOFGA1052
Description:
Standard InformationSI Entry Point:
Project User: DIR\<redacted>
Correlation Id: d09418b6-08aa-4e4c-8f08-60ab5db6f656
PWA Site URL: <redacted>
SSP Name: ProjectServer_SSP
PSError: ReportingProjectChangeMessageFailed (24006)
RDS: The request to synchronize change(s) to project Project
UID='c59a6cc8-4844-4e47-9b27-c0bcf68c5593'. PublishType='ProjectPublish'
failed.
Message: 'ReportingProjectChangeMessageFailed'
Error:Cannot transfer project : 'c59a6cc8-4844-4e47-9b27-c0bcf68c5593'. A FK
constraint was thrown.

Then, I tried to refresh the reporting database:
1. Create backups of the four Project Server databases (or a Sharepoint
Full-Farm Backup if you prefer)
2. Log in to the PWA site => Server Settings => Administrative Backup
3. Select Enterprise Custom Fields and click Backup
4. Monitor the queue to verify this job completes (it will likely be gone
before you open the queue as it is a small task)
5. Go back to Server Settings => Administrative Restore
6. Select Enterprise Custom Fields and click Restore
7. Monitor the queue to verify this job completes

These errors appear in the queue:

Reporting Resource Sync

Error summary/areas:
Reporting message processor failed
ReportingResourceChangeMessageFailed
ReportingResourceChangeMessageFailed
ReportingResourceChangeMessageFailed
ReportingResourceChangeMessageFailed
ReportingResourceChangeMessageFailed
ReportingResourceChangeMessageFailed
Queue
GeneralQueueJobFailed
Error details:

<?xml version="1.0" encoding="utf-16"?>
<errinfo>
<general>
<class name="Reporting message processor failed">
<error id="24008" name="ReportingResourceChangeMessageFailed"
uid="96549899-d7ca-499d-8bf0-7c269bc21473" QueueMessageBody="Resource UID:
'b7992f61-ced9-48f3-921c-0a48e050865c'. ChangeType='Alter'.
ResourceChangeType='All'" Error="Conversion failed when converting from a
character string to uniqueidentifier." />
<error id="24008" name="ReportingResourceChangeMessageFailed"
uid="132b73a6-e846-49d8-a5a0-7e598c339731" QueueMessageBody="Resource UID:
'b7992f61-ced9-48f3-921c-0a48e050865c'. ChangeType='Alter'.
ResourceChangeType='All'" Error="Conversion failed when converting from a
character string to uniqueidentifier." />
<error id="24008" name="ReportingResourceChangeMessageFailed"
uid="a2d364ba-36b2-4236-bff0-ff8b1e6f6ec5" QueueMessageBody="Resource UID:
'b7992f61-ced9-48f3-921c-0a48e050865c'. ChangeType='Alter'.
ResourceChangeType='All'" Error="Conversion failed when converting from a
character string to uniqueidentifier." />
<error id="24008" name="ReportingResourceChangeMessageFailed"
uid="32d82e16-39f5-4b3e-98f5-8d6bdee5489a" QueueMessageBody="Resource UID:
'b7992f61-ced9-48f3-921c-0a48e050865c'. ChangeType='Alter'.
ResourceChangeType='All'" Error="Conversion failed when converting from a
character string to uniqueidentifier." />
<error id="24008" name="ReportingResourceChangeMessageFailed"
uid="6311ab05-68ef-4269-8db1-6ebc1146ecb1" QueueMessageBody="Resource UID:
'b7992f61-ced9-48f3-921c-0a48e050865c'. ChangeType='Alter'.
ResourceChangeType='All'" Error="Conversion failed when converting from a
character string to uniqueidentifier." />
<error id="24008" name="ReportingResourceChangeMessageFailed"
uid="93169460-eb16-4f0c-86e0-6182d876b5e6" QueueMessageBody="Resource UID:
'b7992f61-ced9-48f3-921c-0a48e050865c'. ChangeType='Alter'.
ResourceChangeType='All'" Error="Conversion failed when converting from a
character string to uniqueidentifier." />
</class>
<class name="Queue">
<error id="26000" name="GeneralQueueJobFailed"
uid="9872f082-9d4b-4d80-86b2-c9d9af81ac50"
JobUID="1d5f1c18-8791-4604-a4b0-f6b9c2df2de5" ComputerName="HOFGA1052"
GroupType="ReportingResourceSync" MessageType="ReportResourceSyncMessageEx"
MessageId="1" Stage="" />
</class>
</general>
</errinfo>

Reporting (Project Publish)

Error summary/areas:
Reporting message processor failed
ReportingProjectChangeMessageFailed
ReportingProjectChangeMessageFailed
ReportingProjectChangeMessageFailed
ReportingProjectChangeMessageFailed
ReportingProjectChangeMessageFailed
ReportingProjectChangeMessageFailed
Queue
GeneralQueueJobFailed
Error details:

<?xml version="1.0" encoding="utf-16"?>
<errinfo>
<general>
<class name="Reporting message processor failed">
<error id="24006" name="ReportingProjectChangeMessageFailed"
uid="b39ed1e9-7a74-454a-89ae-0ef8a25e906a" QueueMessageBody="Project
UID='bfa462a2-8823-4190-8e48-75e054a95da1'. PublishType='ProjectPublish'"
Error="Cannot transfer project : 'bfa462a2-8823-4190-8e48-75e054a95da1'. A FK
constraint was thrown." />
<error id="24006" name="ReportingProjectChangeMessageFailed"
uid="56b6b05e-fabe-4e29-9325-911106e49d05" QueueMessageBody="Project
UID='bfa462a2-8823-4190-8e48-75e054a95da1'. PublishType='ProjectPublish'"
Error="Cannot transfer project : 'bfa462a2-8823-4190-8e48-75e054a95da1'. A FK
constraint was thrown." />
<error id="24006" name="ReportingProjectChangeMessageFailed"
uid="6a6577a7-4b73-4496-b4f8-7e91668692aa" QueueMessageBody="Project
UID='bfa462a2-8823-4190-8e48-75e054a95da1'. PublishType='ProjectPublish'"
Error="Cannot transfer project : 'bfa462a2-8823-4190-8e48-75e054a95da1'. A FK
constraint was thrown." />
<error id="24006" name="ReportingProjectChangeMessageFailed"
uid="d9395811-9ab6-41e8-8a11-c427ed5316e8" QueueMessageBody="Project
UID='bfa462a2-8823-4190-8e48-75e054a95da1'. PublishType='ProjectPublish'"
Error="Cannot transfer project : 'bfa462a2-8823-4190-8e48-75e054a95da1'. A FK
constraint was thrown." />
<error id="24006" name="ReportingProjectChangeMessageFailed"
uid="d46c044d-d607-4cd0-8dc9-7e39e3808eda" QueueMessageBody="Project
UID='bfa462a2-8823-4190-8e48-75e054a95da1'. PublishType='ProjectPublish'"
Error="Cannot transfer project : 'bfa462a2-8823-4190-8e48-75e054a95da1'. A FK
constraint was thrown." />
<error id="24006" name="ReportingProjectChangeMessageFailed"
uid="836f8b83-8b7f-4871-a568-4143622147f8" QueueMessageBody="Project
UID='bfa462a2-8823-4190-8e48-75e054a95da1'. PublishType='ProjectPublish'"
Error="Cannot transfer project : 'bfa462a2-8823-4190-8e48-75e054a95da1'. A FK
constraint was thrown." />
</class>
<class name="Queue">
<error id="26000" name="GeneralQueueJobFailed"
uid="5ddd651f-ea64-4012-b973-3d0309c52069"
JobUID="0dfbf379-b2e1-466f-92ad-d22f29b30d99" ComputerName="HOFGA1052"
GroupType="ReportingProjectPublish"
MessageType="ReportProjectPublishMessageEx" MessageId="1" Stage="" />
</class>
</general>
</errinfo>

Reporting Database Refresh

Error summary/areas:
Reporting message processor failed
ReportingRDBRefreshMessageFailed
ReportingRDBRefreshMessageFailed
ReportingRDBRefreshMessageFailed
ReportingRDBRefreshMessageFailed
ReportingRDBRefreshMessageFailed
ReportingRDBRefreshMessageFailed
Queue
GeneralQueueJobFailed
Error details:

<?xml version="1.0" encoding="utf-16"?>
<errinfo>
<general>
<class name="Reporting message processor failed">
<error id="24023" name="ReportingRDBRefreshMessageFailed"
uid="34415da0-0482-440c-bc8e-b0b41f8854e0" QueueMessageBody="One of the
stages of the Refresh operation failed" Error="RDB area: Epm, error mode:
ContinueOnErrors, lock RDB on errors: False, refresh sleep time: 00:02:00" />
<error id="24023" name="ReportingRDBRefreshMessageFailed"
uid="bc01828b-5f08-4641-8158-17ca9b878ea4" QueueMessageBody="One of the
stages of the Refresh operation failed" Error="RDB area: Epm, error mode:
ContinueOnErrors, lock RDB on errors: False, refresh sleep time: 00:02:00" />
<error id="24023" name="ReportingRDBRefreshMessageFailed"
uid="7a167651-926c-4959-bcb9-8bbfa5e2ca60" QueueMessageBody="One of the
stages of the Refresh operation failed" Error="RDB area: Epm, error mode:
ContinueOnErrors, lock RDB on errors: False, refresh sleep time: 00:02:00" />
<error id="24023" name="ReportingRDBRefreshMessageFailed"
uid="d3f16db9-3ea9-45c7-92ed-0987b4c1d405" QueueMessageBody="One of the
stages of the Refresh operation failed" Error="RDB area: Epm, error mode:
ContinueOnErrors, lock RDB on errors: False, refresh sleep time: 00:02:00" />
<error id="24023" name="ReportingRDBRefreshMessageFailed"
uid="22ed992c-be74-40f1-884d-21a6c4be152c" QueueMessageBody="One of the
stages of the Refresh operation failed" Error="RDB area: Epm, error mode:
ContinueOnErrors, lock RDB on errors: False, refresh sleep time: 00:02:00" />
<error id="24023" name="ReportingRDBRefreshMessageFailed"
uid="a0b1e951-2311-45da-aff8-d0a5041d721a" QueueMessageBody="One of the
stages of the Refresh operation failed" Error="RDB area: Epm, error mode:
ContinueOnErrors, lock RDB on errors: False, refresh sleep time: 00:02:00" />
</class>
<class name="Queue">
<error id="26000" name="GeneralQueueJobFailed"
uid="3c47ff05-f732-4a5d-9a52-9d36beeac54e"
JobUID="836988b4-de9b-430f-86f1-a27a164e273e" ComputerName="HOFGA1052"
GroupType="ReportingRefresh" MessageType="ReportRefreshMessage" MessageId="1"
Stage="" />
</class>
</general>
</errinfo>

Thanks very much for any input you can give on this. (Dale, Gary, anyone!!!)

 

[Gord Schmidt]

Hello Adrienne,

I would start by looking at the resource and project identified in the error
messages. There might be something wrong with the related data. I would check
the custom fields first. To determine the real names of the objects, run the
following SQL statements:

use ProjectServer_Published
select * from msp_projects where proj_uid =
'bfa462a2-8823-4190-8e48-75e054a95da1'
select * from msp_resources where res_uid =
'b7992f61-ced9-48f3-921c-0a48e050865c'

Gord