SQL Server Tips
From the Database Community Forum
Archive
August 2004
Beware database compatibility settings in SQL Server
SQL Server has the ability to be configured to run in compatibility mode with previous releases of the product. Used judiciously, this can facilitate upgrading databases from one version to another, but it's very easy to forget that you once constrained your server to a compatibility mode. Once you've done so, you'll be limited to those features available in previous releases. For example, if you set compatibility to SQL Server 6.5, you can't use the TOP clause in your select statements (of course, you can use SET ROWCOUNT, but this is beside the point). To check the compatibility mode settings on your server, issue the following T-SQL statement (in SQL Server 7.0 or later):
EXEC sp_dbcmptlevel '' To set a compatibility mode, should you wish to, use one of the following, depending on the version compatibility you require:
EXEC sp_dbcmptlevel '', 65 EXEC sp_dbcmptlevel '', 70 EXEC sp_dbcmptlevel '', 80 
Alrak's Course Resources ©2002-2007 Karla Carter. All rights reserved. This material (including, but not limited to, Mini-lectures and Challenge Labs) may not be reproduced, displayed, modified or distributed without the express prior written permission of the copyright holder. For permission, contact Karla.