Microsoft Project 2000 Database Format

February, 2000

(C) 2000 Microsoft Corporation. All rights reserved.

Contents

Overview
     The purpose of this document
     What's included in this document
     What's new or changed in the Microsoft Project 2000 database

Working with projects in a database
     Supported databases
     Loading Microsoft Project 98 projects that were stored in a database
     Upgrading the database structure from Microsoft Project 98 to Microsoft Project 2000

Database permissions and configuration
     Required permissions
     Configuring the database
     Performance tuning
     Ensuring data integrity in a project in a database

Working with the Microsoft Project 2000 database structure

How information is stored
     Calendar data
     Timephased data
     Notes
     Custom field values
     Split Tasks
     Task links
     Estimated duration values

Working with projects in a database
     Microsoft Project tables
     Creating the Microsoft Project 2000 database structure
     DSN requirements for multiple users and projects
     Concurrent usage and project locking
     Deleting a project from a database
     Renaming projects in a database

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

Creating project schedule data
     Creating a new project
     Creating an inserted project
     Creating a new resource
     Creating a new calendar
     Specifying calendar working time and exceptions
     Specifying resource availability
     Specifying resource rates
     Creating a new task
     Creating a recurring task
     Creating task dependencies
     Creating a new assignment

Managing timephased data
     Creating or modifying an assignment actual-work contour
     Creating splits in scheduled work
     Creating or modifying an assignment remaining-work contour
     Creating or modifying cost contours
     Creating or modifying task-percent complete contours
     Deleting a contour

Customizing project data
     Specifying custom text fields
     Specifying custom number fields
     Specifying custom date fields
     Specifying custom duration fields
     Field attributes
     Specifying custom WBS codes
     Specifying custom outline codes
     Specifying aliases for custom fields
     Specifying formulae for custom fields
     Reading and writing RTF notes

Managing other data in the database
     Specifying an email address
     Retrieving cross-project link project/task references
     Retrieving the names of sharer files
     Outlining with summary tasks and subtasks
     Editing work on a summary task assignment
     Retrieving Workgroup Message Status


     Specifying hyperlinks

Appendixes

Processing order of externally edited data
     Overview
     Standard processing order

Database object naming conventions
     Table Naming Conventions
     Column naming conventions
     MSP_PROJECTS column qualifiers
     Column name abbreviations

Database tables
     Informational-only columns
     Key columns
     Column data types
     MSP_PROJECTS
     MSP_TASKS
     MSP_RESOURCES
     MSP_ASSIGNMENTS
     MSP_AVAILABILITY
     MSP_CALENDARS
     MSP_CALENDAR_DATA
     MSP_LINKS
     MSP_TIMEPHASED_DATA
     MSP_RESOURCE_RATES
     MSP_TEXT_FIELDS
     MSP_NUMBER_FIELDS
     MSP_DATE_FIELDS
     MSP_DURATION_FIELDS
     MSP_FLAG_FIELDS
     MSP_OUTLINE_CODES
     MSP_CODE_FIELDS
     MSP_STRING_TYPES
     MSP_CONVERSIONS
     MSP_FIELD_ATTRIBUTES
     MSP_ATTRIBUTE_STRINGS

Overview

The purpose of this document

This document provides the information required to create and update project data directly in the database while maintaining the consistency and integrity of the data. The advantages of creating and maintaining project data directly in the database include:

Top

What's included in this document

Specific chapters include the following information:

Top

What's new or changed in the Microsoft Project 2000 database

One of the goals of Microsoft Project 2000 is to simplify the creation and management of project data in the database. To this end, the following changes have been made:

Top

Working with projects in a database

Supported databases

Microsoft Project supports the following databases through Open Database Connectivity (ODBC):

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

Note: 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 it into Microsoft Access or connect to the source directly.

Top

Loading Microsoft Project 98 projects that were stored in a database

You can open a project in Microsoft Project 2000 that was previously saved to database using Microsoft Project 98. However, if you save the project to a database again, it will be saved with the Microsoft Project 2000 database structure. Because the Microsoft Project 2000 database format has been completely changed, you will have two copies of the project each in different database formats. The original Project 98 data will not be affected.

Top

Upgrading the database structure from Microsoft Project 98 to Microsoft Project 2000

You can use the Database Upgrade Utility COM add-in to upgrade some or all of the projects in a Microsoft Access, SQL Server, or Oracle database from the Microsoft Project 98 database structure to the Microsoft Project 2000 database structure. To load the Database Upgrade Utility, right-click on the toolbar and select the Database Upgrade Utility.

Top

Database permissions and configuration

Required permissions

Microsoft Project performs several types of operations on a database, each requiring a corresponding set of object permissions.

It is possible to have multiple projects, for multiple organizations, in the same database, and yet 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 any given database because Microsoft Project uses the first set of tables it finds in the database. Any security scheme must take this into consideration.

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

A similar view must be created for each of the 21 tables in the Microsoft Project database structure. Projects accessed via views with unions cannot be updated.

Notes

Top

Configuring the database

There are two ways to set up a database for use with Microsoft Project. The simplest way is to create a database and use Microsoft Project to create the tables and indexes. This method is the 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 Microsoft Project. Script files are provided with Microsoft Project that contain the data definition language (DDL) to create the Microsoft Project 2000 database structures. These script files are called MPDtable.sql, SQLtable.sql, and OraTable.sql, and can be found on the Microsoft Project 2000 installation CD in the \Database subdirectory under the ValuPack directory. These script files can be modified to specify database files and file groups (SQL Server) or table spaces (Oracle) 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, not a requirement.

It is recommended that, for SQL Server, the transaction log files be put on a separate disk from the data whenever possible. For Oracle, plan for adequate log file space, particularly for archivalogmode.

Before executing the scripts in Oracle or SQL Server, you must do one of the following:

Also note that in all three scripts, the SQL statements that populate the MSP_STRING_TYPES and MSP_CONVERSIONS tables are for the English conversion values and, therefore, will need to be modified for other languages.

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

Top

Performance tuning

The normal standard practices for SQL Server and Oracle database performance and tuning apply to Microsoft Project databases.

Top

Ensuring data integrity in a project in a database

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

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

To prevent errors in changed task, resource, assignment, and task dependency (link) data from causing inconsistencies in the project in the 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. Microsoft Project uses the data in the EXT_EDIT_REF_DATA columns in these tables 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, Microsoft Project provides stored procedures (action queries for projects in Microsoft Access databases) to automate the process. Microsoft Project creates the stored procedures in SQL Server and Oracle 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 Microsoft Project must be run to create the stored procedures. The scripts that create the SQL Server and Oracle stored procedures are called BKP_ora.sql and BKP_sql.sql and can be found on the Microsoft Project 2000 installation CD in the \Database subdirectory under the ValuPack directory.

The files that contain the SQL for the Microsoft Access action queries are called BKP_ASSN.sql, BKP_LINK.sql, BKP_RES.sql, and BKP_TASK.sql. The SQL in each file must be copied into a new action query with the same name as the file from which it is copied (not including the file extension). These files can be found on the Microsoft Project 2000 installation CD in the \Database subdirectory under the ValuPack directory.

Top

Working with the Microsoft Project 2000 database structure

This section generally describes how different types of data are stored in the database. Detailed descriptions of how to create and change data can be found in the next chapter.

Top

How information is stored

Calendar data

Microsoft Project saves calendar information for a project into two tables. The MSP_CALENDARS table contains the information that identifies the calendar and the resources for which it is a base calendar. The MSP_CALENDAR_DATA table contains the working, nonworking, and exception time data for the calendar to which it applies for the day or days specified. Standard working and nonworking times are defined by the calendar as it is constrained by the calendar and working time options.

Top

Timephased data

Storage of time-phased data has been optimized so that a single row can contain values (for example, hours or days) 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 the $/week.

Top

Notes

Storing notes has been simplified in Microsoft Project 2000. Task notes (rich text) are now stored in the TASK_RTF_NOTES column in the MSP_TASKS table. Similar notes columns are available in the MSP_RESOURCES and MSP_ASSIGNMENTS 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 table MSP_TASKS.

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 MSP_OUTLINE_CODES. The definitions for the outline codes are stored in the MSP_ATTRIBUTE_STRINGS and MSP_FIELD_ATTRIBUTES tables.

Top

Split Tasks

The nonworking time of split tasks is stored in MSP_TIMEPHASE_DATA as rows with zero hours of work. (To save time-phased data in readable form in the table MSP_TIMEPHASED_DATA, select the Expand time-phased data in the database check box on the Save tab of the Options dialog box, accessed from the Tools menu.)

Top

Task links

Task links, or dependencies, are stored in the table MSP_LINKS. 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 "True." 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

Working with projects in a database

Microsoft Project tables

You should never drop any tables created by Microsoft Project nor should you drop any of the table columns. Also, you should not change the data type of any column. Last, you should never alter the column RESERVED_BINARY_DATA in the MSP_PROJECTS table in any way.

