[Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

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

Posted on 2014-08-31
5
Medium Priority
?
736 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
0
Comment
Question by:lee_murphy
  • 3
  • 2
5 Comments
 
LVL 12

Expert Comment

by:Dr. Thomas Henkelmann
ID: 40296199
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
0
 

Author Comment

by:lee_murphy
ID: 40297321
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.
0
 
LVL 12

Accepted Solution

by:
Dr. Thomas Henkelmann earned 2000 total points
ID: 40298410
Hi, the query is almost there :)
It checks if you have BASELINE entries for assignments that don't exist anymore. By design they (baseline data) should be deleted whenever an assignment or a task with all assignments is deleted from the project. The original error message in your question above goes one level deeper and complains about the missing assignment baseline record while trying to create the entries to the daily timephased table MSP_EpmAssignmentBaselineByDay that depends on the records in MSP_EpmAssignmentBaseline.
Anyway, yes please ask the PMs for the list of projects and tasks from your query to copy and paste them, then delete the original tasks. Publishing should be fine after that.

Good luck.

Thomas
0
 

Author Closing Comment

by:lee_murphy
ID: 40299863
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.
0
 

Author Comment

by:lee_murphy
ID: 40469881
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.
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Re-planning is just as important as planning. MS Project files need to be updated regularly to reflect the current status of the project and to streamline the upcoming tasks. We have seen a lot of issues where project managers have not updated the p…
A simple overview of the possibilities of using technology for project management.
Integration Management Part 2
Is your data getting by on basic protection measures? In today’s climate of debilitating malware and ransomware—like WannaCry—that may not be enough. You need to establish more than basics, like a recovery plan that protects both data and endpoints.…
Suggested Courses

868 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question