Statusing Transactions (Partial Documentation)

Introduction

Unlike the project Web Access “My Tasks” web part, the Statusing web service API restricts team member task assignment updates that combine with the create/reassign action to one per approval cycle. This means that once a create/reassign plus updated status is submitted it cannot be further updated until accepted or rejected by the Project Manager.

This limitation is caused by the absence of “Node Consistency” functionality in the API. This component ensures that the task is kept schedule consistent during team member updates. The project Server 2007 architecture placed this component on the Project Web Access web front end server, rather than in the Project Server Interface business object layer, so as to deliver a responsive user experience in the UI.

Unfortunately the application developer who is tasked with synchronizing status with an external system (such as an ERP or CRM system) has no means to detect this “blocked” status. We plan to add such a method in an upcoming release of Project Server. In the interim this documentation can be used to extend the Project Server Interface to add a web service/method to deliver the status update status.

Note: This document contains interim documentation for tables in the Published database of a RTM-edition Project Server 2007 installation. Entities in this schema are subject to change without notice. This documentation is made available on a “best efforts” basis, Microsoft Product Support Services have not been trained in its usage and will not offer technical support for issues related to custom queries against this schema. This schema should not be updated by custom code, any updates to the data may break Project client cache code.

Extending the Project Server Interface

This is documented in the following Project Server 2007 Software Development Kit article.

https://msdn2.microsoft.com/en-us/library/bb428837.aspx

Note that any code will be required to connect to the Project Server 2007 Published database – in a single site farm this may safely be hard coded, however in a multi-site farm it is recommended that the developer implement a method for connecting to the correct published database, suggestions include:

• Using the SharePoint site-id to index into an INI file with the correct connection string stored as an external string
• Passing the database (and SQL Server name) as method arguments.

Understanding the MSP_ASSIGNMENT_TRANSACTIONS Table

All status updates are stored in the MSP_ASSIGNMENT_TRANSACTIONS table together with flags indicating the current state of the update. The flags necessary to locate a blocked task assignment are documented below.

There are four ENUM fields that govern state, these are:

So a blocked task assignment and will have ASSN_TRANS_STATE_ENUM = 1 (pending approval) and ASSN_TRANS_TYPE_ENUM = 2,3,4 (indicating a pending task assignment add/change)

SQL Server query to test the status of a particular task assignment:

 SELECT TOP 1 ASSN_TRANS_STATE_ENUM 
FROM MSP_ASSIGNMENT_TRANSACTIONS 
WHERE ASSN_UID = '617633E6-8B2A-4620-BCAD-F82A95AD398D' -- Assignment UID 
AND ASSN_TRANS_STATE_ENUM=1 
AND ASSN_TRANS_TYPE_ENUM IN (2,3,4)

Notes:

If the task assignment status update is not blocked then no data will be returned; if it is blocked then a scalar value of integer 1 will be returned.

See comments in the performance section before changing this query.

Security Considerations

PSI Extensions are responsible for implementing their own security. In this case there are mitigations which mean that additional permissions checks may be unnecessary:

• All callers must be authenticated users making anonymous DoS attacks impossible
• The proposed solution returns a single numeric result that indicates if the assignment is blocked (1) or not (null)
• The argument is a UUID – these are very hard to predict/guess so it isn’t possible to cycle through a sequence hunting for hits
• If an attacker discovers that a particular assignment UID is blocked then there is little they can do with that information
• The call is extremely performant

Note that it would be simple to make the call as the “interface user” and test for that in the PSI extension should further restrictions be required.

Performance Notes

The above query has been optimized for performance – strictly speaking the query is stronger than required as only the second task assignment status update is blocked by the pending approval. However checking for this first pending update would require the addition of a self-join to the query which would reduce performance considerably. This would test for the above condition AND a pending task assignment status update (ASSN_TRANS_TYPE_ENUM = 0). Provided the work is added in the same ChangeXML payload that changes the task assignment, it is superfluous.

It is recommended that a query covering index be created on the MSP_ASSIGNMENT_TRANSACTIONS table to avoid both clustered index scans, secondary index scans or bookmark lookups as the table is indexed on a non-sequential UUID (so data access will be very random).

The following index definition is suggested:

 CREATE NONCLUSTERED INDEX [CUSTOM_STATUS_SYNCH] ON [dbo].[MSP_ASSIGNMENT_TRANSACTIONS] 
( 
  [ASSN_UID] ASC, 
  [ASSN_TRANS_STATE_ENUM] ASC, 
  [ASSN_TRANS_TYPE_ENUM] ASC 
) ON [PRIMARY]

The sample query (above) uses a TOP 1 clause to further reduce the SQL Server results processing (either no rows or a scalar result set will be returned).

If you alter the query to return more data, or to use different search arguments then this index definition might need to change.

Note that creating a stored procedure and using output parameters would further improve performance.

Patrick Conlan