none
Update Project custom field through RRS feed

  • Question

  • Hi 

    I have custom field as an project attribute ((Project Type) Value = 'A') and there are 200 projects in the Project Server 2010. I would like change the custom field (Type value )value 'A' to 'B' of 150 projects through SQL Query.

    It is a laborious job to open each project through PWA update the field and then save, publish and check-in, instead i would like to directly update into database. 

    How can i acheived through SQL Query.

    Thanks 

    Farhan


    farhan

    Tuesday, February 23, 2016 6:59 PM

All replies

  • There is no supported way to do that via SQL query. Programmatically, you need to use PSI or CSOM, but as this is a once off task by far the quickest is to use VBA to open the projects, change the value then save, publish and check-in. It won't be quick, as the macro will need to do it one project at a time, but it will work.

    Rod Gill
    Author of the one and only Project VBA Book
    www.project-systems.co.nz

    Tuesday, February 23, 2016 7:39 PM
    Moderator
  • This can be done using a Bulk Edit tool such as this one:

    https://store.office.com/ipmo-bulk-publish-WA104111715.aspx?assetid=WA104111715

    Or this one:

    https://store.office.com/bulk-edit-WA104148561.aspx?assetid=WA104148561

    If you are using 2010 I am not sure that one would work but you could try to find an older version.

    Good luck

    Ryan Darby

    www.ipmo.com.au

    Friday, February 26, 2016 5:07 AM
  • Direct update in through SQL query is neither recommended nor supported by MS as PS DB schema is very complex and change done in one place needs to be done at other places as well because of different references through manual query update we can not expect it.So,you can create a custom utility based on PSI which will give you a list of projects first and then you can update the custom field  value for multi selected projects.After update,a call to 'Publish' queue would ensure that change in value is updated in all required places in DB.

    This needs obviously a big development effort.For your reference,PFB the URLs which might help you -

    To read custom field values - http://sharepoint1on1.blogspot.co.uk/2013/04/project-server-read-custom-field-with.html

    PSI reference - https://msdn.microsoft.com/en-us/library/office/ms488627%28v=office.14%29.aspx?f=255&MSPPError=-2147217396#pj14_PSIRefOverview_Assemblies

    Thanks,

    Ashish Tyagi

    Friday, February 26, 2016 1:04 PM