Over a year ago, I upgraded my big warehouse to Yukon, but I had to leave most of the DBs in 80 mode because of some old syntax that more than 1,000 of our reports were using. That's OK, though, right? I mean, one step at a time. Well, this last weekend I finally got to turn on native Yukon mode (90), and first thing Monday, I got hit with a few surprises that weren't caught in our deprecated feature checks.
The problem is that apparently SQL Server 2000 wasn't nearly as good as we thought at checking syntax, and it let a lot of things go that Yukon doesn't.
Here are the problems we had and the extent of the damage from each.
1. Trailing commas in a Create Table statement.
Create Table MyTable
SQL2K had no problem running this code inside an SP for years, but the second we went to Yukon, it blew up. And rightfully so too -- this has never been valid syntax, so clearly wouldn't have shown up on any deprecation lists. This affected two SPs, so it was easy to fix.
2. Transposed letters in table call. This one I just don't understand at all, but again, SQL2K ran this for years without any issues.
Let's say we have a table named BusinessParty. The query in question looked like this:
Select * from BusniessParty
Again, this has never been allowed, so there was no way to check for it by normal means. And this one affected like one or two SPs as well.
3. This one is my absolute favorite. This syntax is so incredibly illegal, I shouldn't even be allowed to write about it. Specifically, this works in 80 mode, and I've verified it on two different systems.