MSSQLTips

http://www.mssqltips.com/tip.asp?tip=1436

There are also issues with upping the compatibility level from a previous SQL version to version 90 - you need to also check that your code still works with the stricter rules under SQL 2005.  For an explanation take a look at this:

 http://www.innovartis.co.uk/topical/migratingto2005.aspx

This is true there may be issues with upping the compatiblity level, but these items should be reviewed and corrected.  I have seen many implementations of SQL Server 2005 where databases were migrated, but the compatiblity levels were never changed.  I guess if someone is not using the newer features they wouldn't notice, but at some point they would probably run into a need where they would have to up the compatiblity level.

Actually you the problems would specifically concern the T-SQL that worked perfectly fine under 2000 but does not work when the compat is changed to 90 i.e. after upping the level the database could be broken in some way.  It's finding the issues amongst all the stored procedures, functions etc. that is the arduous part unless you have a 100% coverage automated regression test pack.  That's why we recommend rebuilding your database to see what doesn't compile at level 90...