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

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
lee_murphyAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Dr. Thomas HenkelmannDirector Consulting ServicesCommented:
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
lee_murphyAuthor 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.
0
Dr. Thomas HenkelmannDirector Consulting ServicesCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
lee_murphyAuthor 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.
0
lee_murphyAuthor 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.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Project Management Software

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.