Microsoft Project 2002 Database Information

(C) 2002 Microsoft Corporation. All rights reserved.

Contents

Overview
  What's new in the Microsoft Project database

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

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

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

Working with Projects in the Microsoft Project Database
  Microsoft Project tables
  Creating the Microsoft Project database structure
  DSN requirements and limitations
  Concurrent usage and project locking
  Deleting a project from a database
  Renaming projects in a database
  Appending XML data to existing projects

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 order
  Creating splits in scheduled work
  Creating or modifying an assignment remaining-work order
  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 (cost) fields
  Specifying custom date, start, and finish fields
  Specifying custom duration fields
  Field attributes
  Specifying custom WBS codes
  Specifying custom outline codes
  Specifying custom aliases for custom fields
  Specifying formulae for custom fields
  Reading and writing RTF notes

Managing Other Data in the Database
  Specifying an e-mail 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

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

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

Overview

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

Specific chapters include the following information:

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

Top

What's new in the Microsoft Project database

The Microsoft Project database has been expanded (8 new tables) and includes the following overall improvements:

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

The following fields have been added to existing Microsoft Project database tables (table names listed first):

Top

About the Microsoft Project Database

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

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 and 2002 databases if you directly select the Project Database (.mpd) or Microsoft Access Database (.mdb) as the file type in the File Open and File Save dialog boxes.

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

Top

Loading older versions of Microsoft Project into the database

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

Top

Upgrading the Microsoft Project database from an older version

You can use the Database Upgrade Utility COM add-in to upgrade some or all of the projects in a Microsoft Access or SQL Server database from the Microsoft Project 98 or Microsoft Project 2000 database structure to the Microsoft Project 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 still isolate projects from each other. This is called project isolation. To isolate projects, a separate table structure must be created for each project owner. Each database user can only see one set of project tables in a given database because Microsoft Project uses the first set of tables it finds in the database. Any security scheme must take this into consideration.

