none
SQL Query to retrieve the Process Status in MSPS 2010 PWA RRS feed

  • Question

  • Hi,

    Can someone help us to identify how to retrieve all the task with process status as "Awaiting Approval" in the PWA of MSPS 2010.

    Thank you,

    Friday, February 15, 2013 3:39 AM

All replies

  • I'm not sure I understand what your requirement is. But, if you need to retrieve tasks which are stored in list Project Server Workflow Tasks, then you need to look in your SharePoint DB with this query:

    SELECT *
      FROM [WSS_Content_af78ce2731fa453b910fe647f993b9fc].[dbo].[AllUserData]
      WHERE nvarchar4 like'%In Progress%'

    WSS_Content_af78ce2731fa453b910fe647f993b9fc  - this should be changed to match your SP database name

    Name of your project should be in column 'nvarchar1'.

    Sunday, February 17, 2013 1:42 PM
  • Sanil,

    This information is stored in the Published database, which is not documented. What's the issue you are attempting to address?

    Treb Gatte | Twitter | http://AboutMSProject.com

    Sunday, February 17, 2013 9:56 PM
    Moderator
  • Hi Treb,

    I am trying to produce a report of tasks /assignments that are in the Project Managers plate in "to be approved" Process State.

    When I see the tasks in my Tasks view I could see the Process Status as "Awaiting Approval". I wanted to have a SQL query to identify how many such tasks / assignments are there for the Manager (basically unapproved by my Manager).

    Thanks in anticipation.

    Regards

    Sanil Samuel

    Monday, February 18, 2013 1:33 AM
  • Sorry Sanil, didn't understand your question at first.

    "Awaiting approval" flag is located in ProjectServer_Published database, in MSP_ASSIGNMENT_TRANSACTION table, in column ASSN_TRANS_STATE_ENUM.

    So, this can be your SQL:

    SELECT * FROM MSP_TASKS_SAVED AS tblTask, MSP_ASSIGNMENT_TRANSACTIONS AS tblAssign
    WHERE tblTask.TASK_UID = tblAssign.TASK_UID AND tblAssign.ASSN_TRANS_STATE_ENUM = 1

    I haven't tested this entirely, hope it will help.



    Tuesday, February 19, 2013 8:47 PM
  • Sanil,

    You may also want to look at this: https://technet.microsoft.com/en-us/subscriptions/securedownloads/websvcstatusing.statustransactiondataset.statustransactionsrow.assn_trans_state_enum(v=office.14).aspx, as this provides the ENUM values for the query that Mario provided. Be aware that this report will need to be revisited if you ever upgrade.

    Treb Gatte | Twitter |http://AboutMSProject.com

    Wednesday, February 20, 2013 9:44 PM
    Moderator