Friday, 12 April 2013

Project server 2010 - INSERT statement conflicted with the FOREIGN KEY constraint

Today i got very interesting error in project server 2010, i created one custom field for task and first time is when i input the values and publish the project , open the reporting data base i can view my custom field in MSP_EpmTask_UserView. but after update the custom field values , it effect only in publish database , there is not effects in reporting database. then check the log and got following error in Log.



PWA:http://project.epmpro.net/PWA, ServiceApp:Project Server Service Application, User:.....\spadmin, PSI: SqlException occurred in DAL:  <Error><Class>0</Class><LineNumber>19</LineNumber><Number>547</Number><Procedure>MSP_Epm_InsertTaskBaseline</Procedure>  <Message>  System.Data.SqlClient.SqlError: The INSERT statement conflicted with the FOREIGN KEY constraint "FK_MSP_EpmTaskBaseline_ProjectUID_TaskUID". The conflict occurred in database "DBReporting", table "dbo.MSP_EpmTask".  </Message>  <CallStack>    
 at System.Data.Common.DbDataAdapter.UpdatedRowStatusErrors(RowUpdatedEventArgs rowUpdatedEvent, BatchCommandInfo[] batchCommands, Int32 commandCount)    
 at System.Data.Common.DbDataAdapter.UpdatedRowStatus(RowUpdatedEventArgs rowUpdatedEvent, BatchCommandInfo[] batchCommands, Int32 commandCount)    
 at System.Data.Common.DbDataAdapter.Update(DataRow[] dataRows, DataTableMapping tableMapping)    
 at System.Data.Common.DbDataAdapter.UpdateFromDataTable(DataTable dataTable, DataTableMapping tableMapping)    
 at System.Data.Common.DbDataAdapter.Update(DataTable dataTable)    
 at Microsoft.Office.Project.Server.DataAccessLayer.DAL.SubDal.HandleUpdateUsingSproc(SqlDataAdapter sqlDa, DataTable sourceDataTable)  </CallStack>  </Error>


I am unable to find the cause, But to resolve that i delete the Baseline from Draft and Publish Database , and its start working fine.

How to Delete Baseline Record from Database:



Delete
from MSP_TASK_BASELINES
where MSP_TASK_BASELINES.PROJ_UID ='GUID of Project' AND MSP_TASK_BASELINES.TASK_UID NOT IN
 (
select TASK_UID
from dbo.MSP_TASKS 
where PROJ_UID ='GUID of Project'
)

Replace  Project GUID According to your Project ID

Run this query for Draft and Publish DB , After Execution you will see n number of Rows deleted for DB.
go to Project Plan and Publish it again, You will see now its also populating data in Reporting DB AS Well.