Notes

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

    Configuring the database

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

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

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

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

    Top

    Performance tuning

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

    Top

    Ensuring data integrity

    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:

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

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

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

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

    To make it easier to back up task, resource, assignment, and link data before updating, 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 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.

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

    Top

    How Information is Stored in the Microsoft Project Database

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

    Calendar data

    Microsoft Project saves calendar information to two tables:

    Top

    Timephased data

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

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

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

    Top

    Notes

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

    Top

    Custom field values

    Custom field values are stored in the following tables:

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

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

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

    Top

    Split Tasks

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

    Top

    Task links

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

    Top

    Estimated duration values

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

    Top

    Baselines

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

    Top

    Working with Projects in the Microsoft Project Database

    Microsoft Project tables

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

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

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

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

    Top

    Creating the Microsoft Project 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 (Microsoft Access only), and then create all of the appropriate tables. Creating all of the tables manually is an extensive undertaking because you would need to use this document as a reference to ensure you have exactly the right table and column names for every table and the correct data types for each column.

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

    Top

    DSN requirements and limitations

    Microsoft Project Server supports an integrated database. For users of Microsoft Project Standard, the database is used to store data added or viewed through Microsoft Project Web Access. For users of Microsoft Project Professional, the database is used to store both Microsoft Project and Microsoft Project Web Access data.

    Microsoft Project supports using a data source name (DSN) to connect to a server in the following instances:

    Connection information to the server is automatically communicated to Microsoft Project once the user identifies the correct server running Microsoft Project Server. To connect Microsoft Project to a server using a DSN, do one of the following:

    If you are using Microsoft Project Standard:

    If you are using Microsoft Project Professional in offline mode:

    Note   If multiple users need to access projects in the same database, each user making changes to the data does not need to use the same DSN for connecting to the database. Microsoft Project stores both the combined DSN and project name (and alternatively the connection string) as the identifiers to locate a project using the following format: <DSN>\ProjectName.

    DSN limitations in Microsoft Project:

    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 to the project. 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 Microsoft Project Help for more 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

    Appending XML data to existing projects

    When Microsoft Project appends XML data to an existing project, a summary task's unique ID is incremented from 0 to 1. Microsoft Project then checks for duplicate unique IDs elsewhere in the appending XML data; if any are found, the duplicate unique IDs are given new unique IDs. This process can cause assignments, tasks, resources, and cross-project links to become disassociated with original data.

    Note   To ensure that data doesn't end up in unintended locations, it is recommended that you separate this data into its smallest possible component: a task, a resource, an assignment, etc., before appending. You can also create a custom solution using Microsoft Project VBA and MSXML. See Microsoft Project VBA Help for more information.

    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 MSP_PROJECTS table must be set to 1.

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

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

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

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

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

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

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

    Top

    Specifying times with dates

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

    Thus, if you specify dates without times, it can result in the following situation: You create what you think is a five-day task in the database by specifying the task start as Monday's date and the task finish as Friday's date. The Monday time in the database is really Monday at 12:00 midnight, so Microsoft Project treats this as Monday, 8:00 A.M. (per the calendar default start time). For Friday's time, though, the default is Friday at 12:00 midnight, which is rolled back to Thursday at 5:00 P.M. (the calendar default finish time). Thus, your five-day task becomes only four days when it is read in by 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 contains the mapping between the enumerated field categories and the field name and MSP_CONVERSIONS contains the mapping between the numeric constants and the possible text values for each field. The following field categories are converted to numeric constants:

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

    You can store projects from different language versions of 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 a new project in the database, you must add a new row to the MSP_PROJECTS table and create a project summary task in the MSP_TASKS table. See Creating a new task, Creating a new resource, and Creating a new assignment below. Then, to add the associated tasks, resources, and assignments to the project, you must create the task, resource, and assignment rows as described below. You must enter values for at least the columns specified below.

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

    Notes

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

    Creating an inserted project

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

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

    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.

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

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

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

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

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

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

    Top

    Creating a new resource

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

    Table Fields Notes
    MSP_RESOURCES PROJ_ID Refers to a valid ID in the MSP_PROJECTS table.
    RES_UID The unique ID for the resource.
    RES_ID The position identifier of the resource in the list of resources.
    RES_NAME The name of the resource.
    RES_TYPE The resource type, either Work (0) or Material (1).
    EXT_EDIT_REF_DATA Must be set to 1 for Microsoft Project to process.
    MSP_PROJECTS PROJ_EXT_EDITED Must be set to 1 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',
                  0,
                  '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 Specifying calendar working time and exceptions for more information.) To create a new calendar, you must create a row with values for at least the following columns:

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

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

    Specifying calendar working time and exceptions

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

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

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

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

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

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

    Top

    Specifying resource availability

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

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

    To specify a period of availability from November 15, 2001, to November 20, 2001, 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,
                  '11/15/2001',
                  '11/21/2001',
                  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 Fields Notes
    MSP_RESOURCE_RATES PROJ_ID Refers to a valid ID in the MSP_PROJECTS table.
    RR_UID The unique ID for the resource rate.
    RES_UID Refers to a valid ID in the MSP_RESOURCES table.
    RR_RATE_TABLE Indicates which cost rate table to use for a resource on an assignment; 0-4 identifies cost rate tables A-E respectively.
    RR_STD_RATE or RR_OVT_RATE or RR_PER_USE_COST Must set a value in one of these three columns. Rates are shown in dollars per hour and per-use-costs are shown as dollars * 100. If either a standard or overtime rate is specified, you have the option to specify its format in either RR_STD_RATE_FMT or RR_OVT_RATE_FMT.
    MSP_PROJECTS PROJ_EXT_EDITED Must be set to 1 for Microsoft Project to process.

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

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

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

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

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

    Top

    Creating a new task

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

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

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

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

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

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

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

    Insert into MSP_TASKS (
                  PROJ_ID,
                  TASK_UID,
                  TASK_ID,
                  TASK_NAME,
                  TASK_DUR,
                  TASK_OUTLINE_LEVEL,
                  EXT_EDIT_REF_DATA )
              values (
                  3,
                  23,
                  8,
                  'my task',
                  4800,
                  1,
                  '1' )

    Notes

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

    Creating a recurring task

    While it is possible to create a recurring task in the database, it is preferable to create recurring tasks inside 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 Refers to a valid ID in the MSP_PROJECTS table.
    TASK_UID The unique ID for the task.
    TASK_ID The position identifier of the task in the list of tasks.
    TASK_NAME The name of the task; not required for sub-tasks.
    TASK_OUTLINE_LEVEL Indicates the outline level of the task; the level for a recurring sub-task must be one level lower than a recurring summary task.
    TASK_IS_RECURRING Set to 1 to indicate that a task is part of a series of recurring tasks.
    TASK_IS_RECURRING_SUMMARY Set to 1 to indicate that a task is a recurring summary task.
    TASK_IS_ROLLED_UP Set to 1 to indicate that a summary taskbar displays a rolled-up bar or to indicate that information on the subtask bar is to be rolled up to the summary task bar.
    TASK_CONSTRAINT_TYPE The type of constraint applied when scheduling a task; required for sub-tasks.
    EXT_EDIT_REF_DATA Must be set to 1 to indicate that this task is a new task.
    MSP_PROJECTS PROJ_EXT_EDITED Must be set to 1 for Microsoft Project to process.

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

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

    Top

    Creating task dependencies

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

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

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

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

    Table Fields Notes
    MSP_LINKS LINK_LAG Specifies the amount of lag, specified as duration value (minutes * 10).
    LINK_LAG_FMT Specifies the format for the amount of lag specified in LINK_LAG.
    EXT_EDIT_REF_DATA Must be set to 1 to indicate that this link is a new link.
    MSP_PROJECTS PROJ_EXT_EDITED Must be set to 1 for 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 Refers to a valid ID in the MSP_PROJECTS table.
    ASSN_UID The unique ID for the assignment.
    TASK_UID Refers to a valid ID in the MSP_TASKS table.
    RES_UID Refers to a valid ID in the MSP_RESOURCES table.
    ASSN_UNITS The percentage number of units for which a resource is assigned to a task, expressed as a percentage of 100 percent (%), assuming a resource's MaxUnits value is 100 percent.
    ASSN_WORK The total amount of work scheduled to be performed by a resource on a task; defaults to 0 if not specified.
    EXT_EDIT_REF_DATA Must be set to 1 to indicate that this assignment is a new assignment.
    MSP_PROJECTS PROJ_EXT_EDITED Must be set to 1 for 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

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

    Managing Timephased Data

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

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

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

    Table Fields Notes
    MSP_TIMEPHASED_DATA PROJ_ID Refers to a valid ID in the MSP_PROJECTS table.
    TD_UID The unique ID for the timephased data record.
    TD_FIELD_ID Refers to a valid CONV_VALUE in the MSP_CONVERSIONS table.
    TD_CATEGORY Set to 0 to indicate a Task.
    TD_REF_UID Shows the corresponding TASK_UID, RES_UID, or ASSN_UID value of the row for which the timephased data applies.
    TD_START The start of the first time period for which work, percent complete or cost values will be applied.
    TD_FINISH The end of the first time period for which work, percent complete or cost values will be applied.
    TD_UNITS The unit of time to which the specified values in TD_VALUE1 - TD_VALUE7 apply.
    TD_VALUE1 - TD_VALUE7 The value of the work, percent complete or cost for the first-seventh time period in the units specified in the TD_UNITS column during the date range specified in the TD_START and TD_FINISH columns; only values that fall between the specified start and finish dates will apply.
    TD_EXT_EDITED Must be set to 1 for Microsoft Project to process.
    MSP_PROJECTS PROJ_EXT_EDITED Must be set to 1 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
    Timephased record UID n/a TD_UID 4
    Details Actual Work TD_FIELD_ID 2
    Category n/a TD_CATEGORY 3
    Assignment UID n/a TD_REF_UID 16
    Time period timescale TD_START 10/15/2001 8:00:00 AM
    TD_FINISH 11/26/2001 8:00:00 AM
    Minor scale TD_UNITS 3
    Actual work 8h TD_VALUE1 480000
    4h TD_VALUE2 240000
    0h TD_VALUE3 0
    4h TD_VALUE4 240000

    Top

    Creating or modifying an assignment actual-work order

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

    Insert into MSP_TIMEPHASED_DATA (
                  PROJ_ID,
                  TD_UID,
                  TD_FIELD_ID,
                  TD_CATEGORY,
                  TD_REF_UID,
                  TD_START,
                  TD_FINISH,
                  TD_UNITS,
                  TD_VALUE1,
                  TD_VALUE2,
                  TD_VALUE3,
                  TD_VALUE4,
                  TD_EXT_EDITED )
              values (
                  3,
                  108,
                  2, -- assignment actual work
                  3, -- category = assignment
                  2, -- assignment UID
                  '10/15/2001 8:00:00 AM', -- start of the first time period
                  '11/26/2001 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 Microsoft Project as 11/25/2001 since 11/26/2001 08:00 is interpreted as the end of the working day which begins on 11/25.

    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 order

    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
                  '10/15/2001 8:00:00 AM', -- start of the first time period
                  '11/26/2001 8:00:00 AM', -- end of the last time period
                  3, -- each value column represents a week,
                  10032, -- week one value = $100.32 cost * 100,
                  1 )

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

    Insert into MSP_TIMEPHASED_DATA (
                  PROJ_ID,
                  TD_UID,
                  TD_FIELD_ID,
                  TD_CATEGORY,
                  TD_REF_UID,
                  TD_START,
                  TD_FINISH,
                  TD_UNITS,
                  TD_VALUE1,
                  TD_EXT_EDITED )
              values (
                  3,
                  110,
                  6, -- assignment actual cost
                  3, -- category = assignment
                  3, -- assignment UID
                  '10/15/2001 8:00:00 AM', -- start of the first time period
                  '11/26/2001 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 the PROJ_OPT_CALC_ACT_COSTS column in the MSP_PROJECTS table is set to 1.

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

    Update    MSP_PROJECTS 
    Set       PROJ_OPT_CALC_ACT_COSTS = 0 
    Where     PROJ_ID = 3 

    Top

    Creating or modifying task-percent complete contours

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

    Insert into MSP_TIMEPHASED_DATA (
                  PROJ_ID,
                  TD_UID,
                  TD_FIELD_ID,
                  TD_CATEGORY,
                  TD_REF_UID,
                  TD_START,
                  TD_FINISH,
                  TD_UNITS,
                  TD_VALUE1,
                  TD_VALUE2,
                  TD_VALUE3,
                  TD_EXT_EDITED )
              values (
                  3,
                  111,
                  11, -- task percent complete
                  0, -- category = task
                  3, -- TASK_UID
                  '10/15/2001 8:00:00 AM', -- start of the first time period
                  '11/26/2001 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 10/15/2001 at 8:00:00 AM, 0% completed the next day, and 28% completed on the day that begins 11/25/2001.

    Top

    Deleting a contour

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

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

    Top

    Customizing Project Data

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

    Top

    Specifying custom text fields

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

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

    Top

    Specifying custom number (or cost) fields

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

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

    Top

    Specifying custom date, start, and finish fields

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

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

    Top

    Specifying custom duration fields

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

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

    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_CONVERSIONS table), and either the actual attribute value or a pointer to the value. In the case of numeric and Boolean settings, the value is stored directly in MSP_FIELD_ATTRIBUTES, while string values are stored in the MSP_ATTRIBUTE_STRINGS table, and the index of the string is stored in MSP_FIELD_ATTRIBUTES.

    Top

    Specifying custom WBS codes

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

    Table Columns Notes
    MSP_ATTRIBUTE_STRINGS PROJ_ID Refers to a valid ID in the MSP_PROJECTS table.
    AS_ID Refers to a row in the MSP_ATTRIBUTE_STRINGS table that identifies the WBS string.
    AS_VALUE Shows the formula as expressed in the Formula dialog box in Microsoft Project.
    MSP_PROJECTS PROJ_EXT_EDITED Must be set to 1 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 MSP_FIELD_ATTRIBUTES table. One each for the WBS prefix and WBS code mask. Values must be provided for the following columns:

    Table Columns Notes
    MSP_FIELD_ATTRIBUTES PROJ_ID Refers to a valid ID in the MSP_PROJECTS table.
    ATTRIB_FIELD_ID Refers to a valid Field ID in the CONV_VALUE column in the MSP_CONVERSIONS table.
    ATTRIB_ID Refers to a valid Field Attribute in the STRING_TYPE_ID column of the MSP_CONVERSIONS table.
    ATTRIB_VALUE Shows the value of the attribute; a value of -1 indicates that the value is stored in the MSP_ATTRIBUTE_STRINGS table.
    AS_ID Refers to a row in the MSP_ATTRIBUTE_STRINGS table if ATTRIB_VALUE is set to -1.
    ATTRIB_EXT_EDITED Must be set to 1 for Microsoft Project to process.
    MSP_PROJECTS PROJ_EXT_EDITED Must be set to 1 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 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 Microsoft Project should enforce the uniqueness of new WBS codes in tasks, a row must be inserted into the MSP_FIELD_ATTRIBUTES table. To create this row, copy, modify, and execute the following insert statement:

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

    Finally, to specify whether 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 Refers to a valid ID in the MSP_PROJECTS table.
    CODE_UID Refers to the custom outline code and must be unique within the project.
    OC_PARENT Refers to a valid ID in the MSP_OUTLINE_CODES table; set to 0 for top-level codes without parents.
    OC_FIELD_ID Refers to a valid Field ID in the CONV_VALUE column in the MSP_CONVERSIONS table.
    OC_NAME Shows the name of the custom outline code.
    MSP_PROJECTS PROJ_EXT_EDITED Must be set to 1 for Microsoft Project to process.

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

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

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

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

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

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

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

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

    Top

    Specifying aliases for custom fields

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

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

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

    Table Columns Notes
    MSP_FIELD_ATTRIBUTES PROJ_ID Refers to a valid ID in the MSP_PROJECTS table.
    ATTRIB_FIELD_ID Refers to a valid Field ID in the CONV_VALUE column in the MSP_CONVERSIONS table.
    ATTRIB_ID Refers to a valid Field Attribute in the STRING_TYPE_ID column of the MSP_CONVERSIONS table.
    ATTRIB_VALUE Shows the value of the attribute; a value of -1 indicates that the value is stored in the MSP_ATTRIBUTE_STRINGS table.
    AS_ID Refers to a row in the MSP_ATTRIBUTE_STRINGS table if ATTRIB_VALUE is set to -1.
    ATTRIB_EXT_EDITED Must be set to 1 for Microsoft Project to process.
    MSP_PROJECTS PROJ_EXT_EDITED Must be set to 1 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 Refers to a valid ID in the MSP_PROJECTS table.
    AS_ID The unique ID for the attribute string.
    AS_VALUE Shows the formula as expressed in the Formula dialog box in Microsoft Project.

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

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

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

    Table Columns Notes
    MSP_FIELD_ATTRIBUTES PROJ_ID Refers to a valid ID in the MSP_PROJECTS table.
    ATTRIB_FIELD_ID Refers to a valid Field ID in the CONV_VALUE column in the MSP_CONVERSIONS table.
    ATTRIB_ID Refers to a valid Field Attribute in the STRING_TYPE_ID column of the MSP_CONVERSIONS table.
    AS_ID Refers to a row in the MSP_ATTRIBUTE_STRINGS table if ATTRIB_VALUE is set to -1.
    ATTRIB_EXT_EDITED Must be set to 1 for Microsoft Project to process.
    MSP_PROJECTS PROJ_EXT_EDITED Must be set to 1 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 definition
                  1 ) -- must set for Microsoft Project to process

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

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

    To delete a formula, update MSP_ATTRIBUTE_STRINGS setting AS_VALUE to null in the row that defines the formula. Remember to set ATTRIB_EXT_EDITED in the associated row in MSP_FIELD_ATTRIBUTES to 1 in order for 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 the MSP_TASKS, MSP_RESOURCES or MSP_ASSIGNMENTS tables, copy, modify and execute the following VB script:

    Sub getRtf()
        'This macro extracts RTF data from MSP_TASKS.TASK_RTF_NOTES.  This data can then be written
        'to a file that can be opened with Microsoft Word or displayed in a richedit control.
        
        Dim cn As New ADODB.Connection
        Dim rs As New ADODB.Recordset
        Dim sql As String, rtf As String, cnString
    
        'Open the MSP_TASKS table to look for TASK_RTF_NOTES
        cnString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\temp\MyProject.mpd"
        sql = "select PROJ_ID, TASK_UID, TASK_RTF_NOTES " & _
              "from MSP_TASKS " & _
              "where TASK_RTF_NOTES is not null" 'can specify a specific PROJ_ID and TASK_UID instead
        cn.Open cnString
        rs.Open sql, cn
        
        'Enumerate across the recordset looking for notes
        With rs
            Do While Not .EOF
                rtf = StrConv(.Fields("TASK_RTF_NOTES"), vbUnicode) ' Put binary column data into text string
                Debug.Print rtf
                .MoveNext
            Loop
            .Close
        End With
    End Sub

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

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

    Sub writeRtf()
        'This macro writes RTF data to MSP_TASKS.TASK_RTF_NOTES.
    
        Dim cn As New ADODB.Connection
        Dim cmd As New ADODB.Command
        Dim param As New ADODB.Parameter
        Dim sql As String, rtf As String, cnString As String
    
        cnString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\temp\MyProject.mpd"
        sql = "update MSP_TASKS set TASK_RTF_NOTES = ? where PROJ_ID = 1 and TASK_UID = 1"
        rtf = "{\rtf1\ansi\ansicpg1252\deff0\deflang1033{\fonttbl{\f0\fswiss\fcharset0 Arial;}}" & vbNewLine & _
              "\viewkind4\uc1\pard\f0\fs20 What's in a name? That which we call a rose...     \par" & vbNewLine & _
              "}" & vbNewLine & vbLf & Chr(0)   'be sure to specify valid RTF text here including "vbLf & Chr(0)
    
        cn.Open cnString
    
        param.Direction = adParamInput
        param.Type = adVarBinary
        param.Size = 8000
        param.Value = StrConv(rtf, vbFromUnicode)
    
        cmd.ActiveConnection = cn
        cmd.CommandText = sql
        cmd.Parameters.Append param
        cmd.Execute
    End Sub

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

    The ADO 2.1 (or later) library must be referenced in the Microsoft Visual Basic editor for VBA for these scripts to run. Be sure to set the columns TASK_HAS_NOTES in the MSP_TASKS table and PROJ_EXT_EDITED in the MSP_PROJECTS table to 1 for 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 is stored in the MSP_TEXT_FIELDS table is listed below:

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

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

    The columns PROJ_EXT_EDITED and PROJ_EXT_EDITED_TEXT in the MSP_PROJECTS table must be set to 1 for 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 e-mail address

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

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

    Top

    Retrieving cross-project link project/task references

    Cross-project links allow a task in one project to be dependent on a task in another project. Cross-project links are recorded in both the project which contains the dependent task and the project containing the task on which the other task depends. Cross-project links should only be created, modified or deleted using 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.

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

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

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

    Insert into MSP_TASKS (
                  PROJ_ID,
                  TASK_UID,
                  TASK_ID,
                  TASK_NAME,
                  TASK_OUTLINE_LEVEL,
                  EXT_EDIT_REF_DATA )
              values (
                  3,
                  24, -- new, unique task UID
                  13, -- the ID of the new task
                  'your subtask name', -- the name of the task
                  3, -- outline level of task 12 + 1
                  '1' ) -- set to 1 so 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_UID = 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, specify values for both the ASSN_UNITS and ASSN_WORK columns in the MSP_ASSIGNMENTS table. To change the work on an existing summary task assignment to two days at 50%, copy, modify, and execute the following update statement:

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

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

    Top

    Retrieving workgroup message status

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

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

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

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

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

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

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

    Top

    Specifying hyperlinks

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

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

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

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

    Top

    Database Processing Order, Conventions, and Abbreviations

    Processing order for externally edited data

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

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

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

    When 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

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

    Standard processing order

    The standard processing order for task fields is as follows:

    The standard processing order for assignment fields is as follows:

    Database Object Naming Conventions

    Table naming conventions

    The following conventions apply to table names:

    Top

    Column naming conventions

    The following conventions apply to column names:

    Table Name Column Prefix
    MSP_ASSIGNMENTS ASSN_
    MSP_ASSIGNMENT_BASELINES AB_
    MSP_ATTRIBUTE_STRINGS AS_
    MSP_AVAILABILITY AVAIL_
    MSP_CALENDARS CAL_
    MSP_CALENDAR_DATA CD_
    MSP_CODE_FIELDS CODE_
    MSP_CONVERSIONS CONV_
    MSP_DATE_FIELDS DATE_
    MSP_DURATION_FIELDS DUR_
    MSP_FIELD_ATTRIBUTES ATTRIB_
    MSP_FLAG_FIELDS FLAG_
    MSP_LINKS LINK_
    MSP_NUMBER_FIELDS NUM_
    MSP_OUTLINE_CODES OC_
    MSP_PROJECTS PROJ_
    MSP_RESOURCE_BASELINES RB_
    MSP_RESOURCE_RATES RR_
    MSP_RESOURCES RES_
    MSP_STRING_TYPES STRING_
    MSP_TASK_BASELINES TB_
    MSP_TASKS TASK_
    MSP_TEXT_FIELDS TEXT_
    MSP_TIMEPHASED_DATA TD_
    MSP_VERS_VERSIONS VERS_

    Top

    MSP_PROJECTS column qualifiers

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

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

    Column name abbreviations

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

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

    Top

    Database Tables

    Information-only columns

    Some fields contain data that is written by 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 I in the table descriptions.

    Top

    Bold column names

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

    Top

    Column data types

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

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

    Where 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_ASSIGNMENT_BASELINES

    This table contains custom assignment baseline data.

     Column NameData TypeDescription
    PROJ_IDintegerRefers to a valid ID in the MSP_PROJECTS table.
    ASSN_UIDintegerRefers to a valid ID in the MSP_ASSIGNMENTS table.
    AB_REF_ENT_PROJ_IDintegerRefers to a valid PROJ_ID in the MSP_PROJECTS table if the assignment baseline data is part of a summary assignment.
    AB_BASE_NUMsmallintThe number of the custom baseline, 1-10.
    AB_BASE_STARTdatetimeThe planned beginning date for an assignment at the time a baseline is saved.
    AB_BASE_FINISHdatetimeThe planned finish date for assignments.
    AB_BASE_WORKdecimalThe originally planned amount of work time to be performed by a resource on a task.
    AB_BASE_COSTdecimalThe total planned cost for work to be performed by a resource on a task.
    AB_BASE_COST_PER_USEdecimalThe cost-per-use for the custom baseline, 1-10.

    Top

    MSP_ASSIGNMENTS

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

     Column NameData TypeDescription
    RESERVED_DATA char(1)Temporarily stores information unique to the particular database row; you should not modify the contents of this field.
    PROJ_IDinteger Refers to a valid ID in the MSP_PROJECTS table.
    ASSN_ENTSUMPROJIDinteger Contains an ID of the project to which a summary assignment belongs.
    ASSN_ACT_FINISHdatetimeThe date and time when an assignment was actually completed.
    ASSN_ACT_STARTdatetimeThe date and time that an assignment actually began.
    IASSN_ACWPdecimalThe costs incurred for work already performed by a resource on a task up to the project status date or today's date; also called Actual Cost of Work Performed.
    IASSN_BCWPdecimalThe cumulative value of the assignment's timephased percentage of work complete multiplied by the assignment's timephased baseline cost up to the status date or today's date; also known as Earned Value.
    IASSN_BCWS decimalThe cumulative timephased baseline costs up to the status date or today's date.
    ASSN_RES_TYPEbitThe resource type:
    0 Work (default); people and equipment
    1 Material; consumable supplies like steel, concrete, or soil
    IASSN_IS_OVERALLOCATED bitIndicates whether a resource is assigned to more work on a specific task than can be done within the resource's normal working capacity.
    IASSN_WORK_CONTOUR smallintIndicates how work for an assignment is to be distributed across the duration of the assignment:
    0 Flat (default)
    1 Back Loaded
    2 Front Loaded
    3 Double Peak
    4 Early Peak
    5 Late Peak
    6 Bell
    7 Turtle
    8 Contoured
    IASSN_START_VAR integerThe difference between an assignment's baseline start date and its currently scheduled start date.
    IASSN_FINISH_VAR integerThe difference between an assignment's baseline finish date and its scheduled finish date.
    IASSN_UPDATE_NEEDED bitIndicates whether a TeamUpdate message should be sent to the resource assigned to a task because of changes to the start date, finish date, or resource reassignments.
    EXT_EDIT_REF_DATA textSet to indicate a new row or back up existing data when changing project information in the database.
    ASSN_UIDintegerThe unique ID for the assignment.
    ASSN_HAS_LINKED_FIELDS bitIndicates whether there are OLE links to the assignment.
    ASSN_IS_CONFIRMED bitIndicates whether a resource assigned to a task has accepted or rejected the task assignment in response to a TeamAssign message.
    ASSN_RESPONSE_PENDING bitIndicates whether an answer has been received from a TeamAssign message sent to a resource assigned to a task.
    ASSN_HAS_NOTES bitIndicates whether an assignment has RTF notes.
    ASSN_TEAM_STATUS_PENDING bitIndicates whether a status message has been received in response to a TeamStatus message sent to a resource assigned to a task.
    TASK_UIDintegerRefers to a valid ID in the MSP_TASKS table.
    RES_UIDintegerRefers to a valid ID in the MSP_RESOURCES table.
    ASSN_START_DATE datetimeThe date and time that an assigned resource is scheduled to begin working on a task.
    ASSN_FINISH_DATE datetimeThe date and time that an assigned resource is scheduled to complete work on a task.
    ASSN_DELAY integerThe amount of time a resource is to wait after the task start date before starting work on an assignment.
    ASSN_DELAY_FMT smallintIndicates the delay format:
    0 minute
    1 hour (default)
    2 day
    3 week
    4 month
    ASSN_LEVELING_DELAY integerThe amount of time that an assignment is to be delayed from the scheduled start date as a result of resource leveling.
    ASSN_COST_RATE_TABLE smallintIndicates which cost rate table to use for a resource on an assignment:
    0 A (default)
    1 B
    2 C
    3 D
    4 E
    ASSN_BASE_START datetimeThe planned beginning date for an assignment at the time a baseline is saved.
    ASSN_BASE_FINISH datetimeThe planned finish date for assignments.
    ASSN_MATERIAL_RATE_FMT smallintThe 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 decimalThe number of units for which a resource is assigned to a task, expressed as a percentage of 100 percent (%), assuming a resource's MaxUnits value is 100 percent.
    ASSN_WORK decimalThe total amount of work scheduled to be performed by a resource on a task.
    ASSN_ACT_WORK decimalThe amount of work that has already been done by a resource on a task.
    ASSN_REG_WORK decimalThe total amount of non-overtime work scheduled to be performed by a resource assigned to a task.
    ASSN_REM_WORK decimalThe amount of time required by a resource assigned to a task to complete an assignment.
    ASSN_BASE_WORK decimalThe originally planned amount of work time to be performed by a resource on a task.
    IASSN_COST decimalThe total scheduled (or projected) cost for an assignment based on costs already incurred for work performed by the resource on a task, in addition to the costs planned for the remaining work for the assignment.
    IASSN_ACT_COST decimalThe costs incurred for work already performed by a resource on a task.
    IASSN_REM_COST decimalThe costs associated with completing all remaining scheduled work by any resources on a specific task.
    ASSN_BASE_COST decimalThe total planned cost for work to be performed by a resource on a task.
    ASSN_BASE_COST_PER_USE decimalThe cost-per-use for the original baseline cost.
    ASSN_OVT_WORK decimalThe amount of overtime to be performed by a resource on a task; charged at the resource's overtime rate.
    ASSN_ACT_OVT_WORK decimalThe actual amount of overtime work already performed by a resource on an assigned task.
    ASSN_REM_OVT_WORK decimalThe amount of overtime work that remains on an assignment.
    IASSN_ACT_OVT_COST decimalThe costs incurred for overtime work already performed by a resource on a task.
    IASSN_REM_OVT_COST decimalThe remaining scheduled overtime expense for an assignment.
    ASSN_RTF_NOTES ImageContains notes about an assignment.

    Top

    MSP_ASSN_ENTERPRISE

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

     Column NameData TypeDescription
    RESERVED_DATA char(1)Temporarily stores information unique to the particular database row; you should not modify the contents of this field.
    PROJ_IDinteger Refers to a valid ID in the MSP_PROJECTS table.
    ASSN_ENTSUMPROJIDinteger Contains an ID of the project to which a summary assignment belongs.
    ASSN_ACT_FINISHdatetimeThe date and time when an assignment was actually completed.
    ASSN_ACT_STARTdatetimeThe date and time that an assignment actually began.
    IASSN_ACWPdecimalThe costs incurred for work already performed by a resource on a task up to the project status date or today's date; also called Actual Cost of Work Performed.
    IASSN_BCWPdecimalThe cumulative value of the assignment's timephased percentage of work complete multiplied by the assignment's timephased baseline cost up to the status date or today's date; also known as Earned Value.
    IASSN_BCWSdecimalThe cumulative timephased baseline costs up to the status date or today's date.
    ASSN_RES_TYPEbitThe resource type:
    0 Work (default); people and equipment
    1 Material; consumable supplies like steel, concrete, or soil
    IASSN_IS_OVERALLOCATED bitIndicates whether a resource is assigned to more work on a specific task than can be done within the resource's normal working capacity.
    IASSN_WORK_CONTOUR smallintIndicates how work for an assignment is to be distributed across the duration of the assignment:
    0 Flat (default)
    1 Back Loaded
    2 Front Loaded
    3 Double Peak
    4 Early Peak
    5 Late Peak
    6 Bell
    7 Turtle
    8 Contoured
    IASSN_START_VAR integerThe difference between an assignment's baseline start date and its currently scheduled start date.
    IASSN_FINISH_VAR integerThe difference between an assignment's baseline finish date and its scheduled finish date.
    IASSN_UPDATE_NEEDED bitIndicates whether a TeamUpdate message should be sent to the resource assigned to a task because of changes to the start date, finish date, or resource reassignments.
    EXT_EDIT_REF_DATA textSet to indicate a new row or back up existing data when changing project information in the database.
    ASSN_UIDintegerThe unique ID for the assignment.
    ASSN_HAS_LINKED_FIELDS bitIndicates whether there are OLE links to the assignment.
    ASSN_IS_CONFIRMED bitIndicates whether a resource assigned to a task has accepted or rejected the task assignment in response to a TeamAssign message.
    ASSN_RESPONSE_PENDING bitIndicates whether an answer has been received from a TeamAssign message sent to a resource assigned to a task.
    ASSN_HAS_NOTES bitIndicates whether an assignment has RTF notes.
    ASSN_TEAM_STATUS_PENDING bitIndicates whether a status message has been received in response to a TeamStatus message sent to a resource assigned to a task.
    TASK_UIDintegerRefers to a valid ID in the MSP_TASKS table.
    RES_UIDintegerRefers to a valid ID in the MSP_RESOURCES table.
    ASSN_START_DATE datetimeThe date and time that an assigned resource is scheduled to begin working on a task.
    ASSN_FINISH_DATE datetimeThe date and time that an assigned resource is scheduled to complete work on a task.
    ASSN_DELAY integerThe amount of time a resource is to wait after the task start date before starting work on an assignment.
    ASSN_DELAY_FMT smallintIndicates the delay format:
    0 minute
    1 hour (default)
    2 day
    3 week
    4 month
    ASSN_LEVELING_DELAY integerThe amount of time that an assignment is to be delayed from the scheduled start date as a result of resource leveling.
    ASSN_COST_RATE_TABLE smallintIndicates which cost rate table to use for a resource on an assignment:
    0 A (default)
    1 B
    2 C
    3 D
    4 E
    ASSN_BASE_START datetimeThe planned beginning date for an assignment at the time a baseline is saved.
    ASSN_BASE_FINISH datetimeThe planned finish date for assignments.
    ASSN_MATERIAL_RATE_FMT smallintThe 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 decimalThe number of units for which a resource is assigned to a task, expressed as a percentage of 100 percent (%), assuming a resource's MaxUnits value is 100 percent.
    ASSN_WORK decimalThe total amount of work scheduled to be performed by a resource on a task.
    ASSN_ACT_WORK decimalThe amount of work that has already been done by a resource on a task.
    ASSN_REG_WORK decimalThe total amount of non-overtime work scheduled to be performed by a resource assigned to a task.
    ASSN_REM_WORK decimalThe amount of time required by a resource assigned to a task to complete an assignment.
    ASSN_BASE_WORK decimalThe originally planned amount of work time to be performed by a resource on a task.
    IASSN_COST decimalThe total scheduled (or projected) cost for an assignment based on costs already incurred for work performed by the resource on a task, in addition to the costs planned for the remaining work for the assignment.
    IASSN_ACT_COST decimalThe costs incurred for work already performed by a resource on a task.
    IASSN_REM_COST decimalThe costs associated with completing all remaining scheduled work by any resources on a specific task.
    ASSN_BASE_COST decimalThe total planned cost for work to be performed by a resource on a task.
    ASSN_BASE_COST_PER_USE decimalThe baseline cost-per-use.
    ASSN_OVT_WORK decimalThe amount of overtime to be performed by a resource on a task; charged at the resource's overtime rate.
    ASSN_ACT_OVT_WORK decimalThe actual amount of overtime work already performed by a resource on an assigned task.
    ASSN_REM_OVT_WORK decimalThe amount of overtime work that remains on an assignment.
    IASSN_ACT_OVT_COST decimalThe costs incurred for overtime work already performed by a resource on a task.
    IASSN_REM_OVT_COST decimalThe remaining scheduled overtime expense for an assignment.
    ASSN_RTF_NOTES ImageContains notes about an assignment.

    Top

    MSP_ATTRIBUTE_STRINGS

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

     Column NameData TypeDescription
    RESERVED_DATA char(1)Temporarily stores information unique to the particular database row; you should not modify the contents of this field.
    PROJ_IDintegerRefers to a valid ID in the MSP_PROJECTS table.
    AS_IDintegerThe unique ID for the attribute string.
    AS_POSITION integerIndicates the position of a resource pool sharer in the list of sharer projects.
    AS_VALUE varchar(255)The formula as expressed in the Formula dialog box in Microsoft Project.

    Top

    MSP_AVAILABILITY

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

     Column NameData TypeDescription
    RESERVED_DATA char(1)Temporarily stores information unique to the particular database row; you should not modify the contents of this field.
    PROJ_IDintegerRefers to a valid ID in the MSP_PROJECTS table.
    AVAIL_UID integerThe unique ID for a resource's availability.
    RES_UIDintegerRefers to a valid ID in the MSP_RESOURCES table.
    AVAIL_FROM datetimeThe starting date that a resource is available for work at the units specified for the current time period.
    AVAIL_TOdatetimeThe ending date that a resource will be available for work at the units specified for the current time period.
    AVAIL_UNITS decimalThe number of units for which a resource is assigned to a task, expressed as a percentage of 100 percent (%), assuming a resource's MaxUnits value is 100%.

    Top

    MSP_CALENDAR_DATA

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

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

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

    Top

    MSP_CALENDARS

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

     Column NameData TypeDescription
    RESERVED_DATA char(1)Temporarily stores information unique to the particular database row; you should not modify the contents of this field.
    PROJ_IDintegerRefers to a valid ID in the MSP_PROJECTS table.
    CAL_UIDintegerThe unique ID for the calendar.
    CAL_BASE_UIDintegerRefers a calendar to its parent base calendar; required for all resource calendars. If this calendar is a base calendar, its value is -1.
    RES_UIDintegerRefers to a valid ID in the MSP_RESOURCES table.
    CAL_IS_BASE_CAL bitIndicates whether this calendar is a base calendar; a resource calendar cannot be a base calendar.
    CAL_NAME varchar(255)The name of the calendar; empty if this calendar is a resource calendar, required if the calendar is a base calendar.
    CAL_ENTUIDintegerThe enterprise-level ID for a calendar.

    Top

    MSP_CODE_FIELDS

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

     Column NameData TypeDescription
    PROJ_IDintegerRefers to a valid ID in the MSP_PROJECTS table.
    CODE_CATEGORY integerIndicates whether the custom outline code is a task or a resource.
    CODE_REF_UIDintegerRefers to a valid TASK_UID, or RES_UID in its respective table, as determined by CODE_CATEGORY.
    CODE_FIELD_IDintegerRefers to a valid Field ID found in the CONV_VALUE column in the MSP_CONVERSIONS table.
    CODE_UIDintegerRefers to a valid ID in the MSP_OUTLINE_CODES table.

    Top

    MSP_CONVERSIONS

    This table contains all of the Microsoft Project character strings and links the Microsoft Project database to the Tasks, Resources, and Assignments tables in the Microsoft Project OLE database.

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

    Top

    MSP_DATE_FIELDS

    This table contains custom date data.

     Column NameData TypeDescription
    PROJ_IDintegerRefers to a valid ID in the MSP_PROJECTS table.
    DATE_CATEGORYintegerIndicates whether the custom date category is a Task, Resource, or Assignment:
    0 Task
    1 Resource
    3 Assignment
    DATE_REF_UIDintegerRefers to a valid TASK_UID, RES_UID, or ASSN_UID in its respective table, as determined by DATE_CATEGORY.
    DATE_FIELD_IDintegerRefers to a valid Field ID found in the CONV_VALUE column in the MSP_CONVERSIONS table.
    DATE_VALUE datetimeThe value of the custom date field.

    Top

    MSP_DURATION_FIELDS

    This table contains custom duration data.

     Column NameData TypeDescription
    PROJ_IDintegerRefers to a valid ID in the MSP_PROJECTS table.
    DUR_CATEGORY integerIndicates whether the custom duration category is a Task, Resource, or Assignment:
    0 Task
    1 Resource
    3 Assignment
    DUR_REF_UIDintegerRefers to a valid TASK_UID, RES_UID, or ASSN_UID in its respective table, as determined by DUR_CATEGORY.
    DUR_FIELD_IDintegerRefers to a valid Field ID found in the CONV_VALUE column in the MSP_CONVERSIONS table.
    DUR_VALUE integerThe value of the custom duration field.
    DUR_FMT smallintIndicates the units to use when displaying durations in Microsoft Project:
    0 minute
    1 hour (default)
    2 day
    3 week
    4 month

    Top

    MSP_FIELD_ATTRIBUTES

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

     Column NameData TypeDescription
    RESERVED_DATA charTemporarily stores information unique to the particular database row; you should not modify the contents of this field.
    PROJ_IDintegerRefers to a valid ID in the MSP_PROJECTS table.
    ATTRIB_FIELD_ID integerRefers to the column that the formula affects; see MSP_STRING_TYPES and MSP_CONVERSIONS for 'Field ID'.
    ATTRIB_ID integerRefers to the type of field attribute; see MSP_STRING_TYPES and MSP_CONVERSIONS for 'Field Attributes'.
    ATTRIB_VALUE integerThe value of the attribute; a value of -1 indicates that the value is stored in the MSP_ATTRIBUTE_STRINGS table.
    AS_IDintegerRefers to valid ID in the MSP_ATTRIBUTE_STRINGS table; ignore if ATTRIB_VALUE is set to anything other than -1, which specifies the value of an attribute.
    ATTRIB_EXT_EDITED bitIndicates that the record has been externally edited.

    Top

    MSP_FLAG_FIELDS

    This table contains information about custom flags.

     Column NameData TypeDescription
    PROJ_IDintegerRefers to a valid ID in the MSP_PROJECTS table.
    FLAG_CATEGORY integerIndicates whether the custom flag category is a Task, Resource, or Assignment:
    0 Task
    1 Resource
    3 Assignment
    FLAG_REF_UIDintegerRefers to a valid TASK_UID, RES_UID, or ASSN_UID in its respective table, as determined by FLAG_CATEGORY.
    FLAG_FIELD_IDintegerRefers to a valid Field ID found in the CONV_VALUE column in the MSP_CONVERSIONS table.
    FLAG_VALUE bitThe value of the custom flag field.

    Top

    MSP_LINKS

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

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

    Top

    MSP_NUMBER_FIELDS

    This table contains custom number data.

     Column NameData TypeDescription
    PROJ_IDintegerRefers to a valid ID in the MSP_PROJECTS table.
    NUM_CATEGORY integerIndicates whether the custom number category is a Task, Resource, or Assignment:
    0 Task
    1 Resource
    3 Assignment
    NUM_REF_UIDintegerRefers to a valid TASK_UID, RES_UID, or ASSN_UID in its respective table, as determined by NUM_CATEGORY.
    NUM_FIELD_IDintegerRefers to a valid Field ID found in the CONV_VALUE column in the MSP_CONVERSIONS table.
    NUM_VALUEdecimalThe value of the custom number field.

    Top

    MSP_OUTLINE_CODES

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

     Column NameData TypeDescription
    RESERVED_DATA charTemporarily stores information unique to the particular database row; you should not modify the contents of this field.
    PROJ_IDintegerRefers to a valid ID in the MSP_PROJECTS table.
    CODE_UIDintegerThe unique ID for a custom outline code.
    OC_PARENTintegerRefers to a valid ID in the MSP_OUTLINE_CODES table; set to 0 for top-level codes without parents.
    OC_FIELD_IDintegerThe ID of the task or resource custom outline code; required for enterprise.
    OC_NAMEtextThe name of the custom outline code.
    OC_DESCRIPTIONtextContains a description of the code being defined.
    OC_CACHED_FULL_NAME textThe full outline code value, including all levels above; this value is generated during publish when the enterprise global is checked in.
    OC_CACHED_IS_VALID bitIndicates whether the outline code is valid; only valid outline codes will be made available for Microsoft Project and Microsoft Project Web Access users.

    Top

    MSP_PROJ_SECURITY

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

    Note This table is not accessed directly by Microsoft Project Professional.

     Column NameData TypeDescription
    PROJ_IDintegerRefers to a valid ID in the MSP_PROJECTS table.
    SEC_SPID varchar(255)The SQL Process ID (SPID).
    SEC_SPIDDATESTAMPdatetimeThe creation date of the active SPID utilized in this security row.
    SEC_READCOUNTintegerRepresents the current number of authorized read accesses to this particular project from the particular user:
    0 No users (default)
    1 One user; The PDS enforces a single READWRITE access for a project for a particular time
    SEC_WRITECOUNTintegerRepresents the current number of authorized write accesses to this particular project from the particular user. This value should never be greater than 1.

    Top

    MSP_PROJECTS

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

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

    Note the enterprise version does not include data source name; PROJ_PROJECT.PROJ_VERSION ensures that this is a unique name

    PROJ_PROP_AUTHORvarchar(255)The name of the author of the project; used to group similar projects together.
    PROJ_OPT_CALC_ACT_COSTS bitIndicates whether Microsoft Project should automatically calculate actual costs.
    PROJ_PROP_CATEGORY varchar(255)The category the project belongs to; used to group similar projects together.
    PROJ_PROP_COMPANY varchar(255)The name of the company that created the project; used to group similar projects together.
    PROJ_OPT_CRITICAL_SLACK_LIMIT integerThe number of days past its end date that a task can go before Microsoft Project marks that task as a critical task.
    PROJ_OPT_CURRENTY_DIGITSsmallintThe number of digits that are to appear after the decimal when currency values are shown in Microsoft Project:
    0 No digits after the decimal; $0
    1 One digit after the decimal; $0.0
    2 Two digits after the decimal (default); $0.00
    PROJ_OPT_CURRENCY_POSITIONsmallintIndicates the placement of the currency symbol in relation to the currency value:
    0 Before, no space (default); $0
    1 After, no space; 0$
    2 Before, with space; $ 0
    3 After, with space; 0 $
    PROJ_OPT_CURRENCY_SYMBOLvarchar(20)The current symbol used to represent the type of currency used in the project.
    PROJ_OPT_NEW_ARE_EFFORT_DRIVENbitIndicates whether new tasks are effort-driven.
    IPROJ_INFO_CURRENT_DATE datetimeThe current date for a project.
    PROJ_OPT_DEF_FINISH_TIMEdatetimeThe default finish time for all new tasks.
    PROJ_OPT_DEF_FIX_COST_ACCRUALsmallintIndicates whether fixed costs are accrued.
    PROJ_OPT_MINUTES_PER_DAYintegerThe default number of minutes per day.
    PROJ_OPT_MINUTES_PER_WEEKintegerThe default number of minutes per week.
    PROJ_OPT_DEF_OVT_RATEdecimalThe default overtime rate of pay for new resources; see MSP_RESOURCES.RES_DEF_OVT_RATE.
    PROJ_OPT_DEF_STD_RATEdecimalThe default rate of pay for new resources; see MSP_RESOURCES.RES_DEF_STD_RATE.
    PROJ_OPT_DEF_START_TIMEdatetimeThe default start time for all new tasks.
    PROJ_OPT_DEF_TASK_TYPEsmallintThe default type for all tasks in the project:
    0 Fixed work; the amount of work remains constant, regardless of any change in duration or the number of resources (Assignment Units) assigned to the task
    1 Fixed units (default); the number of resources (Assignment Units) remains constant, regardless of the amount of work or duration on the task
    2 Fixed duration; the duration of the task remains constant, regardless of the number of resources (Assignment Units) assigned or the amount of work
    PROJ_OPT_DUR_ENTRY_FMTsmallintThe default format for all durations in the project:
    0 minute
    1 hour (default)
    2 day
    3 week
    4 month
    PROJ_INFO_FINISH_DATEdatetimeThe date and time that a project is scheduled for completion.
    PROJ_OPT_HONOR_CONSTRAINTSbitIndicates whether Microsoft Project schedules tasks according to their constraint date.
    PROJ_PROP_KEYWORDSvarchar(255)Lists keywords associated with the project; used to group similar projects together.
    PROJ_LAST_SAVEDdatetimeThe date the project was last saved.
    PROJ_PROP_MANAGERvarchar(255)The manager of the project; used to group similar projects together.
    PROJ_OPT_MULT_CRITICAL_PATHSbitIndicates whether Microsoft Project calculates and displays a critical path for each independent network of tasks within a project.
    PROJ_CREATION_DATEdatetimeThe date that the project was created.
    IPROJ_POOL_ATTACHED_TOvarchar(255)The name of the project file that shares resources with this project file; required if PROJ_IS_RES_POOL is set to 1.
    PROJ_INFO_CAL_NAMEvarchar(255)The name of the calendar associated with the project.
    PROJ_IS_RES_POOLbitIndicates whether the project is its own resource pool or whether it shares its resources with another project; see PROJ_POOL_ATTACHED_TO.
    PROJ_INFO_SCHED_FROMsmallintIndicates whether a project is scheduled from the "Project Start Date" (default) or the "Project Finish Date".
    PROJ_OPT_SPLIT_IN_PROGRESSbitIndicates whether in-progress tasks may be split.
    PROJ_OPT_SPREAD_ACT_COSTSbitIndicates whether actual costs are spread to the status date.
    PROJ_OPT_SPREAD_PCT_COMPbitIndicates whether percent complete is spread to the status date.
    PROJ_INFO_START_DATEdatetimeThe date and time that a project is scheduled to begin.
    PROJ_INFO_STATUS_DATEdatetimeThe project status date.
    PROJ_PROP_SUBJECTvarchar(255)The subject of the project; used to group similar projects together.
    PROJ_PROP_TITLEvarchar(255)The title of the project; used to group similar projects together.
    PROJ_OPT_TASK_UPDATES_RESbitIndicates whether Microsoft Project automatically calculates actual and remaining work and costs as you enter task percent complete information in your schedule.
    PROJ_OPT_WORK_ENTRY_FMTsmallintThe default format for all work durations in the project:
    0 minute
    1 hour (default)
    2 day
    3 week
    4 month
    PROJ_OPT_CALC_SUB_AS_SUMMARYbitIndicates whether Microsoft Project calculates sub-tasks as summary tasks.
    PROJ_OPT_WEEK_START_DAYsmallintThe day of the week that a work week starts on:
    0 Sunday (default)
    1 Monday
    2 Tuesday
    3 Wednesday
    4 Thursday
    5 Friday
    6 Saturday
    PROJ_OPT_FY_START_MONTHsmallintThe month that the fiscal year begins:
    0 January (default)
    1 February
    2 March
    3 April
    4 May
    5 June
    6 July
    7 August
    8 September
    9 October
    10 November
    11 December
    PROJ_OPT_FY_USE_START_YEARbitIndicates whether to use the fiscal starting year.
    PROJ_OPT_DAYS_PER_MONTHsmallintThe default number of working days per month.
    PROJ_OPT_NEW_TASK_ESTbitIndicates whether new tasks have estimated durations.
    PROJ_OPT_SHOW_EST_DURbitIndicates whether Microsoft Project displays a ? after any task with an estimated duration.
    PROJ_OPT_EXPAND_TIMEPHASEDbitIndicates whether Microsoft Project saves timephased data in a readable or binary format when saved to a database; see MSP_TIMEPHASED_DATA.
    PROJ_EXT_EDITED_DATEbitIndicates whether the custom date field was edited externally.
    PROJ_EXT_EDITED_DURbitIndicates whether the custom duration field was edited externally.
    PROJ_EXT_EDITED_NUMbitIndicates whether the custom number field was edited externally.
    PROJ_EXT_EDITED_FLAGbitIndicates whether the custom flag field was edited externally.
    PROJ_EXT_EDITED_CODEbitIndicates whether the custom outline code field was edited externally.
    PROJ_EXT_EDITED_textbitIndicates whether the custom text field was edited externally.
    PROJ_EXT_EDITEDbitIndicates whether the project was edited externally.
    IPROJ_DATA_SOURCEvarchar(128)The data source name for the project; used by Microsoft Project when checking for concurrent usage.
    IPROJ_READ_ONLYvarchar(10)Indicates whether the project is read-only.
    IPROJ_READ_WRITEvarchar(4)Indicates whether the project is open for write.
    IPROJ_READ_COUNTvarchar(10)Indicates the number of users who have one or more tables open as read-only.
    IPROJ_LOCKEDvarchar(4)Indicates whether a user can write to a table.
    PROJ_MACHINE_IDvarchar(100)The machine ID of the user who has opened the project.
    IRESERVED_BINARY_DATAImageReserved for use by Microsoft Project; do not change the values in this field.
    PROJ_TYPEintegerThe project type:
    0 Projects (default)
    1 Templates
    2 Enterprise global
    3 Resource global
    100 Created regular project
    101 Created project template
    102 Created enterprise global
    1002 Inactivated enterprise template
    PROJ_CHECKEDOUTintegerIndicates whether the project is checked out.
    PROJ_CHECKOUTBYtext(255)Value is the User ID of the user who last checked out the project. This value is not cleared when the project is checked back in, rather PROJ_CHECKEDOUT should return to 0 when the project is checked back in.
    PROJ_CHECKOUTDATEdatetimeThe timestamp when the project was last checked out. This value is not cleared when the project is checked back in, rather PROJ_CHECKEDOUT should return to 0 when the project is checked back in.
    PROJ_PROJECTvarchar(200)Stores the unique project name (indexed duplicates are OK) without the version suffix; there can be many versions of a project with the same value of Project.
    PROJ_VERSIONvarchar(50)The name of the version, for example: Published.

    Top

    MSP_RES_GLOBAL_BINARY

    This table stores binary data related to the global enterprise resource pool.

     Column NameData TypeDescription
    PROJ_IDintegerRefers to a valid ID in the MSP_PROJECTS table.
    GLB_CATEGORY integerIndicates whether the global binary information is related to a Task, Resource, or Assignment:
    0 Task
    1 Resource
    3 Assignment
    GLB_REF_UIDintegerRefers to a valid TASK_UID, RES_UID, or ASSN_UID in its respective table, as determined by GLB_CATEGORY.
    GLB_REF_ENT_PROJ_IDintegerRefers to a valid ASSN_ENTSUMPROJID in the MSP_ASSIGNMENTS table.
    GLB_BINARY_DATA ImageThis column saves out portions of the contents of the RESERVED_BINARY_DATA column in the MSP_PROJECTS table as binary data.

    Top

    MSP_RES_SECURITY

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

    Note This table is not accessed directly by Microsoft Project Professional.

     Column NameData TypeDescription
    ERUIDintegerRefers to a valid enterprise resource ID in the MSP_RESOURCES table.
    SEC_SPIDvarchar(255)The SQL Process ID (SPID).
    SEC_SPIDDATESTAMPdatetimeThe creation date of the active SPID utilized in this security row.
    SEC_READCOUNTTinyintRepresents the current number of authorized read-accesses to this particular resource from the particular user:
    0 No users (default)
    1 One user; The PDS enforces a single READWRITE access for a project for a particular time
    SEC_WRITECOUNTTinyintRepresents the current number of authorized write-accesses to this particular resource from the particular user for updating the resource data. This value should never be greater than 1.

    Top

    MSP_RESOURCE_BASELINES

    This table contains custom resource baseline data.

     Column NameData TypeDescription
    PROJ_IDintegerRefers to a valid ID in the MSP_PROJECTS table.
    RES_UIDintegerRefers to a valid ID in the MSP_RESOURCES table.
    RB_BASE_NUMsmallintThe number of the custom baseline, 1-10.
    RB_BASE_WORKdecimalThe originally planned amount of work time to be performed by a resource on a task.
    RB_BASE_COSTdecimalThe total planned cost for work to be performed by a resource on a task.

    Top

    MSP_RESOURCE_RATES

    This table contains resource rate data.

     Column NameData TypeDescription
    RESERVED_DATA charTemporarily stores information unique to the particular database row; you should not modify the contents of this field.
    PROJ_IDintegerRefers to a valid ID in the MSP_PROJECTS table.
    RR_UID integerThe unique ID for the resource rate.
    RES_UIDintegerRefers to a valid ID in the MSP_RESOURCES table.
    RR_RATE_TABLE smallintIndicates which cost rate table to use for a resource on an assignment:
    0 A (default)
    1 B
    2 C
    3 D
    4 E
    RR_FROM_DATE datetimeThe first date for which the resource rates are in effect.
    RR_TO_DATE datetimeThe last date for which the resource rates are in effect.
    RR_STD_RATE decimalThe standard rate as entered in the selected cost rate table.
    RR_STD_RATE_FMT smallintThe units in which the standard rate is displayed in Microsoft Project:
    0 minute
    1 hour (default)
    2 day
    3 week
    4 month
    RR_OVT_RATE decimalThe overtime rate as entered in the selected cost rate table.
    RR_OVT_RATE_FMT smallintThe units in which the overtime rate is displayed in Microsoft Project:
    0 minute
    1 hour (default)
    2 day
    3 week
    4 month
    RR_PER_USE_COST decimalThe per use cost as entered in the selected cost rate table; for example, $ * 100.

    Top

    MSP_RESOURCES

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

    Microsoft Project will write four 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 and they should not be edited or deleted.

     Column NameData TypeDescription
    RESERVED_DATA charTemporarily stores information unique to the particular database row; you should not modify the contents of this field.
    PROJ_IDintegerRefers to a valid ID in the MSP_PROJECTS table.
    IRES_ACWPdecimalThe sum of Actual Cost of Work Performed (ACWP) values for all of a resource's assignments, up to the status date or today's date.
    IRES_BCWPdecimalThe rolled-up summary of a resource's BCWP values for all assigned tasks, calculated up to the status date or today's date; also called Budgeted Cost of Work Performed.
    IRES_BCWSdecimalThe rolled-up summary of a resource's BCWS values for all assigned tasks; also called Budgeted Cost of Work Scheduled.
    IRES_NUM_OBJECTS integerThe number of objects associated with a resource, not including those in notes.
    EXT_EDIT_REF_DATA textSet to indicate a new row or back up existing data when changing project information in the database.
    RES_UIDintegerThe unique ID for the resource.
    RES_IDintegerThe position identifier of the resource in the list of resources; determines the order that resources are displayed in Microsoft Project.
    RES_HAS_LINKED_FIELDS bitIndicates whether there are OLE links to the resource, either from elsewhere in the active project, another Microsoft Project file, or from another program.
    RES_IS_OVERALLOCATED bitIndicates whether a resource is assigned to do more work on all assigned tasks than can be done within the resource's normal work capacity.
    RES_TYPE bitThe resource type (Work or Material):
    0 Work (default); people and equipment
    1 Material; consumable supplies like steel, concrete, or soil
    RES_HAS_NOTES bitIndicates whether a resource has Rich text Format (RTF) note.
    RES_CAN_LEVEL bitIndicates whether resource leveling can be done with a resource.
    RES_STD_RATE_FMT smallintThe units in which the standard rate is displayed in Microsoft Project.
    RES_OVT_RATE_FMT smallintThe units in which the overtime rate is displayed in Microsoft Project.
    RES_ACCRUE_AT smallintIndicates how and when resource standard and overtime costs are to be charged, or accrued, to the cost of a task:
    1 Start; costs are accrued as soon as the task starts, as indicated by a date entered in the ActualStart field.
    2 End; costs are not incurred until remaining work is zero.
    3 Pro-rated (default); costs accrue as work is scheduled to occur and as actual work is reported.
    RES_WORKGROUP_MESSAGING smallintThe messaging method to be used to communicate with a project workgroup:
    0 Default
    1 Web (Microsoft Project Web Access)
    2 E-mail only
    3 None; Workgroup messaging is not used on this project
    RES_CAL_UIDintegerRefers to a valid ID in the MSP_CALENDARS table.
    IRES_AVAIL_FROM datetimeThe starting date that a resource is available for work at the units specified for the current time period.
    IRES_AVAIL_TO datetimeThe ending date in which a resource will be available for work at the units specified for the current time period.
    RES_STD_RATE decimalThe rate of pay for regular, non-overtime work performed by a resource.
    RES_OVT_RATE decimalThe rate of pay for overtime work performed by a resource.
    RES_MAX_UNITS decimalThe maximum percentage or number of units representing the maximum capacity for which a resource is available to accomplish any tasks during the current time period:
    0-99 Resource is 0%-99% available for the specified task
    100 Resource is 100% available for the specified task (default)
    RES_WORK decimalThe total amount of work scheduled to be performed by a resource on all assigned tasks.
    RES_ACT_WORK decimalThe actual amount of work that has already been done for all assignments assigned to a resource.
    RES_BASE_WORK decimalThe originally planned amount of work to be performed for all assignments assigned to a resource.
    RES_OVT_WORK decimalThe amount of overtime to be performed for all tasks assigned to a resource and charged at the resource's overtime rate.
    RES_COST_PER_USE decimalThe cost that accrues each time a resource is used.
    RES_REM_WORK decimalThe amount of time, or person-hours, still required by a resource to complete all assigned tasks.
    RES_REG_WORK decimalThe total amount of non-overtime work scheduled to be performed for all assignments assigned to a resource.
    RES_ACT_OVT_WORK decimalThe actual amount of overtime work already performed for all assignments assigned to a resource.
    RES_REM_OVT_WORKdecimalThe remaining amount of overtime required by a resource to complete all tasks.
    IRES_PEAK decimalThe maximum percentage or number of units for which a resource is assigned at any one time for all tasks assigned to the resource.
    RES_ACT_COST decimalThe sum of costs incurred for the work already performed by a resource for all assigned tasks.
    RES_COST decimalThe total scheduled cost for a resource for all assigned tasks, based on costs already incurred for work performed by the resource on all assigned tasks in addition to the costs planned for all remaining work.
    RES_BASE_COST decimalThe total planned cost for a resource for all assigned tasks; also called Budget At Completion (BAC).
    RES_REM_COST decimalThe remaining scheduled expense that will be incurred in completing the remaining work assigned to a resource.
    RES_OVT_COST decimalThe total overtime cost for a resource on all assigned tasks.
    RES_ACT_OVT_COST decimalThe cost incurred for overtime work already performed by a resource for all assigned tasks.
    RES_REM_OVT_COST decimalThe remaining scheduled overtime expense of a resource that will be incurred in completing the remaining planned overtime work by a resource on all assigned tasks.
    RES_NAME varchar(255)The name of the resource; must be unique within the enterprise whether the resource is active.
    RES_INITIALS varchar(255)The abbreviation for a resource name.
    RES_PHONETICS textContains phonetic information in either Hiragana or Katakana for resource names; used only in the Japanese version of Microsoft Project.
    RES_MATERIAL_LABEL textThe unit of measurement entered for a material resource, for example tons, boxes, or cubic yards; used in conjunction with the material resource's Assignment Units; only available if RES_RTYPE is set to Material.
    RES_RTF_NOTES ImageNotes about a resource.
    RES_RTYPEsmallintIndicates whether a resource is normal or generic and whether the normal or generic resource is deactivated:
    0 Normal resource
    1 Generic resource
    100 Deactivated, normal resource
    101 Deactivated, generic resource
    RES_CHECKEDOUTintegerIndicates whether the resource is checked out.
    RES_CHECKOUTBYvarchar(255)Value is the User ID of the user who last checked out the resource. This value is not cleared when the resource is checked back in, rather RES_CHECKEDOUT should return to 0 when the resource is checked back in.
    RES_CHECKOUTDATEdatetimeThe timestamp when the resource was last checked out. This value is not cleared when the resource is checked back in, rather RES_CHECKEDOUT should return to 0 when the resource is checked back in.
    RES_ENTERPRISE_TIMESTAMPdatetime
    RES_EUID integerMaps the RES_UID to the Resource Global enterprise pool.

    Top

    MSP_STRING_TYPES

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

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

    Top

    MSP_TASK_BASELINES

    This table contains custom task baseline data.

     Column NameData TypeDescription
    PROJ_IDintegerRefers to a valid ID in the MSP_PROJECTS table.
    TASK_UIDintegerRefers to a valid ID in the MSP_TASKS table.
    TB_BASE_NUMsmallintThe number of the custom baseline, 1-10.
    TB_BASE_DURintegerThe original span of time planned to complete a task.
    TB_BASE_DUR_FMTsmallintThe default format for the task baseline duration:
    0 minute
    1 hour (default)
    2 day
    3 week
    4 month
    TB_BASE_STARTdatetimeThe planned beginning date for a task at the time you saved a baseline.
    TB_BASE_FINISHdatetimeThe planned completion date for a task at the time you saved a baseline.
    TB_BASE_WORKdecimalThe originally planned amount of work to be performed by all resources assigned to a task.
    TB_BASE_COSTdecimalThe total planned cost for a task; also referred to as Budget At Completion (BAC).

    Top

    MSP_TASKS

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

     Column NameData TypeDescription
    IRESERVED_DATA charTemporarily stores information unique to the particular database row; you should not modify the contents of this field.
    PROJ_IDintegerRefers to a valid ID in the MSP_PROJECTS table.
    ITASK_ACWPdecimalThe costs incurred for work already done on a task, up to the project status date or today's date.
    ITASK_BCWPdecimalThe cumulative value of the task's timephased percent complete multiplied by the task's timephased baseline cost, up to the status date or today's date; also known as Earned Value.
    ITASK_BCWSdecimalThe cumulative timephased baseline costs up to the status date or today's date.
    ITASK_DUR_VARintegerThe difference between the baseline duration of a task and the total duration (current estimate) of a task.
    ITASK_FINISH_VARintegerThe amount of time that represents the difference between a task's baseline finish date and its current finish date.
    ITASK_OUTLINE_NUMvarchar(255)Indicates the exact position of a task in the outline. For example, 7.2 indicates that a task is the 2nd subtask under the 7th top-level summary task.
    ITASK_START_VARintegerThe difference between a task's baseline start date and its currently scheduled start date.
    ITASK_IS_OVERALLOCATEDbitIndicates whether an assigned resource on a task has been assigned to more work on the task than can be done within the normal working capacity.
    ITASK_OVT_WORKdecimalThe amount of overtime scheduled to be performed by all resources assigned to a task and charged at overtime rates.
    TASK_VACdecimalThe difference between the baseline cost and the total cost for a task; also called Variance At Completion (VAC).
    ITASK_REG_WORKdecimalThe total amount of non-overtime work scheduled to be performed by all resources assigned to a task.
    ITASK_NUM_OBJECTSintegerThe number of objects attached to a task.
    TASK_TOTAL_SLACKintegerThe amount of time a task can be delayed without delaying the project's finish date.
    EXT_EDIT_REF_DATA textSet to indicate a new row or back up existing data when changing project information in the database.
    TASK_UIDintegerThe unique ID for the task.
    TASK_ID integerThe position identifier of the task in the list of tasks.
    TASK_HAS_LINKED_FIELDS bitIndicates whether there are OLE links to a task, either from elsewhere in the active project, another Microsoft Project file, or from another program.
    TASK_IS_MILESTONE bitIndicates whether a task is a milestone.
    TASK_IS_CRITICAL bitIndicates whether a task has room in the schedule to slip, or if a task is on the critical path.
    TASK_IS_SUMMARY bitIndicates whether a task is a summary task.
    TASK_IS_SUBPROJ bitIndicates whether a task is an inserted project.
    TASK_IS_MARKED bitIndicates whether a task is marked for further action or identification of some kind.
    TASK_IGNORES_RES_CAL bitIndicates whether the scheduling of the task takes into account the calendars of the resources assigned to the task.
    TASK_IS_ROLLED_UP bitIndicates whether the summary task bar displays rolled-up bars or whether information on the sub-task Gantt bars will be rolled up to the summary task bar; must be set to 1 for sub-tasks to be rolled up to summary tasks.
    TASK_IS_FROM_FINISH_SUBPROJ bitIndicates whether a project is marked as "Schedule-from-Finish" (SF).
    TASK_BAR_IS_HIDDEN bitIndicates whether the Gantt bars and Calendar bars for a task are hidden.
    TASK_IS_RECURRING bitIndicates whether a task is a recurring task.
    TASK_IS_RECURRING_SUMMARY bitIndicates whether a recurring task is a summary task.
    ITASK_IS_EXTERNAL bitIndicates whether the task is linked from another project or whether it originated in the current project.
    TASK_IS_EFFORT_DRIVEN bitIndicates whether scheduling for a task is effort-driven.
    TASK_IS_COLLAPSED bitIndicates whether a summary task is collapsed when displayed in Microsoft Project.
    TASK_HAS_NOTES bitIndicates whether a task has RTF notes.
    TASK_IS_READONLY_SUBPROJ bitIndicates whether the sub-project of this task is a read-only project.
    TASK_LEVELING_CAN_SPLIT bitIndicates whether the resource leveling function can cause splits on remaining work on a task.
    TASK_LEVELING_ADJUSTS_ASSN bitIndicates whether the leveling function can delay and split individual assignments (rather than the entire task) in order to resolve overallocations.
    ITASK_DUR_IS_EST bitIndicates whether the baseline duration is estimated.
    TASK_EARLY_FINISH datetimeThe earliest date that a task could possibly finish, based on early finish dates of predecessor and successor tasks, other constraints, and any leveling delay.
    TASK_LATE_START datetimeThe latest date that a task can start without delaying the finish of the project, based on the start date, as well as the late start and late finish dates of predecessor and successor tasks, and other constraints.
    TASK_STOP_DATE datetimeThe date that represents the end of the actual portion of a task.
    ITASK_RESUME_DATE datetimeThe date that the remaining portion of a task is scheduled to resume after you enter a new value for the Percent (%) Complete field.
    TASK_FREE_SLACK integerThe amount of time that a task can be delayed without delaying any successor tasks; if a task has zero successor tasks, then free slack is the amount of time a task can be delayed without delaying the entire project.
    TASK_OUTLINE_LEVEL smallintThe number that indicates the level of a task in the project outline hierarchy.
    TASK_DUR integerThe total span of active working time for a task.
    TASK_DUR_FMT smallintThe units in which the duration of a task are displayed in Microsoft Project:
    3 m
    4 em
    5 h
    6 eh
    7 d (default)
    8 ed
    9 w
    10 ew
    11 mo
    12 emo
    19 %
    20 e%
    35 m?
    36 em?
    37 h?
    38 eh?
    39 d?
    40 ed?
    41 w?
    42 ew?
    43 mo?
    44 emo?
    51 %?
    52 e%?
    TASK_ACT_DUR integerThe span of actual working time for a task so far, based on the scheduled duration and current remaining work or completion percentage.
    TASK_REM_DUR integerThe amount of time required to complete the unfinished portion of a task. Remaining duration can be calculated in two ways (either based off of Percent (%) Complete or Actual Duration).
    TASK_BASE_DUR integerThe original span of time planned to complete a task.
    TASK_BASE_DUR_FMT smallintThe units in which the baseline duration of a task are displayed in Microsoft Project:
    3 m
    4 em
    5 h
    6 eh
    7 d (default)
    8 ed
    9 w
    10 ew
    11 mo
    12 emo
    19 %
    20 e%
    35 m?
    36 em?
    37 h?
    38 eh?
    39 d?
    40 ed?
    41 w?
    42 ew?
    43 mo?
    44 emo?
    51 %?
    52 e%?
    TASK_CONSTRAINT_TYPE smallintThe constraint on a scheduled task:
    0 As soon as possible
    1 As late as possible
    2 Must start on; TASK_CONSTRAINT_DATE is required
    3 Must finish on; TASK_CONSTRAINT_DATE is required
    4 Start no earlier than; TASK_CONSTRAINT_DATE is required
    5 Start no later than; TASK_CONSTRAINT_DATE is required
    6 Finish no earlier than; TASK_CONSTRAINT_DATE is required
    7 Finish no later than; TASK_CONSTRAINT_DATE is required
    TASK_LEVELING_DELAYintegerThe amount of time that a task is to be delayed from its early start date as a result of resource leveling.
    TASK_LEVELING_DELAY_FMTsmallintThe units in which a leveling delay is displayed in Microsoft Project.
    TASK_START_DATEdatetimeThe date and time that a task is scheduled to begin; this value is automatically calculated if a task has a predecessor.
    TASK_FINISH_DATEdatetimeThe date and time that a task is scheduled to be completed.
    TASK_ACT_STARTdatetimeThe date and time that a task actually began.
    TASK_ACT_FINISHdatetimeThe date and time that a task actually finished.
    TASK_BASE_STARTdatetimeThe planned beginning date for a task at the time you saved a baseline.
    TASK_BASE_FINISHdatetimeThe planned completion date for a task at the time you saved a baseline.
    TASK_CONSTRAINT_DATEdatetimeIndicates the constrained start or finish date as defined in TASK_CONSTRAINT_TYPE. Required unless TASK_CONSTRAINT_TYPE is set to As late as possible or As soon as possible.
    TASK_PRIORITYsmallintIndicates the level of importance assigned to a task; the higher the number, the higher the priority:
    0 Lowest priority; task will always be leveled
    500 Default value
    1000 Highest priority; task will never be leveled
    TASK_PCT_COMPsmallintThe current status of a task, expressed as the percentage of the task's duration that has been completed.
    TASK_PCT_WORK_COMPsmallintThe current status of a task, expressed as the percentage of the task's work that has been completed.
    TASK_TYPEsmallintIndicates the effect that editing work, assignment units, or duration has on the calculations of the other two fields:
    0 Fixed work; the amount of work remains constant, regardless of any change in duration or the number of resources (Assignment Units) assigned to the task
    1 Fixed units (default); the number of Assignment Units remains constant, regardless of the amount of work or duration on the task
    2 Fixed duration; the duration of the task remains constant, regardless of the number of resources (Assignment Units) assigned or the amount of work
    TASK_FIXED_COST_ACCRUALsmallintIndicates how fixed costs are to be charged, or accrued, to the cost of a task:
    1 Start; costs are accrued as soon as the task starts, as indicated by a date entered in the ActualStart field.
    2 End; costs are not incurred until remaining work is zero.
    3 Pro-rated (default); costs accrue as work is scheduled to occur and as actual work is reported.
    TASK_CREATION_DATEdatetimeThe date and time that a task was added to a project.
    TASK_PRELEVELED_STARTdatetimeThe start date of a task as it was before resource leveling was done.
    TASK_PRELEVELED_FINISHdatetimeThe finish date of a task as it was before resource leveling was done.
    TASK_EARLY_STARTdatetimeThe earliest date that a task could possibly begin, based on the early start dates of predecessor and successor tasks, and other constraints.
    TASK_LATE_FINISHdatetimeThe latest date that a task can finish without delaying the finish of the project, based on the task's late start date, as well as the late start and late finish dates of predecessor and successor tasks, and other constraints.
    TASK_CAL_UIDintegerRefers to a valid ID in the MSP_CALENDARS table.
    TASK_DEADLINEdatetimeThe date entered as a deadline for the task.
    TASK_WORKdecimalThe total amount of work scheduled to be performed on a task by all assigned resources.
    TASK_BASE_WORKdecimalThe originally planned amount of work to be performed by all resources assigned to a task.
    TASK_ACT_WORKdecimalThe amount of work that has already been done by the resources assigned to a task.
    TASK_REM_WORKdecimalThe amount of time still required by all assigned resources to complete a task.
    TASK_COSTdecimalThe total scheduled, or projected, cost for a task, based on costs already incurred for work performed by all resources assigned to the task, in addition to the costs planned for the remaining work for the assignment.
    TASK_FIXED_COSTdecimalA task expense that is not associated with a resource cost.
    TASK_ACT_COSTdecimalThe costs incurred for work already performed by all resources on a task, along with any other recorded costs associated with the task.
    ITASK_REM_COSTdecimalThe remaining scheduled expense of a task that will be incurred in completing the remaining scheduled work by all resources assigned to a task.
    TASK_BASE_COSTdecimalThe total planned cost for a task; also referred to as Budget At Completion (BAC).
    TASK_ACT_OVT_WORKdecimalThe actual amount of overtime work already performed by all resources assigned to a task.
    TASK_REM_OVT_WORKdecimalThe amount of remaining overtime scheduled by all assigned resources to complete a task.
    TASK_OVT_COSTdecimalThe actual overtime cost for a task.
    TASK_ACT_OVT_COSTdecimalThe costs incurred for overtime work already performed on a task by all assigned resources.
    TASK_REM_OVT_COSTdecimalThe remaining scheduled overtime expense for a task.
    TASK_WBStextA unique code (WBS) used to represent a task's position within the hierarchical structure of the project.
    TASK_NAMEvarchar(255)The name of a task.
    TASK_WBS_RIGHTMOST_LEVELtextThe right-most level of the task. For example, if the task level was A.01.03 the right-most level would be 03.
    TASK_RTF_NOTESImageContains RTF notes.
    TASK_PHY_PCT_COMPsmallintThe physical percent of the total that a task has been completed.
    TASK_EACdecimalThe total scheduled or projected cost for a task based on costs already incurred, in addition to the costs planned for remaining work.
    TASK_EVMETHODsmallintIndicates whether TASK_PCT_COMP or TASK_PHY_PCT_COMP is used to calculate the budgeted cost of work performed (TASK_BCWP).

    Top

    MSP_TEXT_FIELDS

    This table contains custom text information.

     Column NameData TypeDescription
    PROJ_IDintegerRefers to a valid ID in the MSP_PROJECTS table.
    TEXT_CATEGORY integerIndicates whether the custom text category is Task, Resource, or Assignment; must be set to 0 for an inserted project:
    0 Task
    1 Resource
    3 Assignment
    TEXT_REF_UIDintegerRefers to a valid TASK_UID, RES_UID, or ASSN_UID in its respective table, as determined by text_CATEGORY.
    TEXT_FIELD_IDintegerRefers to a valid CONV_VALUE in the MSP_CONVERSIONS table; always 188743706 for inserted projects.
    TEXT_VALUE varchar(255)The value of the custom text field.

    Top

    MSP_TIMEPHASED_DATA

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

     Column NameData TypeDescription
    RESERVED_DATA charTemporarily stores information unique to the particular database row; you should not modify the contents of this field.
    PROJ_IDintegerRefers to a valid ID in the MSP_PROJECTS table.
    TD_UIDintegerThe unique ID for the timephased data record.
    TD_FIELD_IDsmallintRefers to a valid Field ID in the CONV_VALUE column in the MSP_CONVERSIONS table.
    TD_CATEGORY smallintIndicates whether the timephased data is Task, Resource, or Assignment:
    0 Task
    1 Resource
    3 Assignment
    TD_REF_UIDintegerRefers to a valid TASK_UID, RES_UID, or ASSN_UID in its respective table, as determined by TD_CATEGORY.
    TD_START datetimeThe start of the first time period for which work, percent complete, or cost values will be applied.
    TD_FINISH datetimeThe end of the first time period for which work, percent complete, or cost values will be applied.
    TD_UNITS smallintIndicates the unit of time to which the specified values in TD_VALUE1 - TD_VALUE7 apply:
    0 m
    1 h
    2 d (default)
    3 w
    5 mo
    8 y
    TD_VALUE1 - TD_VALUE7decimalThe value of the work, percent complete or cost for the first through seventh time periods in the units specified in the TD_UNITS column during the date range specified in the TD_START and TD_FINISH columns; only values that fall between the specified start and finish dates will apply.
    TD_EXT_EDITED bitIndicates whether the record has been externally edited.

    Top

    MSP_VERSIONS

    A project may have multiple versions in Microsoft Project. The lowest level of a project is each version of each project, for example: 90-001 Windows XP Upgrade.Published is the published version of the Project 90-001 Windows XP Upgrade. One record for each available version is stored in this table. All enterprise-level projects will have an associated version.

    Note This table is not accessed directly by Microsoft Project Professional.

     Column NameData TypeDescription
    VERS_IDintegerThe position identifier of the version in the list of versions.
    VERS_VERSIONvarchar(50)The name of the version, for example: Published.
    VERS_DEFAULTTinyintIndicates whether this version is the default version.
    VERS_TYPEintegerThe version type:
    0 Editable version
    10 Archive version; disallows all editing, project is read-only, resource records are not refreshed
    1000 Inactive version
    VERS_WGANTT_STYLE_IDintegerThe ID for the Gantt bar style:
    0 Normal task
    1 Critical task
    2 External task
    3 Delegated task
    4 Milestone
    5 Summary task
    6 Project summary (default)
    7 Group by summary
    8 Progress
    9 Summary progress
    10 Baseline task
    11 Baseline summary
    12 Baseline milestone
    13 Pre-leveled task
    14 Pre-leveled summary
    15 Pre-leveled milestone
    16 Split
    17 Critical split
    18 Baseline split
    19 Deadline
    20 Slack
    21 Slippage
    22 Delay
    23 Custom duration 1
    24 Custom duration 2
    25 Custom duration 3
    26 Custom duration 4
    27 Custom duration 5
    28 Custom duration 6
    29 Custom duration 7
    30 Custom duration 8
    31 Custom duration 9
    32 Custom duration 10
    33 Early schedule
    34 Late schedule
    35 External milestone

    Top