MS Project Download
Имя: Пароль:
Забыли пароль?

Статьи

Методика управления проектами [86]

Методические пособия и книги [28]

Готовые отраслевые решения [60]

Обзоры программ для управления проектами [63]

События в мире Управлениия Проектами [129]

Сравнение разных программ для управления проектами [26]

Обучение и сертификация [54]

Управление рисками [4]

Опыт внедрения [38]

Разрешение проблем MS Project и др. системах [4]

Скачать Microsoft Project [3]

Администрирование MS Project Server [36]

Разработка для Microsoft Project [5]


02.12.2003
Microsoft Office Project 2003 Data Reference

Microsoft Office Project 2003 Data Reference

Microsoft Office Project 2003 Data Reference
Overview
B B What's new in the Project 2003 database

About the Project Database
B B Supported databases
B B Loading older versions of Project into the database
B B Upgrading the Project database from an older version

Database Permissions and Configuration
B B Required permissions
B B Configuring the database
B B Performance tuning
B B Ensuring data integrity

How Information is Stored in the Project Database
B B Calendar data
B B Timephased data
B B Notes
B B Custom field values
B B Split tasks
B B Task links
B B Estimated duration values
B B Baselines

Working with Projects in the Project Database
B B Creating the Project database structure
B B DSN requirements and limitations
B B Concurrent usage and project locking
B B Appending XML data to existing projects

Adding and Changing Rows in the Database
B B Setting the flags required to enable updating project data in the database
B B Specifying times with dates
B B Duration, work, rate, and cost values
B B Using the text conversion tables to retrieve strings

Creating Project Schedule Data
B B Creating a new, non-enterprise project
B B Creating an inserted project
B B Creating a new, non-enterprise resource
B B Creating a new, non-enterprise calendar
B B Specifying calendar working time and exceptions
B B Specifying resource availability
B B Specifying resource rates
B B Creating a new task
B B Creating a recurring task
B B Creating task dependencies
B B Creating a new assignment
B B Creating or modifying an assignment actual-work order
B B Creating splits in scheduled work
B B Creating or modifying an assignment remaining-work order
B B Managing timephased data
B B Creating or modifying task-percent complete contours
B B Deleting a contour

Customizing Project Data
B B Specifying custom text fields
B B Specifying custom number (cost) fields
B B Specifying custom date, start, and finish fields
B B Specifying custom duration fields
B B Field attributes
B B Specifying custom WBS codes
B B Specifying custom outline codes
B B Specifying custom aliases for custom fields
B B Specifying formulae for custom fields
B B Reading and writing RTF notes

Managing Other Data in the Database
B B Specifying an e-mail address
B B Retrieving cross-project link project/task references
B B Retrieving the names of sharer files
B B Outlining with summary tasks and subtasks
B B Editing work on a summary task assignment
B B Retrieving workgroup message status
B B Specifying hyperlinks

Database Processing Order, Conventions, and Abbreviations
B B Processing order of externally edited data
B B Standard processing order
B B Table naming conventions
B B Column naming conventions
B B Column name abbreviations

Database Tables
B B Information-only columns
B B Bold column names
B B Column data types
B B MSP_ASSIGNMENT_BASELINES
B B MSP_ASSIGNMENTS
B B MSP_ASSN_ENTERPRISE
B B MSP_ATTRIBUTE_STRINGS
B B MSP_AVAILABILITY
B B MSP_CALENDAR_DATA
B B MSP_CALENDARS
B B MSP_CODE_FIELDS
B B MSP_CONVERSIONS
B B MSP_DATE_FIELDS
B B MSP_DURATION_FIELDS
B B MSP_FIELD_ATTRIBUTES
B B MSP_FLAG_FIELDS
B B MSP_LINKS
B B MSP_MV_FIELDS
B B MSP_NUMBER_FIELDS
B B MSP_OUTLINE_CODES
B B MSP_PROJ_SECURITY
B B MSP_PROJECTS
B B MSP_RESGLOBAL_SECURITY
B B MSP_RES_GLOBAL_BINARY
B B MSP_RES_SECURITY
B B MSP_RESOURCE_BASELINES
B B MSP_RESOURCE_RATES
B B MSP_RESOURCES
B B MSP_STRING_TYPES
B B MSP_TASK_BASELINES
B B MSP_TASKS
B B MSP_TEXT_FIELDS
B B MSP_TIMEPHASED_DATA

Overview

This document provides the information necessary to create and update project data directly in the Microsoft Office Project 2003 database while maintaining the consistency and integrity of the data.

NoteB B The information in this document does not always apply (or may be different) for enterprise project data stored in the Microsoft Office Project Server 2003 database. It is recommended that enterprise project data in the Project Server database only be interacted with by using the Project Data Service (PDS), Microsoft Office Project Professional B  2003, or Microsoft Office Project Web Access 2003.

The advantages of creating and maintaining project data directly in the database include:

  • Automating the creation of projects. This feature allows projects to be created automatically with other software programs without requiring the presence of Project.
  • Automating the recording of new tasks and actual data. One application of this feature is the automated application of timesheet data to a project.

Specific chapters include the following information:

  • Working with projects in a database, including creating and maintaining project databases, and backing up existing individual task, resource, and assignment rows when updating.
  • Working with the Project database structure, including how data is stored in the Project database, access requirements, and project reference requirements.
  • Adding and changing rows in the database, including setting flags to enable updates, specifying date and time values, specifying duration, work, and rate values, retrieving project strings, creating new projects, tasks, task links, resources, assignments, and calendars, plus managing and customizing project data.
  • Database processing order, field and table conventions and abbreviations, and table and column definitions.
  • Database table descriptions, including column names, data types, and descriptions.

NoteB B The script PROJTBLS.sql creates the database tables described in this document. It can be found on the Project Server 2003 CD-ROM in the \SUPPORT\DATABASE folder.

Top

What's new in the Project 2003 database

The Project database has been expanded (1 new table) and includes the following overall improvements:

  • A new table that supports multi-value outline codes.
  • New fields that support timesheet actuals and adjusted timesheet actuals.
  • New fields to support resource booking types.

The following new tables have been added to the Project database:

Top

About the Project Database

The following sections detail what databases are supported by the Project database and how to load and update legacy projects into the current version of Project.

Supported databases

Only Project Professional 2003 can connect to the Project Server database; both Project Professional 2003 and Project Standard 2003 support the following databases through Open Database Connectivity (ODBC):

  • Microsoft Access 2000, Access 2002, and Access 2003.
  • Microsoft SQL Server 2000 or higher.
  • Microsoft Data Engine 2000 or higher.
  • Oracle 8.0 (or higher).

Project can also make the ODBC connection automatically when writing to and reading from Microsoft Access 2000, Access 2002, and Access 2003 databases if you directly select the Project Database (.mpd) or Microsoft Access Database (.mdb) as the file type in the File Open and File Save dialog boxes.

NoteB B Saving or loading data is not supported with tables that are linked in Microsoft Access in such a way that the data exists in another application or database management system and Microsoft Access is just providing the connection. To access the data you must actually import the data into Microsoft Access or connect to the source directly.

Top

Loading older versions of Project into the database

You can open a project in Project 2003 that was previously saved to a database using either Microsoft Project 98, Microsoft Project 2000, or Microsoft Project 2002. However, if you save the project to the database again, it will be saved with the Microsoft Project 2003 database structure. For Microsoft Project 98 only, when the database format is updated, you will end up with two copies of the project, each in different database formats; original project data will not be affected.

Top

Upgrading the Project database from an older version

You can use the Database Upgrade Utility COM add-in to upgrade some or all of the projects in a Microsoft Access or SQL Server database from the Microsoft Project 98, Microsoft Project 2000, or Microsoft Project 2002 database structure to the Project 2003 database structure. To load the Database Upgrade Utility, right-click on the toolbar and select the Database Upgrade Utility. Th Database Upgrade Utility isn't required for Microsoft Project 2000 or Microsoft Project 2002 upgrades.

Top

Database Permissions and Configuration

Required permissions

When Project Standard or Project Professional (in offline mode) connect to an ODBC databaes, they perform several types of operations on a database, each requiring a corresponding set of object permissions:

  • To open a project read-only and view projects in a database, a user must have SELECT permission on all tables in the Project database. In addition, each user must have a view for each table if the user is not the table owner. In all cases, a data source name (DSN) for a user's user ID and database is required.
  • To modify existing projects in a database, save a new project to pre-existing tables in a database, or delete a project from a database, a user must have the following permissions on all tables: INSERT, UPDATE, and DELETE.
  • To save a new project to an empty database or to selectively export data to a database, a user must have CREATE TABLE permission.
  • In addition to a valid logon ID, SQL Server users must be given access to the project database.
  • The user's default database can be set in either the DSN or in SQL Server.

It is possible to have multiple projects for multiple organizations in the same database and still isolate projects from each other. This is called project isolation. To isolate projects, a separate table structure must be created for each project owner. Each database user can only see one set of project tables in a given database because Project uses the first set of tables it finds in the database. Any security scheme must take this into consideration.

  • A project manager who has projects with two sets of users needs two database user IDs, each owning a set of tables.
  • A project user who needs to view data in tables that he or she does not own must access the project tables using views or synonyms. A project user who needs to view data in tables belonging to two different table owners must have different user IDs and views for each set of tables.
  • Alternatively, a set of views or synonyms can be created that performs a union of the two owners' tables. To avoid confusion between different projects with the same project ID, project IDs across all sets of tables must be unique. This can be accomplished in views by incrementing the project IDs in each set of tables. The following example shows how to increment the project IDs in one owner's tables:
    Create view MSP_TEXT_FIELDS as
    Select    PROJ_ID,
              TEXT_CATEGORY,
              TEXT_REF_UID,
              TEXT_FIELD_ID,
              TEXT_VALUE
    From      user1.MSP_TEXT_FIELDS
    Union all
    Select    PROJ_ID + 1000,
              TEXT_CATEGORY,
              TEXT_REF_UID,
              TEXT_FIELD_ID,
              TEXT_VALUE
    From      user2.MSP_TEXT_FIELDS
    

Notes

  • A similar view must be created for each of the 29 tables in the Project database structure except for MSP_STRING_TYPES and MSP_CONVERSIONS. Projects accessed via views with unions cannot be updated.
  • Project does not grant permissions. The database or security administrator must grant the required permissions.
  • Granting column-level permissions should be avoided as either SELECT or SELECT, INSERT, UPDATE, and DELETE permissions are required on all tables in the Project database.
Top

Configuring the database

There are two ways to set up a database for use with Project:

  • The simplest way is to create a database and use Project to create the tables and indexes. This method is easiest when performance and disk usage are not issues.
  • The second method of setting up a database is to manually configure a database for use with Project. Script files are provided with Project that contain the data definition language (DDL) to create the Project database structures.

    These script files can be modified to specify database files and file groups (SQL Server) and to configure other database properties. For example, the database administrator can modify the DDL to save tables to one disk and indexes to another disk. As a general rule, performance improves when the indexes reside on a separate physical disk from the data tables, however, selecting separate disks for performance purposes is optional.

It is recommended that the transaction log files be put on a separate disk from the data whenever possible; before executing the scripts in SQL Server, you must do one of the following:

  • Open the file in a text editor and replace all occurrences of the <dbo.object_name> part of the string <dbo> in PROJTBLS.SQL with the name of the owner for the Project tables.
  • Log on to the database as the user who will own the tables.

NoteB B The SQL Server statements that populate the MSP_STRING_TYPES and MSP_CONVERSIONS tables are for the English conversion values. They will need to be modified for other languages.

If project data will be modified directly in the database, Project provides four stored procedures for backing up task, resource, assignment, and link data. See the section Ensuring data integrity below for more information.

Top

Performance tuning

The normal, standard procedures for SQL Server database performance and tuning apply to Project databases.

Top

Ensuring data integrity

Because Project writes to and reads from a certain database structure, some changes to a database may corrupt a project in a database and prevent Project from opening or saving part or all of the project. The following actions could corrupt a project stored in a database:

  • Changing the values of reserved data columns.
  • Renaming a column or table.
  • Deleting a column or table.
  • Changing the data type of a column.

Project verifies some data when reading a project from a database, or any other external format. The following cases may cause Project to display an alert, change data to an appropriate value, or not read the data at all:

  • A data type is incompatible.
  • A value is out of range.
  • A value creates an inconsistency in a project.
  • A value is part of an interdependent mathematical relationship.
  • A value is automatically calculated by Project.

NoteB B The original data is restored if any changed data contains errors that Project can't reconcile.

To prevent errors in changed task, resource, assignment, and link (task dependency) data from causing inconsistencies in the Project database, the original data should be backed up in the respective EXT_EDIT_REF_DATA columns of the MSP_TASKS, MSP_RESOURCES, MSP_ASSIGNMENTS, and MSP_LINKS tables. Project uses this data to help determine which columns have been modified in the database. These modifications are preserved if they do not cause one of the above listed conditions.

When adding new rows, EXT_EDIT_REF_DATA must be set to the value 1. When changing existing rows, the value 1, plus information in the columns listed in the following table, must be stored in the EXT_EDIT_REF_DATA column of each respective table. All values must be listed in the order below, delimited by commas, and, if a value is null, nothing should be entered between the commas that delimit that value.

Task Fields Resource Fields Assignment Fields Link Fields
Actual Work Accrue At Assignment Units Link Type
Work Available From Start Link Lag
Remaining Work Available To Finish
Duration Assignment Delay
Actual Duration Leveling Delay
Remaining Duration Actual Start
Start Actual Finish
Finish Actual Work
Constraint Type Remaining Work
Constraint Date Regular Work
Actual Start Work
Actual Finish Actual Overtime Work
Stop Remaining Overtime Work
Resume Overtime Work
Percent Complete
Cost
Fixed Cost
Actual Cost
Leveling Delay
Percent Work Complete
Task Fixed Cost Accrual

To make it easier to back up task, resource, assignment, and link data before updating, Project provides stored procedures (action queries for projects in Microsoft Access databases) to automate the process. Project creates the stored procedures in SQL Server when the database tables are created. EXECUTE permissions must be granted to non-owners who want to execute the stored procedures. If the tables are created manually, the scripts to create the stored procedures provided with Project must be run to create the stored procedures.

Placing triggers on Project database tables may cause data corruption or return unexpected results. It is important to note the following:

  • Any procedure fired by an INSERT or UPDATE trigger does not alter the @@identity property.
  • A procedure fired by an INSERT or UPDATE trigger must not perform an INSERT into a table with an Identity column or otherwise cause an Identity value to be generated by Microsoft SQL Server 2000.

If you use comma separators for data stored in the Project database, comma separators will be saved to the EXT_EDIT_REF_DATA field when using stored procedures to update this data. This can affect the integrity of Project data. To get around this, use quotation marks around all data that has a comma in it. The following script examples show how to modify existing stored procedures for resources, assignments, and tasks so the data conversion between decimal points and commas is handled correctly when backing up data to the EXT_EDIT_REF_DATA field in the Project database.

For assignments:

Update    MSP_ASSIGNMENTS
Set       EXT_EDIT_REF_DATA = '1,' &
          ASSN_UNITS &  ',' &
          ASSN_START_DATE & ',' &
          ASSN_FINISH_DATE & ',' &
          ASSN_DELAY & ',' &
          ASSN_LEVELING_DELAY & ',' &
          ASSN_ACT_START & ',' &
          ASSN_ACT_FINISH & ',' &
          ASSN_ACT_WORK & ',' &
          ASSN_REM_WORK & ',' &
          ASSN_REG_WORK & ',' &
          ASSN_WORK & ',' &
          ASSN_ACT_OVT_WORK & ',' &
          ASSN_REM_OVT_WORK & ',' &
          ASSN_OVT_WORK
Where     PROJ_ID=[PROJ ID] 
And       ASSN_UID=[ASSN UID];

For resources:

Update    MSP_RESOURCES
Set       EXT_EDIT_REF_DATA = '1,' &
          RES_ACCRUE_AT &  ',' &
          RES_AVAIL_FROM & ',' &
          RES_AVAIL_TO
Where     PROJ_ID=[PROJ ID] 
And       RES_UID=[RES UID];

For tasks:

Update    MSP_TASKS
Set       EXT_EDIT_REF_DATA = '1,' &
          TASK_ACT_WORK & ',' &
          TASK_WORK &  ',' &
          TASK_REM_WORK & ',' &
          TASK_DUR & ',' &
          TASK_ACT_DUR & ',' &
          TASK_REM_DUR & ',' &
          TASK_START_DATE & ',' &
          TASK_FINISH_DATE & ',' &
          TASK_CONSTRAINT_TYPE & ',' &
          TASK_CONSTRAINT_DATE & ',' &
          TASK_ACT_START & ',' &
          TASK_ACT_FINISH & ',' &
          TASK_STOP_DATE & ',' &
          TASK_RESUME_DATE & ',' &
          TASK_PCT_COMP & ',' &
          TASK_COST & ',' &
          TASK_FIXED_COST & ',' &
          TASK_ACT_COST & ',' &
          TASK_LEVELING_DELAY & ',' &
          TASK_PCT_WORK_COMP & ',' &
          TASK_FIXED_COST_ACCRUAL
Where     PROJ_ID=[PROJ ID] 
And       TASK_UID=[TASK UID];

Top

How Information is Stored in the Project Database

This section generally describes how different types of data are stored in the database.

Calendar data

Project saves calendar information to two tables:

  • MSP_CALENDARS contains the information that identifies the calendar and the resources for which it is a base calendar.
  • MSP_CALENDAR_DATA contains the working, non-working, and exception time data for the calendar to which it applies the day or days specified. Standard working and non-working times are defined by the calendar as it is constrained by the calendar and working time options.
Top

Timephased data

Storage of timephased data has been optimized so that a single row can contain values for up to seven time periods (typically days), thus reducing the number of rows that must be created. Each row contains the start and end dates and the timescale unit for the data values. The data values are the time, cost, or percentage of completion for each period within the specified timescale unit. For example, if the record type is cost and the units is weeks, then each data value contains $/week.

To save timephased data in a readable format in the MSP_TIMEPHASED_DATA table, do the following:

1 Click Tools | Options.
2 Select the Save tab.
3 Check the Expand timephased data in the database, which corresponds to the PROJ_OPT_EXPAND_TIMEPHASED column in the MSP_PROJECTS table.
Top

Notes

Storing notes has been simplified in Project. Notes are now stored in RTF columns in the MSP_ASSIGNMENTS, MSP_RESOURCES, MSP_TASKS and tables.

Top

Custom field values

Custom field values are stored in the following tables:

Each row in these tables refers to a specific task, resource, or assignment as well as the particular custom field (for example, TEXT1 in MSP_TEXT_FIELDS) and holds the value of that field for the specified task, resource, or assignment.

Custom WBS code mask definitions are stored in the MSP_ATTRIBUTE_STRINGS and MSP_FIELD_ATTRIBUTES tables. The work breakdown structure (WBS) code for each task is found in the MSP_TASKS table.

Custom outline codes are stored in the MSP_OUTLINE_CODES and MSP_CODE_FIELDS tables. As with the date, duration, flag, number, and text custom field tables, the MSP_CODE_FIELDS table refers to a specific task, resource, or assignment. The outline code for each referenced task, resource, or assignment is constructed from rows in the MSP_OUTLINE_CODES table. The definitions for the outline codes are stored in the MSP_ATTRTIBUTE_STRINGS and MSP_FIELD_ATTRIBUTES tables.

Mult-value outline codes are stored in the MSP_MV_FIELDS table.

Top

Split Tasks

The non-working times of split tasks are stored in the MSP_TIMEPHASED_DATA table as rows with zero hours of work.

Top

Task links

Task links, or dependencies, are stored in the MSP_LINKS table. Each row defines an association between a predecessor and successor task. Each row defines only one such association. Multiple links to a given task require a row for each link.

Top

Estimated duration values

Estimated duration is simply the duration value of each task formatted to be displayed as estimated. The value of the duration is displayed as estimated if the estimated flag is set to 1. To display custom duration fields as estimated, set the column DUR_FMT to the appropriate value (see Using the text conversion tables to retrieve strings below).

Top

Baselines

Baseline data is stored in three tables: MSP_ASSIGNMENT_BASELINES, MSP_RESOURCE_BASELINES, and MSP_TASK_BASELINES.

Top

Working with Projects in the Project Database

You should never do any of the following when working with the Project database:

  • Drop any tables created by Project.
  • Drop any table columns created by Project.
  • Change the data type of any column.
  • Alter the column RESERVED_BINARY_DATA in the MSP_PROJECTS table in any way.

You may notice that some of the tables in Project contain records with large negative values in their respective UID columns. These records usually appear at the top of the MSP_ASSIGNMENTS, MSP_CALENDARS, MSP_PROJECTS, MSP_RESOURCES, and MSP_TASKS tables; the values are -65536, -65535, -65534 and -65533. These records are used internally by Project and should never be edited or deleted.

If you create your own tables (see the next section Creating the Project database structure), you should never define any of the columns as required or NOT NULL.

NoteB B In the following sections, code samples are provided with values in the code that may be different from the values in your database. Be sure to check all values in the code samples against the actual values in your database.

Top

Creating the Project database structure

If you need to create a Project database from scratch, the easiest method is to save an empty project using Project. After the database is created, you can delete the project from the database from within Project (see Deleting a project from a database below).

If you do not want to use Project to facilitate the process, you will need to create an empty database through the ODBC driver, and then create all of the appropriate tables. Creating all of the tables manually is an extensive undertaking because you would need to use this document as a reference to ensure you have exactly the right table and column names for every table and the correct data types for each column.

To manually create and configure a database for use with Project, see the section Configuring the database below.

