none
Project Server 2007 - How do I delete a project in the Reporting database? RRS feed

  • Question

  • Hi,

    I do not know how this could happened, but users are very creative. Looking at the Reporting database I discovered that there are two projects with same [ProjectName] (no kidding) with different ProjectUID as shown below. As the query is filtering by exact match name, both projects have the same name. This server works with Project Server 2007 x86, + Infrastructure +SP3 +CU Aug 12 with SQL 2005 x64 SP3.

    Same project name, different ProjectUID in Reporting db

    I checked this in the other dbs and this happens only in the Reporting. Looking at other views and tables in this db, tasks, resources, assignments are also populated with data from both projects. Off course in PWA appears only one project, but when I extract a report in Excel Services for instance, it brings data from the ghost copy. The easiest solution is delete the project, leaving the trash behind and save it with a new name, but I would like to delete this ghost project only in the reporting db.

    Can sameone help me with a script?

    Thank you.

    R.Segawa


    Best regards, Ricardo Segawa - Segawas Projetos / Microsoft Partner



    • Edited by R.Segawa Wednesday, November 14, 2012 2:32 PM
    Wednesday, November 14, 2012 2:25 PM

Answers

All replies

  • Hi there,

    You can use the MSP_EPM_DeleteProject stored procedure in the Reporting database to delete this project, this requires the ProjectUID as an input. In SQL Management Studio, expand the reporting database > Programmability > Stored Procedures, right click on MSP_Epm_DeleteProject, select Execute Stored Procedure and enter the project GUID in the Value column.

    Take database backups first.

    Thanks

    Paul


    Paul Mather | Twitter | http://pwmather.wordpress.com | CPS

    • Marked as answer by R.Segawa Saturday, November 17, 2012 3:23 AM
    Wednesday, November 14, 2012 2:58 PM
    Moderator
  • Hi PWMather,

    Great! Thank you.

    I will try this and I will return to you.

    Kind regards,

    R.Segawa


    Best regards, Ricardo Segawa - Segawas Projetos / Microsoft Partner

    Wednesday, November 14, 2012 3:02 PM
  • Hi,

    A safer approach might be to rebuild the Reporting database first using the steps at http://www.projectserverexperts.com/ProjectServerFAQKnowledgeBase/forcerebuildofprojectserverreportingdatabase.aspx

    Deleting the record may have unintended circumstances.


    Alex Burton
    www.epmsource.com | Twitter
    Project Server TechCenter | Project Developer Center | Project Server Help | Project Product Page

    Wednesday, November 14, 2012 8:27 PM
    Moderator
  • Hi Alex,

    Thank you for your help.

    I read the FAQ article but it is not the case. We are not having trouble in the queue. The issue is the orphan project (no correspondence in Draft and Published dbs) but a complete project with the same existing name in reporting db, but different ProjectUID that affects BI reports.

    It is basically trash left in the reporting db due a failure in completing the deletion of a former project.

    R. Segawa


    Best regards, Ricardo Segawa - Segawas Projetos / Microsoft Partner

    • Marked as answer by R.Segawa Saturday, November 17, 2012 3:22 AM
    • Unmarked as answer by R.Segawa Saturday, November 17, 2012 3:22 AM
    Friday, November 16, 2012 1:40 AM
  • Rebuilding the reporting db will remove the content of the reporting db and then rebuild using the published DB as a source of trusted data. If you have duplicate records this should address it. 

    That link was more to show you the process, not necessarily the symptoms. 


    Alex Burton
    www.epmsource.com | Twitter
    Project Server TechCenter | Project Developer Center | Project Server Help | Project Product Page

    Friday, November 16, 2012 6:04 AM
    Moderator
  • Hi Alexander and PWMather,

    Both options worked well. Comparing both, executing the Stored Procedure in the Reporting Database took less than 3 seconds while the restoring the Enterprise Fields took 11 minutes for an environment with only 6 projects and 14 enterprise fields.

    I do not know the reliability of each option in a long term use in Project Server, but IMHO if the issue is restricted to a few projects I would use the Stored Procedure, which is faster. If I am not sure the extension of the problem, I would use the backup and restore the Enterprise fields and wait for a very long time to process it. I used this restore option before for other issues for larger dbs and it took more than 4 hours to finish the processing.

    Thank you for Alexander and PWMather.


    Best regards, Ricardo Segawa - Segawas Projetos / Microsoft Partner

    Saturday, November 17, 2012 3:22 AM