MS Project Server Error when publishing project with resource

Question

Hi,

I'm struggling since 2 days with an major error using MS Project Server 2016 (which is now part of SharePoint 2016) and the Project Professional 2016 client. The server is set up locally.

Always, if I assign in the Project Prof Client a resource (a user created from AD) to a task and then publish the project to the server, I get the following 23000 "internal publishing error" message:

<?xml version="1.0" encoding="utf-16"?> 
  <errinfo>
    <general>
      <class name="Microsoft.Office.Project.Server.BusinessLayer.Queue.Message">
        <error id="23000" name="ProjectPublishFailure" uid="9e994e59-451c-e611-80c3-0050569d010f" projectuid="530d8b4d-341c-e611-827c-0019995e2c70" messagetype="Microsoft.Office.Project.Server.BusinessLayer.QueueMsg.UpdateSRAMessage: projectUid = 530d8b4d-341c-e611-827c-0019995e2c70" messageID="14" stage="" blocking="Block"/>
      </class>
      <class name="Queue">
        <error id="26000" name="GeneralQueueJobFailed" uid="9f994e59-451c-e611-80c3-0050569d010f" JobUID="98ba7f46-451c-e611-827c-0019995e2c70" ComputerName="8c6a1276-f339-46e4-9caf-72aacc98edc9" GroupType="ProjectPublish" MessageType="UpdateSRAMessage" MessageId="14" Stage="" CorrelationUID="a5187d9d-48de-500f-8edc-9fb1252add42"/>
      </class>
    </general>
  </errinfo>

Also, I have checked for the ULS loggs and found three different messages:

First:

*UpdateSummaryResourceAssignments() caught sql exception: While converting a varchar data type to a datetime data type the value is out of the valid range.

(translated into english)

innerException null

Second:

PWA:http://sharepoint2016, ServiceApp:Project Server-Dienstanwendung, User:i:0#.w|(I removed that) PSI: UpdateSummaryResourceAssignments failed with ProjUid:b23d6ba4-1719-e611-827b-0019995e2c70, ResUid:7296a523-e218-e611-80bf-0050569d010f

Third:

PWA:http://sharepoint2016, ServiceApp:Project Server-Dienstanwendung, User:i:0#.w|(I removed that), PSI: a002b91e-071c-e611-80bf-0050569d010f | 00000000-0000-0000-0000-000000000000 | Exception| Batch SqlCommand: '-- BATCH: Update Data Table : SummaryResourceAssignment SELECT @bp0 AS SQLBatchRequestColumnName; INSERT INTO [pjpub].MSP_TVF_ASSN_ENTERPRISE(@siteId) (SiteId, ASSN_UID, PROJ_UID, RES_UID, ASSN_NAME, ASSN_ACT_START, ASSN_ACT_FINISH, ASSN_DELAY, ASSN_LEVELING_DELAY, ASSN_COST_RATE_TABLE, ASSN_UNITS, ASSN_WORK, ASSN_ACT_WORK, ASSN_REG_WORK, ASSN_OVT_WORK, ASSN_REM_WORK, ASSN_ACT_OVT_WORK, ASSN_REM_OVT_WORK, ASSN_START_DATE, ASSN_FINISH_DATE, ASSN_RESUME_DATE, ASSN_STOP_DATE, ASSN_COST, ASSN_ACT_COST, ASSN_ACT_OVT_COST, ASSN_REM_COST, ASSN_REM_OVT_COST, ASSN_OVT_COST, ASSN_BOOKING_TYPE, ASSN_IS_OVERALLOCATED, ASSN_CONTOUR_DATA, MOD_REV_RANK, CREATED_REV_COUNTER, MOD_REV_COUNTER) VALUES (@siteId, @ASSN_UID, @PROJ_UID, @RES_UID, @ASSN_NAME, @ASSN_ACT_START, @ASSN_ACT_FINISH, @ASSN_DELAY, @ASSN_LEVELING_DELAY, @ASSN_COST_RATE_TABLE, @ASSN_UNITS, @ASSN_WORK, @ASSN_ACT_WORK, @ASSN_REG_WORK, @ASSN_OVT_WORK, @ASSN_REM_WORK, @ASSN_ACT_OVT_WORK, @ASSN_REM_OVT_WORK, @ASSN_START_DATE, @ASSN_FINISH_DATE, @ASSN_RESUME_DATE, @ASSN_STOP_DATE, @ASSN_COST, @ASSN_ACT_COST, @ASSN_ACT_OVT_COST, @ASSN_REM_COST, @ASSN_REM_OVT_COST, @ASSN_OVT_COST, @ASSN_BOOKING_TYPE, CASE WHEN @ASSN_IS_OVERALLOCATED IS NULL THEN (0) ELSE @ASSN_IS_OVERALLOCATED END , @ASSN_CONTOUR_DATA, @MOD_REV_RANK, @CREATED_REV_COUNTER, @MOD_REV_COUNTER) ' CommandType: Text Parameter: '@bp0' Type: UniqueIdentifier Direction: Input Value: 'a102b91e-071c-e611-80bf-0050569d010f' Parameter: '@siteId' Type: UniqueIdentifier Direction: Input Value: 'efdfb775-0be6-4364-baf3-d48f83b8450d' Parameter: '@ASSN_UID' Type: UniqueIdentifier Direction: Input Value: '9b02b91e-071c-e611-80bf-0050569d010f' Parameter: '@PROJ_UID' Type: UniqueIdentifier Direction: Input Value: '11111111-1111-1111-1111-111111111111' Parameter: '@RES_UID' Type: UniqueIdentifier Direction: Input Value: '7296a523-e218-e611-80bf-0050569d010f' Parameter: '@ASSN_NAME' Type: NVarChar Direction: Input Size: 255 Value: '' Parameter: '@ASSN_ACT_START' Type: DateTime Direction: Input Parameter: '@ASSN_ACT_FINISH' Type: DateTime Direction: Input Parameter: '@ASSN_DELAY' Type: Int Direction: Input Value: '0' Parameter: '@ASSN_LEVELING_DELAY' Type: Int Direction: Input Value: '0' Parameter: '@ASSN_COST_RATE_TABLE' Type: SmallInt Direction: Input Value: '0' Parameter: '@ASSN_UNITS' Type: Decimal Direction: Input Value: '10000' Parameter: '@ASSN_WORK' Type: Decimal Direction: Input Value: '0' Parameter: '@ASSN_ACT_WORK' Type: Decimal Direction: Input Value: '0' Parameter: '@ASSN_REG_WORK' Type: Decimal Direction: Input Value: '0' Parameter: '@ASSN_OVT_WORK' Type: Decimal Direction: Input Value: '0' Parameter: '@ASSN_REM_WORK' Type: Decimal Direction: Input Value: '0' Parameter: '@ASSN_ACT_OVT_WORK' Type: Decimal Direction: Input Value: '0' Parameter: '@ASSN_REM_OVT_WORK' Type: Decimal Direction: Input Value: '0' Parameter: '@ASSN_START_DATE' Type: DateTime Direction: Input Value: '01/01/2150 23:59:54' Parameter: '@ASSN_FINISH_DATE' Type: DateTime Direction: Input Value: '01/01/1984 00:00:00' Parameter: '@ASSN_RESUME_DATE' Type: DateTime Direction: Input Parameter: '@ASSN_STOP_DATE' Type: DateTime Direction: Input Parameter: '@ASSN_COST' Type: Decimal Direction: Input Value: '0' Parameter: '@ASSN_ACT_COST' Type: Decimal Direction: Input Value: '0' Parameter: '@ASSN_ACT_OVT_COST' Type: Decimal Direction: Input Value: '0' Parameter: '@ASSN_REM_COST' Type: Decimal Direction: Input Value: '0' Parameter: '@ASSN_REM_OVT_COST' Type: Decimal Direction: Input Value: '0' Parameter: '@ASSN_OVT_COST' Type: Decimal Direction: Input Value: '0' Parameter: '@ASSN_BOOKING_TYPE' Type: SmallInt Direction: Input Value: '0' Parameter: '@ASSN_IS_OVERALLOCATED' Type: Bit Direction: Input Value: 'False' Parameter: '@ASSN_CONTOUR_DATA' Type: VarBinary Direction: Input Size: -1 Parameter: '@MOD_REV_RANK' Type: Int Direction: Input Value: '0' Parameter: '@CREATED_REV_COUNTER' Type: Int Direction: Input Value: '0' Parameter: '@MOD_REV_COUNTER' Type: Int Direction: Input Value: '0'

Does anybody have any ideas where this problem comes from and how to avoid it?

Thanks!

Answer 1

The query essentially performs insertion into the [pjpub].[MSP_ASSN_ENTERPRISE] table. This table has check constraint on it with the following expression:

[ASSN_FINISH_DATE] <= '2149-12-31 23:59:00.000'

Just alter this to be:

[ASSN_FINISH_DATE]<=CONVERT(DATETIME, '2149-12-31 23:59:00.000', 120)

You can find this table in project server database you created for Project Server Service Application.

PS: Not sure if this constraint actually needed at all.