You may notice that some of the tables Microsoft Project created in the database contain some records with large negative values in their respective UID columns (for example, TASK_UID). These records usually appear at the top of a table or at the beginning of each project and the values are -65536, -65535, and -65534. These records are used internally by Microsoft Project and should never be edited or deleted.

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

Top

Creating the Microsoft Project 2000 database structure

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

If you do not want to use Microsoft Project to facilitate the process, you will need to create an empty database through the ODBC driver (Access only), and then create all of the appropriate tables. Creating all of the tables manually would be an extensive undertaking because you would need to use this document as a reference and ensure that you have exactly the right table and column names for every table and the correct data types for every column. To facilitate table creation, Microsoft Project includes three script files with the SQL statements necessary to set up all of the tables for Microsoft Access, Microsoft SQL Server, or Oracle Server. To manually create and configure a database for use with Microsoft Project, see the topic Configuring the database above.

Top

DSN requirements for multiple users and projects

If multiple users will access projects in the same database, each user making changes to the data need not use the same data source name (DSN) for connecting to the database. Microsoft Project stores both the combined DSN and project name (in "<DSN>\projectname" format) and alternatively the connection string as the identifiers to locate a project. If someone uses a DSN with a different name to access a project, Microsoft Project uses the connection string information to resolve references for items such as inserted projects, cross-project links, and sharer projects that use the same resource pool. This also applies to projects that are stored in a Microsoft Access database (as an MPD or MDB file). However, if multiple users modify the database, then 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.

Because file DSNs have limitations, they cannot be used for project links such as resource pools, sharer files, cross-project links, or inserted projects.

Because of the way Microsoft 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 DSN connection is established, that connection will be reused even if a different logon name and/or password is entered at logon time. To log on with a different user ID, you must first close all projects opened with the DSN or, for simultaneous connections, you must create additional alternate DSNs to use with each user ID.

Top

Concurrent usage and project locking

If you open a project in a database through Microsoft Project, and that project is not in use by another user, you will be given full read/write access. Until you finish your session with the project, another user will only be able to open that project in Microsoft 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 Microsoft Project to read or update the database. Microsoft 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 Microsoft 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 Microsoft Project, then Microsoft 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 Microsoft 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 Microsoft Project, not even read-only.

Microsoft 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 Microsoft Project will get an appropriate message informing them that the project is currently opened for read/write access by you. Otherwise, Microsoft 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 Microsoft 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

Note: If a user has read/write access to a project in a database through Microsoft Project, and another user changes data in that project directly in the database, that change will not be reflected in Microsoft 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

Deleting a project from a database

You can delete a project in a database by clicking Delete on the Tools drop-down menu of the Open from database dialog box. The table structure will be retained. You can also delete the project data from the database using the DeleteFromDatabase Microsoft Visual Basic for Applications (VBA) method (see the Microsoft Project online Help for information about using VBA).

Top

Renaming projects in a database

To rename a project in a database, bring up the database in the Open from database dialog box and click Rename on the Tools drop-down menu.

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 Microsoft Project, the column PROJ_EXT_EDITED in the table MSP_PROJECTS must be set to "True."

When editing existing rows in the tables MSP_TASKS, MSP_RESOURCES, MSP_ASSIGNMENTS and MSP_LINKS, the column EXT_EDIT_REF_DATA must be set to "1" followed by the values of the columns listed in the table below (all values delimited by commas). When creating new rows, however, the column EXP_EDIT_REF_DATA should be set to "1". (Note: It is recommended that the stored procedures included with Microsoft Project be used to update this column with the required values. See Ensuring integrity in a project in a database.)

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

* While these fields 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 table MSP_AVAILABILITY. Changes to resource availability should be made in the table MSP_AVAILABILITY.

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

Table Flag in MSP_PROJECTS
MSP_DATE_FIELDS PROJ_EXT_EDITED_DATE
MSP_DURATION_FIELDS PROJ_EXT_EDITED_DUR
MSP_FLAG_FIELDS PROJ_EXT_EDITED_FLAG
MSP_NUMBER_FIELDS PROJ_EXT_EDITED_NUM
MSP_OUTLINE_CODES/MSP_CODE_FIELDS PROJ_EXT_EDITED_CODE
MSP_TEXT_FIELDS PROJ_EXT_EDITED_TEXT

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 "True" in each modified or new row.

When updating the MSP_TIMEPHASED_DATA table, the column TD_EXT_EDITED must be set to "True."

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 A.M., which is normally nonworking time in most calendars. When Microsoft Project encounters 12:00 A.M., 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 A.M., so Microsoft Project treats this as Monday, 8:00 A.M. (per the calendar default start time), which works fine. For Friday's time, though, the default is Friday at 12:00 A.M., 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 Microsoft Project. By explicitly specifying the time in each date/time column, you can always avoid this problem.

Top

Duration, work, rate and cost values

Microsoft 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 Microsoft Project saves as absolute values. The FMT column simply indicates which unit label Microsoft 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, Microsoft Project saves each using a standard multiple:

Note: 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 Microsoft Project to read a project in a database, the contents of some columns are converted to numeric constants. Microsoft Project writes two tables to the database containing the conversion information for those columns, MSP_STRING_TYPES, which contains the mapping between the enumerated field categories and the field name, and MSP_CONVERSIONS, which 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_PROJECTS
Link Type LinkType MSP_LINKS
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
Time-phased 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

The MSP_STRING_TYPES and MSP_CONVERSIONS tables are described in detail below, in the section "Microsoft Project database structure."

You can store projects from different language versions of Microsoft Project into the same database. Microsoft 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.

Note: When saving to database, the code pages of the Microsoft Project computer and the database server must be the same.

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

Microsoft 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 Microsoft Project will not be able to read that table and perhaps the entire project.

Top

Creating project schedule data

Creating a new project

To create an entirely 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. You must enter values for at least the columns specified 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.

Table Fields Notes
MSP_PROJECTS PROJ_ID Must be unique within the table.
  PROJ_NAME Must be unique within the table.
PROJ_INFO_START_DATE The project start date and time.
PROJ_EXT_EDITED Must be set to "True" for Microsoft Project to process.
MSP_TASKS PROJ_ID Specify the same PROJ_ID value as in the MSP_PROJECTS table.
  TASK_UID This value must be zero for a project summary task.
TASK_ID This value must be zero for a project summary task.

Notes

Top

Creating an inserted project

The process for creating an inserted project in the database is a combination of the procedures for 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 flag in the MSP_PROJECTS table:

Table Fields Notes
MSP_TASKS PROJ_ID This value must refer to a valid project in the MSP_PROJECTS table. This is the ID of the master project, not the inserted project.
  TASK_UID Must be unique within the master project. This is the unique ID of the inserted project task.
TASK_ID Must be unique within the master project. This is the ID of the inserted project task.
EXT_EDIT_REF_DATA Must enter a "1" for the new task.
MSP_TEXT_FIELDS PROJ_ID Specify the same PROJ_ID value as in the MSP_TASKS table.
  TEXT_CATEGORY The category type is always zero (task) for inserted projects.
TEXT_REF_UID Specify the same TASK_UID value used for the inserted project task in the MSP_TASKS table.
TEXT_FIELD_ID The field ID is always 188743706 for inserted projects.
TEXT_VALUE The full name of the inserted project (that is, path and filename or database and project name).
MSP_PROJECTS PROJ_EXT_EDITED Set this value to "True."
PROJ_EXT_EDITED_TEXT Set this value to "True."

Note: 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 Microsoft Project.

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:

PROJ_ID 1
TASK_ID 5
TASK_UID 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 that you need to add to the MSP_TEXT_FIELDS table:

PROJ_ID 1
TEXT_CATEGORY 0
TEXT_REF_UID 5
TEXT_FIELD_ID 188743706
TEXT_VALUE <Projects>\Subproject

The value of PROJ_ID must match the task record. The TEXT_CATEGORY is zero in this case 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, which, in this case, is specified in the <DSN>\ProjectName syntax for a project in a database.

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

Top

Creating a new 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 Columns Notes
MSP_RESOURCES PROJ_ID Must refer to a valid project in the MSP_PROJECTS table.
  RES_UID Must be unique within the project.
RES_ID Must be unique within the project.
RES_NAME The name of the new resource.
RES_TYPE Enter 1 (True) for work. Default is "0" (False) for material.
EXT_EDIT_REF_DATA Must enter a "1" for the new resource.
MSP_PROJECTS PROJ_EXT_EDITED Must be set to "True" for Microsoft 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',
              1,
              '1' )

Top

Creating a new calendar

Calendars can be created for a project or a 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 the following section, Specifying calendar working time and exceptions.) To create a new calendar, you must create a row with values for at least the following columns:

Table Columns Notes
MSP_CALENDARS PROJ_ID Must refer to a valid project in the MSP_PROJECTS table.
  CAL_UID Must be unique within the project.