Top

DSN requirements and limitations

Connection information to the Project Server database is automatically communicated by Project Server to Project Professional during user authentication. A Data Source Name (DSN) is not required to connect Project Professional to Project Server.

If Project Standard users (and Project Professional users in offline mode) need to access projects in the same database using ODBC, each user making changes to the data does not need to use the same DSN for connecting to the database. Project stores both the combined DSN and project name (and alternatively the connection string) as the identifiers to locate a project using the following format: <DSN>\ProjectName. However, projects that are linked together, using cross-project links for example, will need to use the same DSN.

DSN limitations when using Project Standard or Project Professional users in offline mode:

  • You must use the same DSN for all project links, such as resource pools, sharer files, cross-project links, and inserted projects
  • If multiple users modify the database, those users should not save files by selecting the .mpd or .mdb file type from the Save as type list in the Save As dialog box. They should use the Save as ODBC button from the File Save dialog box.
  • Project caches logon passwords and other connection information. The same DSN cannot be used by more than one user ID on a single machine for different, simultaneous logons to the database. Once a connection is established, that connection will be reused even if a different logon name and/or password is entered at logon time. To logon with a different user ID you must close all projects opened with the DSN. If there are simultaneous connections, you must create additional alternate DSNs for use with each user ID.
  • You should never connect to the Project Server database with a DSN. This can cause conflicts with the database and can cause data to be placed in unintended locations. The only client application that should be allowed to connect to the Project Server database is Project Professional in online mode.
Top

Concurrent usage and project locking

If you open a project in a database through Project Standard and that project is not in use by another user, you will be given full read/write access to the project. Until you finish your session with the project, another user will only be able to open that project in Project as read-only.

Read/write access permission and some other properties used in managing concurrent access are all maintained in five columns in the MSP_PROJECTS table in the database. Use of these access concurrency columns are in effect only when users are using Project to read or update the database. Project does not provide any kind of locking when a database is being read or updated directly by a user using a database query program or other tool. Thus, any program or tool which reads or updates project data in the database should follow the same conventions as Project to ensure consistent data access. Use of the access concurrency columns is described below.

While you have the project open with read/write access through Project, then Project stores the value 1 in the PROJ_READ_WRITE and PROJ_READ_ONLY columns. When you finish your session and close the database, the field values will be reset to 0. While Project is loading from or writing to a database, it will also set the values of the PROJ_LOCKED and PROJ_READ_COUNT columns to 1. While these fields have a value of 1, the project may not be opened by any user using Project, not even read-only.

Project stores the name of the machine that currently has the project open for read/write access in the PROJ_MACHINE_ID column. While PROJ_READ_WRITE is set to 1 in order to update project data in the database, it is recommended that the column PROJ_MACHINE_ID be set to your computer's machine name so that any user attempting to open the project through Project will get an appropriate message informing them that the project is currently opened for read/write access by you. Otherwise, Project can't identify the user who has the project open for read/write access. When you are ready to allow read/write access to the project again, you should set the PROJ_MACHINE_ID field back to a NULL when you reset the flags to 0.

Before you make updates to the database directly, you should first check that all of these flags are set to 0 and then temporarily set them to 1 to prevent other users from opening the project through Project.

Copy, modify and execute the following SQL update statement to lock a project for update:

Update    MSP_PROJECTS
Set       PROJ_READ_ONLY = '1',
          PROJ_READ_WRITE = '1',
          PROJ_READ_COUNT = '1',
          PROJ_LOCKED = '1',
          PROJ_MACHINE_ID = 'your computer or application name'
Where     PROJ_ID = 1

Copy, modify and execute the following SQL update statement to unlock the project after the update has been completed:

Update    MSP_PROJECTS
Set       PROJ_READ_ONLY = '0',
          PROJ_READ_WRITE = '0',
          PROJ_READ_COUNT = '0',
          PROJ_LOCKED = '0',
          PROJ_MACHINE_ID = null
Where     PROJ_ID = 1

NoteB B If a user has read/write access to a project in a database through Project, and another user changes data in that project directly in the database, that change will not be reflected in Project for the first user. In addition, if the first user saves project data back to the database, that data will overwrite any changes made directly in the database by the second user.

Top

Adding and Changing Rows in the Database

Setting the flags required to enable updating project data in the database

In all cases where project data in a database is edited outside Project, the column PROJ_EXT_EDITED in the MSP_PROJECTS table must be set to 1.

When editing existing rows in the MSP_TASKS, MSP_RESOURCES, MSP_ASSIGNMENTS, and MSP_LINKS tables, the column EXT_EDIT_REF_DATA must be set to 1 followed by the values of the columns listed in the tables below (all values delimited by commas). When creating new rows, however, the column EXT_EDIT_REF_DATA should be set to 1.

NoteB B It is recommended that the stored procedures included with Project be used to update this column with the required values. See Ensuring data integrity for more information.

MSP_TASKS MSP_RESOURCES MSP_ASSIGNMENTS MSP_LINKS
TASK_ACT_WORK RES_ACCRUE_AT ASSN_UNITS LINK_TYPE
TASK_WORK RES_AVAIL_FROM * ASSN_START_DATE LINK_LAG
TASK_REM_WORK RES_AVAIL_TO * ASSN_FINISH_DATE
TASK_DUR ASSN_DELAY
TASK_ACT_DUR ASSN_LEVELING_DELAY
TASK_REM_DUR ASSN_ACT_START
TASK_START_DATE ASSN_ACT_FINISH
TASK_FINISH_DATE ASSN_ACT_WORK
TASK_CONSTRAINT_TYPE ASSN_REM_WORK
TASK_CONSTRAINT_DATE ASSN_REG_WORK
TASK_ACT_START ASSN_WORK
TASK_ACT_FINISH ASSN_ACT_OVT_WORK
TASK_STOP_DATE ASSN_REM_OVT_WORK
TASK_RESUME_DATE ASSN_OVT_WORK
TASK_PCT_COMP
TASK_COST
TASK_FIXED_COST
TASK_ACT_COST
TASK_LEVELING_DELAY
TASK_PCT_WORK_COMP
TASK_FIXED_COST_ACCRUAL

NoteB B While the RES_AVAIL_FROM and RES_AVAIL_TO columns are backed up, they should not be updated. These fields are derived for a specific resource from the earliest date value in AVAIL_FROM and the latest date value in AVAIL_TO for that resource in the MSP_AVAILABILITY table. Changes to resource availability should be made in the MSP_AVAILABILITY table.

When updating custom field data, the following flags must be set in the MSP_PROJECTS table:

Flag in MSP_PROJECTS Table flag refers to
PROJ_EXT_EDITED_DATE MSP_DATE_FIELDS
PROJ_EXT_EDITED_DUR MSP_DURATION_FIELDS
PROJ_EXT_EDITED_FLAG MSP_FLAG_FIELDS
PROJ_EXT_EDITED_NUM MSP_NUMBER_FIELDS
PROJ_EXT_EDITED_CODE MSP_CODE_FIELDS
PROJ_EXT_EDITED_TEXT MSP_TEXT_FIELDS

When updating the MSP_FIELD_ATTRIBUTES and MSP_ATTRIBUTE_STRINGS tables, the column ATTRIB_EXT_EDITED in the MSP_FIELD_ATTRIBUTES table must be set to 1 in each modified or new row. When updating the MSP_TIMEPHASED_DATA table, the column TD_EXT_EDITED must be set to 1.

Top

Specifying times with dates

When you enter a date value directly in the database, you should always include the time with the date. Since database date time columns use a default time when you don't specify the time, relying on the default can lead to unexpected results. The database columns usually default to 12:00 midnight, which is normally nonworking time in most calendars. When Project encounters 12:00 midnight, it rounds the time to the next closest working time for start times and to the last working time for finish times.

Thus, if you specify dates without times, it can result in the following situation: You create what you think is a five-day task in the database by specifying the task start as Monday's date and the task finish as Friday's date. The Monday time in the database is really Monday at 12:00 midnight, so Project treats this as Monday, 8:00 A.M. (per the calendar default start time). For Friday's time, though, the default is Friday at 12:00 midnight, which is rolled back to Thursday at 5:00 P.M. (the calendar default finish time). Thus, your five-day task becomes only four days when it is read in by Project. By explicitly specifying the time in each date/time column, you can always avoid this problem.

Top

Duration, work, rate, and cost values

Project saves all duration, work, cost and rate fields to two separate columns in the database. The first column is the duration value, work value, or rate value, and the second field is the corresponding format (FMT) column (for example, TASK_DUR_FMT). If you change the value in a FMT column in the database, it will not affect the value of the corresponding duration, work, rate, or cost column, which Project saves as absolute values. The FMT column simply indicates which unit label Project will use to display the value.

The format for the task duration for a particular task can be determined by executing the following query (substituting the appropriate values for TASK_UID and PROJ_ID):

Select    TASK_UID, CONV_STRING, TASK_NAME 
From      MSP_TASKS t, MSP_CONVERSIONS c, MSP_STRING_TYPES s
Where     t.TASK_DUR_FMT = c.CONV_VALUE and
          c.STRING_TYPE_ID = s.STRING_TYPE_ID and
          s.STRING_TYPE = 'Display Units' and
          t.TASK_UID = 4 and
          t.PROJ_ID = 1

Because duration, work, rate, and cost values can be displayed using different units, Project saves each using a standard multiple:

  • Duration values are saved as minutes * 10. Eight hours would be saved as 4800 (that is, 8*60*10).
  • Work values are saved as minutes * 1000. Eight hours would be saved as 480000 (that is, 8*60*1000).
  • Rate values are saved as units per hour. For example, fifteen dollars an hour would be saved as 15.
  • Cost fields are saved as units * 100. For example, seventy dollars and twenty-five cents would be saved as 7025.

NoteB B All formats are valid for timephased data units except y, or year. Year is only valid for Cost Rate.

Top

Using the text conversion tables to retrieve strings

To enable different language versions of Project to read a project in a database, the contents of some columns are converted to numeric constants. Project writes two tables to the database containing the conversion information for those columns: MSP_STRING_TYPES contains the mapping between the enumerated field categories and the field name and MSP_CONVERSIONS contains the mapping between the numeric constants and the possible text values for each field. The following field categories are converted to numeric constants:

Field Category Fields in this Category From Table
Weekday Weekday MSP_CALENDARS
Schedule From ScheduleFromProjectFinish MSP_PROJECTS
ScheduleFromProjectStart MSP_PROJECTS
Accrual AccrueAt MSP_PROJECTS
FixedCostAccrual MSP_TASKS
DefaultFixedCostAccrual MSP_LINKS
Link Type LinkType MSP_PROJECTS
Display Units LinkLagDisplayUnits MSP_LINKS
DelayDisplayUnits MSP_ASSIGNMENTS
DurationDisplayUnits MSP_TASKS
BaselineDurationDisplayUnits MSP_TASKS
DelayDisplayUnits MSP_TASKS
DurationDisplayUnits MSP_DURATION_FIELDS
Cost Rate Units StandardRateDisplayUnits MSP_RESOURCES
OvertimeRateDisplayUnits MSP_RESOURCES
StandardRateDisplayUnits MSP_RESOURCE_RATES
OvertimeRateDisplayUnits MSP_RESOURCE_RATES
Work Contour Type WorkContour MSP_ASSIGNMENTS
Constraint Type ConstraintType MSP_TASKS
Task Type Type MSP_TASKS
DefaultTaskType MSP_PROJECTS
Calendar Working Working MSP_CALENDAR_DATA
Category Type CategoryType MSP_DATE_FIELDS
CategoryType MSP_DURATION_FIELDS
CategoryType MSP_NUMBER_FIELDS
CategoryType MSP_TEXT_FIELDS
Field ID FieldID MSP_DATE_FIELDS
FieldID MSP_DURATION_FIELDS
FieldID MSP_NUMBER_FIELDS
FieldID MSP_TEXT_FIELDS
FieldID MSP_FIELD_ATTRIBUTES
FieldID MSP_FIELD_CODES
Workgroup Messages Workgroup MSP_RESOURCES
Currency Symbol Position CurrencyPosition MSP_PROJECTS
Field Attributes OutlineCodeName MSP_OUTLINE_CODES
WBSMask MSP_FIELD_ATTRIBUTES
Timephased Contour Type RemainingWork MSP_ASSIGNMENTS
ActualWork MSP_ASSIGNMENTS
ActualOvertimeWork MSP_ASSIGNMENTS
BaselineWork MSP_ASSIGNMENTS
BaselineCost MSP_ASSIGNMENTS
ActualCost MSP_ASSIGNMENTS
BaselineWork MSP_RESOURCES
BaselineCost MSP_RESOURCES
BaselineWork MSP_TASKS
BaselineCost MSP_TASKS
PercentComplete MSP_TASKS
Splits MSP_TIMEPHASED_DATA

You can store projects from different language versions of Project into the same database. Project adds values to both MSP_STRING_TYPES and MSP_CONVERSIONS in the appropriate language the first time a project in a new language is saved to the database.

NoteB B When saving to database, the code pages of the Project computer and the database server must be the same.

If you selectively save partial project data to a database, Project creates the conversion strings in the MSP_STRING_TYPES and MSP_CONVERSIONS tables, if they do not already exist.

Project uses English for the names of the columns and tables in the database, in each language version. If you change the name of a table in a database you will probably corrupt the database, and Project will not be able to read that table and perhaps the entire project.

Top

Creating Project Schedule Data

NoteB B The information in this section does not always apply (or may be different) for enterprise project data stored in the Project Server database. It is recommended that enterprise project data in the Project Server database only be interacted with by using the PDS, Project Professional, or Project Web Access.

Creating a new, non-enterprise project

To create a new project in the database, you must add a new row to the MSP_PROJECTS table and create a project summary task in the MSP_TASKS table. See Creating a new task, Creating a new resource, and Creating a new assignment below. Then, to add the associated tasks, resources, and assignments to the project, you must create the task, resource, and assignment rows as described below. You must enter values for at least the columns specified below.

Table Fields Notes
MSP_PROJECTS PROJ_ID Refers to a valid ID in the MSP_PROJECTS table.
PROJ_NAME The name of the project, as displayed in the project window title bar.
PROJ_INFO_START_DATE The date and time that a project is scheduled to begin.
PROJ_EXT_EDITED Must be set to 1 to indicate that this project is a new project.
MSP_TASKS PROJ_ID Refers to a valid ID in the MSP_PROJECTS table.
TASK_UID The unique ID for the task.
TASK_ID The position identifier of the task in the list of tasks.

Notes

  • For a new project created in the database, all of the Boolean Project option settings will default to 0, not to the normal Project default values. To ensure Project behaves in the desired way once the project is opened, all of the option values in the MSP_PROJECTS table should be set to the desired settings.
  • To create a project that is scheduled from a project finish date, set PROJ_INFO_FINISH_DATE to the project finish date (instead of PROJ_INFO_START_DATE to the start date) and set PROJ_INFO_SCHED_FROM to 0 in the MSP_PROJECTS table.
Top

Creating an inserted project

The process for creating an inserted project in the database is a combination of creating a new task and specifying the value of a custom text field. You must add new records to the MSP_TASKS and MSP_TEXT_FIELDS tables with values for at least the following fields, as well as setting the externally edited flag in the MSP_PROJECTS table:

Table Fields Notes
MSP_TASKS PROJ_ID Refers to a valid ID in the MSP_PROJECTS table.
TASK_UID Contains the ID for the task.
TASK_ID The position identifier of the task in the list of tasks.
EXT_EDIT_REF_DATA Must be set to 1 to indicate that this task is a new task.
MSP_TEXT_FIELDS PROJ_ID Refers to a valid ID in the MSP_PROJECTS table.
TEXT_CATEGORY Set to 0 to indicate a Task.
TEXT_REF_UID Refers to a valid ID in the MSP_TASKS table.
TEXT_FIELD_ID Refers to a valid CONV_VALUE in the MSP_CONVERSIONS table.
TEXT_VALUE The name of the project to be inserted. For example:
<c:\test.mdb>\my project
MSP_PROJECTS PROJ_EXT_EDITED Must be set to 1 for Project to process.
PROJ_EXT_EDITED_TEXT Must be set to 1 for Project to process.

NoteB B When deleting an inserted project from the database, all of the rows for the inserted project from the tables specified above must be deleted to avoid leaving orphaned data in the database, which could cause unpredictable results when the database is opened in Project.

For example, let's say you have two projects stored in a database, and you want to make one of them an inserted project of the other. Assume the two projects are in an .mpd file that you access with a DSN called "Projects" and the project names are Master Project and Subproject. First you need to create a task in Master Project to hold the inserted project. This record in the MSP_TASKS table would appear as follows:

Fields Values
PROJ_ID 1
TASK_UID 5
TASK_ID 5
TASK_NAME My subproject
EXT_EDIT_REF_DATA 1

The TASK_ID and TASK_UID used here are chosen for demonstration purposes and have no significance. The important issue to remember is that they must be unique among all the IDs for the current project. After you have inserted this task, you need to specify the name of the inserted project file. For example, the following is the row to add to the MSP_TEXT_FIELDS table:

Fields Values
PROJ_ID 1
TEXT_CATEGORY 0
TEXT_REF_UID 5
TEXT_FIELD_ID 188743706
TEXT_VALUE 4800

The value of PROJ_ID must refer to a valid ID in the MSP_PROJECTS table. In this case, the TEXT_CATEGORY is 0 because we are setting a task text field. The value of TEXT_REF_UID is taken directly from the TASK_UID column in the MSP_TASKS table. The value of TEXT_FIELD_ID is taken from the CONV_VALUE column in the MSP_CONVERSIONS table from the record where the CONV_STRING equals Task Subproject File. The TEXT_VALUE field gets the actual project name.

In order for Project to read this row, you must set the PROJ_EXT_EDITED_TEXT flag in the MSP_PROJECTS table to 1.

Top

Creating a new, non-enterprise resource

To create a new resource in the database, you must add a new row to the MSP_RESOURCES table and enter values for at least the following columns:

Table Fields Notes
MSP_RESOURCES PROJ_ID Refers to a valid ID in the MSP_PROJECTS table.
RES_UID The unique ID for the resource.
RES_ID The position identifier of the resource in the list of resources.
RES_NAME The name of the resource.
RES_TYPE The resource type, either Work (0) or Material (1).
EXT_EDIT_REF_DATA Must be set to 1 for Project to process.
MSP_PROJECTS PROJ_EXT_EDITED Must be set to 1 for Project to process.

The following SQL insert statement can be modified and used to insert a new work resource.

Insert into MSP_RESOURCES (
              PROJ_ID,
              RES_UID,

              RES_ID,
              RES_NAME,
              RES_TYPE,
              EXT_EDIT_REF_DATA )
          values (
              3,
              4,
              4,
              'John Smith',
              0,
              '1' )
Top

Creating a new, non-enterprise calendar

Calendars can be created for a project or a non-enterprise resource. Optionally, base calendars can be applied to tasks. The base calendar for the project implicitly defines the working hours for other base and resource calendars. Calendar data is stored in the MSP_CALENDARS table. Calendar working times and exceptions are stored in the MSP_CALENDAR_DATA table. (See Specifying calendar working time and exceptions for more information.) To create a new calendar, you must create a row with values for at least the following columns:

Table Fields Notes
MSP_CALENDAR_DATA PROJ_ID Refers to a valid ID in the MSP_PROJECTS table.
CAL_ UID Refers to a valid ID in the MSP_CALENDARS table.
CAL_BASE_UID Refers a calendar to its parent base calendar.
CAL_IS_BASE_CAL Indicates whether this calendar is a base calendar; a calendar has a value of -1 if it is a base calendar.
CAL_NAME The name of the calendar.
MSP_PROJECTS PROJ_EXT_EDITED Must be set to 1 for Project to process.

To create a new base calendar, copy, modify, and execute the following SQL insert statement:

Insert into MSP_CALENDARS (
              PROJ_ID,

              CAL_UID,
              CAL_BASE_UID,
              CAL_IS_BASE_CAL,
              CAL_NAME )
          values (
              3,
              4,
              -1,
              1,
              'new base calendar' )

To create a new resource calendar, copy, modify, and execute the following SQL insert statement:

Insert into MSP_CALENDARS (
              PROJ_ID,
              CAL_UID,
              CAL_BASE_UID,
              RES_UID,
              CAL_IS_BASE_CAL )
          values (
              3,
              5,
              2,
              4,
              0 )

Notes

  • Removing a calendar assigned to a task may cause actual work applied to the task to be moved.
  • It is recommended that CAL_UID values not be modified. Modifying the value of CAL_UID may result in data inconsistencies.
  • Specifying calendar names longer than 51 characters may result in data inconsistencies.
Top

Specifying calendar working time and exceptions

Both standard working times and exceptions for a calendar are stored in the MSP_CALENDAR_DATA table. One row must be inserted for each working day and nonworking day. In addition, one row must be inserted for each exception period. To specify a calendar working day, you must insert a row with values for at least the following columns:

NoteB B You cannot specify calendar working times and exceptions for enterprise resources using this example.

Table Fields Notes
MSP_CALENDAR_DATA PROJ_ID Refers to a valid ID in the MSP_PROJECTS table.
CD_UID The unique ID for calendar working, nonworking, and exception data.
CAL_ UID Refers to a valid ID in the MSP_CALENDARS table.
CD_DAY_OR_EXCEPTION Indicates whether the calendar has a defined working day (1-7 defines the days as Sunday - Saturday respectively) or whether it is an Exception (0).
CD_WORKING Indicates whether the selected days are Working (1) or Nonworking days (0).
CD_FROM_TIME1 Contains the starting day and time of the working day.
CD_TO_TIME1 Contains the ending day and time of the working day.
MSP_PROJECTS PROJ_EXT_EDITED Must be set to 1 for Project to process.

