SQL 2005 Gotcha Stuff

In the last couple of days I have gotten calls and emails from several people who have had sql server 2005 issues related to upgrading from 2000, so I thought I would take a moment and let you know what I have seen and how it was fixed.

The first and easiest one was actually told to me by a colleague the other day and was something I was able to use to help another person that very same day. If you get an error message during a restore that mentions too many headers, you are most likely trying to restore a SQL Server 2005 db to a SQL Server 2000 version of the DBMS. SQL Server does not allow that and will yell at you if you attempt it. Your only course of action it to restore the db on a 2005 server.

Another one we came across was sort of a compound problem. It started with a dbcc checkdb which now does a little more than the old version did. Along with checking the db for consistency errors it checks for catalog errors. So errors where stored procedures and functions reference each other are caught now and will give you a message that looks something like this:

Msg 8992, Level 16, State 1, Line 1
Check Catalog Msg 3853, State 1: Attribute (referenced_major_id=782677886,referenced_minor_id=1) of row (class=0,object_id=1648776981,column_id=0,referenced_major_id=782677886,referenced_minor_id=1) in sys.sql_dependencies does not have a matching row (object_id=782677886,column_id=1) in sys.columns.
Msg 8992, Level 16, State 1, Line 1
Check Catalog Msg 3853, State 1: Attribute (referenced_major_id=782677886,referenced_minor_id=1) of row (class=0,object_id=1632776924,column_id=0,referenced_major_id=782677886,referenced_minor_id=1) in sys.sql_dependencies does not have a matching row (object_id=782677886,column_id=1) in sys.columns.
CHECKDB found 0 allocation errors and 2 consistency errors not associated with any single object.

The fix for this is actually quite easy as well. Simply identify the objects referenced in the error message by querying the sys.objects table. Once you know what they are, drop them and then create them again. It seems to be a bug in the upgrade procedure that can cause this, but again, the fix is simple.

On another note, the same colleague that told me about the restore error also found a decryption tool that works with 2005. It can be found here at www.elitude.net Thanks for finding that Paul.

These of course are just a few of the things that we will see as the world migrates from SQL Server 2000 to 2005. Please continue to email your issues or discoveries in this area and I will do my best to get the information out to everyone.