CAL_BASE_UID Required for resource calendars. Refers to its parent base calendar. -1 for base calendars.
CAL_IS_BASE_CAL Enter 0 (false) for resource calendars.
CAL_NAME Required for base calendars.
MSP_PROJECTS PROJ_EXT_EDITED Must be set to "True" for Microsoft 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

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, and only 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:

Table Columns Notes
MSP_CALENDAR_DATA PROJ_ID Must refer to a valid project in the MSP_PROJECTS table.
  CD_UID Must be unique within the project.
CAL_ UID Must be a calendar within the specified project.
CD_DAY_OR_EXCEPTION Enter 1-7 for Sunday through Saturday (0 for exception day).
CD_WORKING Set to "1" for working day ("0" for nonworking day).
CD_FROM_TIME1 Starting day and time of the working day (not used for exception). Up to five periods per day are supported.
CD_TO_TIME1 Ending day and time of the working day  (not used f or exception).
MSP_PROJECTS PROJ_EXT_EDITED Must be set to "True" for Microsoft Project to process.
Exception days require values for CD_FROM_DATE and CD_TO_DATE but not from and to times.

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,
              1,
              '10/25/1999 8:00:00 AM',
              '10/25/1999 12:00:00 PM',
              '10/25/1999 1:00:00 PM',
              '10/25/1999 5:00:00 PM' )

No value should be specified for CD_FROM_TIMEn or CD_TO_TIMEn for nonworking days.

Top

Specifying resource availability

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

Table Columns Notes
MSP_AVAILABILITY PROJ_ID Must refer to a valid project in the MSP_PROJECTS table.
  AVAIL_UID Must be unique within the project.
RES_UID Must be a valid resource UID within the project.
AVAIL_FROM The first date of the available period. Note: The time component of the date is ignored.
AVAIL_TO The last date of the available period.
AVAIL_UNITS Decimal fraction representing the percent of the available period that the resource is available to work.
MSP_PROJECTS PROJ_EXT_EDITED Must be set to "True" for Microsoft Project to process.

To specify a period of availability from October 25, 1999, to October 30, 1999, 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,
              '10/25/1999',
              '10/30/1999',
              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:

Table Columns Notes
MSP_RESOURCE_RATES PROJ_ID Must refer to a valid project in the MSP_PROJECTS table.
  RR_UID Identifies the rate. Must be unique within the project.
RES_UID Identifies the resource to whom the rate applies. Must be unique within the project.
RR_RATE_TABLE Identifies one of the five rate tables of the specified resource: 0 through 4 identify tables A through E, respectively.
RR_STD_RATE

or

RR_OVT_RATE

or

RR_PER_USE_COST

Rates are in dollars per hour. Per use costs are in dollars * 100. If either a standard or or overtime rate is specified, the format can optionally be specified also. See MSP_STRING_TYPES and MSP_CONVERSIONS for string type 'Cost Rate Data Units'.
MSP_PROJECTS PROJ_EXT_EDITED Must be set to "True" for Microsoft Project to process.

To specify a rate with an effective, or from, date, two rows must be inserted into MSP_RESOURSE_RATES: one for the first rate which does not have an effective date, and one for the row that does. To specify standard and overtime hourly cost rates for a resource for a date range in 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, -- resource UID
              1, -- table B
              '2000-02-12', -- rate end date
              25.00, -- $25/h standard rate
              2, -- standard rate format = h = hours
              35.00, -- $35/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, -- resource UID
              1, -- table B
              '2000-02-12', -- rate start date
              '2000-04-30', -- rate end date
              30.00, -- $25/h standard rate
              2, -- standard rate format = h = hours
              40.00, -- $35/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, -- resource UID
              2, -- table C
              5000.00 ) –- $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 Columns Notes
MSP_TASKS PROJ_ID Must refer to a valid project in the MSP_PROJECTS table.
  TASK_UID Must be unique within the project.
TASK_ID Must be unique within the project.
TASK_NAME The name of the new task.
EXT_EDIT_REF_DATA Must enter a "1" for the new task.
MSP_PROJECTS PROJ_EXT_EDITED Must be set to "True" for Microsoft Project to process.

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:

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_IDs of each subsequent record be adjusted by one to make room (since TASK_IDs 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,
              EXT_EDIT_REF_DATA )
          values (
              3,
              23,
              8,
              'my task',
              4800,
              '1' ) 

Notes

Top

Creating a recurring task

While it is possible to create a recurring task in the database, it's preferable to create recurring tasks inside Microsoft Project because the Recurring Task Information dialog box in Microsoft 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 Must refer to a valid project in the MSP_PROJECTS table.
  TASK_UID Must be unique within the project.
TASK_ID Must be unique within the project.
TASK_NAME The name of the recurring summary task. Not required for subtasks.
TASK_OUTLINE_LEVEL The level of recurring subtasks must be one level lower than the summary recurring task.
TASK_IS_RECURRING Set to true for both summary tasks and subtasks
TASK_IS_RECURRING_SUMMARY Must be set to True for the summary task and false for all of the subtasks.
TASK_IS_ROLLED_UP Must be Set to true for summary and subtasks.
TASK_CONSTRAINT_

TYPE

Required for subtasks. Usually set to 4 (SNET).
EXT_EDIT_REF_DATA Must enter a "1" for Microsoft Project to process.
MSP_PROJECTS PROJ_EXT_EDITED Must be set to "True" for Microsoft Project to process.

To create a recurring task with two recurrences, 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',
              1, -- 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,
              2, -- 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,
              2, -- 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 Must refer to a valid project in the MSP_PROJECTS table.
  LINK_UID Must be unique within the project.
LINK_PRED_UID Must refer to a valid row for the same PROJ_ID in the MSP_TASKS table.
LINK_SUCC_UID Must refer to a valid row for the same PROJ_ID in the MSP_TASKS table.
EXT_EDIT_REF_DATA Must enter a "1" for the new link.
MSP_PROJECTS PROJ_EXT_EDITED Must be set to "True" for Microsoft Project to process.

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 both of the following columns:

Table Fields Notes
MSP_LINKS LINK_LAG The amount of lag, specified as a duration value (that is, minutes * 10).
  LINK_LAG_FMT The value representing the units to use when the LINK_LAG is displayed inside Microsoft Project. The range of values can be obtained from the CONV_VALUE column in the MSP_CONVERSIONS table where the STRING_TYPE_ID equals 57 (Display Units).
EXT_EDIT_REF_DATA Must enter a "1" for the link.
MSP_PROJECTS PROJ_EXT_EDITED Must be set to "True" for Microsoft 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 Must refer to a valid project in the MSP_PROJECTS table.
  ASSN_UID Must be unique within the project.
TASK_UID Must refer to a valid row for the same PROJ_ID in the MSP_TASKS table.
RES_UID Must refer to a valid row for the same PROJ_ID in the MSP_RESOURCES table.
ASSN_UNITS The percentage of work time that the resource will apply to the task.
ASSN_WORK Defaults to zero if not specified.
EXT_EDIT_REF_DATA Must enter a "1" for the new assignment.
MSP_PROJECTS PROJ_EXT_EDITED Must be set to "True" for Microsoft 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

Top

Managing timephased data

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

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

CONV_VALUE Field
1 Assignment remaining work
2 Assignment actual work
3 Assignment actual overtime work
4 Assignment baseline work
5 Assignment baseline cost
6 Assignment actual cost
7 Resource baseline work
8 Resource baseline cost
9 Task baseline work
10 Task baseline cost
11 Task percent complete

Note: 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 table MSP_TIMEPHASED_DATA and enter values for at least the following columns:

Table Fields Notes
MSP_TIMEPHASED_DATA PROJ_ID Must refer to a valid project in the MSP_PROJECTS table.
  TD_UID Must be unique within the project.
TD_FIELD_ID See MSP_STRING_TYPES and MSP_CONVERSIONS for 'Timephased Contour Types' such as 'Actual Work', 'Remaining Work', 'Percent Complete' and 'Actual Cost'.
TD_CATEGORY Specifies whether this record refers to a task, resource or assignment. See MSP_STRING_TYPES and MSP_CONVERSIONS for 'Category Types'.
TD_REF_UID Depending on the category, the corresponding TASK_UID, RES_UID or ASSN_UID value of the row for which the timephased data is being specified.
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 last time period to which work, percent complete or cost values will be applied.
TD_UNITS The units of time to which the specified values in the TD_VALUE1 through TD_VALUE7 columns apply. See MSP_STRING_TYPESand MSP_CONVERSIONS for 'Timephased Data Units'.
TD_VALUE1

To

TD_VALUE7

The value of the work, percent complete or cost for the time period specified in the TD_UNITS column over the date range specified in the TD_START AND TD_FINISH columns. Only values that fall between the specified start and finish dates will be considered.
TD_EXT_EDITED Must be set to "True" for Microsoft Project to process.
MSP_PROJECTS PROJ_EXT_EDITED Must be set to "True" for Microsoft 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
Time-phased record UID n/a TD_UID 4
Details Act. Work TD_FIELD_ID 2
Category n/a TD_CATEGORY 3
Assignment UID n/a TD_REF_UID 16
Time period timescale TD_START 11/08/1999 8:00:00 AM
    TD_FINISH 11/14/1999 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 contour

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
              '11/8/1999 8:00:00 AM', -- start of the first time period
              '11/12/1999 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

