none
Project Server 2010 - Change Status Manager RRS feed

  • Question

  • End Goal: We would like to perform this change via SQL statement - which I believe I have  I feel like I am missing something and not sure what it is.

    Background:  We migrated from a different product to Project Server 2010, while our team members were assisting users to move their projects from the old application to Project 2010 they did not realize their names would appear as status managers.

    The quick solution would be to tell the owners of the projects to change the information themselves (republish or go in themselves and change every line); however we feel this can be cumbersome and too much work due to having hundreds of projects and there are thousands of fields that need to be modified.  I can quickly accomplish what I want with an UPDATE command and be done with it (no user involvement). 

    What I am running into in my test environment is that I can modify the fields (query below) to the appropriate status manager, but I cannot get the changes to appear in Project Professional - it still shows the old value.

    I have used the following command to change the Status Manager to the Project Owner (I'm new to SQL, and we no longer have a DBA - so if my code isnt the greatest...I'll fix it)

    USE ProjectServer_Reporting

    UPDATE MSP_EpmTask

    SET TaskStatusManagerUID = MSP_EpmProject.ProjectOwnerResourceUID

    FROM MSP_EpmProject

    WHERE MSP_EpmTask.ProjectUID = MSP_EpmProject.ProjectUID


    This modifies all the values correctly - Project Owners become the Status Manager in SQL

    Question: Is there something I am missing or can do OR I am not doing to make this new value show up? Is there a better way to do this from an administrative point of view, with no customer/user interaction?

    Your help is greatly appreciated

    Thank You

    kbwrecker





    • Edited by kbwrecker Thursday, May 8, 2014 3:21 PM
    Thursday, May 8, 2014 3:18 PM

Answers

  • Merely my personal opinion having been a Project Server 2010 admin in a medium-sized corporation:

    I think you are going to seriously do some damage if you go about trying to modify SQL tables behind Project Server's back. There are a LOT of operations that go into a simple "publish" and I doubt that the minor changes you want to make will account for all of them. Are you confident you're adjusting all of the values necessary? You may have a detrimental impact on in-flight approvals, timesheets, projects, tasks, assignments.. There is just far too much IMO.

    Believe me, I know it is not an ideal solution to have the business make their own adjustments. However sometimes it is just what has to be done. My vote is to craft brief instructions and send them out to the PMs. It's easy to do, they can change one row and then drag the field down like in Excel to edit all the rest; it's a 1-minute operation, max.

    Due to the nature of the field, (not being something you can edit via PWA, and even in Project Pro it only allows you to change it to yourself or another current Status Mgr) I think you'll have a hard time scripting this one via macro etc.. However if you do find a way, seriously keep in mind your scope and don't edit them all at once, it will need to kick off publishes and check-ins for all the affected projects.

    Good luck!

    • Marked as answer by kbwrecker Wednesday, May 28, 2014 3:05 PM
    Monday, May 12, 2014 7:07 PM

All replies

  • While I do not have the final solution, I can help clarify few things:

    First of all, running the SQL Statement on "Reporting" database, will only change the information that you see in your reports, and most probably will either cause errors or get overwritten when projects are published next time.

    You should be changing the values in the Draft Database, and the issue there is that executing SQL Queeries in Draft Database, is not supported, and you would be vpoiding the warranty from Microsoft.

    So, the only way I think this should be done is to use PSI, may be using PowerShell. Having said that, I am not a PowerShell expert, but many experts on this forum are. I will wait for them to respond further.


    Prasanna Adavi,PMP,MCTS,MCITP,MCT http://thinkepm.blogspot.com

    Thursday, May 8, 2014 3:33 PM
    Moderator
  • Do not do this through SQL Server. Rather, you can use a VBA macro as a better choice. However, try doing this and see if it works:

    As an administrator, delete one of the projects with the incorrect status managers from the published database ONLY. Then have the rightful owner open the project and publish the assignments.


    Gary Chefetz, MCITP, MCP, MVP msProjectExperts
    Project and Project ServerFAQs
    Project Server Help BLOG

    Friday, May 9, 2014 1:43 PM
    Moderator
  • Prasanna Advani:  Thank you for your reply - and I too also hope some of the professionals can assist.

    Gary: I'm unsure how to best accomplish the VBA macro you suggested.  If you could please go over that method a little more in-depth I would be greatly appreciative.

    In regards to your other method, as stated above, asking the managers to do that is not what my management feels is the best solution, nor effective in asking all managers to take the time to do this.

    ========

    I have found the tables which will make the status manager update in the Draft database (WRES_UID_MANAGER) - however I havent found an effective way to change this table via the PSI/VBA/Powershell.  Any and all help would be greatly appreciated.

    Thank you

    Monday, May 12, 2014 2:42 PM
  • Merely my personal opinion having been a Project Server 2010 admin in a medium-sized corporation:

    I think you are going to seriously do some damage if you go about trying to modify SQL tables behind Project Server's back. There are a LOT of operations that go into a simple "publish" and I doubt that the minor changes you want to make will account for all of them. Are you confident you're adjusting all of the values necessary? You may have a detrimental impact on in-flight approvals, timesheets, projects, tasks, assignments.. There is just far too much IMO.

    Believe me, I know it is not an ideal solution to have the business make their own adjustments. However sometimes it is just what has to be done. My vote is to craft brief instructions and send them out to the PMs. It's easy to do, they can change one row and then drag the field down like in Excel to edit all the rest; it's a 1-minute operation, max.

    Due to the nature of the field, (not being something you can edit via PWA, and even in Project Pro it only allows you to change it to yourself or another current Status Mgr) I think you'll have a hard time scripting this one via macro etc.. However if you do find a way, seriously keep in mind your scope and don't edit them all at once, it will need to kick off publishes and check-ins for all the affected projects.

    Good luck!

    • Marked as answer by kbwrecker Wednesday, May 28, 2014 3:05 PM
    Monday, May 12, 2014 7:07 PM