NoteB B If a calendar is an Exception (as determined by CD_DAY_OR_EXCEPTION) then CD_FROM_DATE and CD_TO_DATE are used instead of CD_FROM_TIME and CD_TO_TIME.

To specify working and exception days for a calendar, copy, modify and execute the following insert statement for each working day:

Insert into MSP_CALENDAR_DATA (
              PROJ_ID,
              CD_UID,
              CAL_UID,
              CD_DAY_OR_EXCEPTION,
              CD_WORKING,
              CD_FROM_TIME1,
              CD_TO_TIME1,
              CD_FROM_TIME2,
              CD_TO_TIME2 )
          values (
              3,
              1,
              3,
              2, -- Monday
              1, -- Working
              '7/15/2003 8:00:00 AM',
              '7/15/2003 12:00:00 PM',
              '7/15/2003 1:00:00 PM',
              '7/15/2003 5:00:00 PM' )

NoteB B No value should be specified for CD_FROM_TIMEn or CD_TO_TIMEn if CD_WORKING is set to 0.

Top

Specifying resource availability

To specify resource availability, you must add a row to the MSP_AVAILABILITY table for each available period and enter values for the following columns:

NoteB B You cannot specify resource availability for enterprise resources using this example.

Table Fields Notes
MSP_AVAILABILITY PROJ_ID Refers to a valid ID in the MSP_PROJECTS table.
AVAIL_UID The unique ID for a resource's availability.
RES_UID Refers to a valid ID in the MSP_RESOURCES table.
AVAIL_FROM The date the resource becomes available for the defined period; the time component of the date is ignored.
AVAIL_TO The date the resource is no longer available for the defined period.
AVAIL_UNITS Shows a decimal fraction that represents the percent of the available period that the resource is available to work during the defined period.
MSP_PROJECTS PROJ_EXT_EDITED Must be set to 1 for Project to process.

To specify a period of availability from July 15, 2003, to July 20, 2003, at 50% for a resource, copy, modify, and execute the following insert statement:

Insert into MSP_AVAILABILITY (
              PROJ_ID,
              AVAIL_UID,
              RES_UID,
              AVAIL_FROM,
              AVAIL_TO,
              AVAIL_UNITS )
          values (
              3,
              1,
              5,
              '7/15/2003',
              '7/21/2003',
              0.5 )
Top

Specifying resource rates

To specify a single rate for a resource, you must add a row to the MSP_RESOURCE_RATES table and enter values for at least the following columns:

NoteB B You cannot specify resource rates for enterprise resources using this example.

Table Fields Notes
MSP_RESOURCE_RATES PROJ_ID Refers to a valid ID in the MSP_PROJECTS table.
RR_UID The unique ID for the resource rate.
RES_UID Refers to a valid ID in the MSP_RESOURCES table.
RR_RATE_TABLE Indicates which cost rate table to use for a resource on an assignment; 0-4 identifies cost rate tables A-E respectively.
RR_STD_RATE or RR_OVT_RATE or RR_PER_USE_COST Must set a value in one of these three columns. Rates are shown in dollars per hour and per-use-costs are shown as dollars * 100. If either a standard or overtime rate is specified, you have the option to specify its format in either RR_STD_RATE_FMT or RR_OVT_RATE_FMT.
MSP_PROJECTS PROJ_EXT_EDITED Must be set to 1 for Project to process.

To specify a rate with an effective (from) date, two rows must be inserted into the MSP_RESOURCE_RATES table: one row for the first rate that does not have an effective date, and one row for the rate that does. To specify standard and overtime hourly cost rates for a resource for a date range in cost rate table B, copy, modify, and execute the following insert statements:

-- insert the first rate (from date not required)
Insert into MSP_RESOURCE_RATES (
              PROJ_ID,
              RR_UID,
              RES_UID,
              RR_RATE_TABLE,
              RR_TO_DATE,
              RR_STD_RATE,
              RR_STD_RATE_FMT,
              RR_OVT_RATE,
              RR_OVT_RATE_FMT )
          values (
              3,
              1,
              5, 
              1, -- table B
              '2003-03-15', -- rate end date
              24.00, -- $24/h standard rate
              2, -- standard rate format = h = hours
              36.00, -- $36/h overtime rate
              2 ) -- overtime rate format = h = hours
-- insert the second rate
Insert into MSP_RESOURCE_RATES (
              PROJ_ID,
              RR_UID,
              RES_UID,
              RR_RATE_TABLE,
              RR_FROM_DATE,
              RR_TO_DATE,
              RR_STD_RATE,
              RR_STD_RATE_FMT,
              RR_OVT_RATE,
              RR_OVT_RATE_FMT )
          values (
              3,
              2,
              5, 
              1, -- table B
              '2003-03-15', -- rate start date
              '2003-04-15', -- rate end date
              24.00, -- $24/h standard rate
              2, -- standard rate format = h = hours
              36.00, -- $36/h overtime rate
              2 ) -- overtime rate format = h = hours

To specify a per-use cost for resource for a resource in table C, copy, modify, and execute the following insert statement:

Insert into MSP_RESOURCE_RATES (
              PROJ_ID,
              RR_UID,
              RES_UID,
              RR_RATE_TABLE,
              RR_PER_USE_COST )
          values (
              3,
              3,
              5, 
              2, -- table C
              5000 ) --'- $50.00 per use cost

To delete all of the resource rates in a given rate table (A, B, C, etc.) for a resource, you must delete all existing records for that table for that resource and create one entry with zero costs.

Top

Creating a new task

To create a new task in the database, you must add a new row to the MSP_TASKS table and enter values for at least the following columns:

Table Fields Notes
MSP_TASKS PROJ_ID Refers to a valid ID in the MSP_PROJECTS table.
TASK_UID The unique ID for the task.
TASK_ID The position identifier of the task in the list of tasks.
TASK_NAME The name of the task; not required for sub-tasks.
EXT_EDIT_REF_DATA Must be set to 1 to indicate that this task is a new task.
MSP_PROJECTS PROJ_EXT_EDITED Must be set to 1 for Project to process.

For example, assume your current project has 22 tasks with TASK_UIDs 1 to 22 and TASK_IDs 1 to 22. If you want to create a new one-day task named Research Competitors and you want this task to be the eighth task in the project, you would add the following row to the MSP_TASKS table:

Fields Values
PROJ_ID 3
TASK_UID 23
TASK_ID 8
TASK_NAME Research Competitors
TASK_DUR 4800
EXT_EDIT_REF_DATA 1

Obtain the correct PROJ_ID from the MSP_PROJECTS table. For the TASK_UID, use 23 because it is the next available number. Set TASK_ID to 8, to make this task eighth in the list. This will require that the TASK_ID of each subsequent record be adjusted by one to make room (since a TASK_ID must be unique). The following SQL update statement can be executed to renumber TASK_IDs:

Update    MSP_TASKS 
Set       TASK_ID = TASK_ID + 1 
Where     TASK_ID between 8 and 22 and
          PROJ_ID = 3

Remember to change the value of PROJ_ID to the project whose tasks you are renumbering. Once the existing tasks have been renumbered, the following SQL insert statement can be executed to create the new task:

Insert into MSP_TASKS (
              PROJ_ID,
              TASK_UID,
              TASK_ID,
              TASK_NAME,

              TASK_DUR,
              TASK_OUTLINE_LEVEL,
              EXT_EDIT_REF_DATA )
          values (
              3,
              23,
              8,
              'my task',
              4800,
              1,
              '1' )

Notes

  • When importing task data from a file, table or spreadsheet, the data is processed in the order specified in the section Processing order of externally edited data below.
  • If task IDs for new tasks are specified with gaps (for example, 10, 20, and so on), Project will re-assign task IDs sequentially. There is no way to create gaps in the task IDs in the database.
  • All new tasks that are created in the database that do not have an outline level specified will be inserted at level 0. If the task is inserted between two existing tasks that have a lower outline level, the second task (and any downstream subtasks) will become a child of the inserted task, and it may change the outline level of those subsequent tasks.
  • To specify a duration as estimated, the column TASK_DUR_FMT must be set to an estimated display value. See Duration, work, rate and cost values above for information on setting duration display values.
Top

Creating a recurring task

While it is possible to create a recurring task in the database, it is preferable to create recurring tasks inside Project because the Recurring Task Information dialog box in Project will not reflect the actual recurring task settings for a recurring task created directly in the database. Recurring tasks created in the database will always default to a weekly recurring one-day task that occurs on Mondays starting after the current date. To create a recurring task, you must add a row for the summary task and a row for each instance of a recurring subtask and enter values for at least the following columns:

Table Fields Notes
MSP_TASKS PROJ_ID Refers to a valid ID in the MSP_PROJECTS table.
TASK_UID The unique ID for the task.
TASK_ID The position identifier of the task in the list of tasks.
TASK_NAME The name of the task; not required for sub-tasks.
TASK_OUTLINE_LEVEL Indicates the outline level of the task; the level for a recurring sub-task must be one level lower than a recurring summary task.
TASK_IS_RECURRING Set to 1 to indicate that a task is part of a series of recurring tasks.
TASK_IS_RECURRING_SUMMARY Set to 1 to indicate that a task is a recurring summary task.
TASK_IS_ROLLED_UP Set to 1 to indicate that a summary taskbar displays a rolled-up bar or to indicate that information on the subtask bar is to be rolled up to the summary task bar.
TASK_CONSTRAINT_TYPE The type of constraint applied when scheduling a task; required for sub-tasks.
EXT_EDIT_REF_DATA Must be set to 1 to indicate that this task is a new task.
MSP_PROJECTS PROJ_EXT_EDITED Must be set to 1 for Project to process.

To create a recurring task with two recurring subtasks, copy, modify, and execute the following three insert statements:

-- insert the summary recurring task
Insert into MSP_TASKS (
              PROJ_ID,
              TASK_UID,
              TASK_ID,
              TASK_NAME,
              TASK_OUTLINE_LEVEL,
              TASK_IS_RECURRING,
              TASK_IS_RECURRING_SUMMARY,
              TASK_IS_ROLLED_UP,
              EXT_EDIT_REF_DATA )
          values (
              3,
              9,
              9,
              'my recurring summary task',
              2, -- outline level
              1, -- is recurring
              1, -- is recurring summary
              1, -- is rolled up
              '1' ) 
-- insert the first recurring subtask
Insert into MSP_TASKS (
              PROJ_ID,
              TASK_UID,
              TASK_ID,
              TASK_OUTLINE_LEVEL,
              TASK_IS_RECURRING,
              TASK_IS_RECURRING_SUMMARY,
              TASK_IS_ROLLED_UP,
              TASK_CONSTRAINT_TYPE,
              EXT_EDIT_REF_DATA )
          values (
              3,
              10,
              10,
              3, -- outline level
              1, -- is recurring
              0, -- is recurring summary
              1, -- is rolled up
              4, -- constraint type = 4 = 'Start no earlier than'
              '1' )
-- insert the second recurring subtask
Insert into MSP_TASKS (
              PROJ_ID,
              TASK_UID,
              TASK_ID,
              TASK_OUTLINE_LEVEL,
              TASK_IS_RECURRING,
              TASK_IS_RECURRING_SUMMARY,
              TASK_IS_ROLLED_UP,
              TASK_CONSTRAINT_TYPE,
              EXT_EDIT_REF_DATA )
          values (
              3,
              11,
              11,
              3, -- outline level
              1, -- is recurring
              0, -- is recurring summary
              1, -- is rolled up
              4, -- constraint type = 4 = 'Start no earlier than'
              '1' )
Top

Creating task dependencies

To create a new task link dependency in the database, you must add a new row to the MSP_LINKS table and enter values for at least the following columns (which will give you a simple Finish-to-Start link with zero lag):

Table Fields Notes
MSP_LINKS PROJ_ID Refers to a valid ID in the MSP_PROJECTS table.
LINK_UID The unique ID for the link.
LINK_PRED_UID The unique ID for the predecessor task; refers to a valid ID in the MSP_TASKS table.
LINK_SUCC_UID The unique ID for the successor task; refers to a valid ID in the MSP_TASKS table.
EXT_EDIT_REF_DATA Must be set to 1 to indicate that this link is a new link.
MSP_PROJECTS PROJ_EXT_EDITED Must be set to 1 for Project to process.
Insert into MSP_LINKS (
              PROJ_ID,
              LINK_UID,
              LINK_PRED_UID,
              LINK_SUCC_UID,
              EXT_EDIT_REF_DATA )
          values (
              3,
              1,
              1,
              2,
              0 ) 

To specify a link type other than the default, set the column LINK_TYPE to the type of link desired. The valid values for link type can be obtained from the CONV_VALUE column in the MSP_CONVERSIONS table where the STRING_TYPE_ID equals 3 (Link Type). The valid link types are 0 (Finish-to-Finish), 1 (Finish-to-Start), 2 (Start-to-Finish) and 4 (Start-to-Start).

If you want to specify lag when you create a link, then you must specify the following columns:

Table Fields Notes
MSP_LINKS LINK_LAG Specifies the amount of lag, specified as duration value (minutes * 10).
LINK_LAG_FMT Specifies the format for the amount of lag specified in LINK_LAG.
EXT_EDIT_REF_DATA Must be set to 1 to indicate that this link is a new link.
MSP_PROJECTS PROJ_EXT_EDITED Must be set to 1 for Project to process.
Top

Creating a new assignment

To create a new assignment in the database, you must add a new row to the MSP_ASSIGNMENTS table with values for at least the following columns:

Table Fields Notes
MSP_ASSIGNMENTS PROJ_ID Refers to a valid ID in the MSP_PROJECTS table.
ASSN_UID The unique ID for the assignment.
TASK_UID Refers to a valid ID in the MSP_TASKS table.
RES_UID Refers to a valid ID in the MSP_RESOURCES table.
ASSN_UNITS The percentage number of units for which a resource is assigned to a task, expressed as a percentage of 100 percent (%), assuming a resource's MaxUnits value is 100 percent.
ASSN_WORK The total amount of work scheduled to be performed by a resource on a task; defaults to 0 if not specified.
EXT_EDIT_REF_DATA Must be set to 1 to indicate that this assignment is a new assignment.
MSP_PROJECTS PROJ_EXT_EDITED Must be set to 1 for Project to process.

To create an assignment, copy, modify and execute the following insert statement.

Insert into MSP_ASSIGNMENTS (
              PROJ_ID,
              ASSN_UID,
              RES_UID,
              TASK_UID,
              ASSN_UNITS,
              ASSN_WORK,
              EXT_EDIT_REF_DATA )
          values (
              3,
              11,
              6,
              14,
              0.5, -- 50% of resource 6's available time
              480000, -- eight hours of work
              '1' ) 

To create a new assignment in the database for an existing unassigned task, you must delete the placeholder assignment from the MSP_ASSIGNMENTS table that references the TASK_UID of the task. To delete a placeholder assignment, copy, modify and execute the following delete statement.

Delete from MSP_ASSIGNMENTS
Where TASK_UID = 17
And RES_UID = -65535 -- unassigned resource ID 

Notes

  • Assignment actual cost must be specified in the MSP_TIMEPHASED_DATA table rather than MSP_ASSIGNMENTS for Project to process.
  • When a material resource is assigned to a task, the task duration is ignored. Instead, the duration is determined by the work entered on the assignment.
Top

Managing Timephased Data

Timephased data is stored in the MSP_TIMEPHASED_DATA table and is stored in much the same way as it is displayed in the Task Usage and Resource Usage views. Each row in the table specifies:

NoteB B Project Server maintains timephased data in the View tables stored in the Project Server database. View tables can be easily accessed from within the Project Web Access user interface.

  • The type of data, that is, work, percent complete, or cost values for an assignment, task, or resource.
  • A reference to an assignment, task, or resource record.
  • A date range.
  • One or more values of the specified type of data for each time period in the specified date range.
  • The unit of time (for example, day or week) that each value column spans.

The type of data captured is set to the appropriate value of the CONV_VALUE column in the MSP_CONVERSIONS table where the string type is equal to Timephased Contour Type. The types of data that can be recorded include:

  • Assignment remaining work
  • Assignment actual work
  • Assignment actual overtime work
  • Assignment baseline work
  • Assignment baseline cost
  • Assignment actual cost
  • Resource baseline work
  • Resource baseline cost
  • Task baseline work
  • Task baseline cost
  • Task percent complete

NoteB B Assignment work completed is not saved to the database. Assignment work completed is calculated as assignment actual work / assignment duration.

To record timephased remaining work, actual work, or cost on an assignment or task, you must add one or more new rows to the MSP_TIMEPHASED_DATA table and enter values for at least the following columns:

Table Fields Notes
MSP_TIMEPHASED_DATA PROJ_ID Refers to a valid ID in the MSP_PROJECTS table.
TD_UID The unique ID for the timephased data record.
TD_FIELD_ID Refers to a valid CONV_VALUE in the MSP_CONVERSIONS table.
TD_CATEGORY Set to 0 to indicate a Task.
TD_REF_UID Shows the corresponding TASK_UID, RES_UID, or ASSN_UID value of the row for which the timephased data applies.
TD_START The start of the first time period for which work, percent complete or cost values will be applied.
TD_FINISH The end of the first time period for which work, percent complete or cost values will be applied.
TD_UNITS The unit of time to which the specified values in TD_VALUE1 - TD_VALUE7 apply.
TD_VALUE1 - TD_VALUE7 The value of the work, percent complete or cost for the first-seventh time period in the units specified in the TD_UNITS column during the date range specified in the TD_START and TD_FINISH columns; only values that fall between the specified start and finish dates will apply.
TD_EXT_EDITED Must be set to 1 for Project to process.
MSP_PROJECTS PROJ_EXT_EDITED Must be set to 1 for Project to process.

The following table shows how data stored in MSP_TIMEPHASED_DATA relates to the data for actual work toward an assignment in the task usage view:

Project Field Usage View Value Database Column MSP_TIMEPHASED_DATA Value
Project ID n/a PROJ_ID 3
Timephased record UID n/a TD_UID 4
Details Actual Work TD_FIELD_ID 2
Category n/a TD_CATEGORY 3
Assignment UID n/a TD_REF_UID 16
Time period timescale TD_START 7/15/2003 8:00:00 AM
TD_FINISH 7/26/2003 8:00:00 AM
Minor scale TD_UNITS 3
Actual work 8h TD_VALUE1 480000
4h TD_VALUE2 240000
0h TD_VALUE3 0
4h TD_VALUE4 240000
Top

Creating or modifying an assignment actual-work order

To specify actual work for an assignment, copy, modify, and execute the following insert statement:

Insert into MSP_TIMEPHASED_DATA (
              PROJ_ID,
              TD_UID,
              TD_FIELD_ID,
              TD_CATEGORY,
              TD_REF_UID,
              TD_START,
              TD_FINISH,
              TD_UNITS,
              TD_VALUE1,
              TD_VALUE2,
              TD_VALUE3,
              TD_VALUE4,
              TD_EXT_EDITED )
          values (
              3,
              108,
              2, -- assignment actual work
              3, -- category = assignment
              2, -- assignment UID
              '7/15/2003 8:00:00 AM', -- start of the first time period
              '8/26/2003 8:00:00 AM', -- end of the last time period
              2, -- each value column represents a day,
              480000, -- day one value = 8h = (480 minutes * 1000)
              240000, -- day two value = 4h = (240 minutes * 1000)
              0, -- day three value = 0h = (zero minutes * 1000)
              240000, -- day three value = 4h = (240 minutes * 1000)
              1 ) -- externally edited flag

NoteB B The value of TD_FINISH will be displayed in Project as 7/25/2003 since 8/26/2003 08:00 is interpreted as the end of the working day which begins on 8/25.

Top

Creating splits in scheduled work

Creating splits in scheduled work is much like specifying actual work. The differences are:

  • TD_FIELD_ID is set to 1 for Assignment Remaining Work.
  • TD_CATEGORY is set to 0 to indicate a Task.
  • TD_VALUEn is set to 0 for each of the specified periods with no scheduled work.
Top

Creating or modifying an assignment remaining-work order

Specifying remaining work is similar to specifying actual work. The differences are:

  • TD_FIELD_ID is set to 1 for Assignment Remaining Work.
  • TD_CATEGORY is set to 3 to indicate an Assignment.
Top

Creating or modifying cost contours

To specify a cost contour for an assignment over eight days, copy, modify, and execute the following insert statement:

Insert into MSP_TIMEPHASED_DATA (
              PROJ_ID,
              TD_UID,
              TD_FIELD_ID,
              TD_CATEGORY,
              TD_REF_UID,
              TD_START,
              TD_FINISH,
              TD_UNITS,
              TD_VALUE1,
              TD_EXT_EDITED )
          values (
              3,
              109,
              6, -- assignment actual cost
              3, -- category = assignment
              3, -- assignment UID
              '7/15/2003 8:00:00 AM', -- start of the first time period
              '8/26/2003 8:00:00 AM', -- end of the last time period
              3, -- each value column represents a week,
              10032, -- week one value = $100.32 cost * 100,
              1 )

This example creates a record of $100.32 cost for the assignment over seven days. To create a record for the eighth day, copy and modify the following insert statement:

Insert into MSP_TIMEPHASED_DATA (
              PROJ_ID,
              TD_UID,
              TD_FIELD_ID,
              TD_CATEGORY,
              TD_REF_UID,
              TD_START,
              TD_FINISH,
              TD_UNITS,
              TD_VALUE1,
              TD_EXT_EDITED )
          values (
              3,
              110,
              6, -- assignment actual cost
              3, -- category = assignment
              3, -- assignment UID
              '7/15/2003 8:00:00 AM', -- start of the first time period
              '8/26/20031 8:00:00 AM', -- end of the last time period
              2, -- days,
              1650, -- day one value = $16.50 cost * 100,
              1 )

