We recently tested the upgrade path for 6 large SQL 2000 databases. We'd faithfully used the Upgrade Advisor and implemented all the changes it recommended to TSQL code throughout the databases. So - everything should work - yes?

Well we thought so, until I tried an additional test. I scripted out all the user stored procedures (upgraded ones) in SQL 2005, then deleted them, then I ran the same script. I hoped to not find anything at all but it failed for 2 generic types of error:

Declare @count int
Set Set @count = 1
Select @count

Declare @MyDate datetime
Set @mydate = cast('1/1/99' as datetime(103))
Select @mydate

Yes - I know. This code makes no sense. But if you have 100s or 1000s of procedures, someone may have written something strange like this somewhere.

We have several columns called "External". I was surprised that this wasn't picked up by the advisor, and it's easily fixed by just adding square brackets, but is still a bit of a pain.

If I find anything else it missed I'll post it up.