Microsoft Office Project 2003 Data Reference

Overview

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

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

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

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

Specific chapters include the following information:

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

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

What's new in the Project 2003 database

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

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

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

• MSP_MV_FIELDS

About the Project Database

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

Supported databases

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

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

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

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

Loading older versions of Project into the database

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

Upgrading the Project database from an older version

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

Database Permissions and Configuration

Required permissions

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

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

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

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

  Create view MSP_TEXT_FIELDS as
  Select    PROJ_ID,
            TEXT_CATEGORY,
            TEXT_REF_UID,
            TEXT_FIELD_ID,
            TEXT_VALUE
  From      user1.MSP_TEXT_FIELDS
  Union all
  Select    PROJ_ID + 1000,
            TEXT_CATEGORY,
            TEXT_REF_UID,
            TEXT_FIELD_ID,
            TEXT_VALUE
  From      user2.MSP_TEXT_FIELDS
  

Notes

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

Configuring the database

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

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

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

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

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

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

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

Performance tuning

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

Ensuring data integrity

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

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

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

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

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

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

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

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

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

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

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

For assignments:

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

For resources:

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

For tasks:

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

How Information is Stored in the Project Database

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

Calendar data

Project saves calendar information to two tables:

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

Timephased data

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

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

Notes

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

Custom field values

Custom field values are stored in the following tables:

• MSP_DATE_FIELDS contains custom start and finish dates.
• MSP_DURATION_FIELDS contains custom durations.
• MSP_FLAG_FIELDS contains custom flag information.
• MSP_NUMBER_FIELDS contains custom cost and number values.
• MSP_TEXT_FIELDS contains custom text information.

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

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

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

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

Split Tasks

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

Task links

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

Estimated duration values

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

Baselines

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

Working with Projects in the Project Database

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

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

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

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

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

Creating the Project database structure

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

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

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

DSN requirements and limitations

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

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

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

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

Concurrent usage and project locking

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

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

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

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

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

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

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

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

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

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

Adding and Changing Rows in the Database

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

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

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

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

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

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

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

Specifying times with dates

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

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

Duration, work, rate, and cost values

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

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

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

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

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

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

Using the text conversion tables to retrieve strings

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

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

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

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

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

Creating Project Schedule Data

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

Creating a new, non-enterprise project

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

Notes

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

Creating an inserted project

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

<c:\test.mdb>\my project

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 Project.

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

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

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

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

Creating a new, non-enterprise resource

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

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

