Remove a MSP project from the prdocument table

Products

• Clarity PPM On Premise
• Clarity PPM SaaS

Issue/Introduction

How do you temporarily remove a project's MPP File from the prdocument table for Microsoft Project (MSP) Interface issues and what is the impact?

Note:  Prior to running this query, it's recommended to check with Broadcom support first unless a KB is found with a match for the symptoms are you are facing.

Resolution

Table of Contents

• What does this query do?
• When is this query needed?
• IMPORTANT: IMPACT OF RUNNING SCRIPT
  • MSP Legacy Driver specific
  • MSP Legacy Driver and MSP New Driver
  • MSP New Driver specific
• How to remove from prdocument table
• Notes

What does this query do?

1. It removes the project temporarily from the prdocument table which is where MSP project's mpp files are stored
2. It does not remove any data within Clarity
3. Once the project is opened successfully from Clarity to MSP and saved back, the record is recreated in the prdocument table
4. The query run below creates a backup of the project in the prdocument table as a precaution just in case the project record needs to be restored

When is this query needed?

This may be requested by Broadcom support based on the symptoms reported for one or more MSP Projects and can include:

1. Issues opening projects
2. Issues saving projects
3. In a few scenarios, it may also help with MSP data / setting issues, but this should be checked first with support unless a KB is found that confirms this is needed
4. One common cause that requires this query is due to copying and pasting of MSP tasks in 15.7 and lower in an unsupported method (see MSP copying and pasting tasks - supported method for Clarity for more details)

IMPORTANT: IMPACT OF RUNNING SCRIPT

MSP Legacy Driver specific

 All custom views for the project will be deleted. ​When the project is opened from Clarity after the update, the project will only open with the default columns set for the view. Users will have to recreate their views for the project unless they have them saved in MSP. (This doesn't apply in the new driver, as views are no longer saved. Users should always save the views on their MSP and switch to that view after the project opens).

MSP Legacy Driver and MSP New Driver

 Any custom field data not mapped to Clarity will be deleted from the project.

• This only impacts data entered in an MSP Field (such as a text field) and that data isn't stored in Clarity. This impacts both the MSP New Driver and MSP Legacy Driver.

MSP New Driver specific

All Fixed Work tasks will currently change to Fixed Units. 

• The user will need to manually change all applicable tasks back to Fixed Work the next time the project is opened in MSP.

How to remove from prdocument table

(SaaS customers, contact Broadcom support for assistance)

Run the below query:

update prdocument

set prrecordid = ?? where prrecordid=?

and prtablename= 'PRJ_PROJECTS'

Notes

1. Replace the ? with the 5 million number of the project. For ?? change the 5 to a 9. This will create a backup of the record in the database.
   • This internal ID is found in the URL when you click on the project.
     1. In Clarity, go to Home->Projects
     2. Click on the link to the project
     3. In the URL, at the end of the URL will be 7 digit number, that's the # needed for the query.
        • You can also run a query to get the internal ID, such as: select id from inv_investments where name='<project name>' and object_type = 'PROJECT' 
2. Run the below query to ensure that there is not already a project with the ?? prrecordid:
   
   select * from prdocument where prrecordid = ??

Example:

1. 1. URL to a project is:
      
      http://<clarityserver>/niku/nu#action:projmgr.projectProperties&id=5000000
   2. The ID needed for the query then is: 5000000
   3. Run the following (replacing the first digit with a 9) to make sure there is not a record already there (usually, there isn't)
      
      select * from prdocument where prrecordid = 9000000
      
      (The expected result is that no record is returned with this query)
   4. If step 3 returns no results, run:
      

      update prdocument

      set prrecordid = 9000000 where prrecordid=5000000

      and prtablename= 'PRJ_PROJECTS'

Additional Information

• MSP Integration known issues - master KB