SQL Server Compatibility Level

So lets get to first things first. Bears 26 Packers 0. I know I am gloating, but for a long time I had to go into the Bears Packer game knowing we were going to lose. Brett Favre beat us time and time again for many years, most of which I have blocked from my memory. So it was nice to sit in the stands with the many packer fans, including my wife, and watch the Bears destroy the Packers. It was actually a lot of fun and always is. If you have never been to a game, you owe it to yourself to go and check it out. No matter who your team is, you have to respect the mystique of Lambue Field and the history that is there. It is a special place.

And specking of special places (bear with me, this is going to be a stretch), sql server 2005 has a special place a certain setting lives that I thought I should cover. (Wow, that was awful, even for me). That setting is the compatibility level. If you are having problems running your 2000 sql server db in sql server 2005, this is the first place I would check. Here is how to check and set it. Right click on the db in question and go to properties. If you are not familiar with sql server 2005 this will be a new look for you. I didn't know if I liked it at first, but it grows on you. Anyhow, once you click on the properties a new window will pop up with the general properties listed on the right and other options you can look at on the left. That is where we want to go. From the list on the left select options.
As you can see, the third option from the top is the compatibility level. Here is where you change from sql server 2005 (90) to sql server 2000 (80). This will allow sql server 2005 to run in a compatibility mode consistent with sql server 2000.

So when should you look to this as a possible solution to your problems. Well, because it is a simple thing to check I always check it first when I know something worked in sql server 2000 but doesn't work or gives me errors in sql server 2005. For example, in our testing we have come across an instance where we get the following error in 2005; Msg 213, Level 16, State 7, Server RP2\RENAISSANCEDB, Line 4 Insert Error: Column name or number of supplied values does not match table definition.

So what does this mean and how could this work in sql server 2000 and not in sql server 2005? Good question. It seems there are several "bug fixes" that have taken place from sql server 2000 to 2005. One of those seems to be that the engine would allow the creation of a temp table with a certain number of columns and then allow an insert of a smaller number of values as long as an update occurred that brought the numbers equal. Sql server 2005 does not allow this.

There are several lists on the MS website that detail the differences in functionality and engine features that are good to look at although they don't always address specific issues like this one. It might be an underlying issue caused by one of the things in this list.

As always, comments and suggestions are welcome. Also as always, there is an open invitation to anyone who would like to post via this blog. Just email me and we will set you up.

Thanks and enjoy your day!