General Reporting message processor failed: OR ProjectPublish Failed:  ReportingProjectChangeMessageFailed (24006) - The INSERT statement conflicted with the FOREIGN KEY constraint

Cause:

In the majority of cases, the cause of this is due to baselined tasks being deleted from the schedule, causing it to be out of sync with the Reporting Database. The project does not successfully reach the Reporting Database and the publish job fails.

Fix:

The fix for this depends if it is only affecting 1 or 2 projects or many. It may also be affecting timesheet aggregation jobs to fail. If it is affecting many and you are seeing timesheet jobs failing consistently, use option 2. If it is only affecting 1 or 2 projects, use option 1.

Option 1:

If affecting only 1 or 2 projects:

1. Take a full db backup.

2. Execute the following query on both the Published and Draft databases:

--------
select PROJ_NAME, MTB.PROJ_UID,TASK_UID,TB_BASE_NUM from MSP_TASK_BASELINES MTB

inner join MSP_PROJECTS MP on MTB.proj_uid=MP.proj_uid
where TASK_UID not in
(select TASK_UID from MSP_TASKS)

order by PROJ_Name
delete from MSP_TASK_BASELINES where TASK_UID not in (select TASK_UID from MSP_TASKS)
-----

3. Republish the trouble projects

Option 2:

A resync of the reporting database can resolve the issue if you have multiple publishing/timesheet errors, however first you should ensure that the sync will be successful by following these steps:

NB: Reporting db syncs can take a long time, particularly with many projects/resources. Do this at the quietest time possible to avoid further issues:

1. Take a full backup

2. Execute the same query as posted above to the published and draft dbs

3. Go to Server settings>Administrative Backup and backup the Enterprise Custom Fields

4. Go to Server settings>Administrative Restore and restore the Enterprise Custom Fields

5. Monitor the queue and insert 'successful jobs' to watch the sync do its thing. Eventually the 'Reporting Database Refresh' job will tick over to be successfully completed.

6. The problem now is that the Enterprise Project Type UID is removed from every single project in the reporting db leaving a NULL value. (For unknown reasons. Ideally this will be fixed!). The problem is that if you use data connections or SSRS reports that reference the EPT, these reports won't work as expected.

7. To reapply the Enterprise Project Type UIDs you can publish each project individually which will fix the problem, or if you were like me and had 1200 projects, you might like run project publish from the PSI or use a simpler method as follows:

8. Go to Server Settings>Change and Restart Workflows

9. Select all projects from EPT A and associate (again) with EPT A. Repeat this process for each EPT. Be sure to associate them to the same EPT as they originally were.

10. The UIDs will return in the reporting db following this and your reports should return to normal. Obviously, rebuild the Cube if you did this after the nightly build.

Hope this saves someone out there :)

General Reporting message processor failed: OR ProjectPublish Failed:  ReportingProjectChangeMessageFailed (24006) - The INSERT statement conflicted with the FOREIGN KEY constraint

Cause:

In the majority of cases, the cause of this is due to baselined tasks being deleted from the schedule, causing it to be out of sync with the Reporting Database. The project does not successfully reach the Reporting Database and the publish job fails.

Fix:

The fix for this depends if it is only affecting 1 or 2 projects or many. It may also be affecting timesheet aggregation jobs to fail. If it is affecting many and you are seeing timesheet jobs failing consistently, use option 2. If it is only affecting 1 or 2 projects, use option 1.

Option 1:

If affecting only 1 or 2 projects:

1. Take a full db backup.

2. Execute the following query on both the Published and Draft databases:

--------
select PROJ_NAME, MTB.PROJ_UID,TASK_UID,TB_BASE_NUM from MSP_TASK_BASELINES MTB

inner join MSP_PROJECTS MP on MTB.proj_uid=MP.proj_uid
where TASK_UID not in
(select TASK_UID from MSP_TASKS)

order by PROJ_Name
delete from MSP_TASK_BASELINES where TASK_UID not in (select TASK_UID from MSP_TASKS)
-----

3. Republish the trouble projects

Option 2:

A resync of the reporting database can resolve the issue if you have multiple publishing/timesheet errors, however first you should ensure that the sync will be successful by following these steps:

NB: Reporting db syncs can take a long time, particularly with many projects/resources. Do this at the quietest time possible to avoid further issues:

1. Take a full backup

2. Execute the same query as posted above to the published and draft dbs

3. Go to Server settings>Administrative Backup and backup the Enterprise Custom Fields

4. Go to Server settings>Administrative Restore and restore the Enterprise Custom Fields

5. Monitor the queue and insert 'successful jobs' to watch the sync do its thing. Eventually the 'Reporting Database Refresh' job will tick over to be successfully completed.