Note: The value of TD_FINISH will be displayed in Project as 11/11/1999 since 11/12/1999 08:00 is interpreted as the end of the working day which begins on 11/11.

Top

Creating splits in scheduled work

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

Top

Creating or modifying an assignment remaining-work contour

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

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
              '11/8/1999 8:00:00 AM', -- start of the first time period
              '11/15/1999 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 the seven days of 11/8/1999 to 11/15/1999. 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
              '11/15/1999 8:00:00 AM', -- start of the first time period
              '11/16/1999 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. Note: Microsoft Project will ignore inserted cost data if 'Project calculates actual cost' flag is set to true. 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
              '11/8/1999 8:00:00 AM', -- start of the first time period
              '11/11/1999 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 11/8/1999 at 8:00:00 AM, 0% completed the next day, and 28% completed on the day that begins 11/10/1999.

Top

Deleting a contour

To delete a contour in the MSP_TIMEPHASED_DATA table, update all rows for that contour setting all TD_VALUE columns to zero and TD_EXT_EDITED to true. Setting all TD_VALUE columns to zero 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.

Note: Microsoft Project won't actually delete the updated rows in MSP_TIMEPHASED_DATA 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 zero.

Specifying custom text fields

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

Table Fields Notes
MSP_TEXT_FIELDS PROJ_ID This value must refer to a valid project in the MSP_PROJECTS table.
  TEXT_CATEGORY The category is 0 for task fields, 1 for resource fields, or 3 for assignment fields.
TEXT_REF_UID Depending on the category, specify the corresponding TASK_UID, RES_UID, or ASSN_UID value of the row for which the custom field is being set.
TEXT_FIELD_ID The field identifier of the custom text field, which can be obtained from the CONV_VALUE column in the MSP_CONVERSIONS table.
TEXT_VALUE The custom text value.
MSP_PROJECTS PROJ_EXT_EDITED Must be set to "True" for Microsoft Project to process.
PROJ_EXT_EDITED_TEXT Set this value to "True."

Top

Specifying custom number (or cost) fields

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

Table Fields Notes
MSP_NUMBER_FIELDS PROJ_ID This value must refer to a valid project in the MSP_PROJECTS table.
  NUM_CATEGORY The category is 0 for task fields, 1 for resource fields, or 3 for assignment fields.
NUM_REF_UID Depending on the category, specify the corresponding TASK_UID, RES_UID, or ASSN_UID value of the row for which the custom field is being set.
NUM_FIELD_ID The field identifier of the custom number or cost field, which can be obtained from the CONV_VALUE column in the MSP_CONVERSIONS table.
NUM_VALUE The custom number or cost value.
MSP_PROJECTS PROJ_EXT_EDITED Must be set to "True" for Microsoft Project to process.
  PROJ_EXT_EDITED_NUM Set this value to "True."

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 This value must refer to a valid project in the MSP_PROJECTS table.
  DATE_CATEGORY The category is 0 for task fields, 1 for resource fields, or 3 for assignment fields.
DATE_REF_UID Depending on the category, specify the corresponding TASK_UID, RES_UID, or ASSN_UID value of the row for which the custom field is being set.
DATE_FIELD_ID The field identifier of the custom start, finish or date field, which can be obtained from the CONV_VALUE field in the MSP_CONVERSIONS table.
DATE_VALUE The custom date/time value.
MSP_PROJECTS PROJ_EXT_EDITED Must be set to "True" for Microsoft Project to process.
  PROJ_EXT_EDITED_DATE Set this value to "True."

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 This value must refer to a valid project in the MSP_PROJECTS table.
  DUR_CATEGORY The category is 0 for task fields, 1 for resource fields, or 3 for assignment fields.
DUR_REF_UID Depending on the category, specify the corresponding TASK_UID, RES_UID, or ASSN_UID value of the row for which the custom field is being set.
DUR_FIELD_ID The field identifier of the custom duration field, which can be obtained from the CONV_VALUE field in the MSP_CONVERSIONS table.
DUR_VALUE The custom duration value.
DUR_FMT The units to use when displaying this duration in Microsoft Project.
MSP_PROJECTS PROJ_EXT_EDITED Must be set to "True" for Microsoft Project to process.
  PROJ_EXT_EDITED_DUR Set this value to "True."

Example:

Let's say you want to enter the value 226 into the Number3 field for a particular resource that already exists in the database. Assume that the record for the resource in the MSP_RESOURCES table includes the following information:

PROJ_ID RES_UID RES_NAME
42 17 Bob

To set the Number3 field for this resource to 226, add the following record to the MSP_NUMBER_FIELDS table:

PROJ_ID 42
NUM_CATEGORY 1
NUM_REF_UID 17
NUM_FIELD_ID 205521010
NUM_VALUE 226

The PROJ_ID must match the resource record. The NUM_CATEGORY is 1 in this case, because we are setting a resource custom field. The NUM_REF_UID is taken directly from the RES_UID field in the MSP_RESOURCES table. The NUM_FIELD_ID value is taken from the CONV_VALUE field in the MSP_CONVERSIONS table from the record where the CONV_STRING field equals "Resource Number 3." The NUM_VALUE field contains the actual data.

In order for Microsoft Project to read this record, you must set the PROJ_EXT_EDITED_NUM and PROJ_EXT_EDITED flags in the MSP_PROJECTS table to True.

Top

Field attributes

While the majority of Microsoft 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 Microsoft 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_STRING_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 table MSP_ATTRIBUTE_STRINGS. The actual WBS code values for tasks are stored in the table MSP_TASKS. To define custom WBS codes, you must enter values for the following columns:

Table Columns Notes
MSP_ATTRIBUTE_STRINGS PROJ_ID This value must refer to a valid project in the MSP_PROJECTS table.
  AS_ID This value identifies the WBS attribute string and must be unique within the project.
AS_VALUE Each code is defined by three values: type, length and separator. All values are separated by commas. Types include:

0 = Numbers (orders)

1 = Uppercase Letters (ordered)

2 = Lowercase Letters (orders)

3 = Characters (unordered)

MSP_PROJECTS PROJ_EXT_EDITED Must be set to "True" for Microsoft 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 table MSP_FIELD_ATTRIBUTES: one for the WBS prefix and one for the WBS code mask. Values must be provided for the following columns:

Table Columns Notes
MSP_FIELD_ATTRIBUTES PROJ_ID This value must refer to a valid project in the MSP_PROJECTS table.
  ATTRIB_FIELD_ID Refers to the column TASK_WBS. See MSP_STRING_TYPES and MSP_CONVERSIONS for 'Field ID'.
ATTRIB_ID Refers to the type of field attribute. See MSP_STRING_TYPES and MSP_CONVERSIONS for 'Field Attributes'.
ATTRIB_VALUE Ignore this column if AS_ID refers to a row in MSP_ATTRIBUTE_STRINGS. Otherwise, this column specifies the value of the attribute.
AS_ID Ignore if ATTRIB_VALUE is set to specify the value of an attribute. Otherwise, this column references a row in the MSP_ATTRIBUTE_STRINGS table.
ATTRIB_EXT_EDITED Must be set to "True" for Microsoft Project to process.
MS_PROJECTS PROJ_EXT_EDITED Must be set to "True" for Microsoft 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 Microsoft 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 Microsoft 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 or not Microsoft Project enforces the uniqueness of WBS codes and the automatic generation of WBS codes in new tasks created using Microsoft Project. In order to specify whether or not Microsoft Project should enforce the uniqueness of new WBS codes in tasks, a row must be inserted into the table MSP_FIELD_ATTRIBUTES. 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 Microsoft Project to process

Finally, to specify whether or not Microsoft 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 Microsoft 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 four tables:

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

Table Columns Notes
MSP_OUTLINE_CODES PROJ_ID This value must refer to a valid project in the MSP_PROJECTS table.
  CODE_UID This value identifies the outline code and must be unique within the project.
OC_PARENT The code UID of the parent code, if applicable. Set to zero for top level codes without parents.
OC_FIELD_ID The ID of the task or resource custom outline code field.
OC_NAME The code value.
MSP_PROJECTS PROJ_EXT_EDITED Must be set to "True" for Microsoft Project to process.

Once a custom outline code mask has been defined, you can define the 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 This value must refer to a valid project in the MSP_PROJECTS table.
  CODE_CATEGORY The category is 0 for task fields or 1 for resource fields.
CODE_REF_UID Depending on the category, specify the corresponding TASK_UID or RES_UID value of the row for which the custom outline code is being set.
CODE_FIELD_ID The ID of the task or resource custom outline code field.
CODE_UID Refers to the lowest level code. The entire code is derived from the referenced code and all of its parent codes.

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 outline code field 1 for task 2 is "A.x".

