Sponsored By Aspose - File Format APIs for .NET

Aspose are the market leader of .NET APIs for file business formats – natively work with DOCX, XLSX, PPT, PDF, MSG, MPP, images formats and many more!

Update your compatibility level on your databases

It has been awhile since we’ve all upgraded from 7.0 to 2000 Sql
Server.  Just a reminder, don’t forget to update your
compatibility levels, if applicable, when moving your databases from
2000 (version 8.0) to 2005 (version 9.0) in order to take advantage of
the latest features and enhancements.  I keep forgetting to do
this when moving my databases and discover it every time I try to PIVOT
code (topic coming soon using ‘pubs’ database as an example) and it
throws an error.

The syntax is as follows:
Exec sp_dbcmptlevel ‘pubs’, ’90’

If you move the pubs database from 2000 to 2005, that will change its compatibility level to 2005.  Values accepted are:
70 = SQL Server 7.0.
80 = SQL Server 2000.
90 = SQL Server 2005.

FYI, I have moved 8 databases from 2000 to 2005 Beta2 with no problems.

This entry was posted in Sql Development. Bookmark the permalink. Follow any comments here with the RSS feed for this post.

One Response to Update your compatibility level on your databases

  1. renato2099 says:

    Hey Raymond, I have a question about this migration mystery. I know there are several methods to get this task done but about this compatibility opcion there hasn’t been enough information out. Do you know if there are any mayor disadvantages? Why shouldn’t we use this option? I hope you can answer soon. Thanks in advance.

Leave a Reply