Monday, August 28, 2006

Floating point error in SQL Server 2000 service pack 3a for MS Project 2000

After significant testing I have come up with the following information relating to the floating point error we have been receiving.

The error is created when data in the timephased data table is extended to many decimal places. However, it is not an error that will occur for all timephased records that have large numbers of decimal places.
The error is created when using MS Project 2000 (either original or SP1 versions) and the data is saved to SQL Server 2000 service pack 3a or before.
The error is corrected if opened by MS Project 2002 and saved BEFORE being corrupted (i.e. before saving it in MS Project 2000).
The error does not occur if the file is not corrupted and is being saved by MS Project 2000 into SQL Server 2000 service pack 4.
When the error occurs, MS Project does a partial write of the MS Project file and will start to save ok, appearing to be ok to the end user. However, data will have been lost in the execution and hence the recommendations below should be followed.
I believe that once all new files are created on the SQL Server 2000 service pack 4 database, the error will no longer occur. However, any files that have transitioned across from service pack 3a to service pack 4 have the potential for the error to occur.

For systems transitioning from Service Pack 3a to 4 I would recommend the following risk mitigation:

Open up all the project plans using a version greater than MS Project 2000 (i.e. MS Project 2002 or MS Project 2003).
Save the files
Re-map the files to their appropriate projects in UniPhi

If the error occurs after this, follow the steps below:

Delete the file in the live UniPhi database
Restore the UniPhi database as a new "temp" database.
Open up the offending file in a version greater than MS Project 2000 (i.e. MS Project 2002 or MS Project 2003)
Save the file
Save the file into the live UniPhi database
Remove the temp database.

No comments: