Sync to Reporting Database Fails

After publishing a Project on Project server 2010, you go to see the status of the queue, you find that the job for ‘Sync to Reporting’ fails. An easy way to catch this is when you run a report that is pulling data from a Projects that fails to sync with the reporting database, you will find missing data. But if you open the Project from the server, you will see all the data being saved. When you look at the details of the error, you will find ‘The INSERT statement conflicted with the FOREIGN KEY constraint“. To resolve this issue follow these steps –

1. Publishing from PWA –

The easiest way to fix this issue is to open the concerned project from PWA for editing, click on ‘Save’ in the ‘Project’ section of the ‘Task’ ribbon and then click on ‘Publish’. Now monitor the queue and see if it fails again.

2. Deleting Project from the Published Database.

If the above method fails, we might need to force a sync between the ‘Published’ database and the ‘reporting’ Database.

a. Open the concerned project in Microsoft Project Professional for editing.

b. Click on File –> Save. Now go to your PWA –> Server Settings –> Delete enterprise objects.

c. Under ‘What do you want to delete from Project server’, select ‘Projects’

d. Under ‘delete the selected project(s)’ select ‘Delete projects only from Published database’

e. Select the project from the displayed list and then click on ‘Delete’

f. Go to Server settings –> Manage Queue Jobs.

g. Make sure the project delete job is 100% done.

h. Once deleted, go to Microsoft Project Professional where the project is open, and then click on File –> Publish.

j. Check the Queue status again to make sure the Reporting job is 100% done. This process might take a while.

3. Save the project in XML.

If both the above method fails, the project may be corrupted and we may need to repair it.

a. Open the project in Microsoft Project Professional

b. Click File>Save As.

c. Click Save as File button

d. Save the file to a location on your PC or network drive.

e. Close Project Professional

f. Open Project Professional

g. Click file>Open

h. On the left side, navigate to where you just saved the .xml file version of the schedule

i. In the bottom right area, select XML format to see the xml files available

j. Click on the backed up file and then open

k. Choose ‘Merge the data into the active project’ when prompted by the dialog box.

l. To overwrite the existing schedule in project server with the backed up XML version, click File>Save As.

n. Ensure you provide the exact project name as it currently is in project server. Project server will then prompt you to overwrite the existing project. Choose yes. Please ensure that you type the name as is, if the name is entered incorrectly, Project server will create a new project.

o. Follow the prompts to swap out local with enterprise resources and accept the global calendar warning.

p. Once save is complete, publish the project

q. Go to Manage Queue again and ensure that the Reporting job is finished 100%.