The combination of these two inserts will create a contour over the eight day period specified.

NoteB B  Project will ignore inserted cost data if the PROJ_OPT_CALC_ACT_COSTS column in the MSP_PROJECTS table is set to 1.

To set this flag off, copy, modify, and execute the following update statement:

Update    MSP_PROJECTS 
Set       PROJ_OPT_CALC_ACT_COSTS = 0 
Where     PROJ_ID = 3 
Top

Creating or modifying task-percent complete contours

To specify a percent complete contour for a task, copy, modify, and execute the following insert statement:

Insert into MSP_TIMEPHASED_DATA (
              PROJ_ID,
              TD_UID,
              TD_FIELD_ID,
              TD_CATEGORY,
              TD_REF_UID,
              TD_START,
              TD_FINISH,
              TD_UNITS,
              TD_VALUE1,
              TD_VALUE2,
              TD_VALUE3,
              TD_EXT_EDITED )
          values (
              3,
              111,
              11, -- task percent complete
              0, -- category = task
              3, -- TASK_UID
              '7/15/2003 8:00:00 AM', -- start of the first time period
              '8/26/2003 8:00:00 AM', -- end of the last time period
              2, -- days,
              72, -- day one value = 72 percent * 100,
              0, -- day two value of zero percent * 100,

              28, -- day three value = 28 percent * 100,
              1 )

This example creates a record of 72% of the task completed on the day that begins on 7/15/2003 at 8:00:00 AM, 0% completed the next day, and 28% completed on the day that begins 8/25/2003.

Top

Deleting a contour

To delete a contour in the MSP_TIMEPHASED_DATA table, update all rows for that contour by setting all TD_VALUE columns to 0 and TD_EXT_EDITED to 1. Setting all TD_VALUE columns to 0 for a single row will not cause the row to be deleted if there are one or more rows for the same contour for periods before and after the row being updated. Instead, a split will be created in the contour and the row will be retained.

NoteB B  Project won't actually delete the updated rows in the MSP_TIMEPHASED_DATA table until the project is saved.

Top

Customizing Project Data

Custom flag fields are stored in the database in six special tables, each based on a custom field type. To specify the value of a custom field, you must add a new row to the appropriate custom field table and set the corresponding flag in the MSP_PROJECTS table. To specify custom field values for a project summary task, the procedure is identical, but you must use the special TASK_UID of 0.

NoteB B The information in this section does not always apply (or may be different) for enterprise project data stored in the Project Server database. It is recommended that enterprise project data in the Project Server database only be interacted with by using the Project Data Service (PDS), Project, or Project Web Access.

Top

Specifying custom text fields

You must enter values in the following database fields to specify a custom text field:

Table Columns Notes
MSP_TEXT_FIELDS PROJ_ID Refers to a valid ID in the MSP_PROJECTS table.
TEXT_CATEGORY Indicates whether the custom text category is Task (0), Resource (1), or Assignment (3).
TEXT_REF_UID Refers to a valid TASK_UID, RES_UID, or ASSN_UID in its respective table, as determined by TEXT_CATEGORY.
TEXT_FIELD_ID Refers to a valid CONV_VALUE in the MSP_CONVERSIONS table; always 188743706 for inserted projects.
TEXT_VALUE The value of the custom text field.
MSP_PROJECTS PROJ_EXT_EDITED Must be set to 1 for Project to process.
PROJ_EXT_EDITED_TEXT Must be set to 1 for Project to process.
Top

Specifying custom number (or cost) fields

You must enter values in the following database columns to specify a custom number field:

Table Columns Notes
MSP_NUMBER_FIELDS PROJ_ID Refers to a valid ID in the MSP_PROJECTS table.
NUM_CATEGORY Indicates whether the custom number category is Task (0), Resource (1), or Assignment (3).
NUM_REF_UID Refers to a valid TASK_UID, RES_UID, or ASSN_UID in its respective table, as determined by NUM_CATEGORY.
NUM_FIELD_ID Refers to a valid CONV_VALUE in the MSP_CONVERSIONS table.
NUM_VALUE The value of the custom number field.
MSP_PROJECTS PROJ_EXT_EDITED Must be set to 1 for Project to process.
PROJ_EXT_EDITED_NUM Must be set to 1 for Project to process.
Top

Specifying custom date, start, and finish fields

You must enter values in the following database columns to specify a custom date field:

Table Columns Notes
MSP_DATE_FIELDS PROJ_ID Refers to a valid ID in the MSP_PROJECTS table.
DATE_CATEGORY Indicates whether the custom date category is Task (0), Resource (1), or Assignment (3).
DATE_REF_UID Refers to a valid TASK_UID, RES_UID, or ASSN_UID in its respective table, as determined by DATE_CATEGORY.
DATE_FIELD_ID Refers to a valid CONV_VALUE in the MSP_CONVERSIONS table.
DATE_VALUE The value of the custom date field.
MSP_PROJECTS PROJ_EXT_EDITED Must be set to 1 for Project to process.
PROJ_EXT_EDITED_DATE Must be set to 1 for Project to process.
Top

Specifying custom duration fields

You must enter values in the following database columns to specify a custom duration field:

Table Columns Notes
MSP_DURATION_FIELDS PROJ_ID Refers to a valid ID in the MSP_PROJECTS table.
DUR_CATEGORY Indicates whether the custom duration category is Task (0), Resource (1), or Assignment (3).
DUR_REF_UID Refers to a valid TASK_UID, RES_UID, or ASSN_UID in its respective table, as determined by DUR_CATEGORY.
DUR_FIELD_ID Refers to a valid CONV_VALUE in the MSP_CONVERSIONS table.
DUR_VALUE The value of the custom date field.
DUR_FMT Specifies the units to use when displaying durations in Project.
MSP_PROJECTS PROJ_EXT_EDITED Must be set to 1 for Project to process.
PROJ_EXT_EDITED_DUR Must be set to 1 for Project to process.
Top

Field attributes

While the majority of Project fields have no extra properties beyond the actual data, some fields contain not only the data values but also have some project-level metadata about the field that applies to all values in the column. For example, the WBS field stores the actual WBS codes for each task, but there is also a mask for the codes that can be defined for each project. Similarly, all of the custom fields in Project can have special attributes assigned, such as a formula or user-defined name (alias), and this metadata is stored just once for the whole column, separate from the actual data stored in each row.

Whenever this metadata is exposed in the database, it is stored in the MSP_FIELD_ATTRIBUTES and MSP_ATTRIBUTE_STRINGS tables. MSP_FIELD_ATTRIBUTES contains the fields IDs, the numeric value of the attribute (which can be de-referenced in the MSP_CONVERSIONS table), and either the actual attribute value or a pointer to the value. In the case of numeric and Boolean settings, the value is stored directly in MSP_FIELD_ATTRIBUTES, while string values are stored in the MSP_ATTRIBUTE_STRINGS table, and the index of the string is stored in MSP_FIELD_ATTRIBUTES.

Top

Specifying custom WBS codes

Custom WBS codes are defined in two tables. The code mask is stored in the MSP_ATTRIBUTE_STRINGS table. The actual WBS code values for tasks are stored in the MSP_TASKS table (TASK_WBS and TASK_WBS_RIGHTMOST_LEVEL columns). To define custom WBS codes, you must enter values for the following columns:

Table Columns Notes
MSP_ATTRIBUTE_STRINGS PROJ_ID Refers to a valid ID in the MSP_PROJECTS table.
AS_ID Refers to a row in the MSP_ATTRIBUTE_STRINGS table that identifies the WBS string.
AS_VALUE Shows the formula as expressed in the Formula dialog box in Project.
MSP_PROJECTS PROJ_EXT_EDITED Must be set to 1 for Project to process.

To create the custom WBS mask required to produce the WBS code A.b.01, copy, modify, and execute the following insert statement:

Insert into MSP_ATTRIBUTE_STRINGS (
              PROJ_ID,
              AS_ID,
              AS_VALUE )
          values (
              3,
              1,
              '1,1,.,2,1,.,0,0,.' )

In this example, the value in the column AS_VALUE specifies three levels of WBS codes:

WBS Code Specification WBS Code Max WBS Code Length WBS Code Separator Character
1,1,. Uppercase Letters (ordered) 1 . (period)
2,1,. Lowercase Letters (ordered) 1 . (period)
0,0,. Numbers (ordered) any . (period)

You can also specify a WBS code prefix such as proj1-, resulting in the WBS code proj1-A.b.01. To specify a WBS code prefix mask, copy, modify, and execute the following insert statement:

Insert into MSP_ATTRIBUTE_STRINGS (
              PROJ_ID,
              AS_ID,
              AS_VALUE )
          values (
              3,
              2,
              'proj1-' )

In order to use the WBS prefix and codes specified above in tasks, two rows must be added to the MSP_FIELD_ATTRIBUTES table. One each for the WBS prefix and WBS code mask. Values must be provided for the following columns:

Table Columns Notes
MSP_FIELD_ATTRIBUTES PROJ_ID Refers to a valid ID in the MSP_PROJECTS table.
ATTRIB_FIELD_ID Refers to a valid Field ID in the CONV_VALUE column in the MSP_CONVERSIONS table.
ATTRIB_ID Refers to a valid Field Attribute in the STRING_TYPE_ID column of the MSP_CONVERSIONS table.
ATTRIB_VALUE Shows the value of the attribute; a value of -1 indicates that the value is stored in the MSP_ATTRIBUTE_STRINGS table.
AS_ID Refers to a row in the MSP_ATTRIBUTE_STRINGS table if ATTRIB_VALUE is set to -1.
ATTRIB_EXT_EDITED Must be set to 1 for Project to process.
MSP_PROJECTS PROJ_EXT_EDITED Must be set to 1 for Project to process.

To create these rows, copy, modify, and execute the following insert statements:

Insert into MSP_FIELD_ATTRIBUTES (
              PROJ_ID,
              ATTRIB_FIELD_ID,
              ATTRIB_ID,
              ATTRIB_VALUE,
              AS_ID,
              ATTRIB_EXT_EDITED )
          values (
              3,
              188743696, -- TASK_WBS field ID
              200, -- for 'Outline code and WBS mask'
              -1, -- the value is specified in MSP_ATTRIBUTE_STRINGS
              1, -- the ID of the WBS code definition
              1 ) -- must set for Project to process

Insert into MSP_FIELD_ATTRIBUTES (
              PROJ_ID,
              ATTRIB_FIELD_ID,
              ATTRIB_ID,
              ATTRIB_VALUE,
              AS_ID,
              ATTRIB_EXT_EDITED )
          values (
              3,
              188743696, -- TASK_WBS field ID
              204, -- for 'WBS Prefix'
              -1, -- indicates that the value is in MSP_ATTRIBUTE_STRINGS
              2, -- the ID of the WBS code definition
              1 ) -- must set for Project to process

Once these two rows have been inserted, tasks can be given custom WBS codes. WBS codes specified for tasks should conform to the specified mask. To specify a WBS code for an existing task, copy, modify and execute the following update statement:

Update    MSP_TASKS 
Set       TASK_WBS = 'A.b.03',
          TASK_WBS_RIGHTMOST_LEVEL = '03' 
Where     PROJ_ID = 3
And       TASK_UID = 11

When creating or modifying a project in the database, you can specify whether Project enforces the uniqueness of WBS codes and the automatic generation of WBS codes in new tasks created using Project. In order to specify whether Project should enforce the uniqueness of new WBS codes in tasks, a row must be inserted into the MSP_FIELD_ATTRIBUTES table. To create this row, copy, modify, and execute the following insert statement:

Insert into MSP_FIELD_ATTRIBUTES (
              PROJ_ID,
              ATTRIB_FIELD_ID,
              ATTRIB_ID,
              ATTRIB_VALUE,
              ATTRIB_EXT_EDITED )
          values (
              3,
              188743696, -- TASK_WBS field ID
              203, -- for 'Verify uniqueness of new WBS codes'
              1, -- sets the flag to True
              1 ) -- must set for Project to process

Finally, to specify whether Project generates WBS codes for new tasks, copy, modify, and execute the following insert statement:

Insert into MSP_FIELD_ATTRIBUTES (
              PROJ_ID,
              ATTRIB_FIELD_ID,
              ATTRIB_ID,
              ATTRIB_VALUE,
              ATTRIB_EXT_EDITED )
          values (
              3,
              188743696, -- TASK_WBS field ID
              205, -- for 'Generate WBS code for new tasks'
              1, -- sets the flag to True
              1 ) -- must set for Project to process
Top

Specifying custom outline codes

Custom outline codes are powerful and flexible tools for describing, organizing, and aggregating task information. Care must be taken when defining custom outline codes directly in the database. Custom outline codes are defined in the following five tables:

  • MSP_ATTRIBUTE_STRINGS contains the outline code mask and, optionally, an alias for the specified outline code field. Specifying a custom outline code mask is almost identical to specifying a custom WBS code mask. See the section Specifying custom WBS codes above for information on creating a custom WBS mask. Also, see the section Specifying aliases for custom fields below for information on creating an alias for a custom outline code field.
  • MSP_FIELD_ATTRIBUTES associates the outline code mask to its outline code field (for example, Outline Code1). This table contains the values for the flags 'Only allow outline codes listed in the lookup table' and 'Only allow new outline codes with values at all levels' from the CONV_VALUE column in the MSP_CONVERSIONS table. Also associates the outline code field with its alias if it has one. Associating a custom outline code mask to a specific custom outline code is almost identical to associating a custom WBS mask to the task WBS field. Only the Field ID, which can be retrieved from the MSP_CONVERSIONS table, is different. See the section Specifying custom WBS codes above for information on associating a code mask to a custom field and setting custom flags. Also see the section Specifying aliases for custom fields below on associating a custom outline mask to a custom outline field. Be sure to set the column ATTRIB_EXT_EDITED to 1 when creating or changing an attribute string value or code-mask-to-custom-field association.
  • MSP_OUTLINE_CODES defines the outline code values for each level. Each row contains a code value and a reference to its parent code.
  • MSP_CODE_FIELDS associates the defined codes to the tasks and resources to which they apply. Unlike custom WBS codes, custom outline codes are not stored in the MSP_TASKS table, they are defined in the MSP_OUTLINE_CODES table and associated with one of the custom outline code fields in specific tasks and/or resources.
  • MSP_MV_FIELDS associates the defined multi-value outline codes to the tasks and resources to which they apply.

To create custom outline codes, you must enter values for at least the following columns:

Table Columns Notes
MSP_OUTLINE_CODES PROJ_ID Refers to a valid ID in the MSP_PROJECTS table.
CODE_UID Refers to the custom outline code and must be unique within the project.
OC_PARENT Refers to a valid ID in the MSP_OUTLINE_CODES table; set to 0 for top-level codes without parents.
OC_FIELD_ID Refers to a valid Field ID in the CONV_VALUE column in the MSP_CONVERSIONS table.
OC_NAME Shows the name of the custom outline code.
MSP_PROJECTS PROJ_EXT_EDITED Must be set to 1 for Project to process.

Once a custom outline code mask has been defined, you can define the outline codes themselves. To specify two levels of codes that constitute the outline code A.x, copy, modify, and execute the following insert statements:

Insert into MSP_OUTLINE_CODES (
              PROJ_ID,
              CODE_UID,
              OC_PARENT,
              OC_FIELD_ID,
              OC_NAME )
          values (
              3,
              1,
              0, -- highest level code, no parent
              188744096, -- Task Outline Code1
              'A' ) -- the level 1 code

Insert into MSP_OUTLINE_CODES (
              PROJ_ID,
              CODE_UID,
              OC_PARENT,
              OC_FIELD_ID,
              OC_NAME )
          values (
              3,
              2,
              1, -- parent code UID = 1
              188744096, -- Task Outline Code1
              'x' ) -- the level 1 code

To associate a code with a custom outline code and a specific task or resource, values must be entered for the following columns:

Table Columns Notes
MSP_CODE_FIELDS PROJ_ID Refers to a valid ID in the MSP_PROJECTS table.
CODE_CATEGORY Indicates whether the custom outline code is a task (0) or a resource (1).
CODE_REF_UID Refers to a valid TASK_UID, or RES_UID in its respective table, as determined by CODE_CATEGORY.
CODE_FIELD_ID Refers to a valid Field ID in the CONV_VALUE column in the MSP_CONVERSIONS table.
CODE_UID Refers to the custom outline code and must be unique within the project.

To associate a custom outline code to a task, copy, modify, and execute the following insert statement:

Insert into MSP_CODE_FIELDS (
              PROJ_ID,
              CODE_CATEGORY,
              CODE_REF_UID,
              CODE_FIELD_ID,
              CODE_UID )
          values (
              3, -- project ID
              0, -- task category
              2, -- task UID
              188744096, -- Task Outline Code1
              2 ) -- code UID for the code "x"

The result of inserting this row is that the value of the custom task OutlineCode1 for Task 2 is A.x.

NoteB B When deleting a custom outline code from the MSP_OUTLINE_CODES table, you should delete any child codes as well to avoid orphaned codes.

Top

Specifying aliases for custom fields

Aliases for custom fields are stored in the MSP_ATTRIBUTE_STRINGS and MSP_FIELD_ATTRIBUTES tables. As with custom WBS codes, the alias is specified in the MSP_ATTRIBUTE_STRINGS table. The required fields are:

Table Columns Notes
MSP_ATTRIBUTE_STRINGS PROJ_ID Refers to a valid ID in the MSP_PROJECTS table.
AS_ID Identifies the attribute string, which contains the custom formula.
AS_VALUE The formula as expressed in the Formula dialog box in Project.

The alias is related to a specific custom field in the MSP_FIELD_ATTRIBUTES table. Values must be provided for the following columns:

Table Columns Notes
MSP_FIELD_ATTRIBUTES PROJ_ID Refers to a valid ID in the MSP_PROJECTS table.
ATTRIB_FIELD_ID Refers to a valid Field ID in the CONV_VALUE column in the MSP_CONVERSIONS table.
ATTRIB_ID Refers to a valid Field Attribute in the STRING_TYPE_ID column of the MSP_CONVERSIONS table.
ATTRIB_VALUE Shows the value of the attribute; a value of -1 indicates that the value is stored in the MSP_ATTRIBUTE_STRINGS table.
AS_ID Refers to a row in the MSP_ATTRIBUTE_STRINGS table if ATTRIB_VALUE is set to -1.
ATTRIB_EXT_EDITED Must be set to 1 for Project to process.
MSP_PROJECTS PROJ_EXT_EDITED Must be set to 1 for Project to process.

To specify an alias for a custom field, copy, modify, and execute the following insert statements:

Insert into MSP_ATTRIBUTE_STRINGS (
              PROJ_ID,
              AS_ID,
              AS_VALUE )
          values (
              3,
              5,
              'my text column' )

Insert into MSP_FIELD_ATTRIBUTES (
              PROJ_ID,
              ATTRIB_FIELD_ID,
              ATTRIB_ID,
              ATTRIB_VALUE,
              AS_ID,
              ATTRIB_EXT_EDITED )
          values (
              3,
              188743731, -- refers to custom field 'Task Text1'
              206, -- for 'Field Alias'
              -1, -- indicates that the value is in MSP_ATTRIBUTE_STRINGS
              5, -- the ID of the value in MSP_ATTRIBUTE_STRINGS
              1 ) -- must set for Project to process
Top

Specifying formulae for custom fields

As with custom WBS prefixes and masks, custom formulae are specified in the MSP_FIELD_ATTRIBUTES and MSP_ATTRIBUTE_STRINGS tables. To define a custom formula, you must enter values for the following columns:

Table Columns Notes
MSP_ATTRIBUTE_STRINGS PROJ_ID Refers to a valid ID in the MSP_PROJECTS table.
AS_ID The unique ID for the attribute string.
AS_VALUE Shows the formula as expressed in the Formula dialog box in Project.

For example, to create a custom formula that performs the calculation (2*[Duration])/60, copy, modify, and execute the following insert statement:

Insert into MSP_ATTRIBUTE_STRINGS (
              PROJ_ID,
              AS_ID,
              AS_VALUE )
          values (
              3,
              5,
              '(2*[Duration])/60' )

In order to map the custom formula to a custom field, values must be provided for the following columns in the MSP_FIELD_ATTRIBUTES table:

Table Columns Notes
MSP_FIELD_ATTRIBUTES PROJ_ID Refers to a valid ID in the MSP_PROJECTS table.
ATTRIB_FIELD_ID Refers to a valid Field ID in the CONV_VALUE column in the MSP_CONVERSIONS table.
ATTRIB_ID Refers to a valid Field Attribute in the STRING_TYPE_ID column of the MSP_CONVERSIONS table.
AS_ID Refers to a row in the MSP_ATTRIBUTE_STRINGS table if ATTRIB_VALUE is set to -1.
ATTRIB_EXT_EDITED Must be set to 1 for Project to process.
MSP_PROJECTS PROJ_EXT_EDITED Must be set to 1 for Project to process.

To create a mapping between the custom formula and the Number1 custom field, copy, modify, and execute the following insert statement:

Insert into MSP_FIELD_ATTRIBUTES (
              PROJ_ID,
              ATTRIB_FIELD_ID,
              ATTRIB_ID,
              AS_ID,
              ATTRIB_EXT_EDITED )
          values (
              3,
              188743767, -- Number1 custom field
              207, -- for 'Custom Field Formula'
              5, -- the ID of the custom formula definition
              1 ) -- must set for Project to process

To query the database for the existing formulae for custom field Number3, copy, edit, and execute the following select statement:

Select    PROJ_NAME,
          (select CONV_STRING
             from MSP_CONVERSIONS
            where CONV_VALUE = fa.ATTRIB_FIELD_ID
              and STRING_TYPE_ID = 105) as ATTRIB_FIELD -- field name
          (select CONV_STRING
             from MSP_CONVERSIONS
            where CONV_VALUE = fa.ATTRIB_ID
              and STRING_TYPE_ID = 106) as ATTRIB -- field attribute name
          AS_VALUE
From      MSP_FIELD_ATTRIBUTES fa,
          MSP_ATTRIBUTE_STRINGS s,
          MSP_PROJECTS p
Where     fa.AS_ID = s.AS_ID
And       fa.PROJ_ID = s.PROJ_ID
And       fa.PROJ_ID = p.PROJ_ID
And       fa.ATTRIB_FIELD_ID = 188743769 -- field = Number3
And       p.PROJ_ID = 3  

To delete a formula, update MSP_ATTRIBUTE_STRINGS setting AS_VALUE to null in the row that defines the formula. Remember to set ATTRIB_EXT_EDITED in the associated row in MSP_FIELD_ATTRIBUTES to 1 in order for Project to process. The row will be deleted when the project is saved in Project.

Top

Reading and writing RTF notes

To retrieve data from the RTF notes columns in the MSP_TASKS, MSP_RESOURCES or MSP_ASSIGNMENTS tables, copy, modify and execute the following VB script:

Sub getRtf()
    'This macro extracts RTF data from MSP_TASKS.TASK_RTF_NOTES.  This data can then be written
    'to a file that can be opened with Microsoft Word or displayed in a richedit control.
    
    Dim cn As New ADODB.Connection
    Dim rs As New ADODB.Recordset
    Dim sql As String, rtf As String, cnString

    'Open the MSP_TASKS table to look for TASK_RTF_NOTES
    cnString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\temp\MyProject.mpd"
    sql = "select PROJ_ID, TASK_UID, TASK_RTF_NOTES " & _
          "from MSP_TASKS " & _
          "where TASK_RTF_NOTES is not null" 'can specify a specific PROJ_ID and TASK_UID instead
    cn.Open cnString
    rs.Open sql, cn
    
    'Enumerate across the recordset looking for notes
    With rs
        Do While Not .EOF
            rtf = StrConv(.Fields("TASK_RTF_NOTES"), vbUnicode) ' Put binary column data into text string
            Debug.Print rtf
            .MoveNext
        Loop
        .Close
    End With
End Sub

NoteB B Be sure to check the code listed above for all references to MSP_TASKS, TASK_RTF_NOTES, and TASK_UID. You will need to modify TASKS to RES or ASSN, depending on which table you are pulling RTF data from.

To write data to the RTF notes columns in the MSP_TASKS, MSP_RESOURCES or MSP_ASSIGNMENTS tables, copy, modify and execute the following VB script:

Sub writeRtf()
    'This macro writes RTF data to MSP_TASKS.TASK_RTF_NOTES.

    Dim cn As New ADODB.Connection
    Dim cmd As New ADODB.Command
    Dim param As New ADODB.Parameter
    Dim sql As String, rtf As String, cnString As String

    cnString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\temp\MyProject.mpd"
    sql = "update MSP_TASKS set TASK_RTF_NOTES = ? where PROJ_ID = 1 and TASK_UID = 1"
    rtf = "{\rtf1\ansi\ansicpg1252\deff0\deflang1033{\fonttbl{\f0\fswiss\fcharset0 Arial;}}" & vbNewLine & _
          "\viewkind4\uc1\pard\f0\fs20 What's in a name? That which we call a rose...     \par" & vbNewLine & _
          "}" & vbNewLine & vbLf & Chr(0)   'be sure to specify valid RTF text here including "vbLf & Chr(0)

    cn.Open cnString

    param.Direction = adParamInput
    param.Type = adVarBinary
    param.Size = 8000
    param.Value = StrConv(rtf, vbFromUnicode)

    cmd.ActiveConnection = cn
    cmd.CommandText = sql
    cmd.Parameters.Append param
    cmd.Execute
End Sub

NoteB B Be sure to check the code listed above for all references to MSP_TASKS, TASK_RTF_NOTES, and TASK_UID. You will need to modify TASKS to RES or ASSN, depending on which table you are pulling RTF data from.

The Microsoft ActiveX Data Objects (ADO) 2.1 (or later) library must be referenced in the Microsoft Visual Basic editor for VBA for these scripts to run. Be sure to set the columns TASK_HAS_NOTES in the MSP_TASKS table and PROJ_EXT_EDITED in the MSP_PROJECTS table to 1 for Project to process. The same applies when changing RTF notes in MSP_RESOURCES and MSP_ASSIGNMENTS.

Top

Managing Other Data in the Database

Additional task and resource information that is stored in the MSP_TEXT_FIELDS table is listed below:

Task Fields Resource Fields
Task Contact Resource Code
Cross-project link (project name, task ID) Email Address
Notes (non-RTF text) Notes (non-RTF text)
Task Subproject File Resource Group
WBS Max Value Resource Windows User Account
Task Hyperlink Resource Hyperlink
Task Hyperlink Address Resource Hyperlink Address
Task Hyperlink Subaddress Resource Hyperlink Subaddress

To specify a value for one of the fields above, you must specify the category (Task or Resource), the referenced UID of the specified category, the field type (for example, Task Contact) and the value. The values of field types are stored in the MSP_CONVERSIONS table where the string type is 105, or Field ID.

The columns PROJ_EXT_EDITED and PROJ_EXT_EDITED_TEXT in the MSP_PROJECTS table must be set to 1 for Project to process additional task, resource and assignment data stored in MSP_TEXT_FIELDS.

NoteB B  Project extracts the text into MSP_TEXT_FIELDS from TASK_RTF_NOTES, RES_RTF_NOTES and ASSN_RTF_NOTES as a convenience. These text notes are informational-only and cannot be updated.

Top

Specifying an e-mail address

To specify the e-mail address for a resource, copy, modify, and execute the following insert statement:

Insert into MSP_TEXT_FIELDS (
              PROJ_ID,
              TEXT_CATEGORY,
              TEXT_REF_UID,
              TEXT_FIELD_ID,
              TEXT_VALUE )
          values (
              3,
              1, -- the category is 'Resource'
              6, -- the UID of the resource
              205520931, -- the 'Resource Email Address' field
              'somebody@microsoft.com' ) -- resource's e-mail address
Top

Retrieving cross-project link project/task references

Cross-project links allow a task in one project to be dependent on a task in another project. Cross-project links are recorded in both the project which contains the dependent task and the project containing the task on which the other task depends. Cross-project links should only be created, modified or deleted using Project. Creating, modifying or deleting cross-project link information in the database can cause the project to become corrupted. This includes normal tasks and ghost tasks (the copy of the successor or predecessor task in the other project), links, and the link references in MSP_TEXT_FIELDS.

To retrieve cross-project link project/task references for a specific task, copy modify and execute the following select statement:

Select    PROJ_NAME,
          TASK_NAME,
          CONV_STRING,
          TEXT_VALUE
From      MSP_PROJECTS p,
          MSP_TASKS t,
          MSP_CONVERSIONS c,
          MSP_LINKS l,
          MSP_TEXT_FIELDS x
where     p.PROJ_ID = 2    -- set the project ID
and       t.TASK_UID = 1   -- set the task UID
and       p.PROJ_ID = t.PROJ_ID
and       p.PROJ_ID = l.PROJ_ID
and       p.PROJ_ID = x.PROJ_ID
and       l.LINK_UID = x.TEXT_REF_UID
and       X.TEXT_CATEGORY = 4 -- Dependencies
and       (
             (    t.TASK_ID = l.LINK_PRED_UID
              and c.CONV_VALUE = x.TEXT_FIELD_ID
              and c.STRING_TYPE_ID = 105 -- Field ID
             )
           or
             (    t.TASK_ID = l.LINK_SUCC_UID
              and c.CONV_VALUE = x.TEXT_FIELD_ID


              and c.STRING_TYPE_ID = 105 -- Field ID
             )
          )
Top

Retrieving the names of sharer files

As a convenience, the names of the sharer files for a resource pool project are written to the database. This data is read-only cannot be modified in the database. To obtain a list of the sharer files of a resource pool stored in a database, execute the following select statement:

Select   AS_VALUE
From     MSP_ATTRIBUTE_STRINGS S inner join
         MSP_FIELD_ATTRIBUTES A on
         S.AS_ID = A.AS_ID
Where    A.ATTRIB_ID = 208 -- conversion string 'Sharer File Names'
Order by S.AS_POSITION
Top

Outlining with summary tasks and subtasks

Outlines, which consist of summary tasks with subtasks under them, are created in three steps:

1 Increment the task IDs of all tasks below the task under which a subtask will be inserted.
2 Insert the subtask assigning it the task ID greater than the task ID of the summary task.
3 Mark the summary task as summary.

For example, to increment the task IDs of all tasks after task 12 (which will become the summary task), copy, modify, and execute the following update statement:

Update    MSP_TASKS 
Set       TASK_ID = TASK_ID + 1 
Where     TASK_ID > 12

Once room has been made in the list of task IDs, the subtask can be inserted. (See the section Creating a new task above for information on creating and inserting new tasks.) When inserting a subtask, the outline level must be set to the outline level of the summary task + 1. To insert subtask 13, copy, edit, and execute the following insert statement:

Insert into MSP_TASKS (
              PROJ_ID,
              TASK_UID,
              TASK_ID,
              TASK_NAME,
              TASK_OUTLINE_LEVEL,
              EXT_EDIT_REF_DATA )
          values (
              3,
              24, -- new, unique task UID
              13, -- the ID of the new task
              'your subtask name', -- the name of the task
              3, -- outline level of task 12 + 1
              '1' ) -- set to 1 so Project will process

If task 12 was not a summary task previously, it must be marked as such. To specify a task as a summary task, copy, edit, and modify the following update statement:

Update    MSP_TASKS 
Set       TASK_IS_SUMMARY = 1 
Where     TASK_UID = 12

Be sure to update the column PROJ_EXT_EDITED in MSP_PROJECTS to 1 in order for Project to process the changes.

Top

Editing work on a summary task assignment

If a resource is assigned to a summary task and you want to edit the work on that assignment, specify values for both the ASSN_UNITS and ASSN_WORK columns in the MSP_ASSIGNMENTS table. To change the work on an existing summary task assignment to two days at 50%, copy, modify, and execute the following update statement:

Update    MSP_ASSIGNMENTS 
Set       ASSN_WORK = 96000,
          ASSN_UNITS = .50 
Where     TASK_UID > 12

Be sure to back up task data before updating the row. See the section Ensuring data integrity in a project in a database for information on backing up existing task data.

Top

Retrieving workgroup message status

The task and resource workgroup message status fields (Update Needed, Confirmed, Response Pending, and Team Status Pending) are not saved out to the database. If you want to obtain status information about messages that have been sent out to the team, you have to extract the information from the assignment fields. By looking at all of the associated assignment records for a given task or resource, the values of the task or resource can be determined.

For example, to determine whether a task has been confirmed, you must check the ASSN_IS_CONFIRMED column in the MSP_ASSIGNMENTS table for all assignments against the task. If ASSN_IS_CONFIRMED is 1 for every assignment then the task is confirmed. To determine whether a task is confirmed, copy, modify, and execute the following select statement:

Select    count(*) 
From      MSP_ASSIGNMENTS 
Where     TASK_UID = 12
And       ASSN_IS_CONFIRMED = 0

If the query returns a number greater than 0, the task is not confirmed.

To determine if an update is needed on a task, you must check the column ASSN_UPDATE_NEEDED in MSP_ASSIGNMENTS for all of the assignments against the task. If ASSN_UPDATED_NEEDED is 1 for one or more of the assignments, then an update is needed for the task. To determine whether an update is needed on a task, copy, modify, and execute the following select statement:

Select    count(*) 
From      MSP_ASSIGNMENTS 
Where     TASK_UID = 12
And       ASSN_UPDATE_NEEDED = 1

If the query returns a number greater than 0, the task needs to be updated.

Top

Specifying hyperlinks

To specify a task hyperlink address, copy, modify, and execute the following insert statement:

Insert into MSP_TEXT_FIELDS (
              PROJ_ID,
              TEXT_CATEGORY,
              TEXT_REF_UID,
              TEXT_FIELD_ID,
              TEXT_VALUE )
          values (
              3,
              0, -- task category
              13, -- the UID of the task
              188743898, -- 'Task Hyperlink Address'
              'http://myserver/default.htm' ) -- task hyperlink address

For Project to process, you must set the PROJ_EXT_EDITED and PROJ_EXT_EDITED_TEXT columns in the MSP_PROJECTS table to 1 for the updated project.

NoteB B Hyperlinks, hyperlink addresses and hyperlink subaddresses can be specified for assignments as well as tasks and resources.

Top

Database Processing Order, Conventions, and Abbreviations

Processing order for externally edited data

The processing order for Project enforces the sequence in which the data is applied. For example, looking at the Standard processing order below, Actual Work values are always committed before Remaining Work values. This does not mean that Actual Work always overrides Remaining Work, however.

The task and assignment field processing order lists below contain just the fields that have interdependencies (though not all fields in each list are interdependent with all others). The ordering in the list determines the order in which the data is processed, just as if it was typed into the user interface (UI) in that order. Without this ordering, externally changing more than one of the fields could result in an ambiguous situation. On the other hand, if all the interdependent values are externally changed to repair all inconsistencies, then the processing order does not matter.

While the data can be entered into the UI in any editable field in any order, the processing order list is hard-coded, so a sequence that's possible in the UI may not be possible for data being read in. Rather than trying to follow a data entry sequence in the UI and then mimic it with imported data, however, it is better to determine what data should be changed externally and then test the changes in the UI (based on the processing order list) to check for the desired outcome. In other words, for every possible external change you might make, you can readily test the result in the UI, but every possible change you can make in the UI cannot be replicated with external modifications.

When Project reloads a complete project that has been flagged as externally edited, it first restores the project back to its pre-edited state and then applies the edits in the processing order. On an import operation, Project doesn't have the original values, so default tasks are created and then the updated values are applied according to the processing order.

Notes

  • When performing a full import of project data in order to create a new project, the standard processing order is applied. When using import to merge or append project data to an existing project, Project will generally follow the standard processing order as long as no columns contain null, or zero length, values. In the case of import-merge and import-append, null values in schedule-related columns may cause data being imported to be processed in a different order than the standard processing order. It is recommended that merge and append import maps be tested prior to deployment to ensure they provide the expected results. If they do not provide the expected results, try creating multiple maps with fewer columns and executing them consecutively.
  • Project options (for example, Tools and Options settings) are loaded and applied before any data is loaded. Thus, when any options are externally modified, all schedule data loaded for the project is applied under the modified settings. When creating new projects, all options should be explicitly specified.
  • Timephased data always has the highest precedence and will override any conflicting field changes.
Top

Standard processing order

The standard processing order for task fields is as follows:

  • Actual Duration
  • Remaining Duration
  • Duration
  • Leveling Delay
  • Start
  • Finish
  • Constraint Type
  • Constraint Date
  • Actual Start
  • Actual Finish
  • Stop
  • Resume
  • Percent Complete
  • Actual Work
  • Remaining Work
  • Work
  • Percent Work Complete
  • Fixed Cost
  • Actual Cost

The standard processing order for assignment fields is as follows:

  • Units
  • Assignment Delay
  • Leveling Delay
  • Start
  • Finish
  • Actual Start
  • Actual Finish
  • Actual Overtime Work
  • Actual Work
  • Remaining Work
  • Regular Work
  • Remaining Overtime Work
  • Overtime Work
  • Work

Database Object Naming Conventions

Table naming conventions

The following conventions apply to table names:

  • Table names are all uppercase.
  • All table names begin with the MSP_ prefix to identify them as Project tables.
  • Underscores separate all words.
Top

Column naming conventions

The following conventions apply to column names:

  • Column names are all uppercase.
  • Words are generally separated by underscores.
  • Column names are generally pre-fixed with a unique identifier for their tables:
Table Name Column Prefix
MSP_ASSIGNMENTS ASSN_
MSP_ASSIGNMENT_BASELINES AB_
MSP_ATTRIBUTE_STRINGS AS_
MSP_AVAILABILITY AVAIL_
MSP_CALENDARS CAL_
MSP_CALENDAR_DATA CD_
MSP_CODE_FIELDS CODE_
MSP_CONVERSIONS CONV_
MSP_DATE_FIELDS DATE_
MSP_DURATION_FIELDS DUR_
MSP_FIELD_ATTRIBUTES ATTRIB_
MSP_FLAG_FIELDS FLAG_
MSP_LINKS LINK_
MSP_MV_FIELDS MV_
MSP_NUMBER_FIELDS NUM_
MSP_OUTLINE_CODES OC_
MSP_PROJECTS PROJ_
MSP_RESOURCE_BASELINES RB_
MSP_RESOURCE_RATES RR_
MSP_RESOURCES RES_
MSP_STRING_TYPES STRING_
MSP_TASK_BASELINES TB_
MSP_TASKS TASK_
MSP_TEXT_FIELDS TEXT_
MSP_TIMEPHASED_DATA TD_
MSP_VERS_VERSIONS VERS_
Top

MSP_PROJECTS column qualifiers

The MSP_PROJECTS table contains project options and information. The names of the columns that hold this information contain a qualifier that indicates where this data is set. These qualifiers and their meanings are listed below:

Qualifier Description
OPT_ Options set in the Tools/Options dialog box
PROP_ Properties set in the File/Properties dialog box
INFO_ Project information set in the Project/Project Information dialog box
EXT_ Indicators that flag externally edited data box

Column name abbreviations

In order to keep the word components of column names short and consistent, the following abbreviations are used when naming columns:

Abbreviation Word
ACT Actual
AVAIL Available
BASE Baseline
CAL Calendar
COMP Complete
DEF Default
DUR Duration
EST Estimated
EXT Externally
FMT Format
LANG Language
MAX Maximum
NUM Number
OVT Overtime
PCT Percent
PRED Predecessor
PROJ Project
REF Reference
REG Regular
REM Remaining
RES Resource
SCHED Schedule
STD Standard
SUCC Successor
UID Unique ID
VAR Variance
Top

Database Tables

Information-only columns

Some fields contain data that is written by Project upon exporting to a database but is not read while importing project data into Project. Usually, this data is the result of calculations or settings in Project. If you edit this data directly in the database, the changes will not be reflected when you import the data in Project. These fields are indicated as I in the table descriptions.

Top

Bold column names

Field names in the table descriptions that are formatted in bold indicate a column (field) that is common among more than one table.

Top

Column data types

The following data types are used in the Project database structure:

Data Type Description
bit Boolean where supported; Integer in database where Boolean is not supported.
char(n) Character; (n) indicates the maximum allowed characters.
datetime Date including time.
decimal Decimal.
image Binary data greater than 255 bytes.
integer Integer.
smallint SQL Server system data type that holds whole numbers from -2^31 (-2,147,483,647) through 2^31-1 (2,147,483,647). Its storage size is 2 bytes.
tinyint SQL Server system data type that holds whole numbers from 0 through 255. Its storage size is 1 byte.
text Longest text field available; 8000 bytes in SQL Server, 64k in Microsoft Access.
varchar(n) Variable-length character text unless otherwise indicated; (n) indicates the maximum allowed characters.

Where Project needs to store a time value, but not a date value, the time value is stored as a date. When you read the project from the database, the date component in these fields is ignored.

Top

MSP_ASSIGNMENT_BASELINES

This table contains custom assignment baseline data.

Column Name Data Type Description
PROJ_ID integer Refers to a valid ID in the MSP_PROJECTS table.
ASSN_UID integer Refers to a valid ID in the MSP_ASSIGNMENTS table.
AB_REF_ENT_PROJ_ID integer Refers to a valid PROJ_ID in the MSP_PROJECTS table if the assignment baseline data is part of a summary assignment.
AB_BASE_NUM smallint The number of the custom baseline, 1-10.
AB_BASE_START datetime The planned beginning date for an assignment at the time a baseline is saved.
AB_BASE_FINISH datetime The planned finish date for assignments.
AB_BASE_WORK decimal The originally planned amount of work time to be performed by a resource on a task.
AB_BASE_COST decimal The total planned cost for work to be performed by a resource on a task.
AB_BASE_COST_PER_USE decimal The cost-per-use for the custom baseline, 1-10.
Top

MSP_ASSIGNMENTS

This table contains assignment data. In addition to rows for tasks with assigned resources, Project will write rows for tasks without assigned resources (null assignments).