Note: When deleting a custom outline code from MSP_OUTLINE_CODES, 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. As with custom WBS codes, the alias is specified in the table MSP_ATTRIBUTE_STRINGS. The required fields are:

Table Columns Notes
MSP_ATTRIBUTE_STRINGS PROJ_ID This value must refer to a valid project in the MSP_PROJECTS table.
  AS_ID This value identifies the attribute string and must be unique within the project.
AS_VALUE The alias for the custom field.

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 This value must refer to a valid project in the MSP_PROJECTS table.
  ATTRIB_FIELD_ID Refers to a custom field. See MSP_STRING_TYPES and MSP_CONVERSIONS for 'Field ID'.
ATTRIB_ID Refers to the type of field attribute 206 for 'Field Alias'. See MSP_STRING_TYPES and MSP_CONVERSIONS for 'Field Attributes'.
ATTRIB_VALUE Must be set to -1.
AS_ID Refers to the row in MSP_ATTRIBUTE_STRINGS in which the alias is specified.
ATTRIB_EXT_EDITED Must be set to "True" for Microsoft Project to process.
MSP_PROJECTS PROJ_EXT_EDITED Must be set to "True" for Microsoft 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 Microsoft 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 This value must refer to a valid project in the MSP_PROJECTS table.
  AS_ID This value identifies the attribute string, which contains the formula and must be unique within the project.
AS_VALUE The formula as expressed in the Formula dialog box in Microsoft Project.

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 This value must refer to a valid project in the MSP_PROJECTS table.
  ATTRIB_FIELD_ID Refers to the column that the formula affects. See MSP_STRING_TYPES and MSP_CONVERSIONS for 'Field ID'.
ATTRIB_ID Refers to the type of field attribute. See MSP_STRING_TYPES and MSP_CONVERSIONS for 'Field Attributes'.
AS_ID Refers to the attribute string that defines the formula.
ATTRIB_EXT_EDITED Must be set to "True" for Microsoft Project to process.
MSP_PROJECTS PROJ_EXT_EDITED Must be set to "True" for Microsoft 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 defintion
              1 ) –- must set for Microsoft Project to process

To query the database for the existing formulae for custom field Number 3, 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 true in order for Microsoft Project to process. The row will be deleted when the project is saved in Microsoft Project.

Top

Reading and writing RTF notes

To retrieve data from the RTF notes columns in MSP_TASKS, MSP_RESOURCES or MSP_ASSIGNMENTS, 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

To write data to the RTF notes columns in MSP_TASKS, MSP_RESOURCES or MSP_ASSIGNMENTS, 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

The 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 MSP_TASKS and PROJ_EXT_EDITED in MSP_PROJECTS to true for Microsoft 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 are stored in the MSP_TEXT_FIELDS table are 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 true for Microsoft Project to process additional task, resource and assignment data stored in MSP_TEXT_FIELDS.

Note: Microsoft 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 email 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 email 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 can only be created, modified or deleted using Microsoft 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.

To increment the task IDs of all tasks after the 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 topic 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 Microsoft 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_IUD = 12

Be sure to update the column PROJ_EXT_EDITED in MSP_PROJECTS to 1 in order for Microsoft 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, you must specify values for both the ASSN_UNITS and ASSN_WORK columns in the table MSP_ASSIGNMENTS. 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_ID > 12

Be sure to back up the task data before updating row.  See the topic Ensuring data integrity for information on backup up existing task data.

Top

Retrieving Workgroup Message Status