6. The problem now is that the Enterprise Project Type UID is removed from every single project in the reporting db leaving a NULL value. (For unknown reasons. Ideally this will be fixed!). The problem is that if you use data connections or SSRS reports that reference the EPT, these reports won't work as expected.

7. To reapply the Enterprise Project Type UIDs you can publish each project individually which will fix the problem, or if you were like me and had 1200 projects, you might like run project publish from the PSI or use a simpler method as follows:

8. Go to Server Settings>Change and Restart Workflows

9. Select all projects from EPT A and associate (again) with EPT A. Repeat this process for each EPT. Be sure to associate them to the same EPT as they originally were.

10. The UIDs will return in the reporting db following this and your reports should return to normal. Obviously, rebuild the Cube if you did this after the nightly build.

Hope this saves someone out there :)

Resolution:

Select the Format Ribbon and Text Styles:

Select Item to Change: Changed Cells and apply the required background colour.

Changed Cells will format as below:

Hope that helps those who had otherwise not been making use of this very useful feature.

I am frequently asked how to save straight from an application such as Word or Excel to a SharePoint site, without the manual process of saving it first to a drive and then manually uploading it – which frequently defeats the purpose of using SharePoint as users tend to keep an offline version of each file anyway. Fortunately the function of saving straight to the SharePoint site from Office 2010 applications is very user friendly.

In the first instance the site needs to be added to your list of recently accessed sites. This will happen automatically if you open a file from the site, but in the case where the site is brand new and no documents are available to be opened the site can be added by simply doing the following:

1. In your browser open the SharePoint site and copy the URL
2. From the application (e.g. Word or XL) select File, Save and Send, Save to SharePoint, Browse for a location

     3. Paste the URL into the location bar.

Once the location has been added it is just then a matter of following the same steps - File, Save and Send, Save to SharePoint and selecting the newly added location from the Recent Locations list.

Happy saving.

SharePoint standard functionality does not allow a user to define the width of a column. This is most frequently desired when the list is viewed as a whole, and SharePoint automatically word wraps longer field entries - resulting in a somewhat disjointed view display.

Such as the Title  and Status fields in the below screen shot:

A simple workaround to this it edit the field name to be longer, however entries of space values are automatically stripped out by SharePoint so just adding spaces after the name doesn’t achieve the desired result.
Underscores will work nicely but are somewhat unsightly. The other option here is to enter “hard spaces” (or non-breaking spaces). Hard spaces are not stripped out by Sharepoint and are achieved by holding down the alt key and entering “0160” on the numeric section of the keyboard.

In order to enter a series of these you can enter the first in another application such as Notepad and copy and paste it as many times as required, then copy the series of hard spaces into the SharePoint list column value.

The result looks like the below with both the Status and Title columns having been increased:

It is a little bit fiddly but does present a simple option for those not wanting to edit the list through SharePoint Designer.

Have fun!

So now you would like to link to these documents by creating a new link on the Quick Launch that will take you to the excel web view of the workbook. You do this by copying the shortcut (right click - copy shortcut) and pasting it as a new link on the quick launch.

But what if you don't want it to go to the excel web viewer and you just want the link to automatically open the workbook in excel? This is common due to limitations of the excel viewer around macros and protected sheets etc.

It is the difference in the link you use.

The following link ( copied shortcut) will open the workbook in excel viewer by default:

Http://epm/Pwa/layouts/xlviewer.aspx?id=/Pwa/shared%20documents/document1.xlsm&source=http%3a%2f%2fepm%2fpwa%2fshared%20documents%2fforms%2fallitems%2easpx&defaultitemopen=1 

However if you enter a simpler version of the link, it will open the workbook in excel by default:

Http://epm/Pwa/shared documents/document1.xlsm

So for workbooks that can display and refresh data successfully via the web viewer, just copy the shortcut to the document and paste as a link wherever you like. However if you have a more complex workbook with protected sheets, macros or to be saved locally by the user quickly, use the simpler version of the link above.

Problem

The best solution to be able to efficiently create 1 or more linked child list items from a parent list.

Solution 1

The blog below outlines how to achieve the above using workflows - (See link below).

http://ppm.griff-in.net/2011/10/creating-actions-from-issues-and-risks.html

E.g. A user will create an “Issue” item and use a workflow to create each linked “Action” items.

• Workflows can be set to auto run and create Action” items after creating an Issue item.
• Still requires a few clicks to create an “Action” item.

Solution 2

The blog below uses SharePoint Designer to essentially add a web part to show a custom list in the List Forms for another custom list (DispForm.aspx, EditForm.aspx, NewForm.aspx)

http://geekswithblogs.net/SoYouKnow/archive/2010/12/16/creating-a-sharepoint-parentchild-list-relationshipndash-sharepoint-2010-edition.aspx