Column Name Data Type Description
RESERVED_DATA char(1) Temporarily stores information unique to the particular database row; you should not modify the contents of this field.
PROJ_ID integer Refers to a valid ID in the MSP_PROJECTS table.
ASSN_ENTSUMPROJID integer Contains an ID of the project to which a summary assignment belongs.
I ASSN_ACT_FINISH datetime The date and time when an assignment was actually completed.
I ASSN_ACT_START datetime The date and time that an assignment actually began.
I ASSN_ACWP decimal The costs incurred for work already performed by a resource on a task up to the project status date or today's date; also called Actual Cost of Work Performed.
I ASSN_BCWP decimal The cumulative value of the assignment's timephased percentage of work complete multiplied by the assignment's timephased baseline cost up to the status date or today's date; also known as Earned Value.
I ASSN_BCWS decimal The cumulative timephased baseline costs up to the status date or today's date.
ASSN_RES_TYPE bit The resource type:
0 Material; consumable supplies like steel, concrete, or soil
1 Work (default); people and equipment
I ASSN_IS_OVERALLOCATED bit Indicates whether a resource is assigned to more work on a specific task than can be done within the resource's normal working capacity.
I ASSN_WORK_CONTOUR smallint Indicates how work for an assignment is to be distributed across the duration of the assignment:
0 Flat (default)
1 Back Loaded
2 Front Loaded
3 Double Peak
4 Early Peak
5 Late Peak
6 Bell
7 Turtle
8 Contoured
I ASSN_START_VAR integer The difference between an assignment's baseline start date and its currently scheduled start date.
I ASSN_FINISH_VAR integer The difference between an assignment's baseline finish date and its scheduled finish date.
I ASSN_UPDATE_NEEDED bit Indicates whether a TeamUpdate message should be sent to the resource assigned to a task because of changes to the start date, finish date, or resource reassignments.
EXT_EDIT_REF_DATA text Set to indicate a new row or back up existing data when changing project information in the database.
ASSN_UID integer The unique ID for the assignment.
ASSN_HAS_LINKED_FIELDS bit Indicates whether there are OLE links to the assignment.
ASSN_IS_CONFIRMED bit Indicates whether a resource assigned to a task has accepted or rejected the task assignment in response to a TeamAssign message.
ASSN_RESPONSE_PENDING bit Indicates whether an answer has been received from a TeamAssign message sent to a resource assigned to a task.
ASSN_HAS_NOTES bit Indicates whether an assignment has RTF notes.
ASSN_TEAM_STATUS_PENDING bit Indicates whether a status message has been received in response to a TeamStatus message sent to a resource assigned to a task.
TASK_UID integer Refers to a valid ID in the MSP_TASKS table.
RES_UID integer Refers to a valid ID in the MSP_RESOURCES table.
ASSN_START_DATE datetime The date and time that an assigned resource is scheduled to begin working on a task.
ASSN_FINISH_DATE datetime The date and time that an assigned resource is scheduled to complete work on a task.
ASSN_DELAY integer The amount of time a resource is to wait after the task start date before starting work on an assignment.
ASSN_DELAY_FMT smallint Indicates the delay format:
3 minute
5 hour (default)
7 day
9 week
11 month
ASSN_LEVELING_DELAY integer The amount of time that an assignment is to be delayed from the scheduled start date as a result of resource leveling.
ASSN_COST_RATE_TABLE smallint Indicates which cost rate table to use for a resource on an assignment:
0 A (default)
1 B
2 C
3 D
4 E
ASSN_BASE_START datetime The planned beginning date for an assignment at the time a baseline is saved.
ASSN_BASE_FINISH datetime The planned finish date for assignments.
ASSN_MATERIAL_RATE_FMT smallint The units in which the material rate is displayed in Project. See Cost Rate Data Units in MSP_STRING_TYPES for rate formats.
ASSN_UNITS decimal The number of units for which a resource is assigned to a task, expressed as a percentage of 100 percent (%), assuming a resource's MaxUnits value is 100 percent.
ASSN_WORK decimal The total amount of work scheduled to be performed by a resource on a task.
ASSN_ACT_WORK decimal The amount of work that has already been done by a resource on a task.
ASSN_REG_WORK decimal The total amount of non-overtime work scheduled to be performed by a resource assigned to a task.
ASSN_REM_WORK decimal The amount of time required by a resource assigned to a task to complete an assignment.
ASSN_BASE_WORK decimal The originally planned amount of work time to be performed by a resource on a task.
I ASSN_COST decimal The total scheduled (or projected) cost for an assignment based on costs already incurred for work performed by the resource on a task, in addition to the costs planned for the remaining work for the assignment.
I ASSN_ACT_COST decimal The costs incurred for work already performed by a resource on a task.
I ASSN_REM_COST decimal The costs associated with completing all remaining scheduled work by any resources on a specific task.
ASSN_BASE_COST decimal The total planned cost for work to be performed by a resource on a task.
ASSN_BASE_COST_PER_USE decimal The baseline cost-per-use.
ASSN_OVT_WORK decimal The amount of overtime to be performed by a resource on a task; charged at the resource's overtime rate.
ASSN_ACT_OVT_WORK decimal The actual amount of overtime work already performed by a resource on an assigned task.
ASSN_REM_OVT_WORK decimal The amount of overtime work that remains on an assignment.
I ASSN_ACT_OVT_COST decimal The costs incurred for overtime work already performed by a resource on a task.
I ASSN_REM_OVT_COST decimal The remaining scheduled overtime expense for an assignment.
ASSN_RTF_NOTES image Contains notes about an assignment.
ASSN_BOOKING_TYPE smallint Indicates the booking type of an assignment:
0 Hard
2 Soft
ASSN_ACT_WORK_PROT decimal The actual work that has been protected.
ASSN_ACT_OVT_WORK_PROT decimal The actual overtime work that has been protected.
ASSN_CREATION_DATE datetime The date the assignment was created.
Top

MSP_ASSN_ENTERPRISE

This table is similar to the MSP_ASSIGNMENTS table in function and overall purpose except that assignments contained in this table are project summary assignments and refer to the Resource global project; all assignments that do not refer to the Resource global project (e.g. non summary assignments) are contained in the MSP_ASSIGNMENTS table.

NoteB B This table is only used by Project when saving enterprise projects to the Project Server database.

Column Name Data Type Description
RESERVED_DATA char(1) Temporarily stores information unique to the particular database row; you should not modify the contents of this field.
PROJ_ID integer Refers to a valid ID in the MSP_PROJECTS table.
ASSN_ENTSUMPROJID integer Contains an ID of the project to which a summary assignment belongs.
ASSN_ACT_FINISH datetime The date and time when an assignment was actually completed.
ASSN_ACT_START datetime The date and time that an assignment actually began.
I ASSN_ACWP decimal The costs incurred for work already performed by a resource on a task up to the project status date or today's date; also called Actual Cost of Work Performed.
I ASSN_BCWP decimal The cumulative value of the assignment's timephased percentage of work complete multiplied by the assignment's timephased baseline cost up to the status date or today's date; also known as Earned Value.
I ASSN_BCWS decimal The cumulative timephased baseline costs up to the status date or today's date.
ASSN_RES_TYPE bit The resource type:
0 Material; consumable supplies like steel, concrete, or soil
1 Work (default); people and equipment
I ASSN_IS_OVERALLOCATED bit Indicates whether a resource is assigned to more work on a specific task than can be done within the resource's normal working capacity.
I ASSN_WORK_CONTOUR smallint Indicates how work for an assignment is to be distributed across the duration of the assignment:
0 Flat (default)
1 Back Loaded
2 Front Loaded
3 Double Peak
4 Early Peak
5 Late Peak
6 Bell
7 Turtle
8 Contoured
I ASSN_START_VAR integer The difference between an assignment's baseline start date and its currently scheduled start date.
I ASSN_FINISH_VAR integer The difference between an assignment's baseline finish date and its scheduled finish date.
I ASSN_UPDATE_NEEDED bit Indicates whether a TeamUpdate message should be sent to the resource assigned to a task because of changes to the start date, finish date, or resource reassignments.
EXT_EDIT_REF_DATA text Set to indicate a new row or back up existing data when changing project information in the database.
ASSN_UID integer The unique ID for the assignment.
ASSN_HAS_LINKED_FIELDS bit Indicates whether there are OLE links to the assignment.
ASSN_IS_CONFIRMED bit Indicates whether a resource assigned to a task has accepted or rejected the task assignment in response to a TeamAssign message.
ASSN_RESPONSE_PENDING bit Indicates whether an answer has been received from a TeamAssign message sent to a resource assigned to a task.
ASSN_HAS_NOTES bit Indicates whether an assignment has RTF notes.
ASSN_TEAM_STATUS_PENDING bit Indicates whether a status message has been received in response to a TeamStatus message sent to a resource assigned to a task.
TASK_UID integer Refers to a valid ID in the MSP_TASKS table.
RES_UID integer Refers to a valid ID in the MSP_RESOURCES table.
ASSN_START_DATE datetime The date and time that an assigned resource is scheduled to begin working on a task.
ASSN_FINISH_DATE datetime The date and time that an assigned resource is scheduled to complete work on a task.
ASSN_DELAY integer The amount of time a resource is to wait after the task start date before starting work on an assignment.
ASSN_DELAY_FMT smallint Indicates the delay format:
3 minute
5 hour (default)
7 day
9 week
11 month
ASSN_LEVELING_DELAY integer The amount of time that an assignment is to be delayed from the scheduled start date as a result of resource leveling.
ASSN_COST_RATE_TABLE smallint Indicates which cost rate table to use for a resource on an assignment:
0 A (default)
1 B
2 C
3 D
4 E
ASSN_BASE_START datetime The planned beginning date for an assignment at the time a baseline is saved.
ASSN_BASE_FINISH datetime The planned finish date for assignments.
ASSN_MATERIAL_RATE_FMT smallint The units in which the material rate is displayed in Project. See Cost Rate Data Units in MSP_STRING_TYPES for rate formats.
ASSN_UNITS decimal The number of units for which a resource is assigned to a task, expressed as a percentage of 100 percent (%), assuming a resource's MaxUnits value is 100 percent.
ASSN_WORK decimal The total amount of work scheduled to be performed by a resource on a task.
ASSN_ACT_WORK decimal The amount of work that has already been done by a resource on a task.
ASSN_REG_WORK decimal The total amount of non-overtime work scheduled to be performed by a resource assigned to a task.
ASSN_REM_WORK decimal The amount of time required by a resource assigned to a task to complete an assignment.
ASSN_BASE_WORK decimal The originally planned amount of work time to be performed by a resource on a task.
I ASSN_COST decimal The total scheduled (or projected) cost for an assignment based on costs already incurred for work performed by the resource on a task, in addition to the costs planned for the remaining work for the assignment.
I ASSN_ACT_COST decimal The costs incurred for work already performed by a resource on a task.
I ASSN_REM_COST decimal The costs associated with completing all remaining scheduled work by any resources on a specific task.
ASSN_BASE_COST decimal The total planned cost for work to be performed by a resource on a task.
ASSN_BASE_COST_PER_USE decimal The baseline cost-per-use.
ASSN_OVT_WORK decimal The amount of overtime to be performed by a resource on a task; charged at the resource's overtime rate.
ASSN_ACT_OVT_WORK decimal The actual amount of overtime work already performed by a resource on an assigned task.
ASSN_REM_OVT_WORK decimal The amount of overtime work that remains on an assignment.
I ASSN_ACT_OVT_COST decimal The costs incurred for overtime work already performed by a resource on a task.
I ASSN_REM_OVT_COST decimal The remaining scheduled overtime expense for an assignment.
ASSN_RTF_NOTES image Contains notes about an assignment.
ASSN_BOOKING_TYPE smallint Indicates the booking type of an assignment:
0 Hard
2 Soft
ASSN_ACT_WORK_PROT decimal The actual work that has been protected.
ASSN_ACT_OVT_WORK_PROT decimal The actual overtime work that has been protected.
ASSN_CREATION_DATE datetime The date the assignment UID was created.
ASSN_PROJ_VERSION_ID integer Maps the summary record assignment row back to the version ID of the project that this summary record assignment is for.
ASSN_DEFAULT_VERSION_PROJ_ID integer Maps the summary record assignment row back to the project ID of the default version of this project. If the project in question is the default version, then this row will be the ID of the project that the summary record assignment refers to.
Top

MSP_ATTRIBUTE_STRINGS

This table stores custom WBS code definition, alias, and formulae defined in MSP_FIELD_ATTRIBUTES.

Column Name Data Type Description
RESERVED_DATA char(1) Temporarily stores information unique to the particular database row; you should not modify the contents of this field.
PROJ_ID integer Refers to a valid ID in the MSP_PROJECTS table.
AS_ID integer The unique ID for the attribute string.
AS_POSITION integer Indicates the position of a resource pool sharer in the list of sharer projects.
AS_VALUE varchar(255) The formula as expressed in the Formula dialog box in Project.
Top

MSP_AVAILABILITY

This table contains data about resource availability and tracks the time and availability of any particular resource within a project.

Column Name Data Type Description
RESERVED_DATA char(1) Temporarily stores information unique to the particular database row; you should not modify the contents of this field.
PROJ_ID integer Refers to a valid ID in the MSP_PROJECTS table.
AVAIL_UID integer The unique ID for a resource's availability.
RES_UID integer Refers to a valid ID in the MSP_RESOURCES table.
AVAIL_FROM datetime The starting date that a resource is available for work at the units specified for the current time period.
AVAIL_TO datetime The ending date that a resource will be available for work at the units specified for the current time period.
AVAIL_UNITS decimal The number of units for which a resource is assigned to a task, expressed as a percentage of 100 percent (%), assuming a resource's MaxUnits value is 100%.
Top

MSP_CALENDAR_DATA

This table contains calendar working day, non-working day, and exception data.

NoteB B Be careful not to overlap shift times when creating or editing a working day record in this table. If any times overlap, the entire day will be marked as a non-working day.

Column Name Data Type Description
RESERVED_DATA char(1) Temporarily stores information unique to the particular database row; you should not modify the contents of this field.
PROJ_ID integer Refers to a valid ID in the MSP_PROJECTS table.
CD_UID integer The unique ID for calendar working, non-working, and exception data.
CAL_UID integer Refers to a valid ID in the MSP_CALENDARS table.
CD_DAY_OR_EXCEPTION smallint Indicates whether the calendar has a defined working day or whether it is an exception:
0 Exception
1 Sunday
2 Monday
3 Tuesday
4 Wednesday
5 Thursday
6 Friday
7 Saturday
CD_WORKING smallint Indicates whether the selected days are working or non-working days.
CD_FROM_DATE datetime The date the exception begins.
CD_TO_DATE datetime The date the exception ends.
CD_FROM_TIME1 - 5 datetime The starting day and time of the working day (up to five periods per day are supported); not used if CD_DAY_OR_EXCEPTION is set to "0" (Exception).
CD_TO_TIME1 - 5 datetime The ending day and time of the working day (up to five periods per day are supported); not used if CD_DAY_OR_EXCEPTION is set to "0" (Exception).
Top

MSP_CALENDARS

This table stores basic calendar data. Calendars are used to define standard working and non-working times. Projects must have one base calendar. Tasks and resources may have their own non-base calendars, but any non-base calendar must be based on a base calendar. Project will write six rows to this table with unique IDs of 1, 2, -65536, -65535, -65534, and -65533. Project uses these rows internally and they should not be edited or deleted.

Column Name Data Type Description
RESERVED_DATA char(1) Temporarily stores information unique to the particular database row; you should not modify the contents of this field.
PROJ_ID integer Refers to a valid ID in the MSP_PROJECTS table.
CAL_UID integer The unique ID for the calendar.
CAL_BASE_UID integer Refers a calendar to its parent base calendar; required for all resource calendars. If this calendar is a base calendar, its value is -1.
RES_UID integer Refers to a valid ID in the MSP_RESOURCES table.
CAL_IS_BASE_CAL bit Indicates whether this calendar is a base calendar; a resource calendar cannot be a base calendar.
CAL_NAME varchar(255) The name of the calendar; empty if this calendar is a resource calendar, required if the calendar is a base calendar.
CAL_ENTUID integer The enterprise-level ID for a calendar.
Top

MSP_CODE_FIELDS

This table links custom outline code fields to the appropriate row in the MSP_TASKS and MSP_RESOURCES tables.

Column Name Data Type Description
PROJ_ID integer Refers to a valid ID in the MSP_PROJECTS table.
CODE_CATEGORY integer Indicates whether the custom outline code is a task or a resource.
CODE_REF_UID integer Refers to a valid TASK_UID, or RES_UID in its respective table, as determined by CODE_CATEGORY.
CODE_FIELD_ID integer Refers to a valid Field ID found in the CONV_VALUE column in the MSP_CONVERSIONS table.
CODE_UID integer Refers to a valid ID in the MSP_OUTLINE_CODES table.
Top

MSP_CONVERSIONS

This table contains all of the Project character strings and links the Project database to the Tasks, Resources, and Assignments tables available from the Project OLE DB provider.

Column Name Data Type Description
I STRING_TYPE_ID integer Refers to a valid STRING_TYPE_ID in the MSP_STRING_TYPES table.
I CONV_VALUE integer The value seen in the OLE database fields; depends on the value selected in STRING_TYPE_ID:
=3 0 = FF (finish-to-finish)
1 = FS (finish-to-start)
2 = SF (start-to-finish)
3 = SS (start-to-start)
=14 0 = Flat
1 = Back loaded
2 = Front loaded
3 = Double peak
4 = Early peak
5 = Late peak
6 = Bell
7 = Turtle
8 = Contoured
=29 0 = Fixed units
1 = Fixed duration
2 = Fixed work
=56 0 = Exception
1 = Sunday
2 = Monday
3 = Tuesday
4 = Wednesday
5 = Thursday
6 = Friday
7 = Saturday
=57 3 = m
4 = em
5 = h
6 = eh
7 = d
8 = ed
9 = w
10 = ew
11 = mo
12 = emo
19 = %
20 = e%
21 = ' '
35 = m?
36 = em?
37 = h?
38 = eh?
39 = d?
40 = ed?
41 = w?
42 = ew?
43 = mo?
44 = emo?
51 = %?
52 = e%?
53 = ' '
=68 0 = As soon as possible
1 = As late as possible
2 = Must start on
3 = Must finish on
4 = Start no earlier than
5 = Start no later than
6 = Finish no earlier than
7 = Finish no later than
=70 1 = Start
2 = End
=78 1 = m
2 = h
3 = d
4 = w
5 = mo
7 = y
=81 0 = Default
1 = None
2 = E-mail only
3 = Project Server
=83 1 = Su
2 = Mo
3 = Tu
4 = We
5 = Th
6 = Fr
7 = Sa
=84 1 = (Su)
2 = (Mo)
3 = (Tu)
4 = (We)
5 = (Th)
6 = (Fr)
7 = (Sa)
=85 1 = Sunday
2 = Monday
3 = Tuesday
4 = Wednesday
5 = Thursday
6 = Friday
7 = Saturday
=100 0 = Task
1 = Resource
2 = Calendar
3 = Assignment
4 = Dependencies
=101 0 = Schedule from project finish
1 = Schedule from project start
=102 0 = Non-working
1 = Working
2 = Default
=103 0 = Before
1 = After
2 = Before with space
3 = After with space
=104 0 = Default
1 = None
2 = E-mail only
3 = Project Server
=105 Variable. Relates to Assignments, Resources, and Tasks.
=106 1 = Outline code and WBS mask
2 = Only allow outline codes listed in the lookup table
3 = Only allow new outline codes with values at all levels of mask
4 = Verify uniqueness of new WBS codes
5 = WBS prefix
6 = Generate WBS code for new tasks
7 = Field alias
8 = Custom field formula
9 = Sharer file name
=107 200 = Assignment remaining work
201 = Assignment actual work
202 = Assignment actual overtime work
203 = Assignment baseline work
204 = Assignment baseline cost
205 = Assignment actual cost
206 = Resource baseline work
207 = Resource baseline cost
208 = Task baseline work
209 = Task baseline cost
210 = Task percent complete
=108 0 = m
1 = h
2 = d
3 = w
5 = mo
8 = y
I CONV_LANG_ID integer The ID of the language in which the conversion text is displayed, for example: 1033 = English, the default language for the database.
I CONV_STRING varchar(200) The text value of the field. For example, if STRING_TYPE_ID = 68, CONV_VALUE = 0, and CONV_LANG_ID = 1033, the contents of this field would be As soon as possible.
Top

MSP_DATE_FIELDS

This table contains custom date data.

Column Name Data Type Description
PROJ_ID integer Refers to a valid ID in the MSP_PROJECTS table.
DATE_CATEGORY integer Indicates whether the custom date category is a Task, Resource, or Assignment:
0 Task
1 Resource
3 Assignment
DATE_REF_UID integer Refers to a valid TASK_UID, RES_UID, or ASSN_UID in its respective table, as determined by DATE_CATEGORY.
DATE_FIELD_ID integer Refers to a valid Field ID found in the CONV_VALUE column in the MSP_CONVERSIONS table.
DATE_VALUE datetime The value of the custom date field.
Top

MSP_DURATION_FIELDS

This table contains custom duration data.

Column Name Data Type Description
PROJ_ID integer Refers to a valid ID in the MSP_PROJECTS table.
DUR_CATEGORY integer Indicates whether the custom duration category is a Task, Resource, or Assignment:
0 Task
1 Resource
3 Assignment
DUR_REF_UID integer Refers to a valid TASK_UID, RES_UID, or ASSN_UID in its respective table, as determined by DUR_CATEGORY.
DUR_FIELD_ID integer Refers to a valid Field ID found in the CONV_VALUE column in the MSP_CONVERSIONS table.
DUR_VALUE integer The value of the custom duration field.
DUR_FMT smallint Indicates the units to use when displaying durations in Project:
3 minute
5 hour (default)
7 day
9 week
11 month
Top

MSP_FIELD_ATTRIBUTES

This table contains data about field attributes such as custom WBS, custom field name aliases, and custom field formulae.

