Upgrading MS Project 2002 Standard to MS Project 2003

A new issue has arisen in the integration of MS Project into UniPhi. Extra information is stored in MS Project 2003 Standard and Professional. This extra information requires changes to the database structure. This was not a problem initially as the original version of MS Project 2003 updated the table structure upon saving the first 2003 version into the database. However, this application is already onto its second service pack and in these patches is the elimination of this update functionality. Microsoft now supplies a SQL patch to update the database with the new fields and the one new table required for the application to run in 2003. Versions older than 2003 still function in the updated database structure and hence, we have included this new database design in the standard UniPhi 2.4 deployment.

Service pack 2 for MS Project 2003 (which includes the bug fixes of service pack 1) can be found here.

The SQL Script that updates the database can be downloaded here

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.

Unicode SQL Scripts and TortoiseMerge

Version control is a vital part of collaborative software development. Being able to compare differences between files and resolving differences is a vital aspect of maintaining code in a repository. We use Subversion which is only supplied with a command line interface. The ability to expand and build upon open source programs like Subversion has allowed development of Tortoise SVN, a second program that acts as a GUI interface to Subversion. Its tight integration with Windows file explorer allows easy access to all the functionality of the commandline version.

Comparing differences between your current edition of a file and those in the repository is handled extremely well by Tortoise SVN. TortoiseMerge places the files side by side, you can compare differences per line. However, it needs to know how to display different file formats and currently Unicode isn't supported by TortoiseMerge. We only became aware of this after trying to compare SQL scripts that had been exported from the UniPhi database.

Exporting stored procedures, views and tables from Microsoft SQL Server Enterprise Manager using Generate SQL Script defaults to Unicode file format. By changing the file format to ANSI under the Options tab, you can start visually comparing files that use this format.

Keeping MSSQL Transaction Logs in Check

An essential part of maintaining any Microsoft SQL Server 2000 (MSSQL) Database driven application is a robust backup strategy, not only for the case of disaster recovery but also for keeping the size of your database transaction logs in check. By default, these transaction logs will grow over time to an infinitely large size, or if an upper limit is placed, the log file will fill up without scheduled maintenance, locking users out of the application.

The following Microsoft KB article is very helpful in this regard: http://support.microsoft.com/kb/272318/EN-US/

My recommendations for transaction log maintenance for UniPhi and related databases is as follows:

Since the ES2 (Innerprise Search) database is repopulated on a daily basis, the transaction log is not of a great importance, the following commands could be scheduled for backup purposes, keeping the log file size down:
BACKUP LOG ES2 WITH TRUNCATE_ONLY
DBCC SHRINKFILE(ES2_log, 100)

However for the UniPhi database, we do want to have the transaction logs available in case of an emergency, so:
BACKUP LOG uniphi TO uniphilogbackup
DBCC SHRINKFILE(uniphi_log,100)