We help IT Professionals succeed at work.

Microsoft Project 2013: The INSERT statement conflicted with the FOREIGN KEY constraint

859 Views
Last Modified: 2014-11-27
Hi, when our users try to publish a project they get an error, pretty much the following:

The INSERT statement conflicted with the FOREIGN KEY constraint "FK_MSP_EpmAssignmentBaselineByDay_ProjectUID_AssignmentUID". The conflict occurred in database "PWA", table "dbo.MSP_EpmAssignmentBaseline". The statement has been terminated.. Details: id='24006'

Microsoft has released a hotfix for this issue (http://support.microsoft.com/kb/2883083) but from what I understand that prevents tasks getting corrupted again, however I've quite a few tasks that are corrupted at the moment.  The users can resolve this by deleting and re-adding the tasks, but I'm wondering does anyone know why this happens and is there anything I can do to fix the corrupt task and save the PM's having to re-do their work?

Many thanks,

Lee
Comment
Watch Question

Dr. Thomas HenkelmannDirector Consulting Services
CERTIFIED EXPERT

Commented:
Hi,
whenever something goes wrong at the database level I would recommend to NOT try to fix it at this level but cleanup the projects  and - in your case - the tasks. The error above indicates that the publish Job on the server tries to create timephased baseline recordsets but the baseline Information itself is missing.
How many projects and tasks are affected? The users that experience the issue can just copy and paste the tasks in their project schedule (which will create new GUIDs and thus also new baseline entries). However this won't work if you are using timesheets or "My Tasks" for actual hour tracking as copy & paste will break the link to the timesheet data.

Again, I'm afraid there is some manual work involved to fix this.

Regards

Thomas

Author

Commented:
Thanks Thomas, I found a query online that's helped me identify the tasks that have issues...

select mp.ProjectName, mt.TaskName, * from MSP_EpmAssignmentBaseline as ma
inner join MSP_EpmTask as mt on ma.TaskUID = mt.TaskUID
inner join MSP_EpmProject as mp on ma.ProjectUID = mp.ProjectUID
where AssignmentUID not in (select AssignmentUID from MSP_EpmAssignment)
Order By mp.ProjectName

When I run this I get 172 records (a lot of duplicates).  I'm not sure if they're all effected, but of the users that have reported the issue to me, replacing the list of tasks reported in the above results for their projects seems to fix the issue.  I guess the fact that I'm getting them to delete and re-add the tasks answers the question around timesheets and "My Tasks", so copy and pasting at the moment might be the only option until I get the hotfix installed?  

Thanks.
Director Consulting Services
CERTIFIED EXPERT
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION

Author

Commented:
I've been asked to support Project Server 2013 four weeks ago, and handed a book about it after never seeing it before, I need you in my life! Thanks so much for your help and more specifically your explanation.

Author

Commented:
Quick follow up on this, so Microsoft released a hotfix to resolve this issue however it seems that it stops it from happening to future projects, projects with already corrupted baselines are still corrupt.  Other than clearing the baselines, is there any way to identify where this corruption is so that I can fix it easily, it's a bit of a nightmare getting the PM's to clear and rebuild them manually, lots of work for them.
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.