Column Name Data Type Description
RESERVED_DATA char Temporarily stores information unique to the particular database row; you should not modify the contents of this field.
PROJ_ID integer Refers to a valid ID in the MSP_PROJECTS table.
ATTRIB_FIELD_ID integer Refers to the column that the formula affects; see MSP_STRING_TYPES and MSP_CONVERSIONS for 'Field ID'.
ATTRIB_ID integer Refers to the type of field attribute; see MSP_STRING_TYPES and MSP_CONVERSIONS for 'Field Attributes'.
ATTRIB_VALUE integer The value of the attribute; a value of -1 indicates that the value is stored in the MSP_ATTRIBUTE_STRINGS table.
AS_ID integer Refers to valid ID in the MSP_ATTRIBUTE_STRINGS table; ignore if ATTRIB_VALUE is set to anything other than -1, which specifies the value of an attribute.
ATTRIB_EXT_EDITED bit Indicates that the record has been externally edited.
Top

MSP_FLAG_FIELDS

This table contains information about custom flags.

Column Name Data Type Description
PROJ_ID integer Refers to a valid ID in the MSP_PROJECTS table.
FLAG_CATEGORY integer Indicates whether the custom flag category is a Task, Resource, or Assignment:
0 Task
1 Resource
3 Assignment
FLAG_REF_UID integer Refers to a valid TASK_UID, RES_UID, or ASSN_UID in its respective table, as determined by FLAG_CATEGORY.
FLAG_FIELD_ID integer Refers to a valid Field ID found in the CONV_VALUE column in the MSP_CONVERSIONS table.
FLAG_VALUE bit The value of the custom flag field.
Top

MSP_LINKS

This table stores cross-project links, which allows a project to link with any other project in the enterprise.

Column Name Data Type Description
RESERVED_DATA char Temporarily stores information unique to the particular database row; you should not modify the contents of this field.
PROJ_ID integer Refers to a valid ID in the MSP_PROJECTS table.
EXT_EDIT_REF_DATA text Set to indicate a new row or back up existing data when changing project information in the database.
LINK_UID integer The unique ID for the link.
I LINK_IS_CROSS_PROJ bit Indicates whether a link is a cross-project link (stored in the MSP_TASKS table).
LINK_PRED_UID integer The unique ID for the predecessor task; must be unique within the project and must refer to a valid row for the same PROJ_ID in the MSP_TASKS table.
LINK_SUCC_UID integer The unique ID for the successor task; must be unique within the project and must refer to a valid row for the same PROJ_ID in the MSP_TASKS table.
LINK_TYPE smallint Indicates the type of link:
0 FF (finish-to-finish)
1 FS (finish-to-start)
2 SF (start-to-finish)
3 SS (start-to-start)
LINK_LAG_FMT smallint Indicates the format for the amount of lag specified in LINK_LAG:
3 m
4 em
5 h
6 eh
7 d (default)
8 ed
9 w
10 ew
11 mo
12 emo
19 %
20 e%
35 m?
36 em?
37 h?
38 eh?
39 d?
40 ed?
41 w?
42 ew?
43 mo?
44 emo?
51 %?
52 e%?
LINK_LAG integer Indicates the amount of lag, specified as duration value (minutes * 10).
Top

MSP_MV_FIELDS

This table comtains resource multi-value code information.

NoteB B This table is only used by Project when saving enterprise projects to the Project Server database.

Column Name Data Type Description
RESERVED_DATA char(1) Temporarily stores information unique to the particular database row; you should not modify the contents of this field.
PROJ_ID integer Refers to a valid ID in the MSP_PROJECTS table.
CODE_MV_UID integer The unique ID of the multi-value custom code.
CODE_CATEGORY integer Indicates that the custom multi-value outline code category is for a Resource:
1 Resource
CODE_REF_UID integer Refers to a valid TASK_UID, RES_UID, or ASSN_UID in its respective table, as determined by NUM_CATEGORY.
CODE_FIELD_ID integer Refers to a valid Field ID found in the CONV_VALUE column in the MSP_CONVERSIONS table.
CODE_UID integer Refers to a valid ID in the MSP_CODE_FIELDS table.
Top

MSP_NUMBER_FIELDS

This table contains custom number data.

Column Name Data Type Description
PROJ_ID integer Refers to a valid ID in the MSP_PROJECTS table.
NUM_CATEGORY integer Indicates whether the custom number category is a Task, Resource, or Assignment:
0 Task
1 Resource
3 Assignment
NUM_REF_UID integer Refers to a valid TASK_UID, RES_UID, or ASSN_UID in its respective table, as determined by NUM_CATEGORY.
NUM_FIELD_ID integer Refers to a valid Field ID found in the CONV_VALUE column in the MSP_CONVERSIONS table.
NUM_VALUE decimal The value of the custom number field.
Top

MSP_OUTLINE_CODES

This table contains data about custom outline code definitions associated with a project. Each custom outline code must be unique within the project, but may also be common among several projects in the case of enterprise codes.

Column Name Data Type Description
RESERVED_DATA char Temporarily stores information unique to the particular database row; you should not modify the contents of this field.
PROJ_ID integer Refers to a valid ID in the MSP_PROJECTS table.
CODE_UID integer The unique ID for a custom outline code.
OC_PARENT integer Refers to a valid ID in the MSP_OUTLINE_CODES table; set to 0 for top-level codes without parents.
OC_FIELD_ID integer The ID of the task or resource custom outline code; required for enterprise.
OC_NAME text The name of the custom outline code.
OC_DESCRIPTION text Contains a description of the code being defined.
OC_CACHED_FULL_NAME text The full outline code value, including all levels above; this value is generated during publish when the enterprise global is checked in.
OC_CACHED_IS_VALID bit Indicates whether the outline code is valid; only valid outline codes will be made available for Project and Project Web Access users.
Top

MSP_PROJ_SECURITY

This table enforces security in the MSP_PROJECTS table and is maintained by the Project Data Service (PDS), which enforces either read-only or read/write access on this table. This table should only have data in it during a read or write operation from the Project client, therefore, this table should normally appear empty.

NoteB B  This table is not accessed directly by Project and should not be modified.

Column Name Data Type Description
PROJ_ID integer Refers to a valid ID in the MSP_PROJECTS table.
SEC_SPID varchar(255) The SQL Process ID (SPID).
SEC_SPIDDATESTAMP datetime The creation date of the active SPID utilized in this security row.
SEC_READCOUNT integer Represents the current number of authorized read accesses to this particular project from the particular user:
0 No users (default)
1 One user; The PDS enforces a single READWRITE access for a project for a particular time
SEC_WRITECOUNT integer Represents the current number of authorized write accesses to this particular project from the particular user. This value should never be greater than 1.
Top

MSP_PROJECTS

This table stores the information about projects including project-level settings.

Column Name Data Type Description
PROJ_ID integer The unique ID for a project.
PROJ_NAME varchar(255) The name of the project (it displays in the project window title bar) and must be unique within this table; together with the data source name, cannot exceed 255 characters.

NoteB B The enterprise version does not include data source name; PROJ_PROJECT.PROJ_VERSION ensures that this is a unique name

PROJ_PROP_AUTHOR varchar(255) The name of the author of the project; used to group similar projects together.
PROJ_OPT_CALC_ACT_COSTS bit Indicates whether Project should automatically calculate actual costs.
PROJ_PROP_CATEGORY varchar(255) The category the project belongs to; used to group similar projects together.
PROJ_PROP_COMPANY varchar(255) The name of the company that created the project; used to group similar projects together.
PROJ_OPT_CRITICAL_SLACK_LIMIT integer The number of days past its end date that a task can go before Project marks that task as a critical task.
PROJ_OPT_CURRENCY_DIGITS smallint The number of digits that are to appear after the decimal when currency values are shown in Project:
0 No digits after the decimal; $0
1 One digit after the decimal; $0.0
2 Two digits after the decimal (default); $0.00
PROJ_OPT_CURRENCY_POSITION smallint Indicates the placement of the currency symbol in relation to the currency value:
0 Before, no space (default); $0
1 After, no space; 0$
2 Before, with space; $ 0
3 After, with space; 0 $
PROJ_OPT_CURRENCY_SYMBOL varchar(20) The current symbol used to represent the type of currency used in the project.
PROJ_OPT_NEW_ARE_EFFORT_DRIVEN bit Indicates whether new tasks are effort-driven.
I PROJ_INFO_CURRENT_DATE datetime The current date for a project.
PROJ_OPT_DEF_FINISH_TIME datetime The default finish time for all new tasks.
PROJ_OPT_DEF_FIX_COST_ACCRUAL smallint Indicates whether fixed costs are accrued.
PROJ_OPT_MINUTES_PER_DAY integer The default number of minutes per day.
PROJ_OPT_MINUTES_PER_WEEK integer The default number of minutes per week.
PROJ_OPT_DEF_OVT_RATE decimal The default overtime rate of pay for new resources; see MSP_RESOURCES.RES_DEF_OVT_RATE.
PROJ_OPT_DEF_STD_RATE decimal The default rate of pay for new resources; see MSP_RESOURCES.RES_DEF_STD_RATE.
PROJ_OPT_DEF_START_TIME datetime The default start time for all new tasks.
PROJ_OPT_DEF_TASK_TYPE smallint The default type for all tasks in the project:
0 Fixed units (default); the number of Assignment Units remains constant, regardless of the amount of work or duration on the task
1 Fixed duration; the duration of the task remains constant, regardless of the number of resources (Assignment Units) assigned or the amount of work
2 Fixed work; the amount of work remains constant, regardless of any change in duration or the number of resources (Assignment Units) assigned to the task
PROJ_OPT_DUR_ENTRY_FMT smallint The default format for all durations in the project:
3 minute
5 hour (default)
7 day
9 week
11 month
PROJ_INFO_FINISH_DATE datetime The date and time that a project is scheduled for completion.
PROJ_OPT_HONOR_CONSTRAINTS bit Indicates whether Project schedules tasks according to their constraint date.
PROJ_PROP_KEYWORDS varchar(255) Lists keywords associated with the project; used to group similar projects together.
PROJ_LAST_SAVED datetime The date the project was last saved.
PROJ_PROP_MANAGER varchar(255) The manager of the project; used to group similar projects together.
PROJ_OPT_MULT_CRITICAL_PATHS bit Indicates whether Project calculates and displays a critical path for each independent network of tasks within a project.
PROJ_CREATION_DATE datetime The date that the project was created.
I PROJ_POOL_ATTACHED_TO varchar(255) The name of the project file that shares resources with this project file; required if PROJ_IS_RES_POOL is set to 1.
PROJ_INFO_CAL_NAME varchar(255) The name of the calendar associated with the project.
I PROJ_IS_RES_POOL bit Indicates whether the project is its own resource pool or whether it shares its resources with another project; see PROJ_POOL_ATTACHED_TO.
PROJ_INFO_SCHED_FROM smallint Indicates whether a project is scheduled from the "Project Start Date" (default) or the "Project Finish Date".
PROJ_OPT_SPLIT_IN_PROGRESS bit Indicates whether in-progress tasks may be split.
PROJ_OPT_SPREAD_ACT_COSTS bit Indicates whether actual costs are spread to the status date.
PROJ_OPT_SPREAD_PCT_COMP bit Indicates whether percent complete is spread to the status date.
PROJ_INFO_START_DATE datetime The date and time that a project is scheduled to begin.
PROJ_INFO_STATUS_DATE datetime The project status date.
PROJ_PROP_SUBJECT varchar(255) The subject of the project; used to group similar projects together.
PROJ_PROP_TITLE varchar(255) The title of the project; used to group similar projects together.
PROJ_OPT_TASK_UPDATES_RES bit Indicates whether Project automatically calculates actual and remaining work and costs as you enter task percent complete information in your schedule.
PROJ_OPT_WORK_ENTRY_FMT smallint The default format for all work durations in the project:
3 minute
5 hour (default)
7 day
9 week
11 month
PROJ_OPT_CALC_SUB_AS_SUMMARY bit Indicates whether Project calculates sub-tasks as summary tasks.
PROJ_OPT_WEEK_START_DAY smallint The day of the week that a work week starts on:
0 Sunday (default)
1 Monday
2 Tuesday
3 Wednesday
4 Thursday
5 Friday
6 Saturday
PROJ_OPT_FY_START_MONTH smallint The month that the fiscal year begins:
1 January (default)
2 February
3 March
4 April
5 May
6 June
7 July
8 August
9 September
10 October
11 November
12 December
PROJ_OPT_FY_USE_START_YEAR bit Indicates whether to use the fiscal starting year.
PROJ_OPT_DAYS_PER_MONTH smallint The default number of working days per month.
PROJ_OPT_NEW_TASK_EST bit Indicates whether new tasks have estimated durations.
PROJ_OPT_SHOW_EST_DUR bit Indicates whether Project displays a ? after any task with an estimated duration.
I PROJ_OPT_EXPAND_TIMEPHASED bit Indicates whether Project saves timephased data in a readable or binary format when saved to a database; see MSP_TIMEPHASED_DATA.
PROJ_EXT_EDITED_DATE bit Indicates whether the custom date field was edited externally.
PROJ_EXT_EDITED_DUR bit Indicates whether the custom duration field was edited externally.
PROJ_EXT_EDITED_NUM bit Indicates whether the custom number field was edited externally.
PROJ_EXT_EDITED_FLAG bit Indicates whether the custom flag field was edited externally.
PROJ_EXT_EDITED_CODE bit Indicates whether the custom outline code field was edited externally.
PROJ_EXT_EDITED_TEXT bit Indicates whether the custom text field was edited externally.
PROJ_EXT_EDITED bit Indicates whether the project was edited externally.
I PROJ_DATA_SOURCE varchar(128) The data source name for the project; used by Project when checking for concurrent usage.
I PROJ_READ_ONLY varchar(10) Indicates whether the project is read-only.
I PROJ_READ_WRITE varchar(4) Indicates whether the project is open for write.
I PROJ_READ_COUNT varchar(10) Indicates the number of users who have one or more tables open as read-only.
I PROJ_LOCKED varchar(4) Indicates whether a user can write to a table.
PROJ_MACHINE_ID varchar(100) The machine ID of the user who has opened the project.
I RESERVED_BINARY_DATA Image Reserved for use by Project; do not change the values in this field.
PROJ_TYPE integer The project type:
0 Projects (default)
1 Templates
2 Enterprise global template
3 Enterprise resource pool
100 Created regular project
101 Created project template
102 Created enterprise global
1002 Inactivated enterprise template
PROJ_CHECKEDOUT integer Indicates whether the project is checked out.
PROJ_CHECKOUTBY text(255) Value is the User ID of the user who last checked out the project. This value is not cleared when the project is checked back in, rather PROJ_CHECKEDOUT should return to 0 when the project is checked back in.
PROJ_CHECKOUTDATE datetime The timestamp when the project was last checked out. This value is not cleared when the project is checked back in, rather PROJ_CHECKEDOUT should return to 0 when the project is checked back in.
PROJ_PROJECT varchar(200) Stores the unique project name (indexed duplicates are OK) without the version suffix; there can be many versions of a project with the same value of Project.
PROJ_VERSION varchar(50) The name of the version, for example: Published.
PROJ_CREATION_DATE_EX datetime The date the project was created.
PROJ_ACTUALS_SYNC tinyint Indicates whether a project's actuals are synchronized with a project's protected actuals:
0 Actuals are not synchronized.
1 Actuals are synchronized.
PROJ_ADMINPROJECT tinyint Indicates whether the project is an administrative project.
PROJ_ENT_LIST_SEPARATOR varchar(2) The list separator used by enterprise multi-value outline codes, for example a comma (,).
Top

MSP_RES_GLOBAL_BINARY

This table stores binary data related to the global enterprise resource pool and is only used with Project and Project Server.

Column Name Data Type Description
PROJ_ID integer Refers to a valid ID in the MSP_PROJECTS table.
GLB_CATEGORY integer Indicates whether the global binary information is related to a Task, Resource, or Assignment:
0 Task
1 Resource
3 Assignment
GLB_REF_UID integer Refers to a valid TASK_UID, RES_UID, or ASSN_UID in its respective table, as determined by GLB_CATEGORY.
GLB_REF_ENT_PROJ_ID integer Refers to a valid ASSN_ENTSUMPROJID in the MSP_ASSIGNMENTS table.
GLB_BINARY_DATA Image This column saves out portions of the contents of the RESERVED_BINARY_DATA column in the MSP_PROJECTS table as binary data.
Top

MSP_RES_SECURITY

This table enforces security in the MSP_RESOURCES table and is maintained by the Project Data Service (PDS), which enforces either read-only or read/write access on this table. This table should only have data in it during a read or write operation from Project and, therefore, this table should normally appear empty.

NoteB B This table is not accessed directly by Project and should not be modified.

Column Name Data Type Description
ERUID integer Refers to a valid ID in the MSP_RESOURCES table.
SEC_SPID varchar(255) The SQL Process ID (SPID).
SEC_SPIDDATESTAMP datetime The creation date of the active SPID utilized in this security row.
SEC_READCOUNT tinyint Represents the current number of authorized read-accesses to this particular resource from the particular user:
0 No users (default)
1 One user; the PDS enforces a single READWRITE access for a particular time
SEC_WRITECOUNT tinyiny Represents the current number of authorized write-accesses to this particular resource from the particular user for updating the resource data. This value should never be greater than 1.
Top

MSP_RESGLOBAL_SECURITY

This table is used to synchronize resources and calendars when working with Project Server 2003 data from Microsoft Project Professional 2002.

Column Name Data Type Description
SEC_RES_UID integer Refers to a valid ID in the MSP_RESOURCES table.
SEC_CAL_UID integer Refers to a valid ID in the MSP_CALENDARS table.
Top

MSP_RESOURCE_BASELINES

This table contains custom resource baseline data.

Column Name Data Type Description
PROJ_ID integer Refers to a valid ID in the MSP_PROJECTS table.
RES_UID integer Refers to a valid ID in the MSP_RESOURCES table.
RB_BASE_NUM smallint The number of the custom baseline, 1-10.
RB_BASE_WORK decimal The originally planned amount of work time to be performed by a resource on a task.
RB_BASE_COST decimal The total planned cost for work to be performed by a resource on a task.
Top

MSP_RESOURCE_RATES

This table contains resource rate data.

Column Name Data Type Description
RESERVED_DATA char Temporarily stores information unique to the particular database row; you should not modify the contents of this field.
PROJ_ID integer Refers to a valid ID in the MSP_PROJECTS table.
RR_UID integer The unique ID for the resource rate.
RES_UID integer Refers to a valid ID in the MSP_RESOURCES table.
RR_RATE_TABLE smallint Indicates which cost rate table to use for a resource on an assignment:
0 A (default)
1 B
2 C
3 D
4 E
RR_FROM_DATE datetime The first date for which the resource rates are in effect.
RR_TO_DATE datetime The last date for which the resource rates are in effect.
RR_STD_RATE decimal The standard rate as entered in the selected cost rate table.
RR_STD_RATE_FMT smallint The units in which the standard rate is displayed in Project:
1 minute
2 hour (default)
3 day
4 week
5 month
7 year
RR_OVT_RATE decimal The overtime rate as entered in the selected cost rate table.
RR_OVT_RATE_FMT smallint The units in which the overtime rate is displayed in Project:
1 minute
2 hour (default)
3 day
4 week
5 month
7 year
RR_PER_USE_COST decimal The per use cost as entered in the selected cost rate table; for example, $ * 100.
Top

MSP_RESOURCES

This table stores the information about the resources that make up a project.

Project will write four rows to this table with unique IDs of zero (null resource required for task zero), -65536, -65535, and -65534. Project uses these rows internally and they should not be edited or deleted.