The task and resource workgroup message status fields (Updated 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 column ASSN_IS_CONFIRMED in the table MSP_ASSIGNMENTS for all assignments against the task. If ASSN_IS_CONFIRMED is true for every assignment then Confirmed for the task is true. To determine whether a task is confirmed, copy, modify, and execute the following select statement:

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

If the query returns a number greater than zero, 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 true for one or more of the assignments, then Update Needed for the task is true. To determine whether a task is confirmed, copy, modify, and execute the following select statement:

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

If the query returns a number greater than zero, 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 Microsoft Project to process, you must set PROJ_EXT_EDITED and PROJ_EXT_EDITED_TEXT in MSP_PROJECTS to true for the updated project.

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

Top

Appendixes

Processing order of externally edited data

Overview

The processing order for Microsoft Project 2000 enforces the sequence in which the data is applied. For example, looking at the first list 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 Microsoft 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, Microsoft 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

Top

Standard processing order

Task fields:


Assignment fields:

Top

Database object naming conventions

Table Naming Conventions

Top

Column naming conventions

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

Top

Column name abbreviations

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

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

Top

Database tables

Informational-only columns

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

Top

Key columns

The following table definitions describe the database structure that is created when you save a whole project to a database. The fields that comprise the key for each table are indicated in the Key column by number (ordinal position of the columns within the key).

Top

Column data types

The following field types are used in the Microsoft Project database structure. The field type for each field is indicated in the tables below.

Field Type Description
bit Boolean where supported, Integer in database where Boolean is not supported
char Character
datetime Date including time
decimal(n,n) Decimal (Number in Oracle)
float Floating point number (Number in Oracle)
image Binary data greater than 255 bytes (data-type Blob in Oracle)
integer Integer (Number in Oracle)
long Long integer (Number in Oracle)
smallint Small integer (Number in Oracle)
text Longest text field available (8000 bytes in SQL Server, 64k in Microsoft Access, 255 characters in Oracle)
varchar(n) Variable-length character text unless otherwise indicated (Varchar2(n) in Oracle)

Where Microsoft 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_PROJECTS

Key Column Name Data Type Info. Only Description
1 PROJ_ID Integer   The unique ID for the project
  PROJ_NAME Varchar (255) The project name. Together with the data source name can't exceed 255 characters.
PROJ_PROP_AUTHOR Text The author of the project
PROJ_OPT_CALC_ACT_COSTS Bit Automatically calculate actual costs.
PROJ_PROP_CATEGORY Text Project category.
PROJ_PROP_COMPANY Text Company of the project.
PROJ_OPT_CRITICAL_SLACK_LIMIT Integer Maximum number of slack days to be critical.
PROJ_OPT_CURRENCY_DIGITS Integer Number of digits after the decimal (currency).
PROJ_OPT_CURRENCY_POSITION Integer Position of the currency symbol
PROJ_OPT_CURRENCY_SYMBOL Varchar (20) Currency symbol used in the project.
PROJ_OPT_NEW_ARE_EFFORT_DRIVEN Bit New tasks are effort-driven by default.
PROJ_INFO_CURRENT_DATE Datetime X Current date of the project.
PROJ_OPT_DEF_FINISH_TIME Datetime   Default finish time of new projects.
PROJ_OPT_DEF_FIX_COST_ACCRUAL Smallint Fixed costs are accrued by default.
PROJ_OPT_MINUTES_PER_DAY Integer Default number of minutes per day.
PROJ_OPT_MINUTES_PER_WEEK Integer Default number of minutes per week.
PROJ_OPT_DEF_OVT_RATE Decimal (25,6) Default overtime rate for new resources.
PROJ_OPT_DEF_STD_RATE Decimal (25,6) Default standard rate for new resources.
PROJ_OPT_DEF_START_TIME Datetime Default finish time of new tasks.
PROJ_OPT_DEF_TASK_TYPE Smallint Default task type (0=material, 1=work)
PROJ_OPT_DUR_ENTRY_FMT Smallint Default duration format.
PROJ_INFO_FINISH_DATE Datetime Project finish date.
PROJ_OPT_HONOR_CONSTRAINTS Bit Whether tasks honor their constraint dates.
PROJ_PROP_KEYWORDS Text Keywords for this project.
PROJ_LAST_SAVED Datetime Last date the project was saved.
PROJ_PROP_MANAGER Text Manager of the project.
PROJ_OPT_MULT_CRITICAL_PATHS Bit Whether multiple critical paths are calculated.
PROJ_CREATION_DATE Datetime Date the project was created.
PROJ_POOL_ATTACHED_TO Text X Resource pool file name.
PROJ_INFO_CAL_NAME Text   Name of the project calendar.
PROJ_IS_RES_POOL Bit Whether the project is a resource pool or sharer of a resource pool.
PROJ_INFO_SCHED_FROM Smallint Whether the project is scheduled from a start data or finish date.
PROJ_OPT_SPLIT_IN_PROGRESS Bit Whether in-progress tasks may be split.
PROJ_OPT_SPREAD_ACT_COSTS Bit Whether actual costs are spread to the status date.
PROJ_OPT_SPREAD_PCT_COMP Bit Whether percent complete is spread to the status date.
PROJ_INFO_START_DATE Datetime Project start date.
PROJ_INFO_STATUS_DATE Datetime Project status date.
PROJ_PROP_SUBJECT Text Subject of the project.
PROJ_PROP_TITLE Text Subject of the project.
PROJ_OPT_TASK_UPDATES_RES Bit Whether updates to task update resources.
PROJ_OPT_WORK_ENTRY_FMT Smallint Default work unit.
PROJ_OPT_CALC_SUB_AS_SUMMARY Bit Whether to calculate subtasks as summary tasks.
PROJ_OPT_WEEK_START_DAY Smallint First day of the week.
PROJ_OPT_FY_START_MONTH Smallint First month of the year.
PROJ_OPT_FY_USE_START_YR Bit Whether to use fiscal starting year.
PROJ_OPT_DAYS_PER_MONTH Smallint Default number of days per month.
PROJ_OPT_NEW_TASK_EST Bit Whether new tasks are estimated by default.
PROJ_OPT_SHOW_EST_DUR Bit Whether to show the estimated duration by default.
PROJ_OPT_EXPAND_TIMEPHASED Bit Whether to save expanded time-phasedtimephased data to the MSP_TIMEPHASED database table.
PROJ_EXT_EDITED_DATE Bit Set to indicate that a custom date field was edited externally.
PROJ_EXT_EDITED_DUR Bit Set to indicate that a custom duration field was edited externally.
PROJ_EXT_EDITED_NUM Bit Set to indicate that a custom number field was edited externally.
PROJ_EXT_EDITED_FLAG Bit Set to indicate that a custom flag field was edited externally.
PROJ_EXT_EDITED_CODE Bit Set to indicate that an outline code field was edited externally.
PROJ_EXT_EDITED_TEXT Bit Set to indicate that a custom text field was edited externally.
PROJ_EXT_EDITED Bit Set to indicate that the project was edited externally.
PROJ_DATA_SOURCE Varchar (128) X Data source name for the project. Used by Microsoft Project when checking for concurrent usage.
PROJ_READ_ONLY Varchar (10) X Indicates whether the project is read-only.
PROJ_READ_WRITE Varchar (4) X Indicates whether the project is open for write.
PROJ_READ_COUNT Varchar (10) X Indicates how many users have one or more tables open read-only.
PROJ_LOCKED Varchar (4) X Indicates whether a user is writing to a table.
PROJ_MACHINE_ID Varchar (100)   The machine ID of the user who has opened the project.
RESERVED_BINARY_DATA Image X Reserved for use by Microsoft Project. Do not change.

Top

MSP_TASKS

This table contains task data. Microsoft Project will write three rows to this table with unique IDs of zero (project-level outline), -65536, -65535 and -65534. Microsoft Project uses these rows internally. You should not edit or delete these rows.

Key Column Name Data Type Info. Only Description
  RESERVED_DATA char X Set to indicate whether the table is locked.
1 PROJ_ID int   The unique ID of the project.
  TASK_ACWP decimal X Actual cost of work performed.
TASK_BCWP decimal X Budgeted cost of work performed.
TASK_BCWS decimal X Budgeted cost of work scheduled.
TASK_DUR_VAR integer X Duration variance.
TASK_FINISH_VAR integer X Finish variance.
TASK_OUTLINE_NUM varchar X Outline level.
TASK_START_VAR integer X Start variance.
TASK_IS_OVERALLOCATED bit X Indicates whether task is overallocated.
TASK_OVT_WORK decimal X Amount of overtime work.
TASK_VAC decimal   Amount of vacation.
TASK_REG_WORK decimal X Amount of regular work.
TASK_NUM_OBJECTS integer X Number of objects in the task notes.
TASK_TOTAL_SLACK integer   Amount of total slack.
EXT_EDIT_REF_DATA text Set to indicate a new row or backup existing data when changing project information in the database.
2 TASK_UID integer Unique ID for the task within the project.
  TASK_ID integer Task ID.
TASK_HAS_LINKED_FIELDS bit Indicates whether there are data links to the task.
TASK_IS_MILESTONE bit Indicates whether the task is a milestone.
TASK_IS_CRITICAL bit Indicates whether the task is critical.
TASK_IS_SUMMARY bit Indicates whether the task is a summary task.
TASK_IS_SUBPROJ bit Indicates whether the task is an inserted project.
TASK_IS_MARKED bit Indicates whether the task is marked.
TASK_IGNORES_RES_CAL bit Indicates whether the resource calendar is ignored.
TASK_IS_ROLLED_UP bit Indicates whether the task is rolled up.
TASK_IS_FROM_FINISH_SUBPROJ bit Indicates whether the inserted project is marked as schedule-from-finish.
TASK_BAR_IS_HIDDEN bit Indicates whether the Gantt bars are hidden when displayed in Microsoft Project.
TASK_IS_RECURRING bit Indicates whether the task is recurring.
TASK_IS_RECURRING_SUMMARY bit Indicates whether the task is a summary of recurring tasks.
TASK_IS_EXTERNAL bit X Indicates whether this task is external and cross-project linked to a task in this project.
TASK_IS_EFFORT_DRIVEN bit   Indicates whether the task is effort-driven.
TASK_IS_COLLAPSED bit Indicates whether the summary task is collapsed when displayed in Microsoft Project.
TASK_HAS_NOTES bit Indicates whether the task has RTF notes.
TASK_IS_READONLY_SUBPROJ bit Indicates whether the inserted project is read-only.
TASK_LEVELING_CAN_SPLIT bit Indicates whether leveling can cause a task split.
TASK_LEVELING_ADJUSTS_ASSN bit Indicates whether leveling can adjust assignments.
TASK_DUR_IS_EST bit X Indicates whether task duration is estimated.
TASK_EARLY_FINISH datetime   Early finish date.
TASK_LATE_START datetime Late start date.
TASK_STOP_DATE datetime Stop date.
TASK_RESUME_DATE datetime X Resume date.
TASK_FREE_SLACK integer   Amount of free slack.
TASK_OUTLINE_LEVEL smallint Outline level.
TASK_DUR integer Planned duration of the task.
TASK_DUR_FMT smallint Units in which the duration is displayed in Microsoft Project.
TASK_ACT_DUR integer Actual duration of the task.
TASK_REM_DUR integer Remaining duration of the task.
TASK_BASE_DUR integer Baseline duration of the task.
TASK_BASE_DUR_FMT smallint Units in which the baseline duration is displayed in Microsoft Project.
TASK_CONSTRAINT_TYPE smallint Constraint type.
TASK_LEVELING_DELAY integer Delay caused by leveling.
TASK_LEVELING_DELAY_FMT smallint Units in which the leveling delay is displayed in Microsoft Project.
TASK_START_DATE datetime Scheduled start date.
TASK_FINISH_DATE datetime Scheduled finish date.
TASK_ACT_START datetime Actual start date.
TASK_ACT_FINISH datetime Actual finish date.
TASK_BASE_START datetime Baseline start date.
TASK_BASE_FINISH datetime Baseline finish date.
TASK_CONSTRAINT_DATE datetime Constraint date.
TASK_PRIORITY smallint Priority of the task.
TASK_PCT_COMP smallint Percent complete.
TASK_PCT_WORK_COMP smallint Percent of work complete
TASK_TYPE smallint Task type.
TASK_FIXED_COST_ACCRUAL smallint The method used for accruing fixed costs.
TASK_CREATION_DATE datetime The date the task was created.
TASK_PRELEVELED_START datetime Start date before the task was leveled.
TASK_PRELEVELED_FINISH datetime Finish date before the task was leveled.
TASK_EARLY_START datetime Early start date.
TASK_LATE_FINISH datetime Late finish date.
TASK_CAL_UID integer Task calendar.
TASK_DEADLINE datetime Task schedule deadline date.
TASK_WORK decimal Amount of scheduled work.
TASK_BASE_WORK decimal Amount of baseline work.
TASK_ACT_WORK decimal Amount of actual work.
TASK_REM_WORK decimal Amount of remaining work.
TASK_COST decimal Scheduled cost.
TASK_FIXED_COST decimal Fixed cost of the task.
TASK_ACT_COST decimal Actual cost
TASK_REM_COST decimal X Remaining cost.
TASK_BASE_COST decimal   Baseline cost.
TASK_ACT_OVT_WORK decimal Amount of actual overtime work.
TASK_REM_OVT_WORK decimal Amount of remaining overtime work.
TASK_OVT_COST decimal Scheduled overtime cost.
TASK_ACT_OVT_COST decimal Actual overtime cost.
TASK_REM_OVT_COST decimal Remaining overtime cost.
TASK_WBS text WBS number.
TASK_NAME varchar Task name.
TASK_WBS_RIGHTMOST_LEVEL text The WBS level of the task.
TASK_RTF_NOTES image Rich-text format notes.

Top

MSP_RESOURCES

This table contains resource data. Microsoft Project will write three rows to this table with unique IDs of zero (null resource required for task zero), -65536, -65535 and -65534. Microsoft Project uses these rows internally. You should not edit or delete these rows.

Key Column Name Data Type Info. Only Description
  RESERVED_DATA char    
1 PROJ_ID integer The unique ID for the project.
  RES_ACWP decimal X Actual cost of work performed.
RES_BCWP decimal X Budgeted cost of work performed.
RES_BCWS decimal X Budgeted cost of work scheduled.
RES_NUM_OBJECTS integer X The number of objects attached to the resource, not including those in notes.
EXT_EDIT_REF_DATA text   Set to indicate a new row or backup existing data when changing project information in the database.
2 RES_UID integer The unique ID for the resource in the project.
  RES_ID integer The resource ID
RES_HAS_LINKED_FIELDS bit Whether there are data links to the resource.
RES_IS_OVERALLOCATED bit Whether the resource is overallocated.
RES_TYPE bit The type of resource. 0=material, 1=work.
RES_HAS_NOTES bit Indicates whether the resource has RTF notes.
RES_CAN_LEVEL bit Whether the resource can be leveled.
RES_STD_RATE_FMT smallint The units in which the standard rate is displayed in Microsoft Project.
RES_OVT_RATE_FMT smallint The units in which the overtime rate is displayed in Microsoft Project.
RES_ACCRUE_AT smallint The way resource costs are accrued.
RES_WORKGROUP_MESSAGING smallint How the resource receives workgroup messages.
RES_CAL_UID integer The unique ID for the resource calendar.
RES_AVAIL_FROM datetime X The first date from which the resource is available. This date is derived from the earliest date value in the column AVAIL_FROM in the table MSP_AVAILABILITY for the specified resource. This column should not be updated.
RES_AVAIL_TO datetime X The last date for which the resource is available. This date is derived from the latest date value in the column AVAIL_TO in the table MSP_AVAILABILITY for the specified resource. This column should not be updated.
RES_STD_RATE decimal   The standard rate as of the current date.
RES_OVT_RATE decimal The overtime rate as of the current date.
RES_MAX_UNITS decimal The maximum number of units available for the resource.
RES_WORK decimal The total work scheduled for a resource across all tasks assigned to the resource.
RES_ACT_WORK decimal The actual amount of work.
RES_BASE_WORK decimal The baseline work.
RES_OVT_WORK decimal The amount of overtime work.
RES_COST_PER_USE decimal The cost per use as of the current date.
RES_REM_WORK decimal The amount of remaining work.
RES_REG_WORK decimal The amount of regular work.
RES_ACT_OVT_WORK decimal The actual overtime work.
RES_REM_OVT_WORK decimal The remaining overtime work.
RES_PEAK decimal X The greatest number of resource units assigned at any time.
RES_ACT_COST decimal   The actual cost.
RES_COST decimal The cost.
RES_BASE_COST decimal The baseline cost.
RES_REM_COST decimal The remaining cost.
RES_OVT_COST decimal The overtime cost.
RES_ACT_OVT_COST decimal The actual overtime cost.
RES_REM_OVT_COST decimal The remaining overtime cost.
RES_NAME varchar The resource's name.
RES_INITIALS varchar The resource's initials.
RES_PHONETICS text In some languages, the phonetic spelling of the resource's name.
RES_MATERIAL_LABEL text The unit of measurement applied to a material resource.
RES_RTF_NOTES image Rich-text format notes.

Top

MSP_ASSIGNMENTS

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

Key Column Name Data Type Info. Only Description
  RESERVED_DATA char    
1 PROJ_ID integer The unique ID of the project.
  ASSN_ACT_FINISH datetime The actual finish date of the assignment.
ASSN_ACT_START datetime The actual start date.
ASSN_ACWP decimal X Actual cost of work performed.
ASSN_BCWP decimal X Budgeted cost of work performed.
ASSN_BCWS decimal X Budgeted cost of work scheduled.
ASSN_RES_TYPE bit   The type of resource. 0=material, 1=work.
ASSN_IS_OVERALLOCATED bit X Whether the assignment is overallocated.
ASSN_WORK_CONTOUR smallint X The type of assignment work.
ASSN_START_VAR integer X The start variance.
ASSN_FINISH_VAR integer X The finish variance.
ASSN_UPDATE_NEEDED bit X Whether a workgroup update is needed.
EXT_EDIT_REF_DATA text   Set to indicate a new row or backup existing data when changing project information in the database.
2 ASSN_UID integer The unique ID of the assignment in the project.
  ASSN_HAS_LINKED_FIELDS bit Whether there are data links to the assignment.
ASSN_IS_CONFIRMED bit Whether the assignment is confirmed.
ASSN_RESPONSE_PENDING bit Whether a response to a TeamAssign message is pending from the assigned resource.
ASSN_HAS_NOTES bit Indicates whether the assignment has RTF notes.
ASSN_TEAM_STATUS_PENDING bit Whether responses are pending to an TeamStatus message.
TASK_UID integer The unique ID of the task in the assignment. This value must be a valid TASK_UID in the MSP_TASKS table.
RES_UID integer The unique ID of the resource in assignment. This value must be a valid RES_UID in the MSP_RESOURCES table.
ASSN_START_DATE datetime The start date of the assignment.
ASSN_FINISH_DATE datetime The end date of the assignment.
ASSN_DELAY integer The delay for the assignment.
ASSN_DELAY_FMT smallint The units in which the delay is displayed in Microsoft Project.
ASSN_LEVELING_DELAY integer The amount of time the assignment is delayed due to leveling.
ASSN_COST_RATE_TABLE smallint The cost rate table used for this assignment.
ASSN_BASE_START datetime The baseline start date.
ASSN_BASE_FINISH datetime The baseline finish date.
ASSN_MATERIAL_RATE_FMT smallint The units in which the material rate is displayed in Microsoft Project. See "Cost Rate Data Units" in MSP_STRING_TYPES for rate formats.
ASSN_UNITS decimal The number of units assigned.
ASSN_WORK decimal The amount of work that the resource is scheduled to perform on the assigned task.
ASSN_ACT_WORK decimal The actual amount of work.
ASSN_REG_WORK decimal The amount of regular work.
ASSN_REM_WORK decimal The remaining work.
ASSN_BASE_WORK decimal The baseline work.
ASSN_COST decimal X The cost of the assignment.
ASSN_ACT_COST decimal X The actual cost of the assignment.
ASSN_REM_COST decimal X The remaining cost.
ASSN_BASE_COST decimal   The baseline cost
ASSN_BASE_COST_PER_USE decimal The baseline cost per use.
ASSN_OVT_WORK decimal The amount of overtime work.
ASSN_ACT_OVT_WORK decimal The actual amount of overtime work.
ASSN_REM_OVT_WORK decimal The remaining overtime work.
ASSN_ACT_OVT_COST decimal X The actual overtime cost.
ASSN_REM_OVT_COST decimal X The remaining overtime cost.
ASSN_RTF_NOTES image   Rich Text Format notes.

Top

MSP_AVAILABILITY

This table contains data about resource availability.

Key Column Name Data Type Info. Only Description
  RESERVED_DATA char    
1 PROJ_ID integer The unique ID of the project.
2 AVAIL_UID integer The unique ID of the availability record.
  RES_UID integer The unique ID of the resource in assignment. This value must be a valid RES_UID in the MSP_RESOURCES table.
AVAIL_FROM datetime The date that the resource becomes available for the period defined.
AVAIL_TO datetime The date the the resource is no longer available for the period defined.
AVAIL_UNITS decimal The percent that the resource is available during the period defined.

Top

MSP_CALENDARS

This table contains basic calendar data. Microsoft Project will write three rows to this table with unique IDs of 1, 2, -65536, -65535, -65534 and -65533. Microsoft Project uses these rows internally. You should not edit or delete these rows.

Key Column Name Data Type Info. Only Description
  RESERVED_DATA char    
1 PROJ_ID integer The unique ID for the project.
2 CAL_UID integer The unique ID for the calendar in the project.
  CAL_BASE_UID integer The unique ID of the base calendar if this is a resource calendar.
RES_UID integer The unique ID of the resource to whom the calendar is assigned.
CAL_IS_BASE_CAL bit Whether this is a base calendar.
CAL_NAME varchar The calendar name, not filled in if it is a resource calendar.

Top

MSP_CALENDAR_DATA

This table contains calendar working day and exception data.

Key Column Name Data Type Info. Only Description
  RESERVED_DATA char    
1 PROJ_ID integer The unique ID for the project.
2 CD_UID integer The unique ID for this exception.
  CAL_UID integer The unique ID for the calendar in the project.
CD_DAY_OR_EXCEPTION smallint Specifies whether the day is a standard working day or an exception:

0 = exception

1-7 = day of week

CD_WORKING smallint Whether the date range is working or nonworking. A value of 0 is nonworking and 1 is working.
CD_FROM_DATE datetime Date the exception starts.
CD_TO_DATE datetime Date the exception finishes.
CD_FROM_TIME1 datetime Start time of the first shift.
CD_TO_TIME1 datetime Finish time of the first shift.
CD_FROM_TIME2 datetime Start time of the second shift.
CD_TO_TIME2 datetime Finish time of the second shift.
CD_FROM_TIME3 datetime Start time of the third shift.
CD_TO_TIME3 datetime Finish time of the third shift.
CD_FROM_TIME4 datetime Start time of the fourth shift.
CD_TO_TIME4 datetime Finish time of the fourth shift.
CD_FROM_TIME5 datetime Start time of the fifth shift.
CD_TO_TIME5 datetime Finish time of the fifth shift.

Top

MSP_LINKS

This table contains data about task dependencies.

Key Column Name Data Type Info. Only Description
  RESERVED_DATA char    
1 PROJ_ID integer The unique ID of the project.
  EXT_EDIT_REF_DATA text Set to indicate a new row or backup existing data when changing project information in the database.
2 LINK_UID integer The unique ID of the dependency in the project.
  LINK_IS_CROSS_PROJ bit X Whether this is a cross-project link. The external task is stored in the MSP_TASKS table.
LINK_PRED_UID integer   The unique ID of the predecessor task.
LINK_SUCC_UID integer The unique ID of the successor task.
LINK_TYPE smallint The link type: Finish to Start, Finish to Finish, Start to Start, or Start to Finish.
LINK_LAG_FMT smallint The units in which the lag is displayed in Microsoft Project.
LINK_LAG integer The amount of lag.

Top

MSP_TIMEPHASED_DATA

This table contains work, actual work and cost data over time.

Key Column Name Data Type Info. Only Description
  RESERVED_DATA char    
1 PROJ_ID int The unique ID for the project.
2 TD_UID int The unique ID of the timephased data record.
  TD_FIELD_ID smallint The work, cost, or percent completed field to which the record applies.
TD_CATEGORY smallint Whether this is a record for a task or an assignment.
TD_REF_UID int The unique ID for the task or assignment to which the record applies.
TD_START datetime The start date of the record.
TD_FINISH datetime The finish date of the record
TD_UNITS smallint The unit of time to which the specified values apply.
TD_VALUE1 decimal The value of work, percent complete, or cost in the units specified during the first time period specified in TD_UNITS beginning with the start date.
TD_VALUE2 decimal The value of work, percent complete, or cost in the units specified during the second time period.
TD_VALUE3 decimal The value of work, percent complete, or cost in the units specified during the third time period.
TD_VALUE4 decimal The value of work, percent complete, or cost in the units specified during the fourth time period.
TD_VALUE5 decimal The value of work, percent complete, or cost in the units specified during the fifth time period.
TD_VALUE6 decimal The value of work, percent complete, or cost in the units specified during the sixth time period.
TD_VALUE7 decimal The value of work, percent complete, or cost in the units specified during the seventh time period.
TD_EXT_EDITED bit Indicates that the record has been externally edited.

Top

MSP_RESOURCE_RATES

This table contains data about the five available resource cost rate tables.

Key Column Name Data Type Info. Only Description
  RESERVED_DATA char    
1 PROJ_ID int The unique ID for the project.
2 RR_UID int The unique ID for this rate in the project.
  RES_UID int The unique ID for the resource in the project.
RR_RATE_TABLE smallint The rate table. The integers 0 to 4 correspond to the rate tables A to E.
RR_FROM_DATE datetime The first date for which the rates are effective.
RR_TO_DATE datetime The last date for which the rates are effective.
RR_STD_RATE decimal The standard rate.
RR_STD_RATE_FMT smallint The units in which the standard rate is displayed in Microsoft Project.
RR_OVT_RATE decimal The overtime rate.
RR_OVT_RATE_FMT smallint The units in which the overtime rate is displayed in Microsoft Project.
RR_PER_USE_COST decimal The per-use cost.

Top

MSP_TEXT_FIELDS

This table contains custom text data as well as other task and resource text data.

Key Column Name Data Type Info. Only Description
1 PROJ_ID integer   The unique ID for the project.
2 TEXT_CATEGORY integer Whether this is a record for a task, resource, or an assignment.
3 TEXT_REF_UID integer The unique ID for the task, resource, or assignment to which the custom field applies.
4 TEXT_FIELD_ID integer The unique ID for the custom text field.
  TEXT_VALUE varchar

(255)

The text value stored in the text field.

Top

MSP_NUMBER_FIELDS

This table contains custom number data.

Key Column Name Data Type Info. Only Description
1 PROJ_ID integer   The unique ID for the project.
2 NUM_CATEGORY integer Whether this is a record for a task, resource, or an assignment.
3 NUM_REF_UID integer The unique ID for the task, resource, or assignment to which the custom number or cost field applies.
4 NUM_FIELD_ID integer The unique ID for the custom date, start date, or finish date.
  NUM_VALUE decimal The number value stored in the custom number or cost field.

Top

MSP_DATE_FIELDS

This table contains custom date data.

Key Column Name Data Type Info. Only Description
1 PROJ_ID integer   The unique ID for the project.
2 DATE_CATEGORY integer Whether this is a record for a task, resource, or an assignment.
3 DATE_REF_UID integer The unique ID for the task, resource, or assignment to which the custom date, start, or finish field applies.
4 DATE_FIELD_ID integer The unique ID for the custom date, start, or finish field.
  DATE_VALUE datetime The date value stored in the custom date, start, or finish field.

Top

MSP_DURATION_FIELDS

This table contains custom duration data.

Key Column Name Data Type Info. Only Description
1 PROJ_ID integer   The unique ID for the project.
2 DUR_CATEGORY integer Whether this is a record for a task, resource, or an assignment.
3 DUR_REF_UID integer The unique ID for the task, resource, or assignment for which the custom duration field applies.
4 DUR_FIELD_ID integer The unique ID for the custom duration field.
  DUR_VALUE integer The duration value stored in the custom duration field.
DUR_FMT smallint The units in which the custom field is displayed in Microsoft Project.

Top

MSP_FLAG_FIELDS

This table contains custom flags.

Key Column Name Data Type Info. Only Description
1 PROJ_ID integer   The unique ID for the project.
2 FLAG_CATEGORY integer Whether this is a record for a task, a resource, or an assignment.
3 FLAG_REF_UID integer The unique ID for the task, resource, or assignment for which the custom flag field applies.
4 FLAG_FIELD_ID integer The unique ID for the custom flag field.
  FLAG_VALUE bit The value of the flag: true or false.

Top

MSP_OUTLINE_CODES

This table contains data about custom outline codes.

Key Column Name Data Type Info. Only Description
  RESERVED_DATA char    
1 PROJ_ID integer The unique ID for the project.
2 CODE_UID integer The unique ID of the code for this project.
  OC_PARENT integer The parent code of the the code being defined.
OC_FIELD_ID integer The custom outline code field for which the code is defined.
OC_NAME text The code being defined.
OC_DESCRIPTION text A description of the code being defined.

Top

MSP_CODE_FIELDS

This table contains data about which tasks are related to which outline codes.

Key Column Name Data Type Info. Only Description
1 PROJ_ID integer   The unique ID for the project.
2 CODE_CATEGORY integer Whether this is a task or resource outline code.
3 CODE_REF_UID integer The unique ID for the task or resource to which the custom outline code applies.
4 CODE_FIELD_ID integer The custom outline code field to which the code applies.
  CODE_UID integer Refers to the code in MSP_OUTLINE_CODES.

Top

MSP_STRING_TYPES

This table contains the character string categories. All of the Microsoft Project strings belong to a category.

Key Column Name Data Type Info. Only Description
1 STRING_TYPE_ID integer X A number representing the enumerated field category.
2 STRING_LANG_ID integer X The ID of the language in which the field name is shown.
  STRING_TYPE varchar X The name of the enumerated field category.

Top

MSP_CONVERSIONS

This table contains all of the Microsoft Project character strings.

Key Column Name Data Type Info. Only Description
3 STRING_TYPE_ID integer X A number representing the enumerated field category.
1 CONV_VALUE integer X The value seen in the database fields.
2 CONV_LANG_ID integer X The ID of the language in which the conversion text is displayed.
  CONV_STRING varchar X The text representation of the field contents.

Top

MSP_FIELD_ATTRIBUTES

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

Key Column Name Data Type Info. Only Description
  RESERVED_DATA char    
1 PROJ_ID integer The unique ID for the project.
2 ATTRIB_FIELD_ID integer The custom field field to which the code applies.
3 ATTRIB_ID integer The attribute of the custom field.
  ATTRIB_VALUE integer The value of the attribute in the case of Boolean or enumerated numeric values.
AS_ID integer Refers to the attribute string in the case where the attribute value is not numeric.
ATTRIB_EXT_EDITED bit Indicates that the record has been externally edited.

Top

MSP_ATTRIBUTE_STRINGS

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

Key Column Name Data Type Info. Only Description
  RESERVED_DATA char    
1 PROJ_ID integer The unique ID for the project.
2 AS_ID integer The unique ID of the attribute string.
  AS_POSITION integer Indicates the position of a resource pool sharer in the list of sharer projects.
AS_VALUE varchar The character value of the attribute.

Top