Error 23000 when publishing a project to Project Server 2007

[Frank Jutte]

Hi,

We encountered a problem while publishing a project to Projet Server 2007,
this error was earlier reported by a client but could not be reproduced. The
question now is how to handle?

Scenario:
After saving a project (that already existed on the server) I chose to
Publish the project. In the publishing process it reaches 95% (this % was
also mentioned by my customer) and then MS Project presented the following
error:

<?xml version="1.0" encoding="utf-16"?>
<errinfo>
<general>
<class name="">
<error id="23000"
name="ProjectPublishFailure" uid="898a6145-6258-4213-bf8a-eecfd7ed6427"
projectuid="88a69872-f83f-4ae8-88bf-b3b4122e9ad4"
messagetype="Microsoft.Office.Project.Server.BusinessLayer.QueueMsg.ConflictResolutionMessage" messageID="180" stage="" blocking="Block"/>
</class>
<class name="Queue">
<error id="26000"
name="GeneralQueueJobFailed" uid="418dfc78-6fff-4eb1-bb71-44112ef839cb"
JobUID="d212b9b8-0b88-47eb-a9c8-30da82effac4" ComputerName="EPM01"
GroupType="ProjectPublish" MessageType="ConflictResolutionMessage"
MessageId="180" Stage=""/>
</class>
</general>
</errinfo>

I cannot find a resolution anywhere, hope somebody can help me out soon with
this issue. BTW, we use Project Server 2007 SP1, also the MS Project Client
uses SP1.

Thanks in advance!

 

[Nielsen.Mads]

Hi,

We encountered a problem while publishing a project to Projet Server 2007,
this error was earlier reported by a client but could not be reproduced. The
question now is how to handle?

Scenario:
After saving a project (that already existed on the server) I chose to
Publish the project. In the publishing process it reaches 95% (this % was
also mentioned by my customer) and then MS Project presented the following
error:

<?xml version="1.0" encoding="utf-16"?>
<errinfo>
<general>
<class name="">
<error id="23000"
name="ProjectPublishFailure" uid="898a6145-6258-4213-bf8a-eecfd7ed6427"
projectuid="88a69872-f83f-4ae8-88bf-b3b4122e9ad4"
messagetype="Microsoft.Office.Project.Server.BusinessLayer.QueueMsg.Conflic-tResolutionMessage" messageID="180" stage="" blocking="Block"/>
</class>
<class name="Queue">
<error id="26000"
name="GeneralQueueJobFailed" uid="418dfc78-6fff-4eb1-bb71-44112ef839cb"
JobUID="d212b9b8-0b88-47eb-a9c8-30da82effac4" ComputerName="EPM01"
GroupType="ProjectPublish" MessageType="ConflictResolutionMessage"
MessageId="180" Stage=""/>
</class>
</general>
</errinfo>

I cannot find a resolution anywhere, hope somebody can help me out soon with
this issue. BTW, we use Project Server 2007 SP1, also the MS Project Client
uses SP1.

Thanks in advance!

Hi Frank,

Here is what we do to solve the issue:
1. To find the corrupt assignment(s) we use the following SQL query:

USE [ProjectServer_Published]
SELECT MSP_PROJECTS.PROJ_NAME,
MSP_ASSIGNMENTS_SAVED.TASK_NAME,
MSP_RESOURCES.RES_NAME,
MSP_ASSIGNMENTS_SAVED.ASSN_UID,
MSP_ASSIGNMENTS_SAVED.PROJ_UID,
MSP_ASSIGNMENTS_SAVED.TASK_UID,
MSP_ASSIGNMENTS_SAVED.RES_UID
FROM MSP_ASSIGNMENTS_SAVED INNER JOIN
MSP_PROJECTS ON MSP_ASSIGNMENTS_SAVED.PROJ_UID =
MSP_PROJECTS.PROJ_UID INNER JOIN
MSP_RESOURCES ON MSP_ASSIGNMENTS_SAVED.RES_UID =
MSP_RESOURCES.RES_UID
WHERE (NOT EXISTS
(SELECT TASK_UID
FROM MSP_TASKS_SAVED
WHERE (TASK_UID = MSP_ASSIGNMENTS_SAVED.TASK_UID)))