E.g. A user can create and view linked “Action” items when viewing or editing an “Issue” item.

• A user can view and create “Action” items straight from an “Issue” items form
• Although the Issue ID textbox in the New actions form is pre-filled, a user can potentially change the Issue ID that does not exist which will create an error.
• Unable to create an “Action” item when create a new “Issue” item.

Referencing the above two solutions, here is the solution to create child list items from within a parent list.

E.g. Creating Action Items from Issue list.

Create the Parent-Child Custom Lists

• Create the parent custom list “Issues” and create the necessary columns. E.g. Description, Status

• Create the child custom list “Actions” and create the necessary columns. E.g. Description

 Create the Parent-Child Relationship

• Create the “Issue” column of field type “Lookup” that links the “Action” list to the “Issues” custom list using the ID.
• Check the tickbox “Enforce relationship behaviour”

Update the List Forms for Child List

• Go to the List > Edit List to edit the list using SharePoint Designer and select the DispForm.aspx in the forms panel
• Delete the existing WebPartPage.
• In the Insert ribbon click SharePoint > Custom List Form…
• Select the options as shown in screenshot. 

• Go to List > Edit List to edit the list using SharePoint Designer and select the EditForm.aspx in the forms panel
• Delete the existing WebPartPage.
• In the Insert ribbon click SharePoint > Custom List Form…
• Select the options as shown in screenshot. 

• Go to the List > Edit List to edit the list using SharePoint Designer and select the NewForm.aspx in the forms panel
• Delete the existing WebPartPage.
• In the Insert ribbon click SharePoint > Custom List Form…
• Select the options as shown in screenshot. 

• Select custom list form in DispForm.aspx  and go to “Options” ribbon.
• Add a new query string parameter by clicking “Parameters” and setting the options as screenshot.

• Update the Issues field to reference the new parameter created - “ThisIssueID”

• Select custom list form in EditForm.aspx  and go to “Options” ribbon.
• Add a new query string parameter by clicking “Parameters” and setting the options as screenshot.

• Update the “Issues” field to reference the new parameter created - “ThisIssueID”

• Select custom list form in NewForm.aspx  and go to “Options” ribbon.
• Add a new query string parameter by clicking “Parameters” and setting the options as screenshot.

• Change the Format for “Issues” field to TextBox
• Update the “Issues” field to reference the new parameter created - “ThisIssueID”

Update the List Forms for Parent List

• Go to List >Edit List to edit the list using SharePoint Designer and select the DispForm.aspx in the forms panel
• Insert a related item view below the list form by going to Insert > Related Item View > Action Issues

• Go to List > Form Web Parts > Default Display Form
• Add a content editor web part to input a link and javascript function as shown in screenshot

• Go to List > Edit List to edit the list using SharePoint Designer and select the EditForm.aspx in the forms panel
• Insert a related item view below the list form by going to Insert > Related Item View > Action Issues

• Go to List > Form Web Parts > Default Edit Form
• Add a content editor web part to input a link and javascript function as shown in screenshot

Display Form for Parent List

Edit Form for Parent List

There is in fact a long list which I have included below (forgive me copyright gods in MS!), to use one of these icons, just update the image URL under Server Settings –> Enterprise Project Types, for example to update the default Sample Proposal with any of the icons below, simply modify the image URL under:

For a Master Project icon (taken from the image below), replace the URL above with the following:

/_layouts/inc/pwa/images/CenterMasterProject.png

Full list of icons (16×16 size only):

Enjoy!

The key benefits of this web part include:

• The ability to create a ‘One stop shop’ for Project Managers

The project manager doesn’t have to keep opening the project site to modify project artefacts, then swap back to the project information and schedule in Project Centre. It can all be accessed via the same interface

• The ability to hide/display important artefacts relevant to the workflow stage

You may store document templates in the document libraries ready for the PM to use, however with the PWLV you can display these documents by linking the library to a Project Detail page that is displayed at certain points of the workflow!

• The ability to display any type of information

There is no limit to the amount of custom information that you can display for project managers. All you need to do is create the content on the Project Site template.

Examples of how you can use the PWLV include:

• Displaying the Risks and Issues lists
• Displaying the Deliverables list
• Displaying project document libraries
• Displaying team discussion board
• Displaying custom lists designed for:
  • Change Requests
  • Actions
  • Decisions
  • Dependencies
  • Financial Information
  • Report Libraries
  • Status Updates
  • Document templates
  • Requirements log
  • Test Cases
  • Post Implementation Surveys
  • Wiki Pages
  • And so on….

• To understand its potential, begin by downloading the solution starters and deploy the PWLV to your Project Server 2010 environment.
• Create your Project Site lists and content that you wish to display (recommend deploying via Project Site templates)
• It all begins with Project Detail Pages, another great new feature of Project Server 2010 as they allow us to now organise our Project Custom fields in a better presentation than ever before:

Providing the Project Manager with all of their project information and artefacts at their fingertips is important. To do this, we can create Project Detail Pages to display key project information, status update information and project contacts by adding the Project Fields web part to project detail pages.

• However we can also display key project artefacts from the Project site once we have deployed the PWLV solution starter by adding this web part:

• Once it is added to a project detail page, select to Edit Web Part from the web part drop down

• Provide the name of the list that you wish to display from the Project Site, you must enter the name exactly how it appears on the site:

You can also rename the web part so that it is relevant to the list you are displaying, e.g. Project Risks

• Select OK on the Edit Web part dialog, and Stop Editing on the SharePoint ribbon

Make sure you also name your Project Detail Page something relevant to the list you are displaying

• Then you just need to add the Project Detail pages to the relevant Workflow Stages and Enterprise Project Types as required

Then bingo! Any project that has this detail page and a project site will be able to see the list data from their Project Detail Pages.

Be creative and enjoy this new feature, I bet you won’t deploy Project Server without it!

Problem:

When attempting to disable BCM mode from Server Settings in a migrated Project Server 2010 environment, unchecking the BCM mode option leads to corruption in multiple project schedules when opened in MS Project 2010.

The following message is displayed:

An unexpected problem occurred while opening the file.

The file may be damaged. Try using a backup copy.

Cause:

After much investigation it seems that the enterprise custom fields configured as “Workflow Controlled” are at fault here, it seems that when disabling BCM mode some (not necessarily all!) projects which have values set in custom fields that are workflow controlled will become corrupt according to MS Project 2010. No service patch or cumulative update (as yet: Dec/2011) helps, but clearly something in the configuration of those fields gets messed up when BCM is switched off.

For Google and those into debugging WinProj, the internal error is:

The error occurs because the following error is returned when NonCoreProjectData is read.

The queried PID is Bad_PID, and the call winproj!TBkndPropCntr::GetAccessInfoPid cannot get Access information.

Solution:

Fortunately there is one, although a hotfix might come in the future, for my customer(s) working with MS we were able to find a procedure to fix the corruption.

Firstly when you disable BCM (by unchecking Enable Project 2007 Compatibility Mode in Server Settings – Additional Server Settings) the following SQL query can be used to identify any projects that may fail:

select distinct PROJ.PROJ_NAME from MSP_PROJECTS as PROJ

inner join MSP_PROJ_CUSTOM_FIELD_VALUES as PROJCF

on PROJ.PROJ_UID = PROJCF.PROJ_UID

inner join MSP_CUSTOM_FIELDS CF

on CF.MD_PROP_ID = PROJCF.MD_PROP_ID

where MD_PROP_IS_WORKFLOW_CONTROLLED = 1

order by PROJ.PROJ_NAME

In my recent case it was just about every project! But regardless of how many the following steps will correct the issue for those identified projects:

1. Backup your 4 x Project Server and 1 x      SharePoint databases!
2. Copy Workflow Stage configuration from Server      Settings - Workflow Stages. Copy Grid data to Excel, the columns required      most are; Stage Name, Required Custom Fields and Read Only Custom Fields.      (You’ll need this info later!)
3. Copy Enterprise Custom Field configuration from      Server Settings - Enterprise Custom Fields. Copy Grid to Excel. (Again      this is for later reference)
4. Open each Workflow Controlled custom field      from Server Settings and uncheck “Workflow Controlled” then save, this      removes every field from every workflow stage configuration.
1. Note: For each field this       will REMOVE the Read Only and Required configuration from each       workflow stage where this field is used! (Make sure you have your backup       from step #1!)
5. If required Force Check in all checked out projects,      check queue to ensure all jobs complete before continuing.
6. From Server Setting - Additional Server      Settings - Uncheck "Enable Project 2007 Compatibility Mode"
1. Restart MS Project
7. Open each affected project, save and publish      them. (Note a full publish from MS Project is required – nope bulk publish      using ProjTool doesn’t help!)
8. Now to correct the custom field and workflow      configuration, re-open each custom field previously changed and recheck      the “Workflow Controlled” setting. (Using the information backed up in      step #3)
9. Now Reconfigure the required custom fields and      read-only custom fields for ALL workflow stages. (Using the information      backed up in step #2)
10. Re-test affected projects.

The process effectively removes the “corruption” caused by the Workflow Controlled attribute in those projects, and fortunately if you are stuck after unchecking the BCM box without a backup using these steps (minus step 6) still should work!

I hope if you have this issue that you are seeing it only in Dev, as there is no better test of a DR procedure than unchecking that one little check box! :)

Hope that helps someone else out there.