Column Name Data Type Description
RESERVED_DATA char Temporarily stores information unique to the particular database row; you should not modify the contents of this field.
PROJ_ID integer Refers to a valid ID in the MSP_PROJECTS table.
I RES_ACWP decimal The sum of Actual Cost of Work Performed (ACWP) values for all of a resource's assignments, up to the status date or today's date.
I RES_BCWP decimal The rolled-up summary of a resource's BCWP values for all assigned tasks, calculated up to the status date or today's date; also called Budgeted Cost of Work Performed.
I RES_BCWS decimal The rolled-up summary of a resource's BCWS values for all assigned tasks; also called Budgeted Cost of Work Scheduled.
I RES_NUM_OBJECTS integer The number of objects associated with a resource, not including those in notes.
EXT_EDIT_REF_DATA text Set to indicate a new row or back up existing data when changing project information in the database.
RES_UID integer The unique ID for the resource.
RES_ID integer The position identifier of the resource in the list of resources; determines the order that resources are displayed in Project.
RES_HAS_LINKED_FIELDS bit Indicates whether there are OLE links to the resource, either from elsewhere in the active project, another Project file, or from another program.
RES_IS_OVERALLOCATED bit Indicates whether a resource is assigned to do more work on all assigned tasks than can be done within the resource's normal work capacity.
RES_TYPE bit The resource type (Work or Material):
0 Material; consumable supplies like steel, concrete, or soil
1 Work (default); people and equipment
RES_HAS_NOTES bit Indicates whether a resource has Rich text Format (RTF) note.
RES_CAN_LEVEL bit Indicates whether resource leveling can be done with a resource.
RES_STD_RATE_FMT smallint The units in which the standard rate is displayed in Project.
RES_OVT_RATE_FMT smallint The units in which the overtime rate is displayed in Project.
RES_ACCRUE_AT smallint Indicates how and when resource standard and overtime costs are to be charged, or accrued, to the cost of a task:
1 Start; costs are accrued as soon as the task starts, as indicated by a date entered in the ActualStart field.
2 End; costs are not incurred until remaining work is zero.
3 Pro-rated (default); costs accrue as work is scheduled to occur and as actual work is reported.
RES_WORKGROUP_MESSAGING smallint The messaging method to be used to communicate with a project workgroup:
0 Default
1 None
3 Project Server
RES_CAL_UID integer Refers to a valid ID in the MSP_CALENDARS table.
I RES_AVAIL_FROM datetime The starting date that a resource is available for work at the units specified for the current time period.
I RES_AVAIL_TO datetime The ending date in which a resource will be available for work at the units specified for the current time period.
RES_STD_RATE decimal The rate of pay for regular, non-overtime work performed by a resource.
RES_OVT_RATE decimal The rate of pay for overtime work performed by a resource.
RES_MAX_UNITS decimal The maximum percentage, or number of units, that represents the maximum capacity that a resource is available to accomplish any tasks during the current time period. This can exceed 100%.
RES_WORK decimal The total amount of work scheduled to be performed by a resource on all assigned tasks.
RES_ACT_WORK decimal The actual amount of work that has already been done for all assignments assigned to a resource.
RES_BASE_WORK decimal The originally planned amount of work to be performed for all assignments assigned to a resource.
RES_OVT_WORK decimal The amount of overtime to be performed for all tasks assigned to a resource and charged at the resource's overtime rate.
RES_COST_PER_USE decimal The cost that accrues each time a resource is used.
RES_REM_WORK decimal The amount of time, or person-hours, still required by a resource to complete all assigned tasks.
RES_REG_WORK decimal The total amount of non-overtime work scheduled to be performed for all assignments assigned to a resource.
RES_ACT_OVT_WORK decimal The actual amount of overtime work already performed for all assignments assigned to a resource.
RES_REM_OVT_WORK decimal The remaining amount of overtime required by a resource to complete all tasks.
I RES_PEAK decimal The maximum percentage or number of units for which a resource is assigned at any one time for all tasks assigned to the resource.
RES_ACT_COST decimal The sum of costs incurred for the work already performed by a resource for all assigned tasks.
RES_COST decimal The total scheduled cost for a resource for all assigned tasks, based on costs already incurred for work performed by the resource on all assigned tasks in addition to the costs planned for all remaining work.
RES_BASE_COST decimal The total planned cost for a resource for all assigned tasks; also called Budget At Completion (BAC).
RES_REM_COST decimal The remaining scheduled expense that will be incurred in completing the remaining work assigned to a resource.
RES_OVT_COST decimal The total overtime cost for a resource on all assigned tasks.
RES_ACT_OVT_COST decimal The cost incurred for overtime work already performed by a resource for all assigned tasks.
RES_REM_OVT_COST decimal The remaining scheduled overtime expense of a resource that will be incurred in completing the remaining planned overtime work by a resource on all assigned tasks.
RES_NAME varchar(255) The name of the resource; must be unique within the enterprise whether or not the resource is active.
RES_INITIALS varchar(255) The abbreviation for a resource name.
RES_PHONETICS text Contains phonetic information in either Hiragana or Katakana for resource names; used only in the Japanese version of Project.
RES_MATERIAL_LABEL text The unit of measurement entered for a material resource, for example tons, boxes, or cubic yards; used in conjunction with the material resource's Assignment Units; only available if RES_RTYPE is set to Material.
RES_RTF_NOTES Image Notes about a resource.
RES_RTYPE smallint Indicates whether a resource is normal or generic and whether the normal or generic resource is deactivated:
0 Normal resource
1 Generic resource
100 Deactivated, normal resource
101 Deactivated, generic resource
RES_CHECKEDOUT integer Indicates whether the resource is checked out.
RES_CHECKOUTBY varchar(255) Value is the User ID of the user who last checked out the resource. This value is not cleared when the resource is checked back in, rather RES_CHECKEDOUT should return to 0 when the resource is checked back in.
RES_CHECKOUTDATE datetime The timestamp when the resource was last checked out. This value is not cleared when the resource is checked back in, rather RES_CHECKEDOUT should return to 0 when the resource is checked back in.
RES_ENTERPRISE_TIMESTAMP datetime The date and time that the enterprise resource was checked-in or checked-out.
RES_EUID integer Maps the RES_UID to the Resource Global enterprise pool.
RES_BOOKING_TYPE smallint Indicates the booking type for a resource:
0 Hard
2 Soft
RES_AD_GUID uniqueidentifier The AD GUID used to map a Project Server resource from Active Directory.
RES_ACT_WORK_PROT decimal The actual work that has been protected.
RES_ACT_OVT_WORK_PROT decimal The actual overtime work that has been protected.
RES_CREATION_DATE datetime The date the resource UID was created.
Top

MSP_STRING_TYPES

This table stores character string categories and links the Project database to the Project OLE DB Provider. All of the Project strings belong to a category.

Column Name Data Type Description
I STRING_TYPE_ID integer Shows a number that represents the enumerated field's category; the STRING_TYPE text representation for the field is shown in the right-column in the table below:
3 Link type
14 Work contour type
29 Task type
56 Weekday
57 Display units
68 Constraint type
70 Accrual
78 Cost rate data units
81 Workgroup user type
83 Weekday
84 Weekday
85 Weekday
100 Category type
101 Schedule from
102 Calendar working
103 Currency symbol position
104 Workgroup messages
105 Field ID
106 Field attributes
107 Timephased contour type
108 Timephased data units
I STRING_LANG_ID integer The ID of the language in which the conversion text is displayed. For example: 1033 = English, the default language for the database.
I STRING_TYPE varchar(200) The text representation of the field value as determined by the value of STRING_TYPE_ID; see the right column in STRING_TYPE_ID for the STRING_TYPE text representation for each value of STRING_TYPE_ID. For example: if STRING_TYPE_ID = 103, the contents of this field would be Currency symbol position.
Top

MSP_TASK_BASELINES

This table contains custom task baseline data.

Column Name Data Type Description
PROJ_ID integer Refers to a valid ID in the MSP_PROJECTS table.
TASK_UID integer Refers to a valid ID in the MSP_TASKS table.
TB_BASE_NUM smallint The number of the custom baseline, 1-10.
TB_BASE_DUR integer The original span of time planned to complete a task.
TB_BASE_DUR_FMT smallint The default format for the task baseline duration:
3 minute
5 hour (default)
7 day
9 week
11 month
TB_BASE_START datetime The planned beginning date for a task at the time you saved a baseline.
TB_BASE_FINISH datetime The planned completion date for a task at the time you saved a baseline.
TB_BASE_WORK decimal The originally planned amount of work to be performed by all resources assigned to a task.
TB_BASE_COST decimal The total planned cost for a task; also referred to as Budget At Completion (BAC).
Top

MSP_TASKS

This table stores the information about the tasks that make up a project. Project will write four rows to this table with unique IDs of zero, -65536, -65535, and -65534. Project uses these rows internally and they should not be edited or deleted.

Column Name Data Type Description
I RESERVED_DATA char Temporarily stores information unique to the particular database row; you should not modify the contents of this field.
PROJ_ID integer Refers to a valid ID in the MSP_PROJECTS table.
I TASK_ACWP decimal The costs incurred for work already done on a task, up to the project status date or today's date.
I TASK_BCWP decimal The cumulative value of the task's timephased percent complete multiplied by the task's timephased baseline cost, up to the status date or today's date; also known as Earned Value.
I TASK_BCWS decimal The cumulative timephased baseline costs up to the status date or today's date.
I TASK_DUR_VAR integer The difference between the baseline duration of a task and the total duration (current estimate) of a task.
I TASK_FINISH_VAR integer The amount of time that represents the difference between a task's baseline finish date and its current finish date.
I TASK_OUTLINE_NUM varchar(255) Indicates the exact position of a task in the outline. For example, 7.2 indicates that a task is the 2nd subtask under the 7th top-level summary task.
I TASK_START_VAR integer The difference between a task's baseline start date and its currently scheduled start date.
I TASK_IS_OVERALLOCATED bit Indicates whether an assigned resource on a task has been assigned to more work on the task than can be done within the normal working capacity.
I TASK_OVT_WORK decimal The amount of overtime scheduled to be performed by all resources assigned to a task and charged at overtime rates.
I TASK_VAC decimal The difference between the baseline cost and the total cost for a task; also called Variance At Completion (VAC).
I TASK_REG_WORK decimal The total amount of non-overtime work scheduled to be performed by all resources assigned to a task.
I TASK_NUM_OBJECTS integer The number of objects attached to a task.
TASK_TOTAL_SLACK integer The amount of time a task can be delayed without delaying the project's finish date.
EXT_EDIT_REF_DATA text Set to indicate a new row or back up existing data when changing project information in the database.
TASK_UID integer The unique ID for the task.
TASK_ID integer The position identifier of the task in the list of tasks.
TASK_HAS_LINKED_FIELDS bit Indicates whether there are OLE links to a task, either from elsewhere in the active project, another Project file, or from another program.
TASK_IS_MILESTONE bit Indicates whether a task is a milestone.
TASK_IS_CRITICAL bit Indicates whether a task has room in the schedule to slip, or if a task is on the critical path.
TASK_IS_SUMMARY bit Indicates whether a task is a summary task.
TASK_IS_SUBPROJ bit Indicates whether a task is an inserted project.
TASK_IS_MARKED bit Indicates whether a task is marked for further action or identification of some kind.
TASK_IGNORES_RES_CAL bit Indicates whether the scheduling of the task takes into account the calendars of the resources assigned to the task.
TASK_IS_ROLLED_UP bit Indicates whether the summary task bar displays rolled-up bars or whether information on the sub-task Gantt bars will be rolled up to the summary task bar; must be set to 1 for sub-tasks to be rolled up to summary tasks.
TASK_IS_FROM_FINISH_SUBPROJ bit Indicates whether a project is marked as "Schedule-from-Finish" (SF).
TASK_BAR_IS_HIDDEN bit Indicates whether the Gantt bars and Calendar bars for a task are hidden.
TASK_IS_RECURRING bit Indicates whether a task is a recurring task.
TASK_IS_RECURRING_SUMMARY bit Indicates whether a recurring task is a summary task.
I TASK_IS_EXTERNAL bit Indicates whether the task is linked from another project or whether it originated in the current project.
TASK_IS_EFFORT_DRIVEN bit Indicates whether scheduling for a task is effort-driven.
TASK_IS_COLLAPSED bit Indicates whether a summary task is collapsed when displayed in Project.
TASK_HAS_NOTES bit Indicates whether a task has RTF notes.
TASK_IS_READONLY_SUBPROJ bit Indicates whether the sub-project of this task is a read-only project.
TASK_LEVELING_CAN_SPLIT bit Indicates whether the resource leveling function can cause splits on remaining work on a task.
TASK_LEVELING_ADJUSTS_ASSN bit Indicates whether the leveling function can delay and split individual assignments (rather than the entire task) in order to resolve overallocations.
I TASK_DUR_IS_EST bit Indicates whether the baseline duration is estimated.
TASK_EARLY_FINISH datetime The earliest date that a task could possibly finish, based on early finish dates of predecessor and successor tasks, other constraints, and any leveling delay.
TASK_LATE_START datetime The latest date that a task can start without delaying the finish of the project, based on the start date, as well as the late start and late finish dates of predecessor and successor tasks, and other constraints.
TASK_STOP_DATE datetime The date that represents the end of the actual portion of a task.
I TASK_RESUME_DATE datetime The date that the remaining portion of a task is scheduled to resume after you enter a new value for the Percent (%) Complete field.
TASK_FREE_SLACK integer The amount of time that a task can be delayed without delaying any successor tasks; if a task has zero successor tasks, then free slack is the amount of time a task can be delayed without delaying the entire project.
TASK_OUTLINE_LEVEL smallint The number that indicates the level of a task in the project outline hierarchy.
TASK_DUR integer The total span of active working time for a task.
TASK_DUR_FMT smallint The units in which the duration of a task are displayed in Project:
3 m
4 em
5 h
6 eh
7 d (default)
8 ed
9 w
10 ew
11 mo
12 emo
19 %
20 e%
35 m?
36 em?
37 h?
38 eh?
39 d?
40 ed?
41 w?
42 ew?
43 mo?
44 emo?
51 %?
52 e%?
TASK_ACT_DUR integer The span of actual working time for a task so far, based on the scheduled duration and current remaining work or completion percentage.
TASK_REM_DUR integer The amount of time required to complete the unfinished portion of a task. Remaining duration can be calculated in two ways (either based off of Percent (%) Complete or Actual Duration).
TASK_BASE_DUR integer The original span of time planned to complete a task.
TASK_BASE_DUR_FMT smallint The units in which the baseline duration of a task are displayed in Project:
3 m
4 em
5 h
6 eh
7 d (default)
8 ed
9 w
10 ew
11 mo
12 emo
19 %
20 e%
35 m?
36 em?
37 h?
38 eh?
39 d?
40 ed?
41 w?
42 ew?
43 mo?
44 emo?
51 %?
52 e%?
TASK_CONSTRAINT_TYPE smallint The constraint on a scheduled task:
0 As soon as possible
1 As late as possible
2 Must start on; TASK_CONSTRAINT_DATE is required
3 Must finish on; TASK_CONSTRAINT_DATE is required
4 Start no earlier than; TASK_CONSTRAINT_DATE is required
5 Start no later than; TASK_CONSTRAINT_DATE is required
6 Finish no earlier than; TASK_CONSTRAINT_DATE is required
7 Finish no later than; TASK_CONSTRAINT_DATE is required
TASK_LEVELING_DELAY integer The amount of time that a task is to be delayed from its early start date as a result of resource leveling.
TASK_LEVELING_DELAY_FMT smallint The units in which a leveling delay is displayed in Project.
TASK_START_DATE datetime The date and time that a task is scheduled to begin; this value is automatically calculated if a task has a predecessor.
TASK_FINISH_DATE datetime The date and time that a task is scheduled to be completed.
TASK_ACT_START datetime The date and time that a task actually began.
TASK_ACT_FINISH datetime The date and time that a task actually finished.
TASK_BASE_START datetime The planned beginning date for a task at the time you saved a baseline.
TASK_BASE_FINISH datetime The planned completion date for a task at the time you saved a baseline.
TASK_CONSTRAINT_DATE datetime Indicates the constrained start or finish date as defined in TASK_CONSTRAINT_TYPE. Required unless TASK_CONSTRAINT_TYPE is set to As late as possible or As soon as possible.
TASK_PRIORITY smallint Indicates the level of importance assigned to a task; the higher the number, the higher the priority:
0 Lowest priority; task will always be leveled
500 Default value
1000 Highest priority; task will never be leveled
TASK_PCT_COMP smallint The current status of a task, expressed as the percentage of the task's duration that has been completed.
TASK_PCT_WORK_COMP smallint The current status of a task, expressed as the percentage of the task's work that has been completed.
TASK_TYPE smallint Indicates the effect that editing work, assignment units, or duration has on the calculations of the other two fields:
0 Fixed units (default); the number of Assignment Units remains constant, regardless of the amount of work or duration on the task
1 Fixed duration; the duration of the task remains constant, regardless of the number of resources (Assignment Units) assigned or the amount of work
2 Fixed work; the amount of work remains constant, regardless of any change in duration or the number of resources (Assignment Units) assigned to the task
TASK_FIXED_COST_ACCRUAL smallint Indicates how fixed costs are to be charged, or accrued, to the cost of a task:
1 Start; costs are accrued as soon as the task starts, as indicated by a date entered in the ActualStart field.
2 End; costs are not incurred until remaining work is zero.
3 Pro-rated (default); costs accrue as work is scheduled to occur and as actual work is reported.
TASK_CREATION_DATE datetime The date and time that a task was added to a project.
TASK_PRELEVELED_START datetime The start date of a task as it was before resource leveling was done.
TASK_PRELEVELED_FINISH datetime The finish date of a task as it was before resource leveling was done.
TASK_EARLY_START datetime The earliest date that a task could possibly begin, based on the early start dates of predecessor and successor tasks, and other constraints.
TASK_LATE_FINISH datetime The latest date that a task can finish without delaying the finish of the project, based on the task's late start date, as well as the late start and late finish dates of predecessor and successor tasks, and other constraints.
TASK_CAL_UID integer Refers to a valid ID in the MSP_CALENDARS table.
TASK_DEADLINE datetime The date entered as a deadline for the task.
TASK_WORK decimal The total amount of work scheduled to be performed on a task by all assigned resources.
TASK_BASE_WORK decimal The originally planned amount of work to be performed by all resources assigned to a task.
TASK_ACT_WORK decimal The amount of work that has already been done by the resources assigned to a task.
TASK_REM_WORK decimal The amount of time still required by all assigned resources to complete a task.
TASK_COST decimal The total scheduled, or projected, cost for a task, based on costs already incurred for work performed by all resources assigned to the task, in addition to the costs planned for the remaining work for the assignment.
TASK_FIXED_COST decimal A task expense that is not associated with a resource cost.
TASK_ACT_COST decimal The costs incurred for work already performed by all resources on a task, along with any other recorded costs associated with the task.
I TASK_REM_COST decimal The remaining scheduled expense of a task that will be incurred in completing the remaining scheduled work by all resources assigned to a task.
TASK_BASE_COST decimal The total planned cost for a task; also referred to as Budget At Completion (BAC).
TASK_ACT_OVT_WORK decimal The actual amount of overtime work already performed by all resources assigned to a task.
TASK_REM_OVT_WORK decimal The amount of remaining overtime scheduled by all assigned resources to complete a task.
TASK_OVT_COST decimal The actual overtime cost for a task.
TASK_ACT_OVT_COST decimal The costs incurred for overtime work already performed on a task by all assigned resources.
TASK_REM_OVT_COST decimal The remaining scheduled overtime expense for a task.
TASK_WBS text A unique code (WBS) used to represent a task's position within the hierarchical structure of the project.
TASK_NAME varchar(255) The name of a task.
TASK_WBS_RIGHTMOST_LEVEL text The right-most level of the task. For example, if the task level was A.01.03 the right-most level would be 03.
TASK_RTF_NOTES Image Contains RTF notes.
TASK_PHY_PCT_COMP smallint The physical percent of the total that a task has been completed.
TASK_EAC decimal The total scheduled or projected cost for a task based on costs already incurred, in addition to the costs planned for remaining work.
TASK_EVMETHOD smallint Indicates whether TASK_PCT_COMP or TASK_PHY_PCT_COMP is used to calculate the budgeted cost of work performed (TASK_BCWP).
TASK_ACT_WORK_PROT decimal The actual work that has been protected.
TASK_ACT_OVT_WORK_PROT decimal The actual overtime work that has been protected.
Top

MSP_TEXT_FIELDS

This table contains custom text information.

Column Name Data Type Description
PROJ_ID integer Refers to a valid ID in the MSP_PROJECTS table.
TEXT_CATEGORY integer Indicates whether the custom text category is Task, Resource, or Assignment; must be set to 0 for an inserted project:
0 Task
1 Resource
3 Assignment
TEXT_REF_UID integer Refers to a valid TASK_UID, RES_UID, or ASSN_UID in its respective table, as determined by TEXT_CATEGORY.
TEXT_FIELD_ID integer Refers to a valid CONV_VALUE in the MSP_CONVERSIONS table; always 188743706 for inserted projects.
TEXT_VALUE varchar(255) The value of the custom text field.
Top

MSP_TIMEPHASED_DATA

This table stores timephased data in a readable format when the PROJ_OPT_EXPAND_TIMEPHASED column in the MSP_PROJECTS table is set to 1 or when a user clicks Options on the Tools menu, clicks the Save tab, then selects the Expand timephased data in the database check box through Project. Each row in this table describes the type of data (work, percent complete, or cost), a date range, the unit of time, values for the specified data, and a reference to whether the timephased data refers to an assignment, task, or resource.

Column Name Data Type Description
RESERVED_DATA char Temporarily stores information unique to the particular database row; you should not modify the contents of this field.
PROJ_ID integer Refers to a valid ID in the MSP_PROJECTS table.
TD_UID integer The unique ID for the timephased data record.
TD_FIELD_ID smallint Refers to a valid Field ID in the CONV_VALUE column in the MSP_CONVERSIONS table.
TD_CATEGORY smallint Indicates whether the timephased data is Task, Resource, or Assignment:
0 Task
1 Resource
3 Assignment
TD_REF_UID integer Refers to a valid TASK_UID, RES_UID, or ASSN_UID in its respective table, as determined by TD_CATEGORY.
TD_START datetime The start of the first time period for which work, percent complete, or cost values will be applied.
TD_FINISH datetime The end of the first time period for which work, percent complete, or cost values will be applied.
TD_UNITS smallint Indicates the unit of time to which the specified values in TD_VALUE1 - TD_VALUE7 apply:
0 m
1 h
2 d (default)
3 w
5 mo
8 y
TD_VALUE1 - TD_VALUE7 decimal The value of the work, percent complete or cost for the first through seventh time periods in the units specified in the TD_UNITS column during the date range specified in the TD_START and TD_FINISH columns; only values that fall between the specified start and finish dates will apply.
TD_EXT_EDITED bit Indicates whether the record has been externally edited.

Расскажите о статье друзьям

Вы не вошли под своим пользователем на MicrosoftProject.ru.
Рекомендуется нажать "Закрыть" и зарегистрироваться на сайте.
Зарегистрированные пользователи, выступающие как редакторы,
имеют различные бонусы по доступу к закрытым материалам.
Если Вам не важны бонусы, можете отправить правку прямо сейчас.

Фрагмент, требующий улучшения
Ваша версия фрагмента. Отредактируйте текст
Степень серьезности проблемы
Проблемы содержания
Стилистические проблемы
Синтаксические и орфографические проблемы
Ваш комментарий:

Если вы заметили любую ошибку в статье, вы можете сообщить об этой ошибке редакторам сайта, выделив мышью отрывок текста с ошибкой и нажав Ctrl+Enter. Ваша помощь в улучшении материалов для нас неоценима!

© 2003-2017, Портал MicrosoftProject.Ru. Все права защищены.

E-mail: обратная связь