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.”

The fix

;With BOM_No

Literally added the semicolon to the front of the with statement and error was resolved.