Insert into MSP_RESOURCES (
              PROJ_ID,
              RES_UID,

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

Creating a new, non-enterprise calendar

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

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

Insert into MSP_CALENDARS (
              PROJ_ID,

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

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

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

Notes

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

Specifying calendar working time and exceptions

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

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

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

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

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

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

Specifying resource availability

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

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

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

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

Specifying resource rates

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

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

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

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

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

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

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

Creating a new task

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

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

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

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

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

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

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

Notes

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

Creating a recurring task

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

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

-- insert the summary recurring task
Insert into MSP_TASKS (
              PROJ_ID,
              TASK_UID,
              TASK_ID,
              TASK_NAME,
              TASK_OUTLINE_LEVEL,
              TASK_IS_RECURRING,
              TASK_IS_RECURRING_SUMMARY,
              TASK_IS_ROLLED_UP,
              EXT_EDIT_REF_DATA )
          values (
              3,
              9,
              9,
              'my recurring summary task',
              2, -- outline level
              1, -- is recurring
              1, -- is recurring summary
              1, -- is rolled up
              '1' ) 

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

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

Creating task dependencies

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

Insert into MSP_LINKS (
              PROJ_ID,
              LINK_UID,
              LINK_PRED_UID,
              LINK_SUCC_UID,
              EXT_EDIT_REF_DATA )
          values (
              3,
              1,
              1,
              2,
              0 ) 

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

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

Creating a new assignment

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

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

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

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

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

Notes

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

Managing Timephased Data

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

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

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

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

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

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

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

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:

Creating or modifying an assignment actual-work order

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

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

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

Creating splits in scheduled work

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

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

Creating or modifying an assignment remaining-work order

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

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

Creating or modifying cost contours

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

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

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

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

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

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

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

Update    MSP_PROJECTS 
Set       PROJ_OPT_CALC_ACT_COSTS = 0 
Where     PROJ_ID = 3 

Creating or modifying task-percent complete contours

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

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

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

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

Deleting a contour

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

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

Customizing Project Data

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

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

Specifying custom text fields

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

Specifying custom number (or cost) fields

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

Specifying custom date, start, and finish fields

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

Specifying custom duration fields

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

Field attributes

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

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

Specifying custom WBS codes

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

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:

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

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

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

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

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

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

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

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

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

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

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

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

Specifying custom outline codes

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

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

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

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

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

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

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

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

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

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

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

Specifying aliases for custom fields

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

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

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

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

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

Specifying formulae for custom fields

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

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

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

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

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

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

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

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

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

Reading and writing RTF notes

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

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

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

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

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

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

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

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

    cn.Open cnString

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

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

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

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

Managing Other Data in the Database

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

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

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

Note   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.

Specifying an e-mail address

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

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

Retrieving cross-project link project/task references

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

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

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


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

Retrieving the names of sharer files

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

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

Outlining with summary tasks and subtasks

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

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

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

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

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

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

Update    MSP_TASKS 
Set       TASK_IS_SUMMARY = 1 
Where     TASK_UID = 12

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

Editing work on a summary task assignment

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

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

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

Retrieving workgroup message status

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

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

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

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

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

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

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

Specifying hyperlinks

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

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

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

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

Database Processing Order, Conventions, and Abbreviations

Processing order for externally edited data

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

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

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

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

Notes

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

Standard processing order

The standard processing order for task fields is as follows:

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

The standard processing order for assignment fields is as follows:

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

Database Object Naming Conventions

Table naming conventions

The following conventions apply to table names:

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

Column naming conventions

The following conventions apply to column names:

• Column names are all uppercase.
• Words are generally separated by underscores.
• Column names are generally pre-fixed with a unique identifier for their tables:

MSP_PROJECTS column qualifiers

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

Column name abbreviations

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

Database Tables

Information-only columns

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

Bold column names

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

Column data types

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

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

MSP_ASSIGNMENT_BASELINES

This table contains custom assignment baseline data.

MSP_ASSIGNMENTS

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

MSP_ASSN_ENTERPRISE

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

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

MSP_ATTRIBUTE_STRINGS

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

MSP_AVAILABILITY

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

MSP_CALENDAR_DATA

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

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

MSP_CALENDARS

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

MSP_CODE_FIELDS

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

MSP_CONVERSIONS

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

MSP_DATE_FIELDS

This table contains custom date data.

MSP_DURATION_FIELDS

This table contains custom duration data.

MSP_FIELD_ATTRIBUTES

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

MSP_FLAG_FIELDS

This table contains information about custom flags.

MSP_LINKS

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

MSP_MV_FIELDS

This table comtains resource multi-value code information.

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

MSP_NUMBER_FIELDS

This table contains custom number data.

MSP_OUTLINE_CODES

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

MSP_PROJ_SECURITY

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

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

MSP_PROJECTS

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

MSP_RES_GLOBAL_BINARY

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

MSP_RES_SECURITY

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

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

MSP_RESGLOBAL_SECURITY

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

MSP_RESOURCE_BASELINES

This table contains custom resource baseline data.

MSP_RESOURCE_RATES

This table contains resource rate data.

MSP_RESOURCES

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

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

MSP_STRING_TYPES

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

MSP_TASK_BASELINES

This table contains custom task baseline data.

MSP_TASKS

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

MSP_TEXT_FIELDS

This table contains custom text information.

MSP_TIMEPHASED_DATA

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