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:

Attribute	Value	Enumeration
ASSN_TRANS_STATE_ENUM	012	Not SubmittedSubmitted Pending ApprovalApproved or Rejected
ASSN_TRANS_TYPE_ENUM	01234567	Task Assignment UpdateDeclined Task AssignmentCreate Task RequestDelegate Task Assignment RequestCreate Task Assignment RequestTeam Delegation RequestDelete Task RequestDelete Task Assignment Request
ASSN_TRANS_ACTION_ENUM	012	PendingApprovedRejected
ASSN_TRANS_ERROR_ENUM	0123457	AppliedUndefinedConflictProjectDeletedInvalidUpdate; InvalidDelegationInternalErrorCount

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

Comments

• Anonymous
  January 06, 2008
  Hi I haven't time to study custom PSI extensions but I believe that microsoft can provide us with sample PSI extension which will be seamlessly integrated with project server.(will not be nessesary store DB connection string in extra file) On MS project conference Christophe Fiessinger presented same sample where Connection string was stored in XML config file, but such customization cannot be easily suportd in production environment. PS Chriss will have same other presentation regarding this topic so I belive that he is the rigth guch who can prepare sample PSI extenbsion which cover following topics. how to get DB  connection string, and how to create custom premission for your new functionality. how to create stub web service.

• Anonymous
  January 09, 2008
  Hi and thanks for the helpfull info. You say that there are 3 differents values for the ASSN_TRANS_STATE_ENUM (0,1,2) but I have many records with the value 3 ! What is the meaning of this value ?

• Anonymous
  February 11, 2008
  Hello, Just a small update to the Statusing Transaction (Partial Documentation) post. The ASSN_TRANS_ACTION_ENUM

• Anonymous
  February 11, 2008
  Hello, Just a small update to the Statusing Transaction (Partial Documentation) post. The ASSN_TRANS_ACTION_ENUM

• Anonymous
  February 18, 2009
  Suite à l'article de Molnar Tibor sur Project Server 2007, j'ai voulu pousser les recherches un peu plus

• Anonymous
  January 08, 2010
  I also saw a value of 3 in the ASSN_TRANS_STATE_ENUM field.  I only see this if it's ACCEPTED.  The article states the value of 2 is rejected or accepted; this doesn't make sense.  2 is rejected, 3 is accepted.  If it's rejected, the system creates a new entry for the same task; if it's accepted it updates that same row.  That's how it keeps track of the earlier posted time; although I don't know who would care. I think it's for history so when the user gets a time entry rejected, it stays on the timesheet.

• Anonymous
  January 10, 2012
  How do we see transaction history in PS 2010?