locked
PS2007 List of archived projects RRS feed

  • Question

  • In PS2007, how do i get a list of all projects in the Archive DB that have finish date in 2011?

    All those projects have been deleted from published and Draft DBs. I can;t see ProjectFinishDate in Archive DB.

    Friday, February 3, 2012 8:24 PM

Answers

  •  
    If I remember well, the "Project System Health Report" gathers summary data to help for capacity planning, in order to increase system performance. It's not there to provide a reliable report on project data.

    Microsoft may require you to do non-supported things like querying or even, in the case of a support call for example, writing / deleting data from the databases. This is acceptable because the Microsoft team is supposed to know what they're doing, since they designed those databases. If you decide to do it on your own, though, it's at your own risk and not supported.

    The "Archive" database is a data backup (you can say its purpose is similar to the recycle bin, it allows you to restore lost data). It's not a mean to archive closed projects. If you still need to query the data and display it in reports, it's not a good idea to delete the projects from the published and draft DBs. I suggest you follow Paul's advice.

    • Marked as answer by pwa guy Tuesday, February 7, 2012 4:32 PM
    Tuesday, February 7, 2012 9:22 AM
    • Marked as answer by pwa guy Monday, February 6, 2012 5:52 PM
    Friday, February 3, 2012 10:07 PM

All replies

    • Marked as answer by pwa guy Monday, February 6, 2012 5:52 PM
    Friday, February 3, 2012 10:07 PM
  • PWMather,

    So is there no way that I gather metrics for my Archived projects using SQL queries?

    Suppose I want to know the number of the archived projects that finished off in time? Is using PSI the only way?

    Monday, February 6, 2012 4:33 PM
  • Hi there,

    The supported approach is to use the PSI, it is only supported to query the Reporting DB directly via T-SQL, other Project Server PWA DBs should be queried via the PSI.

    Thanks

    Paul


    Paul Mather | Twitter | http://pwmather.wordpress.com
    Monday, February 6, 2012 4:51 PM
  • PWMather,

    I'm supporting a PS2007 environment which I did not develop/configure. I came across a report which was 'supposed' to display projects from archive DB.

    The query runs against both reporting DB and archive DB. The query seems to have been release by microsoft . Can you help me understand why microsoft released this query.

    I'm assuming looking at the initial comments you will be able to tell me the purpose of this query.

    Initial few lines of query:

    --Project System Health Report V1.0

    -- Extracts a series of data points from the Archive & Reporting databases to qualify the data in a Project system

    --

    -- Customization

    --   1. If running against Project Server 14 then search & fix "**P14**" code

    --

    -- Privacy/Information Protection Notes

    --   1. Report contains "Proj.PROJ_NAME" - please replace with N''*** INFORMATION REDACTED ***'' to remove this information

    --   2. Report contains data that can be used to quantify the amount of project work in the org, please review this for sensitivity prior to sending the data to Microsoft

    Monday, February 6, 2012 9:40 PM
  •  
    If I remember well, the "Project System Health Report" gathers summary data to help for capacity planning, in order to increase system performance. It's not there to provide a reliable report on project data.

    Microsoft may require you to do non-supported things like querying or even, in the case of a support call for example, writing / deleting data from the databases. This is acceptable because the Microsoft team is supposed to know what they're doing, since they designed those databases. If you decide to do it on your own, though, it's at your own risk and not supported.

    The "Archive" database is a data backup (you can say its purpose is similar to the recycle bin, it allows you to restore lost data). It's not a mean to archive closed projects. If you still need to query the data and display it in reports, it's not a good idea to delete the projects from the published and draft DBs. I suggest you follow Paul's advice.

    • Marked as answer by pwa guy Tuesday, February 7, 2012 4:32 PM
    Tuesday, February 7, 2012 9:22 AM