Syntax nightmares

When going from SQL Server 2000 to 2005, you may be surprised by what you'll find.

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

(

Col1 int,

Col2 varchar(20),

Col3 int,

)

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.

IF @ReportType IN ('DAILY','CURMONTH','PREVMONTH','CURWEEK','PREVWEEK','CURYEAR','PREVYEAR')      

SET @EndDate = (SELECT EndDate FROM kmartin.Met_fn_Set_Dates(@ReportType)) 

ELSE    SET SELECT @EndDate = CONVERT(CHAR(10),@EndDate,101) + ' 18:30:00.000'

Did you spot the syntax error? If you know SQL, it's pretty obvious. The problem is with the 'SET SELECT'. There's no such thing in SQL. You can assign a value to a variable using either set or select, but not both together.

This is the problem that dug itself into more than 1,700 SPs and brought our environment to a complete halt. Not to mention fixing it took me down a path of SMO deficiencies, coding problems, and even more syntax issues. I did get a solution coded finally, though. The issue was to script alter statements for all of them after scripting them out and fixing them. I used a combination of powershell and regular expressions to get us back up and running. Of course, it did take a day to get all the bugs worked out, but it beats doing it all by hand, right?

4. This one was new to Yukon, so I'm not sure how it happened, but there was allowed a CTE without the previous statement being delimited by a ';'. This one was easy and only took out two SPs, so it was no big deal. But it is a fabulous question how that happened to begin with.

5. This next one I'm still investigating. I don't have a handle on how pervasive it's going to be yet, but I've found a few of them so far, and the worst part about it is there's no way to write code to look for it.

Select isnull(o.col1, 0) AS col1
o.col2 from Orders o
inner join OrderDetail od
on o.id = od.id
ORDER BY o.col1

This is no longer allowed. You have to 'ORDER BY col1'. Leave the alias qualifier off.

So what does all this boil down to? Testing, of course. It's not enough to look at the deprecation list or trace your code with the deprecated features class turned on. You have to actually script out your SPs and functions, and then upgrade. Then you can try to install them again (typically with an ALTER) and see what fails because you just never know when a particular version is going to have syntax checking troubles.

So by all means, run your compatibility tests, your performance benchmarks, and your deprecation checks, but add this step as well: Script out all your DB code and re-insert it into the DB on your test system. You may be amazed at what turns up. And you could actually save yourself tons of downtime. And if nothing shakes out, you can say you've done your due diligence and you don't have to worry about things failing when you flip the switch on the new version.

From CIO: 8 Free Online Courses to Grow Your Tech Skills
Join the discussion
Be the first to comment on this article. Our Commenting Policies