2. After having located the corrupt assignment(s) we insert the column
'Publish' and set the value to 'No' for each assignment.
3. Next we publish the project via Project Pro.
4. Then we set the value back to 'Yes' and 'Publish' again.
5. Then we are able to publish the project without the progress stalls
at 95 %
6. Finally we run the SQL query again, just to do a final check for
corrupted assignments

This workaround is not a permanent solution, but hopefully it wil last
for a while ;o)

/Mads

 

[Frank]

Error 23000 solved

Each time the 'corrupted' project was published it reported a 23000 error at
95% progress, after that the process ended.

Write lock

Another problem was that Project Server, while publishing, added a write
lock on the corrupted project and doesn't remove this lock by itself. When a
project has a write lock it is no longer possible to read from the project
any longer as well, this had some negative effects:

• The project no longer had any tasks when you looked at it in PWA.
• Resources involved in the project were no longer being able to look at
their MyTasks.
• Timesheets no longer could be exported.
• New projects no longer could be created in the system.

As a work-around the de write lock can be removed manually. You should look
for the corrupted project in the Project_Published database in the
MSP_PROJECTS table. After that you change the value of the column
PROJ_WRITE_LOCK_DATE into NULL for this project and you change the value for
the column PROJ_PUBLISH_STATUS into 0. Example:

USE ProjectServer_Published
UPDATE
MSP_PROJECTS
SET
PROJ_WRITE_LOCK_DATE = NULL,
PROJ_PUBLISH_STATUS = 0
WHERE
PROJ_UID = '88A69872-F83F-4AE8-88BF-B3B4122E9AD4'

After that all processes will (re)start being able to read from the project
again. However, this doesn't solve the problem.

ULS Log

Investigating the Project Server ULS log came up with the following results:
(formatted for readability)
SqlException occurred in DAL:
<Error>
<State>1</State>
<LineNumber>20</LineNumber>
<Number>2627</Number>
<Procedure>MSP_WEB_SP_QRY_CreateSavedTasksForProject</Procedure>
<Message> System.Data.SqlClient.SqlError: Violation of PRIMARY KEY
constraint 'PK_MSP_TASKS_SAVED'. Cannot insert duplicate key in object
'dbo.MSP_TASKS_SAVED'. </Message>
<CallStack>
at System.Data.SqlClient.SqlConnection.OnError(SqlException
exception, Boolean breakConnection)
at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException
exception, Boolean breakConnection)
at
System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject
stateObj)
at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior,
SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet
bulkCopyHandler, TdsParserStateObject stateObj)
at
System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds,
RunBehavior runBehavior, String resetOptionsString)
at
System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior
cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async)

at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior
cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method,
DbAsyncResult result)
at
System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(DbAsyncResult
result, String methodName, Boolean sendToPipe)
at System.Data.SqlClient.SqlCommand.ExecuteNonQuery()
at
Microsoft.Office.Project.Server.DataAccessLayer.DAL.SubDal.ExecuteStoredProcedureNoResult(String storedProcedureName, SqlParameter[] parameters)
</CallStack>
</Error>

The MSP_TASKS_SAVED and MSP_ASSIGNMENTS_SAVED tales had duplicate keys.
Emtying these tables works for the next publish, but the problem will return
immediatly after this action and thus this was no structual solution.

Solution

Thanks to the MSFT tips (received by Mads) we found 1 usefull tip that
solved the issue for us:

The Publish database is out of synchronization with the Draft database.
Workaround: NOTE: Take the Backup of Published DB and test the below Action
plan in your test environment first.
- go to Project Web Access > Server Settings > Delete Enterprise Objects >
check the option "Project, Proposals, and activities"
- Select the option "Delete Project only from Published database"
- Select the <PROJECT NAME HAVING ISSUE> problem project/s
- Click Delete
- Open Project Professional
- Click File>Open > "Retrieve the list of all projects from Project Server"
- Open the plan/s you deleted in step 3 and republish or use the ProjTool
available with the SDK
In general this problem might be caused by the processing of changed
assignments for a resource, the Summary Resource Assignment (SRA) record is
also updated and the error sometimes points to a problem with SRA. Review the
Server event logs and ULS logs to get the best error messages. It could be
that some assignments are not getting properly updated in publishing DB.

After performing these actions the problem didn't show up anymore.

Rgds,

Frank Jutte
(e-mail address removed)
(e-mail address removed)

 

[Durvasula]

Couple of project publish jobs failed in Project Server 2007. As suggested in
this solution, we executed the following SQL statement, but it did not return
any corrupted assignments. We received the following error message for
project publish job failure. Any help is greatly appreciated.

Thanks
Sastry

Error summary/areas:

ProjectPublishFailure
Queue
GeneralQueueJobFailed
Error details:

<?xml version="1.0" encoding="utf-16"?>
<errinfo>
<general>
<class name="">
<error id="23000" name="ProjectPublishFailure"
uid="b504ef96-a07a-4af6-8c81-b8c23d15ebf4"
projectuid="f8aae712-91f8-4f20-9312-25b8c6adfa52"
messagetype="Microsoft.Office.Project.Server.BusinessLayer.QueueMsg.UpdateSRAMessage" messageID="177" stage="" blocking="Block" />
</class>
<class name="Queue">
<error id="26000" name="GeneralQueueJobFailed"
uid="0419c3df-717f-44b6-a259-7533ae998be4"
JobUID="901477bc-af41-4c94-bc8b-94e7ba0332b8" ComputerName="CMHPRDMPS01"
GroupType="ProjectPublish" MessageType="UpdateSRAMessage" MessageId="177"
Stage="" />
</class>
</general>
</errinfo>

Hi,

We encountered a problem while publishing a project to Projet Server 2007,
this error was earlier reported by a client but could not be reproduced. The
question now is how to handle?

Scenario:
After saving a project (that already existed on the server) I chose to
Publish the project. In the publishing process it reaches 95% (this % was
also mentioned by my customer) and then MS Project presented the following
error:

<?xml version="1.0" encoding="utf-16"?>
<errinfo>
<general>
<class name="">
<error id="23000"
name="ProjectPublishFailure" uid="898a6145-6258-4213-bf8a-eecfd7ed6427"
projectuid="88a69872-f83f-4ae8-88bf-b3b4122e9ad4"
messagetype="Microsoft.Office.Project.Server.BusinessLayer.QueueMsg.Conflic-tResolutionMessage" messageID="180" stage="" blocking="Block"/>
</class>
<class name="Queue">
<error id="26000"
name="GeneralQueueJobFailed" uid="418dfc78-6fff-4eb1-bb71-44112ef839cb"
JobUID="d212b9b8-0b88-47eb-a9c8-30da82effac4" ComputerName="EPM01"
GroupType="ProjectPublish" MessageType="ConflictResolutionMessage"
MessageId="180" Stage=""/>
</class>
</general>
</errinfo>

I cannot find a resolution anywhere, hope somebody can help me out soon with
this issue. BTW, we use Project Server 2007 SP1, also the MS Project Client
uses SP1.

Thanks in advance!

Hi Frank,

Here is what we do to solve the issue:
1. To find the corrupt assignment(s) we use the following SQL query:

USE [ProjectServer_Published]
SELECT MSP_PROJECTS.PROJ_NAME,
MSP_ASSIGNMENTS_SAVED.TASK_NAME,
MSP_RESOURCES.RES_NAME,
MSP_ASSIGNMENTS_SAVED.ASSN_UID,
MSP_ASSIGNMENTS_SAVED.PROJ_UID,
MSP_ASSIGNMENTS_SAVED.TASK_UID,
MSP_ASSIGNMENTS_SAVED.RES_UID
FROM MSP_ASSIGNMENTS_SAVED INNER JOIN
MSP_PROJECTS ON MSP_ASSIGNMENTS_SAVED.PROJ_UID =
MSP_PROJECTS.PROJ_UID INNER JOIN
MSP_RESOURCES ON MSP_ASSIGNMENTS_SAVED.RES_UID =
MSP_RESOURCES.RES_UID
WHERE (NOT EXISTS
(SELECT TASK_UID
FROM MSP_TASKS_SAVED
WHERE (TASK_UID = MSP_ASSIGNMENTS_SAVED.TASK_UID)))

2. After having located the corrupt assignment(s) we insert the column
'Publish' and set the value to 'No' for each assignment.
3. Next we publish the project via Project Pro.
4. Then we set the value back to 'Yes' and 'Publish' again.
5. Then we are able to publish the project without the progress stalls
at 95 %
6. Finally we run the SQL query again, just to do a final check for
corrupted assignments

This workaround is not a permanent solution, but hopefully it wil last
for a while ;o)

/Mads

 

[Elena Miller]

Tried your solution but it didn't return any records. any other ideas I can try.

Thanks,

Elena



Nielsen.Mad wrote:

Re: Error 23000 when publishing a project to Project Server 2007
15-Feb-08


Hi Frank

Here is what we do to solve the issue
1. To find the corrupt assignment(s) we use the following SQL query

