Overview
The purpose of this document
What's included in this document
What's new or changed in the Microsoft Project 2000 database
Working with projects in a database
Supported databases
Loading Microsoft Project 98 projects that were stored in a database
Upgrading the database structure from Microsoft Project 98 to Microsoft Project 2000
Database permissions and configuration
Required permissions
Configuring the database
Performance tuning
Ensuring data integrity in a project in a database
Working with the Microsoft Project 2000 database structure
How information is stored
Calendar data
Timephased data
Notes
Custom field values
Split Tasks
Task links
Estimated duration values
Working with projects in a database
Microsoft Project tables
Creating the Microsoft Project 2000 database structure
DSN requirements for multiple users and projects
Concurrent usage and project locking
Deleting a project from a database
Renaming projects in a database
Adding and changing rows in the database
Setting the flags required to enable updating project data in the database
Specifying times with dates
Duration, work, rate and cost values
Using the text conversion tables to retrieve strings
Creating project schedule data
Creating a new project
Creating an inserted project
Creating a new resource
Creating a new calendar
Specifying calendar working time and exceptions
Specifying resource availability
Specifying resource rates
Creating a new task
Creating a recurring task
Creating task dependencies
Creating a new assignment
Managing timephased data
Creating or modifying an assignment actual-work contour
Creating splits in scheduled work
Creating or modifying an assignment remaining-work contour
Creating or modifying cost contours
Creating or modifying task-percent complete contours
Deleting a contour
Customizing project data
Specifying custom text fields
Specifying custom number fields
Specifying custom date fields
Specifying custom duration fields
Field attributes
Specifying custom WBS codes
Specifying custom outline codes
Specifying aliases for custom fields
Specifying formulae for custom fields
Reading and writing RTF notes
Managing other data in the database
Specifying an email address
Retrieving cross-project link project/task references
Retrieving the names of sharer files
Outlining with summary tasks and subtasks
Editing work on a summary task assignment
Retrieving Workgroup Message Status
Processing order of externally edited data
Overview
Standard processing order
Database object naming conventions
Table Naming Conventions
Column naming conventions
MSP_PROJECTS column qualifiers
Column name abbreviations
Database tables
Informational-only columns
Key columns
Column data types
MSP_PROJECTS
MSP_TASKS
MSP_RESOURCES
MSP_ASSIGNMENTS
MSP_AVAILABILITY
MSP_CALENDARS
MSP_CALENDAR_DATA
MSP_LINKS
MSP_TIMEPHASED_DATA
MSP_RESOURCE_RATES
MSP_TEXT_FIELDS
MSP_NUMBER_FIELDS
MSP_DATE_FIELDS
MSP_DURATION_FIELDS
MSP_FLAG_FIELDS
MSP_OUTLINE_CODES
MSP_CODE_FIELDS
MSP_STRING_TYPES
MSP_CONVERSIONS
MSP_FIELD_ATTRIBUTES
MSP_ATTRIBUTE_STRINGS
This document provides the information required to create and update project data directly in the database while maintaining the consistency and integrity of the data. The advantages of creating and maintaining project data directly in the database include:
Specific chapters include the following information:
One of the goals of Microsoft Project 2000 is to simplify the creation and management of project data in the database. To this end, the following changes have been made:
Microsoft Project supports the following databases through Open Database Connectivity (ODBC):
Microsoft Project can also make the ODBC connection automatically when writing to and reading from Microsoft Access 2000 databases if you directly select Project Database (.mpd) or Microsoft Access Database (.mdb) as the file type in the File Open and File Save dialog boxes.
Note: Saving or loading data is not supported with tables that are linked in Microsoft Access in such a way that the data exists in another application or database management system, and Microsoft Access is just providing the connection. To access the data you must actually import it into Microsoft Access or connect to the source directly.
You can open a project in Microsoft Project 2000 that was previously saved to database using Microsoft Project 98. However, if you save the project to a database again, it will be saved with the Microsoft Project 2000 database structure. Because the Microsoft Project 2000 database format has been completely changed, you will have two copies of the project each in different database formats. The original Project 98 data will not be affected.
You can use the Database Upgrade Utility COM add-in to upgrade some or all of the projects in a Microsoft Access, SQL Server, or Oracle database from the Microsoft Project 98 database structure to the Microsoft Project 2000 database structure. To load the Database Upgrade Utility, right-click on the toolbar and select the Database Upgrade Utility.
Microsoft Project performs several types of operations on a database, each requiring a corresponding set of object permissions.
It is possible to have multiple projects, for multiple organizations, in the same database, and yet isolate projects from each other. This is called project isolation. To isolate projects, a separate table structure must be created for each project owner. Each database user can only see one set of project tables in any given database because Microsoft Project uses the first set of tables it finds in the database. Any security scheme must take this into consideration.
Create view MSP_TEXT_FIELDS as
Select PROJ_ID,
TEXT_CATEGORY,
TEXT_REF_UID,
TEXT_FIELD_ID,
TEXT_VALUE
From user1.MSP_TEXT_FIELDS
Union all
Select PROJ_ID + 1000,
TEXT_CATEGORY,
TEXT_REF_UID,
TEXT_FIELD_ID,
TEXT_VALUE
From user2.MSP_TEXT_FIELDS
A similar view must be created for each of the 21 tables in the Microsoft Project database structure. Projects accessed via views with unions cannot be updated.
Notes
There are two ways to set up a database for use with Microsoft Project. The simplest way is to create a database and use Microsoft Project to create the tables and indexes. This method is the easiest when performance and disk usage are not issues.
The second method of setting up a database is to manually configure a database for use with Microsoft Project. Script files are provided with Microsoft Project that contain the data definition language (DDL) to create the Microsoft Project 2000 database structures. These script files are called MPDtable.sql, SQLtable.sql, and OraTable.sql, and can be found on the Microsoft Project 2000 installation CD in the \Database subdirectory under the ValuPack directory. These script files can be modified to specify database files and file groups (SQL Server) or table spaces (Oracle) and to configure other database properties. For example, the database administrator can modify the DDL to save tables to one disk and indexes to another disk. As a general rule, performance improves when the indexes reside on a separate physical disk from the data tables. However, selecting separate disks for performance purposes is optional, not a requirement.
It is recommended that, for SQL Server, the transaction log files be put on a separate disk from the data whenever possible. For Oracle, plan for adequate log file space, particularly for archivalogmode.
Before executing the scripts in Oracle or SQL Server, you must do one of the following:
Also note that in all three scripts, the SQL statements that populate the MSP_STRING_TYPES and MSP_CONVERSIONS tables are for the English conversion values and, therefore, will need to be modified for other languages.
If project data will be modified directly in the database, Microsoft Project provides four stored procedures for backing up task, resource, assignment, and link data. See the topic Ensuring data integrity in a project in a database below for more information.
The normal standard practices for SQL Server and Oracle database performance and tuning apply to Microsoft Project databases.
Because Microsoft Project writes to and reads from a certain database structure, some changes to a database may corrupt a project in a database and prevent Microsoft Project from opening or saving part or all of the project. The following actions could corrupt a project stored in a database:
Microsoft Project verifies some data when reading a project from a database (or any other external format). The following cases may cause Microsoft Project to display an alert, change data to an appropriate value, or not read the data at all:
To prevent errors in changed task, resource, assignment, and task dependency (link) data from causing inconsistencies in the project in the database, the original data should be backed up in the respective EXT_EDIT_REF_DATA columns of the MSP_TASKS, MSP_RESOURCES, MSP_ASSIGNMENTS and MSP_LINKS tables. Microsoft Project uses the data in the EXT_EDIT_REF_DATA columns in these tables to help determine which columns have been modified in the database. These modifications are preserved if they do not cause one of the above listed conditions. When adding new rows, EXT_EDIT_REF_DATA must be set to the value "1." When changing existing rows, the value "1" plus information in the columns listed in the following table must be stored in the EXT_EDIT_REF_DATA column of each respective table. All values must be listed in the order below, delimited by commas, and, if a value is null, nothing should be entered between the commas that delimit that value.
Task Fields | Resource Fields | Assignment Fields | Link Fields |
---|---|---|---|
Actual Work | Accrue At | Assignment Units | Link Type |
Work | Available From | Start | Link Lag |
Remaining Work | Available To | Finish | |
Duration | Assignment Delay | ||
Actual Duration | Leveling Delay | ||
Remaining Duration | Actual Start | ||
Start | Actual Finish | ||
Finish | Actual Work | ||
Constraint Type | Remaining Work | ||
Constraint Date | Regular Work | ||
Actual Start | Work | ||
Actual Finish | Actual Overtime Work | ||
Stop | Remaining Overtime Work | ||
Resume | Overtime Work | ||
Percent Complete | |||
Cost | |||
Fixed Cost | |||
Actual Cost | |||
Leveling Delay | |||
Percent Work Complete | |||
Task Fixed Cost Accrual |
To make it easier to back up task, resource, assignment, and link data before updating, Microsoft Project provides stored procedures (action queries for projects in Microsoft Access databases) to automate the process. Microsoft Project creates the stored procedures in SQL Server and Oracle when the database tables are created. EXECUTE permissions must be granted to non-owners who want to execute the stored procedures. If the tables are created manually, the scripts to create the stored procedures provided with Microsoft Project must be run to create the stored procedures. The scripts that create the SQL Server and Oracle stored procedures are called BKP_ora.sql and BKP_sql.sql and can be found on the Microsoft Project 2000 installation CD in the \Database subdirectory under the ValuPack directory.
The files that contain the SQL for the Microsoft Access action queries are called BKP_ASSN.sql, BKP_LINK.sql, BKP_RES.sql, and BKP_TASK.sql. The SQL in each file must be copied into a new action query with the same name as the file from which it is copied (not including the file extension). These files can be found on the Microsoft Project 2000 installation CD in the \Database subdirectory under the ValuPack directory.
This section generally describes how different types of data are stored in the database. Detailed descriptions of how to create and change data can be found in the next chapter.
Microsoft Project saves calendar information for a project into two tables. The MSP_CALENDARS table contains the information that identifies the calendar and the resources for which it is a base calendar. The MSP_CALENDAR_DATA table contains the working, nonworking, and exception time data for the calendar to which it applies for the day or days specified. Standard working and nonworking times are defined by the calendar as it is constrained by the calendar and working time options.
Storage of time-phased data has been optimized so that a single row can contain values (for example, hours or days) for up to seven time periods (typically days) thus reducing the number of rows that must be created. Each row contains the start and end dates and the timescale unit for the data values. The data values are the time, cost, or percentage of completion for each period within the specified timescale unit. For example, if the record type is cost and the units is weeks then each data value contains the $/week.
Storing notes has been simplified in Microsoft Project 2000. Task notes (rich text) are now stored in the TASK_RTF_NOTES column in the MSP_TASKS table. Similar notes columns are available in the MSP_RESOURCES and MSP_ASSIGNMENTS tables.
Custom field values are stored in the following tables:
Each row in these tables refers to a specific task, resource, or assignment as well as the particular custom field (for example, TEXT1 in MSP_TEXT_FIELDS) and holds the value of that field for the specified task, resource, or assignment.
Custom WBS code mask definitions are stored in the MSP_ATTRIBUTE_STRINGS and MSP_FIELD_ATTRIBUTES tables. The work breakdown structure (WBS) code for each task is found in the table MSP_TASKS.
Custom outline codes are stored in the MSP_OUTLINE_CODES and MSP_CODE_FIELDS tables. As with the DATE, DURATION, FLAG, NUMBER, and TEXT custom field tables, the MSP_CODE_FIELDS table refers to a specific task, resource, or assignment. The outline code for each referenced task, resource, or assignment is constructed from rows in MSP_OUTLINE_CODES. The definitions for the outline codes are stored in the MSP_ATTRIBUTE_STRINGS and MSP_FIELD_ATTRIBUTES tables.
The nonworking time of split tasks is stored in MSP_TIMEPHASE_DATA as rows with zero hours of work. (To save time-phased data in readable form in the table MSP_TIMEPHASED_DATA, select the Expand time-phased data in the database check box on the Save tab of the Options dialog box, accessed from the Tools menu.)
Task links, or dependencies, are stored in the table MSP_LINKS. Each row defines an association between a predecessor and successor task. Each row defines only one such association. Multiple links to a given task require a row for each link.
Estimated duration is simply the duration value of each task formatted to be displayed as estimated. The value of the duration is displayed as estimated if the estimated flag is set to "True." To display custom duration fields as estimated, set the column DUR_FMT to the appropriate value (see Using the text conversion tables to retrieve strings below).
You should never drop any tables created by Microsoft Project nor should you drop any of the table columns. Also, you should not change the data type of any column. Last, you should never alter the column RESERVED_BINARY_DATA in the MSP_PROJECTS table in any way.
You may notice that some of the tables Microsoft Project created in the database contain some records with large negative values in their respective UID columns (for example, TASK_UID). These records usually appear at the top of a table or at the beginning of each project and the values are -65536, -65535, and -65534. These records are used internally by Microsoft Project and should never be edited or deleted.
If you create the tables yourself (see the next section Creating the Microsoft Project database structure), you should never define any of the columns as required or NOT NULL.
If you need to create a Microsoft Project database from scratch, the easiest method is to save an empty project using Microsoft Project. After the database is created, you can delete the project from the database from within Microsoft Project (see Deleting a project from a database below).
If you do not want to use Microsoft Project to facilitate the process, you will need to create an empty database through the ODBC driver (Access only), and then create all of the appropriate tables. Creating all of the tables manually would be an extensive undertaking because you would need to use this document as a reference and ensure that you have exactly the right table and column names for every table and the correct data types for every column. To facilitate table creation, Microsoft Project includes three script files with the SQL statements necessary to set up all of the tables for Microsoft Access, Microsoft SQL Server, or Oracle Server. To manually create and configure a database for use with Microsoft Project, see the topic Configuring the database above.
If multiple users will access projects in the same database, each user making changes to the data need not use the same data source name (DSN) for connecting to the database. Microsoft Project stores both the combined DSN and project name (in "<DSN>\projectname" format) and alternatively the connection string as the identifiers to locate a project. If someone uses a DSN with a different name to access a project, Microsoft Project uses the connection string information to resolve references for items such as inserted projects, cross-project links, and sharer projects that use the same resource pool. This also applies to projects that are stored in a Microsoft Access database (as an MPD or MDB file). However, if multiple users modify the database, then those users should not save files by selecting the MPD or MDB file-type from the Save as type list in the Save As dialog box.
Because file DSNs have limitations, they cannot be used for project links such as resource pools, sharer files, cross-project links, or inserted projects.
Because of the way Microsoft Project caches logon passwords and other connection information, the same DSN cannot be used by more than one user ID on a single machine for different simultaneous logons to the database. Once a DSN connection is established, that connection will be reused even if a different logon name and/or password is entered at logon time. To log on with a different user ID, you must first close all projects opened with the DSN or, for simultaneous connections, you must create additional alternate DSNs to use with each user ID.
If you open a project in a database through Microsoft Project, and that project is not in use by another user, you will be given full read/write access. Until you finish your session with the project, another user will only be able to open that project in Microsoft Project as read-only. Read/write access permission and some other properties used in managing concurrent access are all maintained in five columns in the MSP_PROJECTS table in the database. Use of these access concurrency columns are in effect only when users are using Microsoft Project to read or update the database. Microsoft Project does not provide any kind of locking when a database is being read or updated directly by a user using a database query program or other tool. Thus, any program or tool which reads or updates project data in the database should follow the same conventions as Microsoft Project to ensure consistent data access. Use of the access concurrency columns is described below.
While you have the project open with read/write access through Microsoft Project, then Microsoft Project stores the value "1" in the PROJ_READ_WRITE and PROJ_READ_ONLY columns. When you finish your session and close the database, the field values will be reset to "0,". While Microsoft Project is loading from or writing to a database, it will also set the values of the PROJ_LOCKED and PROJ_READ_COUNT columns to "1". While these fields have a value of "1", the project may not be opened by any user using Microsoft Project, not even read-only.
Microsoft Project stores the name of the machine that currently has the project open for read/write access in the PROJ_MACHINE_ID column. While PROJ_READ_WRITE is set to "1" in order to update project data in the database, it is recommended that the column PROJ_MACHINE_ID be set to your computer's machine name so that any user attempting to open the project through Microsoft Project will get an appropriate message informing them that the project is currently opened for read/write access by you. Otherwise, Microsoft Project can't identify the user who has the project open for read/write access. When you are ready to allow read/write access to the project again, you should set the PROJ_MACHINE_ID field back to a NULL when you reset the flags to "0."
Before you make updates to the database directly, you should first check that all of these flags are set to "0" and then temporarily set them to "1" to prevent other users from opening the project through Microsoft Project.
Copy, modify and execute the following SQL update statement to lock a project for update:
Update MSP_PROJECTS
Set PROJ_READ_ONLY = '1',
PROJ_READ_WRITE = '1',
PROJ_READ_COUNT = '1',
PROJ_LOCKED = '1',
PROJ_MACHINE_ID = 'your computer or application name',
Where PROJ_ID = 1
Copy, modify and execute the following SQL update statement to unlock the project after the update has been completed:
Update MSP_PROJECTS
Set PROJ_READ_ONLY = '0',
PROJ_READ_WRITE = '0',
PROJ_READ_COUNT = '0',
PROJ_LOCKED = '0',
PROJ_MACHINE_ID = null,
Where PROJ_ID = 1
Note: If a user has read/write access to a project in a database through Microsoft Project, and another user changes data in that project directly in the database, that change will not be reflected in Microsoft Project for the first user. In addition, if the first user saves project data back to the database, that data will overwrite any changes made directly in the database by the second user.
You can delete a project in a database by clicking Delete on the Tools drop-down menu of the Open from database dialog box. The table structure will be retained. You can also delete the project data from the database using the DeleteFromDatabase Microsoft Visual Basic for Applications (VBA) method (see the Microsoft Project online Help for information about using VBA).
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.
In all cases where project data in a database is edited outside Microsoft Project, the column PROJ_EXT_EDITED in the table MSP_PROJECTS must be set to "True."
When editing existing rows in the tables MSP_TASKS, MSP_RESOURCES, MSP_ASSIGNMENTS and MSP_LINKS, the column EXT_EDIT_REF_DATA must be set to "1" followed by the values of the columns listed in the table below (all values delimited by commas). When creating new rows, however, the column EXP_EDIT_REF_DATA should be set to "1". (Note: It is recommended that the stored procedures included with Microsoft Project be used to update this column with the required values. See Ensuring integrity in a project in a database.)
MSP_TASKS | MSP_RESOURCES | MSP_ASSIGNMENTS | MSP_LINKS |
---|---|---|---|
TASK_ACT_WORK | RES_ACCRUE_AT | ASSN_UNITS | LINK_TYPE |
TASK_WORK | RES_AVAIL_FROM* | ASSN_START_DATE | LINK_LAG |
TASK_REM_WORK | RES_AVAIL_TO* | ASSN_FINISH_DATE | |
TASK_DUR | ASSN_DELAY | ||
TASK_ACT_DUR | ASSN_LEVELING_DELAY | ||
TASK_REM_DUR | ASSN_ACT_START | ||
TASK_START_DATE | ASSN_ACT_FINISH | ||
TASK_FINISH_DATE | ASSN_ACT_WORK | ||
TASK_CONSTRAINT_TYPE | ASSN_REM_WORK | ||
TASK_CONSTRAINT_DATE | ASSN_REG_WORK | ||
TASK_ACT_START | ASSN_WORK | ||
TASK_ACT_FINISH | ASSN_ACT_OVT_WORK | ||
TASK_STOP_DATE | ASSN_REM_OVT_WORK | ||
TASK_RESUME_DATE | ASSN_OVT_WORK | ||
TASK_PCT_COMP | |||
TASK_COST | |||
TASK_FIXED_COST | |||
TASK_ACT_COST | |||
TASK_LEVELING_DELAY | |||
TASK_PCT_WORK_COMP | |||
TASK_FIXED_COST_ACCRUAL |
* While these fields are backed up, they should not be updated. These fields are derived for a specific resource from the earliest date value in AVAIL_FROM and the latest date value in AVAIL_TO for that resource in the table MSP_AVAILABILITY. Changes to resource availability should be made in the table MSP_AVAILABILITY.
When updating custom field data, the following flags must be set in the MSP_PROJECTS table:
Table | Flag in MSP_PROJECTS |
---|---|
MSP_DATE_FIELDS | PROJ_EXT_EDITED_DATE |
MSP_DURATION_FIELDS | PROJ_EXT_EDITED_DUR |
MSP_FLAG_FIELDS | PROJ_EXT_EDITED_FLAG |
MSP_NUMBER_FIELDS | PROJ_EXT_EDITED_NUM |
MSP_OUTLINE_CODES/MSP_CODE_FIELDS | PROJ_EXT_EDITED_CODE |
MSP_TEXT_FIELDS | PROJ_EXT_EDITED_TEXT |
When updating the MSP_FIELD_ATTRIBUTES and MSP_ATTRIBUTE_STRINGS tables, the column ATTRIB_EXT_EDITED in the MSP_FIELD_ATTRIBUTES table must be set to "True" in each modified or new row.
When updating the MSP_TIMEPHASED_DATA table, the column TD_EXT_EDITED must be set to "True."
When you enter a date value directly in the database, you should always include the time with the date. Since database date time columns use a default time when you don't specify the time, relying on the default can lead to unexpected results. The database columns usually default to 12:00 A.M., which is normally nonworking time in most calendars. When Microsoft Project encounters 12:00 A.M., it rounds the time to the next closest working time for start times and to the last working time for finish times.
Thus, if you specify dates without times, it can result in the following situation: You create what you think is a five-day task in the database by specifying the task start as Monday's date and the task finish as Friday's date. The Monday time in the database is really Monday at 12:00 A.M., so Microsoft Project treats this as Monday, 8:00 A.M. (per the calendar default start time), which works fine. For Friday's time, though, the default is Friday at 12:00 A.M., which is rolled back to Thursday at 5:00 P.M. (the calendar default finish time). Thus, your five-day task becomes only four days when it is read in by Microsoft Project. By explicitly specifying the time in each date/time column, you can always avoid this problem.
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.
To enable different language versions of Microsoft Project to read a project in a database, the contents of some columns are converted to numeric constants. Microsoft Project writes two tables to the database containing the conversion information for those columns, MSP_STRING_TYPES, which contains the mapping between the enumerated field categories and the field name, and MSP_CONVERSIONS, which contains the mapping between the numeric constants and the possible text values for each field. The following field categories are converted to numeric constants:
Field Category | Fields in this Category | From Table |
---|---|---|
Weekday | Weekday | MSP_CALENDARS |
Schedule From | ScheduleFromProjectFinish | MSP_PROJECTS |
ScheduleFromProjectStart | MSP_PROJECTS | |
Accrual | AccrueAt | MSP_PROJECTS |
FixedCostAccrual | MSP_TASKS | |
DefaultFixedCostAccrual | MSP_PROJECTS | |
Link Type | LinkType | MSP_LINKS |
Display Units | LinkLagDisplayUnits | MSP_LINKS |
DelayDisplayUnits | MSP_ASSIGNMENTS | |
DurationDisplayUnits | MSP_TASKS | |
BaselineDurationDisplayUnits | MSP_TASKS | |
DelayDisplayUnits | MSP_TASKS | |
DurationDisplayUnits | MSP_DURATION_FIELDS | |
Cost Rate Units | StandardRateDisplayUnits | MSP_RESOURCES |
OvertimeRateDisplayUnits | MSP_RESOURCES | |
StandardRateDisplayUnits | MSP_RESOURCE_RATES | |
OvertimeRateDisplayUnits | MSP_RESOURCE_RATES | |
Work Contour Type | WorkContour | MSP_ASSIGNMENTS |
Constraint Type | ConstraintType | MSP_TASKS |
Task Type | Type | MSP_TASKS |
DefaultTaskType | MSP_PROJECTS | |
Calendar Working | Working | MSP_CALENDAR_DATA |
Category Type | CategoryType | MSP_DATE_FIELDS |
CategoryType | MSP_DURATION_FIELDS | |
CategoryType | MSP_NUMBER_FIELDS | |
CategoryType | MSP_TEXT_FIELDS | |
Field ID | FieldID | MSP_DATE_FIELDS |
FieldID | MSP_DURATION_FIELDS | |
FieldID | MSP_NUMBER_FIELDS | |
FieldID | MSP_TEXT_FIELDS | |
FieldID | MSP_FIELD_ATTRIBUTES | |
FieldID | MSP_FIELD_CODES | |
Workgroup Messages | Workgroup | MSP_RESOURCES |
Currency Symbol Position | CurrencyPosition | MSP_PROJECTS |
Field Attributes | OutlineCodeName | MSP_OUTLINE_CODES |
WBSMask | MSP_FIELD_ATTRIBUTES | |
Time-phased Contour Type | RemainingWork | MSP_ASSIGNMENTS |
ActualWork | MSP_ASSIGNMENTS | |
ActualOvertimeWork | MSP_ASSIGNMENTS | |
BaselineWork | MSP_ASSIGNMENTS | |
BaselineCost | MSP_ASSIGNMENTS | |
ActualCost | MSP_ASSIGNMENTS | |
BaselineWork | MSP_RESOURCES | |
BaselineCost | MSP_RESOURCES | |
BaselineWork | MSP_TASKS | |
BaselineCost | MSP_TASKS | |
PercentComplete | MSP_TASKS | |
Splits | MSP_TIMEPHASED_DATA |
The MSP_STRING_TYPES and MSP_CONVERSIONS tables are described in detail below, in the section "Microsoft Project database structure."
You can store projects from different language versions of Microsoft Project into the same database. Microsoft Project adds values to both MSP_STRING_TYPES and MSP_CONVERSIONS in the appropriate language the first time a project in a new language is saved to the database.
Note: When saving to database, the code pages of the Microsoft Project computer and the database server must be the same.
If you selectively save partial project data to a database, Microsoft Project creates the conversion strings in the MSP_STRING_TYPES and MSP_CONVERSIONS tables, if they do not already exist.
Microsoft Project uses English for the names of the columns and tables in the database, in each language version. If you change the name of a table in a database you will probably corrupt the database, and Microsoft Project will not be able to read that table and perhaps the entire project.
To create an entirely new project in the database, you must add a new row to the MSP_PROJECTS table and create a project summary task in the MSP_TASKS table. You must enter values for at least the columns specified below. Then, to add the associated tasks, resources, and assignments to the project, you must create the task, resource, and assignment rows as described below.
Table | Fields | Notes |
---|---|---|
MSP_PROJECTS | PROJ_ID | Must be unique within the table. |
PROJ_NAME | Must be unique within the table. | |
PROJ_INFO_START_DATE | The project start date and time. | |
PROJ_EXT_EDITED | Must be set to "True" for Microsoft Project to process. | |
MSP_TASKS | PROJ_ID | Specify the same PROJ_ID value as in the MSP_PROJECTS table. |
TASK_UID | This value must be zero for a project summary task. | |
TASK_ID | This value must be zero for a project summary task. |
Notes
The process for creating an inserted project in the database is a combination of the procedures for creating a new task and specifying the value of a custom text field. You must add new records to the MSP_TASKS and MSP_TEXT_FIELDS tables with values for at least the following fields, as well as setting the flag in the MSP_PROJECTS table:
Table | Fields | Notes |
---|---|---|
MSP_TASKS | PROJ_ID | This value must refer to a valid project in the MSP_PROJECTS table. This is the ID of the master project, not the inserted project. |
TASK_UID | Must be unique within the master project. This is the unique ID of the inserted project task. | |
TASK_ID | Must be unique within the master project. This is the ID of the inserted project task. | |
EXT_EDIT_REF_DATA | Must enter a "1" for the new task. | |
MSP_TEXT_FIELDS | PROJ_ID | Specify the same PROJ_ID value as in the MSP_TASKS table. |
TEXT_CATEGORY | The category type is always zero (task) for inserted projects. | |
TEXT_REF_UID | Specify the same TASK_UID value used for the inserted project task in the MSP_TASKS table. | |
TEXT_FIELD_ID | The field ID is always 188743706 for inserted projects. | |
TEXT_VALUE | The full name of the inserted project (that is, path and filename or database and project name). | |
MSP_PROJECTS | PROJ_EXT_EDITED | Set this value to "True." |
PROJ_EXT_EDITED_TEXT | Set this value to "True." |
Note: When deleting an inserted project from the database, all of the rows for the inserted project from the tables specified above must be deleted to avoid leaving orphaned data in the database, which could cause unpredictable results when the database is opened in Microsoft Project.
Example:
Let's say you have two projects stored in a database, and you want to make one of them an inserted project of the other. Assume the two projects are in an MPD file that you access with a DSN called "Projects" and the project names are "Master Project" and "Subproject." First you need to create a task in Master Project to hold the inserted project. This record in the MSP_TASKS table would appear as follows:
PROJ_ID | 1 |
TASK_ID | 5 |
TASK_UID | 5 |
TASK_NAME | My subproject |
EXT_EDIT_REF_DATA | 1 |
The TASK_ID and TASK_UID used here are chosen for demonstration purposes and have no significance. The important issue to remember is that they must be unique among all the IDs for the current project. After you have inserted this task, you need to specify the name of the inserted project file. For example, the following is the row that you need to add to the MSP_TEXT_FIELDS table:
PROJ_ID | 1 |
TEXT_CATEGORY | 0 |
TEXT_REF_UID | 5 |
TEXT_FIELD_ID | 188743706 |
TEXT_VALUE | <Projects>\Subproject |
The value of PROJ_ID must match the task record. The TEXT_CATEGORY is zero in this case because we are setting a task text field. The value of TEXT_REF_UID is taken directly from the TASK_UID column in the MSP_TASKS table. The value of TEXT_FIELD_ID is taken from the CONV_VALUE column in the MSP_CONVERSIONS table from the record where the CONV_STRING equals "Task Subproject File." The TEXT_VALUE field gets the actual project name, which, in this case, is specified in the <DSN>\ProjectName syntax for a project in a database.
In order for Microsoft Project to read this row, you must set the PROJ_EXT_EDITED_TEXT flag in the MSP_PROJECTS table to "True."
To create a new resource in the database, you must add a new row to the MSP_RESOURCES table and enter values for at least the following columns:
Table | Columns | Notes |
---|---|---|
MSP_RESOURCES | PROJ_ID | Must refer to a valid project in the MSP_PROJECTS table. |
RES_UID | Must be unique within the project. | |
RES_ID | Must be unique within the project. | |
RES_NAME | The name of the new resource. | |
RES_TYPE | Enter 1 (True) for work. Default is "0" (False) for material. | |
EXT_EDIT_REF_DATA | Must enter a "1" for the new resource. | |
MSP_PROJECTS | PROJ_EXT_EDITED | Must be set to "True" for Microsoft Project to process. |
The following SQL insert statement can be modified and used to insert a new work resource.
Insert into MSP_RESOURCES (
PROJ_ID,
RES_UID,
RES_ID,
RES_NAME,
RES_TYPE,
EXT_EDIT_REF_DATA )
values (
3,
4,
4,
'John Smith',
1,
'1' )
Calendars can be created for a project or a resource. Optionally, base calendars can be applied to tasks. The base calendar for the project implicitly defines the working hours for other base and resource calendars. Calendar data is stored in the MSP_CALENDARS table. Calendar working times and exceptions are stored in the MSP_CALENDAR_DATA table. (See the following section, Specifying calendar working time and exceptions.) To create a new calendar, you must create a row with values for at least the following columns:
Table | Columns | Notes |
---|---|---|
MSP_CALENDARS | PROJ_ID | Must refer to a valid project in the MSP_PROJECTS table. |
CAL_UID | Must be unique within the project. | |
CAL_BASE_UID | Required for resource calendars. Refers to its parent base calendar. -1 for base calendars. | |
CAL_IS_BASE_CAL | Enter 0 (false) for resource calendars. | |
CAL_NAME | Required for base calendars. | |
MSP_PROJECTS | PROJ_EXT_EDITED | Must be set to "True" for Microsoft Project to process. |
To create a new base calendar, copy, modify, and execute the following SQL insert statement:
Insert into MSP_CALENDARS (
PROJ_ID,
CAL_UID,
CAL_BASE_UID,
CAL_IS_BASE_CAL,
CAL_NAME )
values (
3,
4,
-1,
1,
'new base calendar' )
To create a new resource calendar, copy, modify, and execute the following SQL insert statement:
Insert into MSP_CALENDARS (
PROJ_ID,
CAL_UID,
CAL_BASE_UID,
RES_UID,
CAL_IS_BASE_CAL )
values (
3,
5,
2,
4,
0 )
Notes
Both standard working times and exceptions for a calendar are stored in the MSP_CALENDAR_DATA table. One row must be inserted for each working day and nonworking day. In addition, one, and only one, row must be inserted for each exception period. To specify a calendar working day, you must insert a row with values for at least the following columns:
Table | Columns | Notes |
---|---|---|
MSP_CALENDAR_DATA | PROJ_ID | Must refer to a valid project in the MSP_PROJECTS table. |
CD_UID | Must be unique within the project. | |
CAL_ UID | Must be a calendar within the specified project. | |
CD_DAY_OR_EXCEPTION | Enter 1-7 for Sunday through Saturday (0 for exception day). | |
CD_WORKING | Set to "1" for working day ("0" for nonworking day). | |
CD_FROM_TIME1 | Starting day and time of the working day (not used for exception). Up to five periods per day are supported. | |
CD_TO_TIME1 | Ending day and time of the working day (not used f or exception). | |
MSP_PROJECTS | PROJ_EXT_EDITED | Must be set to "True" for Microsoft Project to process. |
To specify working and exception days for a calendar, copy, modify and execute the following insert statement for each working day:
Insert into MSP_CALENDAR_DATA (
PROJ_ID,
CD_UID,
CAL_UID,
CD_DAY_OR_EXCEPTION,
CD_WORKING,
CD_FROM_TIME1,
CD_TO_TIME1,
CD_FROM_TIME2,
CD_TO_TIME2 )
values (
3,
1,
3,
2,
1,
'10/25/1999 8:00:00 AM',
'10/25/1999 12:00:00 PM',
'10/25/1999 1:00:00 PM',
'10/25/1999 5:00:00 PM' )
No value should be specified for CD_FROM_TIMEn or CD_TO_TIMEn for nonworking days.
To specify available dates for resources, you must add a row to the table MSP_AVAILABILITY for each available period and enter values for the following columns:
Table | Columns | Notes |
MSP_AVAILABILITY | PROJ_ID | Must refer to a valid project in the MSP_PROJECTS table. |
AVAIL_UID | Must be unique within the project. | |
RES_UID | Must be a valid resource UID within the project. | |
AVAIL_FROM | The first date of the available period. Note: The time component of the date is ignored. | |
AVAIL_TO | The last date of the available period. | |
AVAIL_UNITS | Decimal fraction representing the percent of the available period that the resource is available to work. | |
MSP_PROJECTS | PROJ_EXT_EDITED | Must be set to "True" for Microsoft Project to process. |
To specify a period of availability from October 25, 1999, to October 30, 1999, at 50% for a resource, copy, modify, and execute the following insert statement:
Insert into MSP_AVAILABILITY (
PROJ_ID,
AVAIL_UID,
RES_UID,
AVAIL_FROM,
AVAIL_TO,
AVAIL_UNITS )
values (
3,
1,
5,
'10/25/1999',
'10/30/1999',
0.5 )
To specify a single rate for a resource, you must add a row to the MSP_RESOURCE_RATES table and enter values for at least the following columns:
Table | Columns | Notes |
MSP_RESOURCE_RATES | PROJ_ID | Must refer to a valid project in the MSP_PROJECTS table. |
RR_UID | Identifies the rate. Must be unique within the project. | |
RES_UID | Identifies the resource to whom the rate applies. Must be unique within the project. | |
RR_RATE_TABLE | Identifies one of the five rate tables of the specified resource: 0 through 4 identify tables A through E, respectively. | |
RR_STD_RATE
or RR_OVT_RATE or RR_PER_USE_COST |
Rates are in dollars per hour. Per use costs are in dollars * 100. If either a standard or or overtime rate is specified, the format can optionally be specified also. See MSP_STRING_TYPES and MSP_CONVERSIONS for string type 'Cost Rate Data Units'. | |
MSP_PROJECTS | PROJ_EXT_EDITED | Must be set to "True" for Microsoft Project to process. |
To specify a rate with an effective, or from, date, two rows must be inserted into MSP_RESOURSE_RATES: one for the first rate which does not have an effective date, and one for the row that does. To specify standard and overtime hourly cost rates for a resource for a date range in table B, copy, modify, and execute the following insert statements:
-- insert the first rate (from date not required)
Insert into MSP_RESOURCE_RATES (
PROJ_ID,
RR_UID,
RES_UID,
RR_RATE_TABLE,
RR_TO_DATE,
RR_STD_RATE,
RR_STD_RATE_FMT,
RR_OVT_RATE,
RR_OVT_RATE_FMT )
values (
3,
1,
5, -- resource UID
1, -- table B
'2000-02-12', -- rate end date
25.00, -- $25/h standard rate
2, -- standard rate format = h = hours
35.00, -- $35/h overtime rate
2 ) -- overtime rate format = h = hours
-- insert the second rate
Insert into MSP_RESOURCE_RATES (
PROJ_ID,
RR_UID,
RES_UID,
RR_RATE_TABLE,
RR_FROM_DATE,
RR_TO_DATE,
RR_STD_RATE,
RR_STD_RATE_FMT,
RR_OVT_RATE,
RR_OVT_RATE_FMT )
values (
3,
2,
5, -- resource UID
1, -- table B
'2000-02-12', -- rate start date
'2000-04-30', -- rate end date
30.00, -- $25/h standard rate
2, -- standard rate format = h = hours
40.00, -- $35/h overtime rate
2 ) -- overtime rate format = h = hours
To specify a per-use cost for resource for a resource in table C, copy, modify, and execute the following insert statement:
Insert into MSP_RESOURCE_RATES (
PROJ_ID,
RR_UID,
RES_UID,
RR_RATE_TABLE,
RR_PER_USE_COST )
values (
3,
3,
5, -- resource UID
2, -- table C
5000.00 ) –- $50.00 per use cost
To delete all of the resource rates in a given rate table (A, B, C, etc.) for a resource, you must delete all existing records for that table for that resource and create one entry with zero costs.
To create a new task in the database, you must add a new row to the MSP_TASKS table and enter values for at least the following columns:
Table | Columns | Notes |
MSP_TASKS | PROJ_ID | Must refer to a valid project in the MSP_PROJECTS table. |
TASK_UID | Must be unique within the project. | |
TASK_ID | Must be unique within the project. | |
TASK_NAME | The name of the new task. | |
EXT_EDIT_REF_DATA | Must enter a "1" for the new task. | |
MSP_PROJECTS | PROJ_EXT_EDITED | Must be set to "True" for Microsoft Project to process. |
Example:
Assume your current project has 22 tasks with TASK_UIDs 1 to 22 and TASK_IDs 1 to 22. If you want to create a new one-day task named "Research Competitors" and you want this task to be the eighth task in the project, you would add the following row to the MSP_TASKS table:
PROJ_ID | 3 |
TASK_UID | 23 |
TASK_ID | 8 |
TASK_NAME | Research Competitors |
TASK_DUR | 4800 |
EXT_EDIT_REF_DATA | 1 |
Obtain the correct PROJ_ID from the MSP_PROJECTS table. For the TASK_UID, use 23 because it is the next available number. Set TASK_ID to 8, to make this task eighth in the list. This will require that the TASK_IDs of each subsequent record be adjusted by one to make room (since TASK_IDs must be unique). The following SQL update statement can be executed to renumber TASK_IDs:
Update MSP_TASKS
Set TASK_ID = TASK_ID + 1
Where TASK_ID between 8 and 22 and
PROJ_ID = 3
Remember to change the value of PROJ_ID to the project whose tasks you are renumbering. Once the existing tasks have been renumbered, the following SQL insert statement can be executed to create the new task:
Insert into MSP_TASKS (
PROJ_ID,
TASK_UID,
TASK_ID,
TASK_NAME,
TASK_DUR,
EXT_EDIT_REF_DATA )
values (
3,
23,
8,
'my task',
4800,
'1' )
Notes
While it is possible to create a recurring task in the database, it's preferable to create recurring tasks inside Microsoft Project because the Recurring Task Information dialog box in Microsoft Project will not reflect the actual recurring task settings for a recurring task created directly in the database. Recurring tasks created in the database will always default to a weekly recurring one-day task that occurs on Mondays starting after the current date. To create a recurring task, you must add a row for the summary task and a row for each instance of a recurring subtask and enter values for at least the following columns:
Table | Fields | Notes |
---|---|---|
MSP_TASKS | PROJ_ID | Must refer to a valid project in the MSP_PROJECTS table. |
TASK_UID | Must be unique within the project. | |
TASK_ID | Must be unique within the project. | |
TASK_NAME | The name of the recurring summary task. Not required for subtasks. | |
TASK_OUTLINE_LEVEL | The level of recurring subtasks must be one level lower than the summary recurring task. | |
TASK_IS_RECURRING | Set to true for both summary tasks and subtasks | |
TASK_IS_RECURRING_SUMMARY | Must be set to True for the summary task and false for all of the subtasks. | |
TASK_IS_ROLLED_UP | Must be Set to true for summary and subtasks. | |
TASK_CONSTRAINT_
TYPE |
Required for subtasks. Usually set to 4 (SNET). | |
EXT_EDIT_REF_DATA | Must enter a "1" for Microsoft Project to process. | |
MSP_PROJECTS | PROJ_EXT_EDITED | Must be set to "True" for Microsoft Project to process. |
To create a recurring task with two recurrences, copy, modify, and execute the following three insert statements:
-- insert the summary recurring task
Insert into MSP_TASKS (
PROJ_ID,
TASK_UID,
TASK_ID,
TASK_NAME,
TASK_OUTLINE_LEVEL,
TASK_IS_RECURRING,
TASK_IS_RECURRING_SUMMARY,
TASK_IS_ROLLED_UP,
EXT_EDIT_REF_DATA )
values (
3,
9,
9,
'my recurring summary task',
1, -- outline level
1, -- is recurring
1, -- is recurring summary
1, -- is rolled up
'1' )
-- insert the first recurring subtask
Insert into MSP_TASKS (
PROJ_ID,
TASK_UID,
TASK_ID,
TASK_OUTLINE_LEVEL,
TASK_IS_RECURRING,
TASK_IS_RECURRING_SUMMARY,
TASK_IS_ROLLED_UP,
TASK_CONSTRAINT_TYPE,
EXT_EDIT_REF_DATA )
values (
3,
10,
10,
2, -- outline level
1, -- is recurring
0, -- is recurring summary
1, -- is rolled up
4, -- constraint type = 4 = 'Start no earlier than'
'1' )
-- insert the second recurring subtask
Insert into MSP_TASKS (
PROJ_ID,
TASK_UID,
TASK_ID,
TASK_OUTLINE_LEVEL,
TASK_IS_RECURRING,
TASK_IS_RECURRING_SUMMARY,
TASK_IS_ROLLED_UP,
TASK_CONSTRAINT_TYPE,
EXT_EDIT_REF_DATA )
values (
3,
11,
11,
2, -- outline level
1, -- is recurring
0, -- is recurring summary
1, -- is rolled up
4, -- constraint type = 4 = 'Start no earlier than'
'1' )
To create a new task link dependency in the database, you must add a new row to the MSP_LINKS table and enter values for at least the following columns (which will give you a simple Finish-to-Start link with zero lag):
Table | Fields | Notes |
---|---|---|
MSP_LINKS | PROJ_ID | Must refer to a valid project in the MSP_PROJECTS table. |
LINK_UID | Must be unique within the project. | |
LINK_PRED_UID | Must refer to a valid row for the same PROJ_ID in the MSP_TASKS table. | |
LINK_SUCC_UID | Must refer to a valid row for the same PROJ_ID in the MSP_TASKS table. | |
EXT_EDIT_REF_DATA | Must enter a "1" for the new link. | |
MSP_PROJECTS | PROJ_EXT_EDITED | Must be set to "True" for Microsoft Project to process. |
To specify a link type other than the default, set the column LINK_TYPE to the type of link desired. The valid values for link type can be obtained from the CONV_VALUE column in the MSP_CONVERSIONS table where the STRING_TYPE_ID equals 3 (Link Type). The valid link types are 0 (Finish-to-Finish), 1 (Finish-to-Start), 2 (Start-to-Finish) and 4 (Start-to-Start).
If you want to specify lag when you create a link, then you must specify both of the following columns:
Table | Fields | Notes |
---|---|---|
MSP_LINKS | LINK_LAG | The amount of lag, specified as a duration value (that is, minutes * 10). |
LINK_LAG_FMT | The value representing the units to use when the LINK_LAG is displayed inside Microsoft Project. The range of values can be obtained from the CONV_VALUE column in the MSP_CONVERSIONS table where the STRING_TYPE_ID equals 57 (Display Units). | |
EXT_EDIT_REF_DATA | Must enter a "1" for the link. | |
MSP_PROJECTS | PROJ_EXT_EDITED | Must be set to "True" for Microsoft Project to process. |
To create a new assignment in the database, you must add a new row to the MSP_ASSIGNMENTS table with values for at least the following columns:
Table | Fields | Notes |
---|---|---|
MSP_ASSIGNMENTS | PROJ_ID | Must refer to a valid project in the MSP_PROJECTS table. |
ASSN_UID | Must be unique within the project. | |
TASK_UID | Must refer to a valid row for the same PROJ_ID in the MSP_TASKS table. | |
RES_UID | Must refer to a valid row for the same PROJ_ID in the MSP_RESOURCES table. | |
ASSN_UNITS | The percentage of work time that the resource will apply to the task. | |
ASSN_WORK | Defaults to zero if not specified. | |
EXT_EDIT_REF_DATA | Must enter a "1" for the new assignment. | |
MSP_PROJECTS | PROJ_EXT_EDITED | Must be set to "True" for Microsoft Project to process. |
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
Timephased data is stored in the MSP_TIMEPHASED_DATA table. The data is stored in much the same way it is displayed in the Task Usage and Resource Usage Views. Each row in the table specifies:
The type of data captured is set to the appropriate value of the column CONV_VALUE in the table MSP_CONVERSIONS where the string type is equal to 'Timephased Contour Type'. The types of data that can be recorded include:
CONV_VALUE | Field |
---|---|
1 | Assignment remaining work |
2 | Assignment actual work |
3 | Assignment actual overtime work |
4 | Assignment baseline work |
5 | Assignment baseline cost |
6 | Assignment actual cost |
7 | Resource baseline work |
8 | Resource baseline cost |
9 | Task baseline work |
10 | Task baseline cost |
11 | Task percent complete |
Note: Assignment work completed is not saved to the database. Assignment work completed is calculated as assignment actual work/assignment duration.
To record timephased remaining work, actual work, or cost on an assignment or task, you must add one or more new rows to the table MSP_TIMEPHASED_DATA and enter values for at least the following columns:
Table | Fields | Notes |
---|---|---|
MSP_TIMEPHASED_DATA | PROJ_ID | Must refer to a valid project in the MSP_PROJECTS table. |
TD_UID | Must be unique within the project. | |
TD_FIELD_ID | See MSP_STRING_TYPES and MSP_CONVERSIONS for 'Timephased Contour Types' such as 'Actual Work', 'Remaining Work', 'Percent Complete' and 'Actual Cost'. | |
TD_CATEGORY | Specifies whether this record refers to a task, resource or assignment. See MSP_STRING_TYPES and MSP_CONVERSIONS for 'Category Types'. | |
TD_REF_UID | Depending on the category, the corresponding TASK_UID, RES_UID or ASSN_UID value of the row for which the timephased data is being specified. | |
TD_START | The start of the first time period for which work, percent complete or cost values will be applied. | |
TD_FINISH | The end of the last time period to which work, percent complete or cost values will be applied. | |
TD_UNITS | The units of time to which the specified values in the TD_VALUE1 through TD_VALUE7 columns apply. See MSP_STRING_TYPESand MSP_CONVERSIONS for 'Timephased Data Units'. | |
TD_VALUE1
To TD_VALUE7 |
The value of the work, percent complete or cost for the time period specified in the TD_UNITS column over the date range specified in the TD_START AND TD_FINISH columns. Only values that fall between the specified start and finish dates will be considered. | |
TD_EXT_EDITED | Must be set to "True" for Microsoft Project to process. | |
MSP_PROJECTS | PROJ_EXT_EDITED | Must be set to "True" for Microsoft Project to process. |
The following table shows how data stored in MSP_TIMEPHASED_DATA relates to the data for actual work toward an assignment in the task usage view.
Project Field | Usage View Value | Database Column | MSP_TIMEPHASED_DATA Value |
---|---|---|---|
Project ID | n/a | PROJ_ID | 3 |
Time-phased record UID | n/a | TD_UID | 4 |
Details | Act. Work | TD_FIELD_ID | 2 |
Category | n/a | TD_CATEGORY | 3 |
Assignment UID | n/a | TD_REF_UID | 16 |
Time period | timescale | TD_START | 11/08/1999 8:00:00 AM |
TD_FINISH | 11/14/1999 8:00:00 AM | ||
Minor scale | TD_UNITS | 3 | |
Actual work | 8h | TD_VALUE1 | 480000 |
4h | TD_VALUE2 | 240000 | |
0h | TD_VALUE3 | 0 | |
4h | TD_VALUE4 | 240000 |
To specify actual work for an assignment, copy, modify, and execute the following insert statement:
Insert into MSP_TIMEPHASED_DATA (
PROJ_ID,
TD_UID,
TD_FIELD_ID,
TD_CATEGORY,
TD_REF_UID,
TD_START,
TD_FINISH,
TD_UNITS,
TD_VALUE1,
TD_VALUE2,
TD_VALUE3,
TD_VALUE4,
TD_EXT_EDITED )
values (
3,
108,
2, -- assignment actual work
3, -- category = assignment
2, -- assignment UID
'11/8/1999 8:00:00 AM', -- start of the first time period
'11/12/1999 8:00:00 AM', -- end of the last time period
2, -- each value column represents a day,
480000, –- day one value = 8h = (480 minutes * 1000)
240000, -- day two value = 4h = (240 minutes * 1000)
0, -- day three value = 0h = (zero minutes * 1000)
240000, -- day three value = 4h = (240 minutes * 1000)
1 ) –- externally edited flag
Note: The value of TD_FINISH will be displayed in Project as 11/11/1999 since 11/12/1999 08:00 is interpreted as the end of the working day which begins on 11/11.
Creating splits in scheduled work is much like specifying actual work. The differences are:
Specifying remaining work is similar to specifying actual work. The differences are:
To specify a cost contour for an assignment over eight days, copy, modify, and execute the following insert statement:
Insert into MSP_TIMEPHASED_DATA (
PROJ_ID,
TD_UID,
TD_FIELD_ID,
TD_CATEGORY,
TD_REF_UID,
TD_START,
TD_FINISH,
TD_UNITS,
TD_VALUE1,
TD_EXT_EDITED )
values (
3,
109,
6, -- assignment actual cost
3, -- category = assignment
3, -- assignment UID
'11/8/1999 8:00:00 AM', -- start of the first time period
'11/15/1999 8:00:00 AM', -- end of the last time period
3, -- each value column represents a week,
10032, –- week one value = $100.32 cost * 100,
1 )
This example creates a record of $100.32 cost for the assignment over the seven days of 11/8/1999 to 11/15/1999. To create a record for the eighth day, copy and modify the following insert statement:
Insert into MSP_TIMEPHASED_DATA (
PROJ_ID,
TD_UID,
TD_FIELD_ID,
TD_CATEGORY,
TD_REF_UID,
TD_START,
TD_FINISH,
TD_UNITS,
TD_VALUE1,
TD_EXT_EDITED )
values (
3,
110,
6, -- assignment actual cost
3, -- category = assignment
3, -- assignment UID
'11/15/1999 8:00:00 AM', -- start of the first time period
'11/16/1999 8:00:00 AM', -- end of the last time period
2, -- days,
1650, –- day one value = $16.50 cost * 100,
1 )
The combination of these two inserts will create a contour over the eight-day period specified. Note: Microsoft Project will ignore inserted cost data if 'Project calculates actual cost' flag is set to true. To set this flag off, copy, modify, and execute the following update statement:
Update MSP_PROJECTS
Set PROJ_OPT_CALC_ACT_COSTS = 0
Where PROJ_ID = 3
To specify a percent complete contour for a task, copy, modify, and execute the following insert statement:
Insert into MSP_TIMEPHASED_DATA (
PROJ_ID,
TD_UID,
TD_FIELD_ID,
TD_CATEGORY,
TD_REF_UID,
TD_START,
TD_FINISH,
TD_UNITS,
TD_VALUE1,
TD_VALUE2,
TD_VALUE3,
TD_EXT_EDITED )
values (
3,
111,
11, -- task percent complete
0, -- category = task
3, -- TASK_UID
'11/8/1999 8:00:00 AM', -- start of the first time period
'11/11/1999 8:00:00 AM', -- end of the last time period
2, -- days,
72, –- day one value = 72 percent * 100,
0, -- day two value of zero percent * 100,
28, -- day three value = 28 percent * 100,
1 )
This example creates a record of 72% of the task completed on the day that begins on 11/8/1999 at 8:00:00 AM, 0% completed the next day, and 28% completed on the day that begins 11/10/1999.
To delete a contour in the MSP_TIMEPHASED_DATA table, update all rows for that contour setting all TD_VALUE columns to zero and TD_EXT_EDITED to true. Setting all TD_VALUE columns to zero for a single row will not cause the row to be deleted if there are one or more rows for the same contour for periods before and after the row being updated. Instead, a split will be created in the contour and the row will be retained.
Note: Microsoft Project won't actually delete the updated rows in MSP_TIMEPHASED_DATA until the project is saved.
Custom flag fields are stored in the database in six special tables, each based on a custom field type. To specify the value of a custom field, you must add a new row to the appropriate custom field table and set the corresponding flag in the MSP_PROJECTS table. To specify custom field values for a project summary task, the procedure is identical, but you must use the special TASK_UID of zero.
You must enter values in the following database fields to specify a custom text field:
Table | Fields | Notes |
---|---|---|
MSP_TEXT_FIELDS | PROJ_ID | This value must refer to a valid project in the MSP_PROJECTS table. |
TEXT_CATEGORY | The category is 0 for task fields, 1 for resource fields, or 3 for assignment fields. | |
TEXT_REF_UID | Depending on the category, specify the corresponding TASK_UID, RES_UID, or ASSN_UID value of the row for which the custom field is being set. | |
TEXT_FIELD_ID | The field identifier of the custom text field, which can be obtained from the CONV_VALUE column in the MSP_CONVERSIONS table. | |
TEXT_VALUE | The custom text value. | |
MSP_PROJECTS | PROJ_EXT_EDITED | Must be set to "True" for Microsoft Project to process. |
PROJ_EXT_EDITED_TEXT | Set this value to "True." |
You must enter values in the following database columns to specify a custom number field:
Table | Fields | Notes |
---|---|---|
MSP_NUMBER_FIELDS | PROJ_ID | This value must refer to a valid project in the MSP_PROJECTS table. |
NUM_CATEGORY | The category is 0 for task fields, 1 for resource fields, or 3 for assignment fields. | |
NUM_REF_UID | Depending on the category, specify the corresponding TASK_UID, RES_UID, or ASSN_UID value of the row for which the custom field is being set. | |
NUM_FIELD_ID | The field identifier of the custom number or cost field, which can be obtained from the CONV_VALUE column in the MSP_CONVERSIONS table. | |
NUM_VALUE | The custom number or cost value. | |
MSP_PROJECTS | PROJ_EXT_EDITED | Must be set to "True" for Microsoft Project to process. |
PROJ_EXT_EDITED_NUM | Set this value to "True." |
You must enter values in the following database columns to specify a custom date field:
Table | Columns | Notes |
---|---|---|
MSP_DATE_FIELDS | PROJ_ID | This value must refer to a valid project in the MSP_PROJECTS table. |
DATE_CATEGORY | The category is 0 for task fields, 1 for resource fields, or 3 for assignment fields. | |
DATE_REF_UID | Depending on the category, specify the corresponding TASK_UID, RES_UID, or ASSN_UID value of the row for which the custom field is being set. | |
DATE_FIELD_ID | The field identifier of the custom start, finish or date field, which can be obtained from the CONV_VALUE field in the MSP_CONVERSIONS table. | |
DATE_VALUE | The custom date/time value. | |
MSP_PROJECTS | PROJ_EXT_EDITED | Must be set to "True" for Microsoft Project to process. |
PROJ_EXT_EDITED_DATE | Set this value to "True." |
You must enter values in the following database columns to specify a custom duration field:
Table | Columns | Notes |
---|---|---|
MSP_DURATION_FIELDS | PROJ_ID | This value must refer to a valid project in the MSP_PROJECTS table. |
DUR_CATEGORY | The category is 0 for task fields, 1 for resource fields, or 3 for assignment fields. | |
DUR_REF_UID | Depending on the category, specify the corresponding TASK_UID, RES_UID, or ASSN_UID value of the row for which the custom field is being set. | |
DUR_FIELD_ID | The field identifier of the custom duration field, which can be obtained from the CONV_VALUE field in the MSP_CONVERSIONS table. | |
DUR_VALUE | The custom duration value. | |
DUR_FMT | The units to use when displaying this duration in Microsoft Project. | |
MSP_PROJECTS | PROJ_EXT_EDITED | Must be set to "True" for Microsoft Project to process. |
PROJ_EXT_EDITED_DUR | Set this value to "True." |
Example:
Let's say you want to enter the value 226 into the Number3 field for a particular resource that already exists in the database. Assume that the record for the resource in the MSP_RESOURCES table includes the following information:
PROJ_ID | RES_UID | RES_NAME |
---|---|---|
42 | 17 | Bob |
To set the Number3 field for this resource to 226, add the following record to the MSP_NUMBER_FIELDS table:
PROJ_ID | 42 |
NUM_CATEGORY | 1 |
NUM_REF_UID | 17 |
NUM_FIELD_ID | 205521010 |
NUM_VALUE | 226 |
The PROJ_ID must match the resource record. The NUM_CATEGORY is 1 in this case, because we are setting a resource custom field. The NUM_REF_UID is taken directly from the RES_UID field in the MSP_RESOURCES table. The NUM_FIELD_ID value is taken from the CONV_VALUE field in the MSP_CONVERSIONS table from the record where the CONV_STRING field equals "Resource Number 3." The NUM_VALUE field contains the actual data.
In order for Microsoft Project to read this record, you must set the PROJ_EXT_EDITED_NUM and PROJ_EXT_EDITED flags in the MSP_PROJECTS table to True.
While the majority of Microsoft Project fields have no extra properties beyond the actual data, some fields contain not only the data values but also have some project-level metadata about the field that applies to all values in the column. For example, the WBS field stores the actual WBS codes for each task, but there is also a mask for the codes that can be defined for each project. Similarly, all of the custom fields in Microsoft Project can have special attributes assigned, such as a formula or user-defined name (alias), and this metadata is stored just once for the whole column, separate from the actual data stored in each row.
Whenever this metadata is exposed in the database, it is stored in the MSP_FIELD_ATTRIBUTES and MSP_ATTRIBUTE_STRINGS tables. MSP_FIELD_ATTRIBUTES contains the fields IDs, the numeric value of the attribute (which can be de-referenced in the MSP_STRING_CONVERSIONS table), and either the actual attribute value or a pointer to the value. In the case of numeric and Boolean settings, the value is stored directly in MSP_FIELD_ATTRIBUTES, while string values are stored in the MSP_ATTRIBUTE_STRINGS table, and the index of the string is stored in MSP_FIELD_ATTRIBUTES.
Custom WBS codes are defined in two tables. The code mask is stored in the table MSP_ATTRIBUTE_STRINGS. The actual WBS code values for tasks are stored in the table MSP_TASKS. To define custom WBS codes, you must enter values for the following columns:
Table | Columns | Notes |
---|---|---|
MSP_ATTRIBUTE_STRINGS | PROJ_ID | This value must refer to a valid project in the MSP_PROJECTS table. |
AS_ID | This value identifies the WBS attribute string and must be unique within the project. | |
AS_VALUE | Each code is defined by three values: type, length and separator. All values are separated by commas. Types include:
0 = Numbers (orders) 1 = Uppercase Letters (ordered) 2 = Lowercase Letters (orders) 3 = Characters (unordered) |
|
MSP_PROJECTS | PROJ_EXT_EDITED | Must be set to "True" for Microsoft Project to process. |
To create the custom WBS mask required to produce the WBS code "A.b.01", copy, modify, and execute the following insert statement:
Insert into MSP_ATTRIBUTE_STRINGS (
PROJ_ID,
AS_ID,
AS_VALUE )
values (
3,
1,
'1,1,.,2,1,.,0,0,.' )
In this example, the value in the column AS_VALUE specifies three levels of WBS codes:
WBS Code
Specification |
WBS Code | Max WBS Code Length | WBS Code Separator Character |
---|---|---|---|
1,1,. | Uppercase Letters (ordered) | 1 | . (period) |
2,1,. | Lowercase Letters (ordered) | 1 | . (period) |
0,0,. | Numbers (ordered) | any | . (period) |
You can also specify a WBS code prefix such as "proj1-" resulting in the WBS code "proj1-A.b.01". To specify a WBS code prefix mask, copy, modify, and execute the following insert statement:
Insert into MSP_ATTRIBUTE_STRINGS (
PROJ_ID,
AS_ID,
AS_VALUE )
values (
3,
2,
'proj1-' )
In order to use the WBS prefix and codes specified above in tasks, two rows must be added to the table MSP_FIELD_ATTRIBUTES: one for the WBS prefix and one for the WBS code mask. Values must be provided for the following columns:
Table | Columns | Notes |
---|---|---|
MSP_FIELD_ATTRIBUTES | PROJ_ID | This value must refer to a valid project in the MSP_PROJECTS table. |
ATTRIB_FIELD_ID | Refers to the column TASK_WBS. See MSP_STRING_TYPES and MSP_CONVERSIONS for 'Field ID'. | |
ATTRIB_ID | Refers to the type of field attribute. See MSP_STRING_TYPES and MSP_CONVERSIONS for 'Field Attributes'. | |
ATTRIB_VALUE | Ignore this column if AS_ID refers to a row in MSP_ATTRIBUTE_STRINGS. Otherwise, this column specifies the value of the attribute. | |
AS_ID | Ignore if ATTRIB_VALUE is set to specify the value of an attribute. Otherwise, this column references a row in the MSP_ATTRIBUTE_STRINGS table. | |
ATTRIB_EXT_EDITED | Must be set to "True" for Microsoft Project to process. | |
MS_PROJECTS | PROJ_EXT_EDITED | Must be set to "True" for Microsoft Project to process. |
To create these rows, copy, modify, and execute the following insert statements:
Insert into MSP_FIELD_ATTRIBUTES (
PROJ_ID,
ATTRIB_FIELD_ID,
ATTRIB_ID,
ATTRIB_VALUE,
AS_ID,
ATTRIB_EXT_EDITED )
values (
3,
188743696, -- TASK_WBS field ID
200, -- for 'Outline code and WBS mask'
-1, -- the value is specified in MSP_ATTRIBUTE_STRINGS
1, -- the ID of the WBS code definition
1 ) –- must set for Microsoft Project to process
Insert into MSP_FIELD_ATTRIBUTES (
PROJ_ID,
ATTRIB_FIELD_ID,
ATTRIB_ID,
ATTRIB_VALUE,
AS_ID,
ATTRIB_EXT_EDITED )
values (
3,
188743696, -- TASK_WBS field ID
204, -- for 'WBS Prefix'
-1, -- indicates that the value is in MSP_ATTRIBUTE_STRINGS
2, -- the ID of the WBS code definition
1 ) –- must set for Microsoft Project to process
Once these two rows have been inserted, tasks can be given custom WBS codes. WBS codes specified for tasks should conform to the specified mask. To specify a WBS code for an existing task, copy, modify and execute the following update statement:
Update MSP_TASKS
Set TASK_WBS = 'A.b.03',
TASK_WBS_RIGHTMOST_LEVEL = '03'
Where PROJ_ID = 3
And TASK_UID = 11
When creating or modifying a project in the database, you can specify whether or not Microsoft Project enforces the uniqueness of WBS codes and the automatic generation of WBS codes in new tasks created using Microsoft Project. In order to specify whether or not Microsoft Project should enforce the uniqueness of new WBS codes in tasks, a row must be inserted into the table MSP_FIELD_ATTRIBUTES. To create this row, copy, modify, and execute the following insert statement:
Insert into MSP_FIELD_ATTRIBUTES (
PROJ_ID,
ATTRIB_FIELD_ID,
ATTRIB_ID,
ATTRIB_VALUE,
ATTRIB_EXT_EDITED )
values (
3,
188743696, -- TASK_WBS field ID
203, -- for 'Verify uniqueness of new WBS codes'
1, -- sets the flag to True
1 ) –- must set for Microsoft Project to process
Finally, to specify whether or not Microsoft Project generates WBS codes for new tasks, copy, modify, and execute the following insert statement:
Insert into MSP_FIELD_ATTRIBUTES (
PROJ_ID,
ATTRIB_FIELD_ID,
ATTRIB_ID,
ATTRIB_VALUE,
ATTRIB_EXT_EDITED )
values (
3,
188743696, -- TASK_WBS field ID
205, -- for 'Generate WBS code for new tasks'
1, -- sets the flag to True
1 ) –- must set for Microsoft Project to process
Custom outline codes are powerful and flexible tools for describing, organizing, and aggregating task information. Care must be taken when defining custom outline codes directly in the database. Custom outline codes are defined in the following four tables:
To create custom outline codes, you must enter values for at least the following columns:
Table | Columns | Notes |
---|---|---|
MSP_OUTLINE_CODES | PROJ_ID | This value must refer to a valid project in the MSP_PROJECTS table. |
CODE_UID | This value identifies the outline code and must be unique within the project. | |
OC_PARENT | The code UID of the parent code, if applicable. Set to zero for top level codes without parents. | |
OC_FIELD_ID | The ID of the task or resource custom outline code field. | |
OC_NAME | The code value. | |
MSP_PROJECTS | PROJ_EXT_EDITED | Must be set to "True" for Microsoft Project to process. |
Once a custom outline code mask has been defined, you can define the codes themselves. To specify two levels of codes that constitute the outline code "A.x", copy, modify, and execute the following insert statements:
Insert into MSP_OUTLINE_CODES (
PROJ_ID,
CODE_UID,
OC_PARENT,
OC_FIELD_ID,
OC_NAME )
values (
3,
1,
0, -- highest level code, no parent
188744096, -- Task Outline Code1
'A' ) –- the level 1 code
Insert into MSP_OUTLINE_CODES (
PROJ_ID,
CODE_UID,
OC_PARENT,
OC_FIELD_ID,
OC_NAME )
values (
3,
2,
1, -- parent code UID = 1
188744096, -- Task Outline Code1
'x' ) –- the level 1 code
To associate a code with a custom outline code and a specific task or resource, values must be entered for the following columns:
Table | Columns | Notes |
---|---|---|
MSP_CODE_FIELDS | PROJ_ID | This value must refer to a valid project in the MSP_PROJECTS table. |
CODE_CATEGORY | The category is 0 for task fields or 1 for resource fields. | |
CODE_REF_UID | Depending on the category, specify the corresponding TASK_UID or RES_UID value of the row for which the custom outline code is being set. | |
CODE_FIELD_ID | The ID of the task or resource custom outline code field. | |
CODE_UID | Refers to the lowest level code. The entire code is derived from the referenced code and all of its parent codes. |
To associate a custom outline code to a task, copy, modify, and execute the following insert statement:
Insert into MSP_CODE_FIELDS (
PROJ_ID,
CODE_CATEGORY,
CODE_REF_UID,
CODE_FIELD_ID,
CODE_UID )
values (
3, -- project ID
0, -- task category
2, -- task UID
188744096, -- Task Outline Code1
2 ) –- code UID for the code "x"
The result of inserting this row is that the value of the custom outline code field 1 for task 2 is "A.x".
Note: When deleting a custom outline code from MSP_OUTLINE_CODES, you should delete any child codes as well to avoid orphaned codes.
Aliases for custom fields are stored in the MSP_ATTRIBUTE_STRINGS and MSP_FIELD_ATTRIBUTES. As with custom WBS codes, the alias is specified in the table MSP_ATTRIBUTE_STRINGS. The required fields are:
Table | Columns | Notes |
---|---|---|
MSP_ATTRIBUTE_STRINGS | PROJ_ID | This value must refer to a valid project in the MSP_PROJECTS table. |
AS_ID | This value identifies the attribute string and must be unique within the project. | |
AS_VALUE | The alias for the custom field. |
The alias is related to a specific custom field in the MSP_FIELD_ATTRIBUTES table. Values must be provided for the following columns:
Table | Columns | Notes |
---|---|---|
MSP_FIELD_ATTRIBUTES | PROJ_ID | This value must refer to a valid project in the MSP_PROJECTS table. |
ATTRIB_FIELD_ID | Refers to a custom field. See MSP_STRING_TYPES and MSP_CONVERSIONS for 'Field ID'. | |
ATTRIB_ID | Refers to the type of field attribute 206 for 'Field Alias'. See MSP_STRING_TYPES and MSP_CONVERSIONS for 'Field Attributes'. | |
ATTRIB_VALUE | Must be set to -1. | |
AS_ID | Refers to the row in MSP_ATTRIBUTE_STRINGS in which the alias is specified. | |
ATTRIB_EXT_EDITED | Must be set to "True" for Microsoft Project to process. | |
MSP_PROJECTS | PROJ_EXT_EDITED | Must be set to "True" for Microsoft Project to process. |
To specify an alias for a custom field, copy, modify, and execute the following insert statements:
Insert into MSP_ATTRIBUTE_STRINGS (
PROJ_ID,
AS_ID,
AS_VALUE )
values (
3,
5,
'my text column' )
Insert into MSP_FIELD_ATTRIBUTES (
PROJ_ID,
ATTRIB_FIELD_ID,
ATTRIB_ID,
ATTRIB_VALUE,
AS_ID,
ATTRIB_EXT_EDITED )
values (
3,
188743731, -- refers to custom field 'Task Text1'
206, -- for 'Field Alias'
-1, -- indicates that the value is in MSP_ATTRIBUTE_STRINGS
5, -- the ID of the value in MSP_ATTRIBUTE_STRINGS
1 ) –- must set for Microsoft Project to process
As with custom WBS prefixes and masks, custom formulae are specified in the MSP_FIELD_ATTRIBUTES and MSP_ATTRIBUTE_STRINGS tables. To define a custom formula, you must enter values for the following columns:
Table | Columns | Notes |
---|---|---|
MSP_ATTRIBUTE_STRINGS | PROJ_ID | This value must refer to a valid project in the MSP_PROJECTS table. |
AS_ID | This value identifies the attribute string, which contains the formula and must be unique within the project. | |
AS_VALUE | The formula as expressed in the Formula dialog box in Microsoft Project. |
To create a custom formula that performs the calculation '(2*[Duration])/60', copy, modify, and execute the following insert statement:
Insert into MSP_ATTRIBUTE_STRINGS (
PROJ_ID,
AS_ID,
AS_VALUE )
values (
3,
5,
'(2*[Duration])/60' )
In order to map the custom formula to a custom field, values must be provided for the following columns in the MSP_FIELD_ATTRIBUTES table:
Table | Columns | Notes |
---|---|---|
MSP_FIELD_ATTRIBUTES | PROJ_ID | This value must refer to a valid project in the MSP_PROJECTS table. |
ATTRIB_FIELD_ID | Refers to the column that the formula affects. See MSP_STRING_TYPES and MSP_CONVERSIONS for 'Field ID'. | |
ATTRIB_ID | Refers to the type of field attribute. See MSP_STRING_TYPES and MSP_CONVERSIONS for 'Field Attributes'. | |
AS_ID | Refers to the attribute string that defines the formula. | |
ATTRIB_EXT_EDITED | Must be set to "True" for Microsoft Project to process. | |
MSP_PROJECTS | PROJ_EXT_EDITED | Must be set to "True" for Microsoft Project to process. |
To create a mapping between the custom formula and the Number1 custom field, copy, modify, and execute the following insert statement:
Insert into MSP_FIELD_ATTRIBUTES (
PROJ_ID,
ATTRIB_FIELD_ID,
ATTRIB_ID,
AS_ID,
ATTRIB_EXT_EDITED )
values (
3,
188743767, -- Number1 custom field
207, -- for 'Custom Field Formula'
5, -- the ID of the custom formula defintion
1 ) –- must set for Microsoft Project to process
To query the database for the existing formulae for custom field Number 3, copy, edit, and execute the following select statement:
Select PROJ_NAME,
(select CONV_STRING
from MSP_CONVERSIONS
where CONV_VALUE = fa.ATTRIB_FIELD_ID
and STRING_TYPE_ID = 105) as ATTRIB_FIELD –- field name
(select CONV_STRING
from MSP_CONVERSIONS
where CONV_VALUE = fa.ATTRIB_ID
and STRING_TYPE_ID = 106) as ATTRIB –- field attribute name
AS_VALUE
From MSP_FIELD_ATTRIBUTES fa,
MSP_ATTRIBUTE_STRINGS s,
MSP_PROJECTS p
Where fa.AS_ID = s.AS_ID
And fa.PROJ_ID = s.PROJ_ID
And fa.PROJ_ID = p.PROJ_ID
And fa.ATTRIB_FIELD_ID = 188743769 –- field = Number3
And p.PROJ_ID = 3
To delete a formula, update MSP_ATTRIBUTE_STRINGS setting AS_VALUE to null in the row that defines the formula. Remember to set ATTRIB_EXT_EDITED in the associated row in MSP_FIELD_ATTRIBUTES to true in order for Microsoft Project to process. The row will be deleted when the project is saved in Microsoft Project.
To retrieve data from the RTF notes columns in MSP_TASKS, MSP_RESOURCES or MSP_ASSIGNMENTS, copy, modify and execute the following VB script:
Sub getRtf()
'This macro extracts RTF data from MSP_TASKS.TASK_RTF_NOTES. This data can then be written
'to a file that can be opened with Microsoft Word or displayed in a richedit control.
Dim cn As New ADODB.Connection
Dim rs As New ADODB.Recordset
Dim sql As String, rtf As String, cnString
'Open the MSP_TASKS table to look for TASK_RTF_NOTES
cnString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\temp\MyProject.mpd"
sql = "select PROJ_ID, TASK_UID, TASK_RTF_NOTES " & _
"from MSP_TASKS " & _
"where TASK_RTF_NOTES is not null" 'can specify a specific PROJ_ID and TASK_UID instead
cn.Open cnString
rs.Open sql, cn
'Enumerate across the recordset looking for notes
With rs
Do While Not .EOF
rtf = StrConv(.Fields("TASK_RTF_NOTES"), vbUnicode) ' Put binary column data into text string
Debug.Print rtf
.MoveNext
Loop
.Close
End With
End Sub
To write data to the RTF notes columns in MSP_TASKS, MSP_RESOURCES or MSP_ASSIGNMENTS, copy, modify and execute the following VB script:
Sub writeRtf()
'This macro writes RTF data to MSP_TASKS.TASK_RTF_NOTES.
Dim cn As New ADODB.Connection
Dim cmd As New ADODB.Command
Dim param As New ADODB.Parameter
Dim sql As String, rtf As String, cnString As String
cnString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\temp\MyProject.mpd"
sql = "update MSP_TASKS set TASK_RTF_NOTES = ? where PROJ_ID = 1 and TASK_UID = 1"
rtf = "{\rtf1\ansi\ansicpg1252\deff0\deflang1033{\fonttbl{\f0\fswiss\fcharset0 Arial;}}" & vbNewLine & _
"\viewkind4\uc1\pard\f0\fs20 What's in a name? That which we call a rose... \par" & vbNewLine & _
"}" & vbNewLine & vbLf & Chr(0) 'be sure to specify valid RTF text here including "vbLf & Chr(0)
cn.Open cnString
param.Direction = adParamInput
param.Type = adVarBinary
param.Size = 8000
param.Value = StrConv(rtf, vbFromUnicode)
cmd.ActiveConnection = cn
cmd.CommandText = sql
cmd.Parameters.Append param
cmd.Execute
End Sub
The ADO 2.1 (or later) library must be referenced in the Microsoft Visual Basic editor for VBA for these scripts to run. Be sure to set the columns TASK_HAS_NOTES in MSP_TASKS and PROJ_EXT_EDITED in MSP_PROJECTS to true for Microsoft Project to process. The same applies when changing RTF notes in MSP_RESOURCES and MSP_ASSIGNMENTS.
Additional task and resource information that are stored in the MSP_TEXT_FIELDS table are listed below:
Task Fields | Resource Fields |
---|---|
Task Contact | Resource Code |
Cross-project link (project name, task ID) | Email Address |
Notes (non-RTF text) | Notes (non-RTF text) |
Task Subproject File | Resource Group |
WBS Max Value | Resource Windows User Account |
Task Hyperlink | Resource Hyperlink |
Task Hyperlink Address | Resource Hyperlink Address |
Task Hyperlink Subaddress | Resource Hyperlink Subaddress |
To specify a value for one of the fields above, you must specify the category (task or resource), the referenced UID of the specified category, the field type (for example, Task Contact) and the value. The values of field types are stored in the MSP_CONVERSIONS table where the string type is 105, or 'Field ID'.
The columns PROJ_EXT_EDITED and PROJ_EXT_EDITED_TEXT in the MSP_PROJECTS table must be set to true for Microsoft Project to process additional task, resource and assignment data stored in MSP_TEXT_FIELDS.
Note: Microsoft Project extracts the text into MSP_TEXT_FIELDS from TASK_RTF_NOTES, RES_RTF_NOTES and ASSN_RTF_NOTES as a convenience. These text notes are informational-only and cannot be updated.
To specify the e-mail address for a resource, copy, modify, and execute the following insert statement:
Insert into MSP_TEXT_FIELDS (
PROJ_ID,
TEXT_CATEGORY,
TEXT_REF_UID,
TEXT_FIELD_ID,
TEXT_VALUE )
values (
3,
1, -- the category is 'Resource'
6, -- the UID of the resource
205520931, -- the 'Resource Email Address' field
'somebody@microsoft.com' ) –- resource's email address
Cross-project links allow a task in one project to be dependent on a task in another project. Cross-project links are recorded in both the project which contains the dependent task and the project containing the task on which the other task depends. Cross-project links can only be created, modified or deleted using Microsoft Project. Creating, modifying or deleting cross-project link information in the database can cause the project to become corrupted. This includes normal tasks and ghost tasks (the copy of the successor or predecessor task in the other project), links, and the link references in MSP_TEXT_FIELDS.
To retrieve cross-project link project/task references for a specific task, copy modify and execute the following select statement:
Select PROJ_NAME,
TASK_NAME,
CONV_STRING,
TEXT_VALUE
From MSP_PROJECTS p,
MSP_TASKS t,
MSP_CONVERSIONS c,
MSP_LINKS l,
MSP_TEXT_FIELDS x
where p.PROJ_ID = 2 -- set the project ID
and t.TASK_UID = 1 -- set the task UID
and p.PROJ_ID = t.PROJ_ID
and p.PROJ_ID = l.PROJ_ID
and p.PROJ_ID = x.PROJ_ID
and l.LINK_UID = x.TEXT_REF_UID
and X.TEXT_CATEGORY = 4 -- Dependencies
and (
( t.TASK_ID = l.LINK_PRED_UID
and c.CONV_VALUE = x.TEXT_FIELD_ID
and c.STRING_TYPE_ID = 105 -- Field ID
)
or
( t.TASK_ID = l.LINK_SUCC_UID
and c.CONV_VALUE = x.TEXT_FIELD_ID
and c.STRING_TYPE_ID = 105 -- Field ID
)
)
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
Outlines, which consist of summary tasks with subtasks under them, are created in three steps:
To increment the task IDs of all tasks after the task 12 (which will become the summary task), copy, modify, and execute the following update statement:
Update MSP_TASKS
Set TASK_ID = TASK_ID + 1
Where TASK_ID > 12
Once room has been made in the list of task IDs, the subtask can be inserted. (See the topic Creating a new task above for information on creating and inserting new tasks.) When inserting a subtask, the outline level must be set to the outline level of the summary task + 1. To insert subtask 13, copy, edit, and execute the following insert statement:
Insert into MSP_TASKS (
PROJ_ID,
TASK_UID,
TASK_ID,
TASK_NAME,
TASK_OUTLINE_LEVEL,
EXT_EDIT_REF_DATA )
values (
3,
24, -- new, unique task UID
13, -- the ID of the new task
'your subtask name', -- the name of the task
3, -- outline level of task 12 + 1
'1' ) –- set to 1 so Microsoft Project will process
If task 12 was not a summary task previously, it must be marked as such. To specify a task as a summary task, copy, edit, and modify the following update statement:
Update MSP_TASKS
Set TASK_IS_SUMMARY = 1
Where TASK_IUD = 12
Be sure to update the column PROJ_EXT_EDITED in MSP_PROJECTS to 1 in order for Microsoft Project to process the changes.
If a resource is assigned to a summary task, and you want to edit the work on that assignment, you must specify values for both the ASSN_UNITS and ASSN_WORK columns in the table MSP_ASSIGNMENTS. To change the work on an existing summary task assignment to two days at 50%, copy, modify, and execute the following update statement:
Update MSP_ASSIGNMENTS
Set ASSN_WORK = 96000,
ASSN_UNITS = .50
Where TASK_ID > 12
Be sure to back up the task data before updating row. See the topic Ensuring data integrity for information on backup up existing task data.
The task and resource workgroup message status fields (Updated Needed, Confirmed, Response Pending and Team Status Pending) are not saved out to the database. If you want to obtain status information about messages that have been sent out to the team, you have to extract the information from the assignment fields. By looking at all of the associated assignment records for a given task or resource, the values of the task or resource can be determined.
For example, to determine whether a task has been confirmed, you must check the column ASSN_IS_CONFIRMED in the table MSP_ASSIGNMENTS for all assignments against the task. If ASSN_IS_CONFIRMED is true for every assignment then Confirmed for the task is true. To determine whether a task is confirmed, copy, modify, and execute the following select statement:
Select count(*)
From MSP_ASSIGNMENTS
Where TASK_IUD = 12
And ASSN_IS_CONFIRMED = 0
If the query returns a number greater than zero, the task is not confirmed.
To determine if an update is needed on a task, you must check the column ASSN_UPDATE_NEEDED in MSP_ASSIGNMENTS for all of the assignments against the task. If ASSN_UPDATED_NEEDED is true for one or more of the assignments, then Update Needed for the task is true. To determine whether a task is confirmed, copy, modify, and execute the following select statement:
Select count(*)
From MSP_ASSIGNMENTS
Where TASK_IUD = 12
And ASSN_UPDATE_NEEDED = 1
If the query returns a number greater than zero, the task needs to be updated.
To specify a task hyperlink address, copy, modify, and execute the following insert statement:
Insert into MSP_TEXT_FIELDS (
PROJ_ID,
TEXT_CATEGORY,
TEXT_REF_UID,
TEXT_FIELD_ID,
TEXT_VALUE )
values (
3,
0, -- task category
13, -- the UID of the task
188743898, -- 'Task Hyperlink Address'
'http://myserver/default.htm' ) -- task hyperlink address
For Microsoft Project to process, you must set PROJ_EXT_EDITED and PROJ_EXT_EDITED_TEXT in MSP_PROJECTS to true for the updated project.
Note: Hyperlinks, hyperlink addresses and hyperlink subaddresses can be specified for assignments as well as tasks and resources.
The processing order for Microsoft Project 2000 enforces the sequence in which the data is applied. For example, looking at the first list below, Actual Work values are always committed before Remaining Work values. This does not mean that Actual Work always overrides Remaining Work, however.
The task and assignment field processing order lists below contain just the fields that have interdependencies (though not all fields in each list are interdependent with all others). The ordering in the list determines the order in which the data is processed, just as if it was typed into the user interface (UI) in that order. Without this ordering, externally changing more than one of the fields could result in an ambiguous situation. On the other hand, if all the interdependent values are externally changed to repair all inconsistencies, then the processing order does not matter.
While the data can be entered into the UI in any editable field in any order, the processing order list is hard-coded, so a sequence that's possible in the UI may not be possible for data being read in. Rather than trying to follow a data entry sequence in the UI and then mimic it with imported data, however, it is better to determine what data should be changed externally and then test the changes in the UI (based on the processing order list) to check for the desired outcome. In other words, for every possible external change you might make, you can readily test the result in the UI, but every possible change you can make in the UI cannot be replicated with external modifications.
When Microsoft Project reloads a complete project that has been flagged as externally edited, it first restores the project back to its pre-edited state and then applies the edits in the processing order. On an import operation, Microsoft Project doesn't have the original values, so default tasks are created and then the updated values are applied according to the processing order.
Notes
Task fields:
Assignment fields:
Table Name | Column Prefix |
---|---|
MSP_PROJECTS | PROJ_ |
MSP_TASKS | TASK_ |
MSP_RESOURCES | RES_ |
MSP_ASSIGNMENTS | ASSN_ |
MSP_LINKS | LINK_ |
MSP_TIMEPHASED_DATA | TD_ |
MSP_CALENDARS | CAL_ |
MSP_CALENDAR_DATA | CD_ |
MSP_RESOURCE_RATES | RR_ |
MSP_AVAILABILITY | AVAIL_ |
MSP_DATE_FIELDS | DATE_ |
MSP_DURATION_FIELDS | DUR_ |
MSP_FLAG_FIELDS | FLAG_ |
MSP_NUMBER_FIELDS | NUM_ |
MSP_TEXT_FIELDS | TEXT_ |
MSP_CODE_FIELDS | CODE_ |
MSP_OUTLINE_CODES | OC_ |
MSP_FIELD_ATTRIBUTES | ATTRIB_ |
MSP_ATTRIBUTE_STRINGS | AS_ |
MSP_STRING_TYPES | STRING_ |
MSP_CONVERSIONS | CONV_ |
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 |
In order to keep the word components of column names short but consistent, the following abbreviations are used when naming columns:
Abbreviation | Word | Abbreviation | Word |
---|---|---|---|
ACT | Actual | OVT | Overtime |
AVAIL | Available | PCT | Percent |
BASE | Baseline | PRED | Predecessor |
CAL | Calendar | PROJ | Project |
COMP | Complete | REF | Reference |
DEF | Default | REG | Regular |
DUR | Duration | REM | Remaining |
EST | Estimated | RES | Resource |
EXT | Externally | SCHED | Schedule |
FMT | Format | STD | Standard |
LANG | Language | SUCC | Successor |
MAX | Maximum | UID | Unique ID |
NUM | Number | VAR | Variance |
Some fields contain data that is written by Microsoft Project upon exporting to a database but is not read while importing project data into Microsoft Project. Usually, this data is the result of calculations or settings in Microsoft Project. If you edit this data directly in the database, the changes will not be reflected when you import the data in Microsoft Project. These fields are indicated as "Info. Only " in the table descriptions.
The following table definitions describe the database structure that is created when you save a whole project to a database. The fields that comprise the key for each table are indicated in the Key column by number (ordinal position of the columns within the key).
The following field types are used in the Microsoft Project database structure. The field type for each field is indicated in the tables below.
Field Type | Description |
---|---|
bit | Boolean where supported, Integer in database where Boolean is not supported |
char | Character |
datetime | Date including time |
decimal(n,n) | Decimal (Number in Oracle) |
float | Floating point number (Number in Oracle) |
image | Binary data greater than 255 bytes (data-type Blob in Oracle) |
integer | Integer (Number in Oracle) |
long | Long integer (Number in Oracle) |
smallint | Small integer (Number in Oracle) |
text | Longest text field available (8000 bytes in SQL Server, 64k in Microsoft Access, 255 characters in Oracle) |
varchar(n) | Variable-length character text unless otherwise indicated (Varchar2(n) in Oracle) |
Where Microsoft Project needs to store a time value, but not a date value, the time value is stored as a date. When you read the project from the database, the date component in these fields is ignored.
Key | Column Name | Data Type | Info. Only | Description |
---|---|---|---|---|
1 | PROJ_ID | Integer | The unique ID for the project | |
PROJ_NAME | Varchar (255) | The project name. Together with the data source name can't exceed 255 characters. | ||
PROJ_PROP_AUTHOR | Text | The author of the project | ||
PROJ_OPT_CALC_ACT_COSTS | Bit | Automatically calculate actual costs. | ||
PROJ_PROP_CATEGORY | Text | Project category. | ||
PROJ_PROP_COMPANY | Text | Company of the project. | ||
PROJ_OPT_CRITICAL_SLACK_LIMIT | Integer | Maximum number of slack days to be critical. | ||
PROJ_OPT_CURRENCY_DIGITS | Integer | Number of digits after the decimal (currency). | ||
PROJ_OPT_CURRENCY_POSITION | Integer | Position of the currency symbol | ||
PROJ_OPT_CURRENCY_SYMBOL | Varchar (20) | Currency symbol used in the project. | ||
PROJ_OPT_NEW_ARE_EFFORT_DRIVEN | Bit | New tasks are effort-driven by default. | ||
PROJ_INFO_CURRENT_DATE | Datetime | X | Current date of the project. | |
PROJ_OPT_DEF_FINISH_TIME | Datetime | Default finish time of new projects. | ||
PROJ_OPT_DEF_FIX_COST_ACCRUAL | Smallint | Fixed costs are accrued by default. | ||
PROJ_OPT_MINUTES_PER_DAY | Integer | Default number of minutes per day. | ||
PROJ_OPT_MINUTES_PER_WEEK | Integer | Default number of minutes per week. | ||
PROJ_OPT_DEF_OVT_RATE | Decimal (25,6) | Default overtime rate for new resources. | ||
PROJ_OPT_DEF_STD_RATE | Decimal (25,6) | Default standard rate for new resources. | ||
PROJ_OPT_DEF_START_TIME | Datetime | Default finish time of new tasks. | ||
PROJ_OPT_DEF_TASK_TYPE | Smallint | Default task type (0=material, 1=work) | ||
PROJ_OPT_DUR_ENTRY_FMT | Smallint | Default duration format. | ||
PROJ_INFO_FINISH_DATE | Datetime | Project finish date. | ||
PROJ_OPT_HONOR_CONSTRAINTS | Bit | Whether tasks honor their constraint dates. | ||
PROJ_PROP_KEYWORDS | Text | Keywords for this project. | ||
PROJ_LAST_SAVED | Datetime | Last date the project was saved. | ||
PROJ_PROP_MANAGER | Text | Manager of the project. | ||
PROJ_OPT_MULT_CRITICAL_PATHS | Bit | Whether multiple critical paths are calculated. | ||
PROJ_CREATION_DATE | Datetime | Date the project was created. | ||
PROJ_POOL_ATTACHED_TO | Text | X | Resource pool file name. | |
PROJ_INFO_CAL_NAME | Text | Name of the project calendar. | ||
PROJ_IS_RES_POOL | Bit | Whether the project is a resource pool or sharer of a resource pool. | ||
PROJ_INFO_SCHED_FROM | Smallint | Whether the project is scheduled from a start data or finish date. | ||
PROJ_OPT_SPLIT_IN_PROGRESS | Bit | Whether in-progress tasks may be split. | ||
PROJ_OPT_SPREAD_ACT_COSTS | Bit | Whether actual costs are spread to the status date. | ||
PROJ_OPT_SPREAD_PCT_COMP | Bit | Whether percent complete is spread to the status date. | ||
PROJ_INFO_START_DATE | Datetime | Project start date. | ||
PROJ_INFO_STATUS_DATE | Datetime | Project status date. | ||
PROJ_PROP_SUBJECT | Text | Subject of the project. | ||
PROJ_PROP_TITLE | Text | Subject of the project. | ||
PROJ_OPT_TASK_UPDATES_RES | Bit | Whether updates to task update resources. | ||
PROJ_OPT_WORK_ENTRY_FMT | Smallint | Default work unit. | ||
PROJ_OPT_CALC_SUB_AS_SUMMARY | Bit | Whether to calculate subtasks as summary tasks. | ||
PROJ_OPT_WEEK_START_DAY | Smallint | First day of the week. | ||
PROJ_OPT_FY_START_MONTH | Smallint | First month of the year. | ||
PROJ_OPT_FY_USE_START_YR | Bit | Whether to use fiscal starting year. | ||
PROJ_OPT_DAYS_PER_MONTH | Smallint | Default number of days per month. | ||
PROJ_OPT_NEW_TASK_EST | Bit | Whether new tasks are estimated by default. | ||
PROJ_OPT_SHOW_EST_DUR | Bit | Whether to show the estimated duration by default. | ||
PROJ_OPT_EXPAND_TIMEPHASED | Bit | Whether to save expanded time-phasedtimephased data to the MSP_TIMEPHASED database table. | ||
PROJ_EXT_EDITED_DATE | Bit | Set to indicate that a custom date field was edited externally. | ||
PROJ_EXT_EDITED_DUR | Bit | Set to indicate that a custom duration field was edited externally. | ||
PROJ_EXT_EDITED_NUM | Bit | Set to indicate that a custom number field was edited externally. | ||
PROJ_EXT_EDITED_FLAG | Bit | Set to indicate that a custom flag field was edited externally. | ||
PROJ_EXT_EDITED_CODE | Bit | Set to indicate that an outline code field was edited externally. | ||
PROJ_EXT_EDITED_TEXT | Bit | Set to indicate that a custom text field was edited externally. | ||
PROJ_EXT_EDITED | Bit | Set to indicate that the project was edited externally. | ||
PROJ_DATA_SOURCE | Varchar (128) | X | Data source name for the project. Used by Microsoft Project when checking for concurrent usage. | |
PROJ_READ_ONLY | Varchar (10) | X | Indicates whether the project is read-only. | |
PROJ_READ_WRITE | Varchar (4) | X | Indicates whether the project is open for write. | |
PROJ_READ_COUNT | Varchar (10) | X | Indicates how many users have one or more tables open read-only. | |
PROJ_LOCKED | Varchar (4) | X | Indicates whether a user is writing to a table. | |
PROJ_MACHINE_ID | Varchar (100) | The machine ID of the user who has opened the project. | ||
RESERVED_BINARY_DATA | Image | X | Reserved for use by Microsoft Project. Do not change. |
This table contains task data. Microsoft Project will write three rows to this table with unique IDs of zero (project-level outline), -65536, -65535 and -65534. Microsoft Project uses these rows internally. You should not edit or delete these rows.
Key | Column Name | Data Type | Info. Only | Description |
---|---|---|---|---|
RESERVED_DATA | char | X | Set to indicate whether the table is locked. | |
1 | PROJ_ID | int | The unique ID of the project. | |
TASK_ACWP | decimal | X | Actual cost of work performed. | |
TASK_BCWP | decimal | X | Budgeted cost of work performed. | |
TASK_BCWS | decimal | X | Budgeted cost of work scheduled. | |
TASK_DUR_VAR | integer | X | Duration variance. | |
TASK_FINISH_VAR | integer | X | Finish variance. | |
TASK_OUTLINE_NUM | varchar | X | Outline level. | |
TASK_START_VAR | integer | X | Start variance. | |
TASK_IS_OVERALLOCATED | bit | X | Indicates whether task is overallocated. | |
TASK_OVT_WORK | decimal | X | Amount of overtime work. | |
TASK_VAC | decimal | Amount of vacation. | ||
TASK_REG_WORK | decimal | X | Amount of regular work. | |
TASK_NUM_OBJECTS | integer | X | Number of objects in the task notes. | |
TASK_TOTAL_SLACK | integer | Amount of total slack. | ||
EXT_EDIT_REF_DATA | text | Set to indicate a new row or backup existing data when changing project information in the database. | ||
2 | TASK_UID | integer | Unique ID for the task within the project. | |
TASK_ID | integer | Task ID. | ||
TASK_HAS_LINKED_FIELDS | bit | Indicates whether there are data links to the task. | ||
TASK_IS_MILESTONE | bit | Indicates whether the task is a milestone. | ||
TASK_IS_CRITICAL | bit | Indicates whether the task is critical. | ||
TASK_IS_SUMMARY | bit | Indicates whether the task is a summary task. | ||
TASK_IS_SUBPROJ | bit | Indicates whether the task is an inserted project. | ||
TASK_IS_MARKED | bit | Indicates whether the task is marked. | ||
TASK_IGNORES_RES_CAL | bit | Indicates whether the resource calendar is ignored. | ||
TASK_IS_ROLLED_UP | bit | Indicates whether the task is rolled up. | ||
TASK_IS_FROM_FINISH_SUBPROJ | bit | Indicates whether the inserted project is marked as schedule-from-finish. | ||
TASK_BAR_IS_HIDDEN | bit | Indicates whether the Gantt bars are hidden when displayed in Microsoft Project. | ||
TASK_IS_RECURRING | bit | Indicates whether the task is recurring. | ||
TASK_IS_RECURRING_SUMMARY | bit | Indicates whether the task is a summary of recurring tasks. | ||
TASK_IS_EXTERNAL | bit | X | Indicates whether this task is external and cross-project linked to a task in this project. | |
TASK_IS_EFFORT_DRIVEN | bit | Indicates whether the task is effort-driven. | ||
TASK_IS_COLLAPSED | bit | Indicates whether the summary task is collapsed when displayed in Microsoft Project. | ||
TASK_HAS_NOTES | bit | Indicates whether the task has RTF notes. | ||
TASK_IS_READONLY_SUBPROJ | bit | Indicates whether the inserted project is read-only. | ||
TASK_LEVELING_CAN_SPLIT | bit | Indicates whether leveling can cause a task split. | ||
TASK_LEVELING_ADJUSTS_ASSN | bit | Indicates whether leveling can adjust assignments. | ||
TASK_DUR_IS_EST | bit | X | Indicates whether task duration is estimated. | |
TASK_EARLY_FINISH | datetime | Early finish date. | ||
TASK_LATE_START | datetime | Late start date. | ||
TASK_STOP_DATE | datetime | Stop date. | ||
TASK_RESUME_DATE | datetime | X | Resume date. | |
TASK_FREE_SLACK | integer | Amount of free slack. | ||
TASK_OUTLINE_LEVEL | smallint | Outline level. | ||
TASK_DUR | integer | Planned duration of the task. | ||
TASK_DUR_FMT | smallint | Units in which the duration is displayed in Microsoft Project. | ||
TASK_ACT_DUR | integer | Actual duration of the task. | ||
TASK_REM_DUR | integer | Remaining duration of the task. | ||
TASK_BASE_DUR | integer | Baseline duration of the task. | ||
TASK_BASE_DUR_FMT | smallint | Units in which the baseline duration is displayed in Microsoft Project. | ||
TASK_CONSTRAINT_TYPE | smallint | Constraint type. | ||
TASK_LEVELING_DELAY | integer | Delay caused by leveling. | ||
TASK_LEVELING_DELAY_FMT | smallint | Units in which the leveling delay is displayed in Microsoft Project. | ||
TASK_START_DATE | datetime | Scheduled start date. | ||
TASK_FINISH_DATE | datetime | Scheduled finish date. | ||
TASK_ACT_START | datetime | Actual start date. | ||
TASK_ACT_FINISH | datetime | Actual finish date. | ||
TASK_BASE_START | datetime | Baseline start date. | ||
TASK_BASE_FINISH | datetime | Baseline finish date. | ||
TASK_CONSTRAINT_DATE | datetime | Constraint date. | ||
TASK_PRIORITY | smallint | Priority of the task. | ||
TASK_PCT_COMP | smallint | Percent complete. | ||
TASK_PCT_WORK_COMP | smallint | Percent of work complete | ||
TASK_TYPE | smallint | Task type. | ||
TASK_FIXED_COST_ACCRUAL | smallint | The method used for accruing fixed costs. | ||
TASK_CREATION_DATE | datetime | The date the task was created. | ||
TASK_PRELEVELED_START | datetime | Start date before the task was leveled. | ||
TASK_PRELEVELED_FINISH | datetime | Finish date before the task was leveled. | ||
TASK_EARLY_START | datetime | Early start date. | ||
TASK_LATE_FINISH | datetime | Late finish date. | ||
TASK_CAL_UID | integer | Task calendar. | ||
TASK_DEADLINE | datetime | Task schedule deadline date. | ||
TASK_WORK | decimal | Amount of scheduled work. | ||
TASK_BASE_WORK | decimal | Amount of baseline work. | ||
TASK_ACT_WORK | decimal | Amount of actual work. | ||
TASK_REM_WORK | decimal | Amount of remaining work. | ||
TASK_COST | decimal | Scheduled cost. | ||
TASK_FIXED_COST | decimal | Fixed cost of the task. | ||
TASK_ACT_COST | decimal | Actual cost | ||
TASK_REM_COST | decimal | X | Remaining cost. | |
TASK_BASE_COST | decimal | Baseline cost. | ||
TASK_ACT_OVT_WORK | decimal | Amount of actual overtime work. | ||
TASK_REM_OVT_WORK | decimal | Amount of remaining overtime work. | ||
TASK_OVT_COST | decimal | Scheduled overtime cost. | ||
TASK_ACT_OVT_COST | decimal | Actual overtime cost. | ||
TASK_REM_OVT_COST | decimal | Remaining overtime cost. | ||
TASK_WBS | text | WBS number. | ||
TASK_NAME | varchar | Task name. | ||
TASK_WBS_RIGHTMOST_LEVEL | text | The WBS level of the task. | ||
TASK_RTF_NOTES | image | Rich-text format notes. |
This table contains resource data. Microsoft Project will write three rows to this table with unique IDs of zero (null resource required for task zero), -65536, -65535 and -65534. Microsoft Project uses these rows internally. You should not edit or delete these rows.
Key | Column Name | Data Type | Info. Only | Description |
---|---|---|---|---|
RESERVED_DATA | char | |||
1 | PROJ_ID | integer | The unique ID for the project. | |
RES_ACWP | decimal | X | Actual cost of work performed. | |
RES_BCWP | decimal | X | Budgeted cost of work performed. | |
RES_BCWS | decimal | X | Budgeted cost of work scheduled. | |
RES_NUM_OBJECTS | integer | X | The number of objects attached to the resource, not including those in notes. | |
EXT_EDIT_REF_DATA | text | Set to indicate a new row or backup existing data when changing project information in the database. | ||
2 | RES_UID | integer | The unique ID for the resource in the project. | |
RES_ID | integer | The resource ID | ||
RES_HAS_LINKED_FIELDS | bit | Whether there are data links to the resource. | ||
RES_IS_OVERALLOCATED | bit | Whether the resource is overallocated. | ||
RES_TYPE | bit | The type of resource. 0=material, 1=work. | ||
RES_HAS_NOTES | bit | Indicates whether the resource has RTF notes. | ||
RES_CAN_LEVEL | bit | Whether the resource can be leveled. | ||
RES_STD_RATE_FMT | smallint | The units in which the standard rate is displayed in Microsoft Project. | ||
RES_OVT_RATE_FMT | smallint | The units in which the overtime rate is displayed in Microsoft Project. | ||
RES_ACCRUE_AT | smallint | The way resource costs are accrued. | ||
RES_WORKGROUP_MESSAGING | smallint | How the resource receives workgroup messages. | ||
RES_CAL_UID | integer | The unique ID for the resource calendar. | ||
RES_AVAIL_FROM | datetime | X | The first date from which the resource is available. This date is derived from the earliest date value in the column AVAIL_FROM in the table MSP_AVAILABILITY for the specified resource. This column should not be updated. | |
RES_AVAIL_TO | datetime | X | The last date for which the resource is available. This date is derived from the latest date value in the column AVAIL_TO in the table MSP_AVAILABILITY for the specified resource. This column should not be updated. | |
RES_STD_RATE | decimal | The standard rate as of the current date. | ||
RES_OVT_RATE | decimal | The overtime rate as of the current date. | ||
RES_MAX_UNITS | decimal | The maximum number of units available for the resource. | ||
RES_WORK | decimal | The total work scheduled for a resource across all tasks assigned to the resource. | ||
RES_ACT_WORK | decimal | The actual amount of work. | ||
RES_BASE_WORK | decimal | The baseline work. | ||
RES_OVT_WORK | decimal | The amount of overtime work. | ||
RES_COST_PER_USE | decimal | The cost per use as of the current date. | ||
RES_REM_WORK | decimal | The amount of remaining work. | ||
RES_REG_WORK | decimal | The amount of regular work. | ||
RES_ACT_OVT_WORK | decimal | The actual overtime work. | ||
RES_REM_OVT_WORK | decimal | The remaining overtime work. | ||
RES_PEAK | decimal | X | The greatest number of resource units assigned at any time. | |
RES_ACT_COST | decimal | The actual cost. | ||
RES_COST | decimal | The cost. | ||
RES_BASE_COST | decimal | The baseline cost. | ||
RES_REM_COST | decimal | The remaining cost. | ||
RES_OVT_COST | decimal | The overtime cost. | ||
RES_ACT_OVT_COST | decimal | The actual overtime cost. | ||
RES_REM_OVT_COST | decimal | The remaining overtime cost. | ||
RES_NAME | varchar | The resource's name. | ||
RES_INITIALS | varchar | The resource's initials. | ||
RES_PHONETICS | text | In some languages, the phonetic spelling of the resource's name. | ||
RES_MATERIAL_LABEL | text | The unit of measurement applied to a material resource. | ||
RES_RTF_NOTES | image | Rich-text format notes. |
This table contains assignment data. In addition to rows for tasks with assigned resources, Microsoft Project will write rows for tasks without assigned resources (null assignments).
Key | Column Name | Data Type | Info. Only | Description |
---|---|---|---|---|
RESERVED_DATA | char | |||
1 | PROJ_ID | integer | The unique ID of the project. | |
ASSN_ACT_FINISH | datetime | The actual finish date of the assignment. | ||
ASSN_ACT_START | datetime | The actual start date. | ||
ASSN_ACWP | decimal | X | Actual cost of work performed. | |
ASSN_BCWP | decimal | X | Budgeted cost of work performed. | |
ASSN_BCWS | decimal | X | Budgeted cost of work scheduled. | |
ASSN_RES_TYPE | bit | The type of resource. 0=material, 1=work. | ||
ASSN_IS_OVERALLOCATED | bit | X | Whether the assignment is overallocated. | |
ASSN_WORK_CONTOUR | smallint | X | The type of assignment work. | |
ASSN_START_VAR | integer | X | The start variance. | |
ASSN_FINISH_VAR | integer | X | The finish variance. | |
ASSN_UPDATE_NEEDED | bit | X | Whether a workgroup update is needed. | |
EXT_EDIT_REF_DATA | text | Set to indicate a new row or backup existing data when changing project information in the database. | ||
2 | ASSN_UID | integer | The unique ID of the assignment in the project. | |
ASSN_HAS_LINKED_FIELDS | bit | Whether there are data links to the assignment. | ||
ASSN_IS_CONFIRMED | bit | Whether the assignment is confirmed. | ||
ASSN_RESPONSE_PENDING | bit | Whether a response to a TeamAssign message is pending from the assigned resource. | ||
ASSN_HAS_NOTES | bit | Indicates whether the assignment has RTF notes. | ||
ASSN_TEAM_STATUS_PENDING | bit | Whether responses are pending to an TeamStatus message. | ||
TASK_UID | integer | The unique ID of the task in the assignment. This value must be a valid TASK_UID in the MSP_TASKS table. | ||
RES_UID | integer | The unique ID of the resource in assignment. This value must be a valid RES_UID in the MSP_RESOURCES table. | ||
ASSN_START_DATE | datetime | The start date of the assignment. | ||
ASSN_FINISH_DATE | datetime | The end date of the assignment. | ||
ASSN_DELAY | integer | The delay for the assignment. | ||
ASSN_DELAY_FMT | smallint | The units in which the delay is displayed in Microsoft Project. | ||
ASSN_LEVELING_DELAY | integer | The amount of time the assignment is delayed due to leveling. | ||
ASSN_COST_RATE_TABLE | smallint | The cost rate table used for this assignment. | ||
ASSN_BASE_START | datetime | The baseline start date. | ||
ASSN_BASE_FINISH | datetime | The baseline finish date. | ||
ASSN_MATERIAL_RATE_FMT | smallint | The units in which the material rate is displayed in Microsoft Project. See "Cost Rate Data Units" in MSP_STRING_TYPES for rate formats. | ||
ASSN_UNITS | decimal | The number of units assigned. | ||
ASSN_WORK | decimal | The amount of work that the resource is scheduled to perform on the assigned task. | ||
ASSN_ACT_WORK | decimal | The actual amount of work. | ||
ASSN_REG_WORK | decimal | The amount of regular work. | ||
ASSN_REM_WORK | decimal | The remaining work. | ||
ASSN_BASE_WORK | decimal | The baseline work. | ||
ASSN_COST | decimal | X | The cost of the assignment. | |
ASSN_ACT_COST | decimal | X | The actual cost of the assignment. | |
ASSN_REM_COST | decimal | X | The remaining cost. | |
ASSN_BASE_COST | decimal | The baseline cost | ||
ASSN_BASE_COST_PER_USE | decimal | The baseline cost per use. | ||
ASSN_OVT_WORK | decimal | The amount of overtime work. | ||
ASSN_ACT_OVT_WORK | decimal | The actual amount of overtime work. | ||
ASSN_REM_OVT_WORK | decimal | The remaining overtime work. | ||
ASSN_ACT_OVT_COST | decimal | X | The actual overtime cost. | |
ASSN_REM_OVT_COST | decimal | X | The remaining overtime cost. | |
ASSN_RTF_NOTES | image | Rich Text Format notes. |
This table contains data about resource availability.
Key | Column Name | Data Type | Info. Only | Description |
---|---|---|---|---|
RESERVED_DATA | char | |||
1 | PROJ_ID | integer | The unique ID of the project. | |
2 | AVAIL_UID | integer | The unique ID of the availability record. | |
RES_UID | integer | The unique ID of the resource in assignment. This value must be a valid RES_UID in the MSP_RESOURCES table. | ||
AVAIL_FROM | datetime | The date that the resource becomes available for the period defined. | ||
AVAIL_TO | datetime | The date the the resource is no longer available for the period defined. | ||
AVAIL_UNITS | decimal | The percent that the resource is available during the period defined. |
This table contains basic calendar data. Microsoft Project will write three rows to this table with unique IDs of 1, 2, -65536, -65535, -65534 and -65533. Microsoft Project uses these rows internally. You should not edit or delete these rows.
Key | Column Name | Data Type | Info. Only | Description |
---|---|---|---|---|
RESERVED_DATA | char | |||
1 | PROJ_ID | integer | The unique ID for the project. | |
2 | CAL_UID | integer | The unique ID for the calendar in the project. | |
CAL_BASE_UID | integer | The unique ID of the base calendar if this is a resource calendar. | ||
RES_UID | integer | The unique ID of the resource to whom the calendar is assigned. | ||
CAL_IS_BASE_CAL | bit | Whether this is a base calendar. | ||
CAL_NAME | varchar | The calendar name, not filled in if it is a resource calendar. |
This table contains calendar working day and exception data.
Key | Column Name | Data Type | Info. Only | Description |
---|---|---|---|---|
RESERVED_DATA | char | |||
1 | PROJ_ID | integer | The unique ID for the project. | |
2 | CD_UID | integer | The unique ID for this exception. | |
CAL_UID | integer | The unique ID for the calendar in the project. | ||
CD_DAY_OR_EXCEPTION | smallint | Specifies whether the day is a standard working day or an exception:
0 = exception 1-7 = day of week |
||
CD_WORKING | smallint | Whether the date range is working or nonworking. A value of 0 is nonworking and 1 is working. | ||
CD_FROM_DATE | datetime | Date the exception starts. | ||
CD_TO_DATE | datetime | Date the exception finishes. | ||
CD_FROM_TIME1 | datetime | Start time of the first shift. | ||
CD_TO_TIME1 | datetime | Finish time of the first shift. | ||
CD_FROM_TIME2 | datetime | Start time of the second shift. | ||
CD_TO_TIME2 | datetime | Finish time of the second shift. | ||
CD_FROM_TIME3 | datetime | Start time of the third shift. | ||
CD_TO_TIME3 | datetime | Finish time of the third shift. | ||
CD_FROM_TIME4 | datetime | Start time of the fourth shift. | ||
CD_TO_TIME4 | datetime | Finish time of the fourth shift. | ||
CD_FROM_TIME5 | datetime | Start time of the fifth shift. | ||
CD_TO_TIME5 | datetime | Finish time of the fifth shift. |
This table contains data about task dependencies.
Key | Column Name | Data Type | Info. Only | Description |
---|---|---|---|---|
RESERVED_DATA | char | |||
1 | PROJ_ID | integer | The unique ID of the project. | |
EXT_EDIT_REF_DATA | text | Set to indicate a new row or backup existing data when changing project information in the database. | ||
2 | LINK_UID | integer | The unique ID of the dependency in the project. | |
LINK_IS_CROSS_PROJ | bit | X | Whether this is a cross-project link. The external task is stored in the MSP_TASKS table. | |
LINK_PRED_UID | integer | The unique ID of the predecessor task. | ||
LINK_SUCC_UID | integer | The unique ID of the successor task. | ||
LINK_TYPE | smallint | The link type: Finish to Start, Finish to Finish, Start to Start, or Start to Finish. | ||
LINK_LAG_FMT | smallint | The units in which the lag is displayed in Microsoft Project. | ||
LINK_LAG | integer | The amount of lag. |
This table contains work, actual work and cost data over time.
Key | Column Name | Data Type | Info. Only | Description |
---|---|---|---|---|
RESERVED_DATA | char | |||
1 | PROJ_ID | int | The unique ID for the project. | |
2 | TD_UID | int | The unique ID of the timephased data record. | |
TD_FIELD_ID | smallint | The work, cost, or percent completed field to which the record applies. | ||
TD_CATEGORY | smallint | Whether this is a record for a task or an assignment. | ||
TD_REF_UID | int | The unique ID for the task or assignment to which the record applies. | ||
TD_START | datetime | The start date of the record. | ||
TD_FINISH | datetime | The finish date of the record | ||
TD_UNITS | smallint | The unit of time to which the specified values apply. | ||
TD_VALUE1 | decimal | The value of work, percent complete, or cost in the units specified during the first time period specified in TD_UNITS beginning with the start date. | ||
TD_VALUE2 | decimal | The value of work, percent complete, or cost in the units specified during the second time period. | ||
TD_VALUE3 | decimal | The value of work, percent complete, or cost in the units specified during the third time period. | ||
TD_VALUE4 | decimal | The value of work, percent complete, or cost in the units specified during the fourth time period. | ||
TD_VALUE5 | decimal | The value of work, percent complete, or cost in the units specified during the fifth time period. | ||
TD_VALUE6 | decimal | The value of work, percent complete, or cost in the units specified during the sixth time period. | ||
TD_VALUE7 | decimal | The value of work, percent complete, or cost in the units specified during the seventh time period. | ||
TD_EXT_EDITED | bit | Indicates that the record has been externally edited. |
This table contains data about the five available resource cost rate tables.
Key | Column Name | Data Type | Info. Only | Description |
---|---|---|---|---|
RESERVED_DATA | char | |||
1 | PROJ_ID | int | The unique ID for the project. | |
2 | RR_UID | int | The unique ID for this rate in the project. | |
RES_UID | int | The unique ID for the resource in the project. | ||
RR_RATE_TABLE | smallint | The rate table. The integers 0 to 4 correspond to the rate tables A to E. | ||
RR_FROM_DATE | datetime | The first date for which the rates are effective. | ||
RR_TO_DATE | datetime | The last date for which the rates are effective. | ||
RR_STD_RATE | decimal | The standard rate. | ||
RR_STD_RATE_FMT | smallint | The units in which the standard rate is displayed in Microsoft Project. | ||
RR_OVT_RATE | decimal | The overtime rate. | ||
RR_OVT_RATE_FMT | smallint | The units in which the overtime rate is displayed in Microsoft Project. | ||
RR_PER_USE_COST | decimal | The per-use cost. |
This table contains custom text data as well as other task and resource text data.
Key | Column Name | Data Type | Info. Only | Description |
---|---|---|---|---|
1 | PROJ_ID | integer | The unique ID for the project. | |
2 | TEXT_CATEGORY | integer | Whether this is a record for a task, resource, or an assignment. | |
3 | TEXT_REF_UID | integer | The unique ID for the task, resource, or assignment to which the custom field applies. | |
4 | TEXT_FIELD_ID | integer | The unique ID for the custom text field. | |
TEXT_VALUE | varchar
(255) |
The text value stored in the text field. |
This table contains custom number data.
Key | Column Name | Data Type | Info. Only | Description |
---|---|---|---|---|
1 | PROJ_ID | integer | The unique ID for the project. | |
2 | NUM_CATEGORY | integer | Whether this is a record for a task, resource, or an assignment. | |
3 | NUM_REF_UID | integer | The unique ID for the task, resource, or assignment to which the custom number or cost field applies. | |
4 | NUM_FIELD_ID | integer | The unique ID for the custom date, start date, or finish date. | |
NUM_VALUE | decimal | The number value stored in the custom number or cost field. |
This table contains custom date data.
Key | Column Name | Data Type | Info. Only | Description |
---|---|---|---|---|
1 | PROJ_ID | integer | The unique ID for the project. | |
2 | DATE_CATEGORY | integer | Whether this is a record for a task, resource, or an assignment. | |
3 | DATE_REF_UID | integer | The unique ID for the task, resource, or assignment to which the custom date, start, or finish field applies. | |
4 | DATE_FIELD_ID | integer | The unique ID for the custom date, start, or finish field. | |
DATE_VALUE | datetime | The date value stored in the custom date, start, or finish field. |
This table contains custom duration data.
Key | Column Name | Data Type | Info. Only | Description |
---|---|---|---|---|
1 | PROJ_ID | integer | The unique ID for the project. | |
2 | DUR_CATEGORY | integer | Whether this is a record for a task, resource, or an assignment. | |
3 | DUR_REF_UID | integer | The unique ID for the task, resource, or assignment for which the custom duration field applies. | |
4 | DUR_FIELD_ID | integer | The unique ID for the custom duration field. | |
DUR_VALUE | integer | The duration value stored in the custom duration field. | ||
DUR_FMT | smallint | The units in which the custom field is displayed in Microsoft Project. |
This table contains custom flags.
Key | Column Name | Data Type | Info. Only | Description |
---|---|---|---|---|
1 | PROJ_ID | integer | The unique ID for the project. | |
2 | FLAG_CATEGORY | integer | Whether this is a record for a task, a resource, or an assignment. | |
3 | FLAG_REF_UID | integer | The unique ID for the task, resource, or assignment for which the custom flag field applies. | |
4 | FLAG_FIELD_ID | integer | The unique ID for the custom flag field. | |
FLAG_VALUE | bit | The value of the flag: true or false. |
This table contains data about custom outline codes.
Key | Column Name | Data Type | Info. Only | Description |
---|---|---|---|---|
RESERVED_DATA | char | |||
1 | PROJ_ID | integer | The unique ID for the project. | |
2 | CODE_UID | integer | The unique ID of the code for this project. | |
OC_PARENT | integer | The parent code of the the code being defined. | ||
OC_FIELD_ID | integer | The custom outline code field for which the code is defined. | ||
OC_NAME | text | The code being defined. | ||
OC_DESCRIPTION | text | A description of the code being defined. |
This table contains data about which tasks are related to which outline codes.
Key | Column Name | Data Type | Info. Only | Description |
---|---|---|---|---|
1 | PROJ_ID | integer | The unique ID for the project. | |
2 | CODE_CATEGORY | integer | Whether this is a task or resource outline code. | |
3 | CODE_REF_UID | integer | The unique ID for the task or resource to which the custom outline code applies. | |
4 | CODE_FIELD_ID | integer | The custom outline code field to which the code applies. | |
CODE_UID | integer | Refers to the code in MSP_OUTLINE_CODES. |
This table contains the character string categories. All of the Microsoft Project strings belong to a category.
Key | Column Name | Data Type | Info. Only | Description |
---|---|---|---|---|
1 | STRING_TYPE_ID | integer | X | A number representing the enumerated field category. |
2 | STRING_LANG_ID | integer | X | The ID of the language in which the field name is shown. |
STRING_TYPE | varchar | X | The name of the enumerated field category. |
This table contains all of the Microsoft Project character strings.
Key | Column Name | Data Type | Info. Only | Description |
---|---|---|---|---|
3 | STRING_TYPE_ID | integer | X | A number representing the enumerated field category. |
1 | CONV_VALUE | integer | X | The value seen in the database fields. |
2 | CONV_LANG_ID | integer | X | The ID of the language in which the conversion text is displayed. |
CONV_STRING | varchar | X | The text representation of the field contents. |
This table contains data about field attributes such as custom WBS, custom field name aliases and custom field formulae.
Key | Column Name | Data Type | Info. Only | Description |
---|---|---|---|---|
RESERVED_DATA | char | |||
1 | PROJ_ID | integer | The unique ID for the project. | |
2 | ATTRIB_FIELD_ID | integer | The custom field field to which the code applies. | |
3 | ATTRIB_ID | integer | The attribute of the custom field. | |
ATTRIB_VALUE | integer | The value of the attribute in the case of Boolean or enumerated numeric values. | ||
AS_ID | integer | Refers to the attribute string in the case where the attribute value is not numeric. | ||
ATTRIB_EXT_EDITED | bit | Indicates that the record has been externally edited. |
This table contains the custom WBS code definition, alias and formulae defined in MSP_FIELD_ATTRIBUTES.
Key | Column Name | Data Type | Info. Only | Description |
---|---|---|---|---|
RESERVED_DATA | char | |||
1 | PROJ_ID | integer | The unique ID for the project. | |
2 | AS_ID | integer | The unique ID of the attribute string. | |
AS_POSITION | integer | Indicates the position of a resource pool sharer in the list of sharer projects. | ||
AS_VALUE | varchar | The character value of the attribute. |