Project Server 2013: After migrating from 2010 some resources can’t be edited


We have only seen this recently – so not sure if something changed – but in certain conditions (which I’ll come to later) after a migration from Project Server 2010 to Project Server 2013 you will not be able to edit some users/resources in Project Server 2013 through the Manage Users or Edit resources features.  The error will show as a red message when you save the resource/user from PWA and will say:

The resource could not be saved due to the following reasons:

    • A resource availability contour cannot go outside the range of the resource's earliest and latest availability dates.

image

The resource name would not normally be blank – I’ve edited it out here.  This will only occur if the resource’s ‘available to’ date (held in the database as RES_TERMINATION_DATE) contains a time element of 23:59:00.000 as well as the date.  If the time is 00:00:00.000 then no problem.  If a resource pool is edited in Project Professional 2010 and saved to Project Server 2010 it appears the date will get saved with a time of 23:59  – saving from PWA will not – it uses 00:00.  Once migrated it looks like PWA is getting confused by the 23:59 and thinking the resource’s availability contour (that ends at 23:59) is beyond the truncated time of 00:00.

A couple of ways to fix this.  If you discover this before migration then editing all your affected resources and saving in PWA will leave all the RES_TERMINATION_DATE fields set with 00:00 and you will be good to go.

To detect the problem you can run this query on the 2010 ‘published’ database (and if you don’t know how to edit this to work with your specific database name then you probably shouldn’t be running it – get assistance from your DBA).:

SELECT [RES_UID]
      ,[RES_NAME]
      ,[RES_HIRE_DATE]
      ,[RES_TERMINATION_DATE]
       FROM [ProjectServer_Published].[dbo].[MSP_RESOURCES]
      where (convert(varchar, RES_TERMINATION_DATE, 8)) = '23:59:00'

If this isn’t practical because you find too many resources to edit – then you can run a SQL command to set the 23:59 back to 00:00 BEFORE migration using the following two queries: -

UPDATE [ProjectServer_Published].[dbo].[MSP_RESOURCES]
Set RES_TERMINATION_DATE = DATEADD(hh, -23, RES_TERMINATION_DATE)
where (convert(varchar, RES_TERMINATION_DATE, 8)) = '23:59:00'


UPDATE [ProjectServer_Published].[dbo].[MSP_RESOURCES]
Set RES_TERMINATION_DATE = DATEADD(mi, -59, RES_TERMINATION_DATE)
where (convert(varchar, RES_TERMINATION_DATE, 8)) = '00:59:00'

If you have already done your final migration (and didn’t come across this issue during your testing – shame on you…) then you can still fix this in the 2013 database running similar queries to fix things AFTER migration:-

UPDATE [ProjectWebApp].[pub].[MSP_RESOURCES]
Set RES_TERMINATION_DATE = DATEADD(hh, -23, RES_TERMINATION_DATE)
where (convert(varchar, RES_TERMINATION_DATE, 8)) = '23:59:00'


UPDATE [ProjectWebApp].[pub].[MSP_RESOURCES]
Set RES_TERMINATION_DATE = DATEADD(mi, -59, RES_TERMINATION_DATE)
where (convert(varchar, RES_TERMINATION_DATE, 8)) = '00:59:00'

We will be looking at a potential fix here so that 2013 can overcome the problem – and also look at including this in our migration docuemntation at https://technet.microsoft.com/en-us/library/ff603504.aspx - Prepare your environment for an upgrade to Project Server 2013.


Comments (1)

  1. Thanks Brian for the helpful blog.

Skip to main content