Don’t “save-as” and use ODBC from Project Professional 2003 to a 2007 Database!

If you have the right database permissions it is perfectly possible to point Project Professional 2003 at a Project Server 2007 database using the save-as option and specifying an ODBC connection.  Please do not do this!  Reason 1 is that it will not work and will give an error message, but reason 2 is that it could come back and haunt you later.  In failing it does try to make the database look like a 2003 version, and it adds some data, and even adds some columns to tables where it thinks they are missing (I said you need the right permissions so only someone with a high level of SQL Server access would get this far).

The time you find the failure is if you ever try to recover your system on another server (or even the same server after a re-build).  These extra columns then cause a failure of the PWA provisioning process against the set of databases.  During the creating databases stage it will fail with a “Failed – See the Event Log” error.  In the event log will be Exception: Insert Error: Column name or number of supplied values does not match table definition.  You will now have an MSP_TASKS table with 130 columns compared to the expected 128 – so when it tries to create a stored procedure that will later be used to copy data from MSP_TASKS to MSP_TASKS_SAVED the columns no longer match.  The extra columns are:-


Other evidence of the issue is:-

  1. In the MSP_CALENDARS table you show a field called CAL_ENTUID
  2. In the Project Server 2007 database you see the following “extra” tables

Recovery is reasonably painless, the extra columns can be deleted, and then provisioning will work.

Technorati Tags: Project Server 2007