USE [ProjectServer_Published
SELECT MSP_PROJECTS.PROJ_NAME
MSP_ASSIGNMENTS_SAVED.TASK_NAME
MSP_RESOURCES.RES_NAME
MSP_ASSIGNMENTS_SAVED.ASSN_UID
MSP_ASSIGNMENTS_SAVED.PROJ_UID
MSP_ASSIGNMENTS_SAVED.TASK_UID
MSP_ASSIGNMENTS_SAVED.RES_UI
FROM MSP_ASSIGNMENTS_SAVED INNER JOI
MSP_PROJECTS ON MSP_ASSIGNMENTS_SAVED.PROJ_UID
MSP_PROJECTS.PROJ_UID INNER JOI
MSP_RESOURCES ON MSP_ASSIGNMENTS_SAVED.RES_UID
MSP_RESOURCES.RES_UI
WHERE (NOT EXIST
(SELECT TASK_UI
FROM MSP_TASKS_SAVE
WHERE (TASK_UID = MSP_ASSIGNMENTS_SAVED.TASK_UID))

2. After having located the corrupt assignment(s) we insert the colum
'Publish' and set the value to 'No' for each assignment
3. Next we publish the project via Project Pro
4. Then we set the value back to 'Yes' and 'Publish' again
5. Then we are able to publish the project without the progress stall
at 95
6. Finally we run the SQL query again, just to do a final check fo
corrupted assignment

This workaround is not a permanent solution, but hopefully it wil las
for a while ;o

/Mads

Previous Posts In This Thread:

Error 23000 when publishing a project to Project Server 2007
Hi

We encountered a problem while publishing a project to Projet Server 2007,
this error was earlier reported by a client but could not be reproduced. The
question now is how to handle

Scenario
After saving a project (that already existed on the server) I chose to
Publish the project. In the publishing process it reaches 95% (this % was
also mentioned by my customer) and then MS Project presented the following
error

<?xml version="1.0" encoding="utf-16"?
<errinfo
<general
<class name=""
<error id="23000"
name="ProjectPublishFailure" uid="898a6145-6258-4213-bf8a-eecfd7ed6427"
projectuid="88a69872-f83f-4ae8-88bf-b3b4122e9ad4"
messagetype="Microsoft.Office.Project.Server.BusinessLayer.QueueMsg.ConflictResolutionMessage" messageID="180" stage="" blocking="Block"/
</class
<class name="Queue"
<error id="26000"
name="GeneralQueueJobFailed" uid="418dfc78-6fff-4eb1-bb71-44112ef839cb"
JobUID="d212b9b8-0b88-47eb-a9c8-30da82effac4" ComputerName="EPM01"
GroupType="ProjectPublish" MessageType="ConflictResolutionMessage"
MessageId="180" Stage=""/
</class
</general
</errinfo

I cannot find a resolution anywhere, hope somebody can help me out soon with
this issue. BTW, we use Project Server 2007 SP1, also the MS Project Client
uses SP1

Thanks in advance

--
Frank Jutt
Winvisio
http://www.winvision.nl

Re: Error 23000 when publishing a project to Project Server 2007

Hi Frank

Here is what we do to solve the issue
1. To find the corrupt assignment(s) we use the following SQL query

USE [ProjectServer_Published
SELECT MSP_PROJECTS.PROJ_NAME
MSP_ASSIGNMENTS_SAVED.TASK_NAME
MSP_RESOURCES.RES_NAME
MSP_ASSIGNMENTS_SAVED.ASSN_UID
MSP_ASSIGNMENTS_SAVED.PROJ_UID
MSP_ASSIGNMENTS_SAVED.TASK_UID
MSP_ASSIGNMENTS_SAVED.RES_UI
FROM MSP_ASSIGNMENTS_SAVED INNER JOI
MSP_PROJECTS ON MSP_ASSIGNMENTS_SAVED.PROJ_UID
MSP_PROJECTS.PROJ_UID INNER JOI
MSP_RESOURCES ON MSP_ASSIGNMENTS_SAVED.RES_UID
MSP_RESOURCES.RES_UI
WHERE (NOT EXIST
(SELECT TASK_UI
FROM MSP_TASKS_SAVE
WHERE (TASK_UID = MSP_ASSIGNMENTS_SAVED.TASK_UID))

2. After having located the corrupt assignment(s) we insert the colum
'Publish' and set the value to 'No' for each assignment.
3. Next we publish the project via Project Pro.
4. Then we set the value back to 'Yes' and 'Publish' again.
5. Then we are able to publish the project without the progress stalls
at 95 %
6. Finally we run the SQL query again, just to do a final check for
corrupted assignments

This workaround is not a permanent solution, but hopefully it wil last
for a while ;o)

/Mads

Re: Error 23000 when publishing a project to Project Server 2007
Couple of project publish jobs failed in Project Server 2007. As suggested in
this solution, we executed the following SQL statement, but it did not return
any corrupted assignments. We received the following error message for
project publish job failure. Any help is greatly appreciated.

Thanks
Sastry

Error summary/areas:

ProjectPublishFailure
Queue
GeneralQueueJobFailed
Error details:

<?xml version="1.0" encoding="utf-16"?>
<errinfo>
<general>
<class name="">
<error id="23000" name="ProjectPublishFailure"
uid="b504ef96-a07a-4af6-8c81-b8c23d15ebf4"
projectuid="f8aae712-91f8-4f20-9312-25b8c6adfa52"
messagetype="Microsoft.Office.Project.Server.BusinessLayer.QueueMsg.UpdateSRAMessage" messageID="177" stage="" blocking="Block" />
</class>
<class name="Queue">
<error id="26000" name="GeneralQueueJobFailed"
uid="0419c3df-717f-44b6-a259-7533ae998be4"
JobUID="901477bc-af41-4c94-bc8b-94e7ba0332b8" ComputerName="CMHPRDMPS01"
GroupType="ProjectPublish" MessageType="UpdateSRAMessage" MessageId="177"
Stage="" />
</class>
</general>
</errinfo>


:

EggHeadCafe - Software Developer Portal of Choice
..NET Generics - Sorting List By Class Properties
http://www.eggheadcafe.com/tutorial...a84-de63d9dd172c/net-generics--sorting-l.aspx