One of the requirements for the GP 2010 upgrade is to upgrade the compatibility level on the SQL database to 2005. We did the upgrade to SQL2005 from 2000 a while back, but we left the compatibility level as 2000. I have done a lot of upgrades from SQL 2000 to SQL 2005 and even on up to 2008. I only really saw one thing that sometimes caused an issue with applications and that was the conversion of the bit field from being 0 and 1 to true and false. Even that had not caused really any issues.
Well I found one today. Good thing the error message was easy to identify the fix.
“Incorrect syntax near the keyword ‘with’. If this statement is a common table expression or an xmlnamespaces clause, the previous statement must be terminated with a semicolon.”
Literally added the semicolon to the front of the with statement and error was resolved.