How to Set Compatibility Level for SQL Server 2008 or 2005 Database

When selecting data or performing query on Microosft SQL Server 2008 or SQL Server 2005 database, the following error message or failure may be returned:

[Microsoft][ODBC SQL Server Driver][SQL Server][Msg 4147, Level 15, State 1, Line 4] The query uses non-ANSI outer join operators (”*=” or “=*”). To run this query without modification, please set the compatibility level for current database to 80, using the SET COMPATIBILITY_LEVEL option of ALTER DATABASE. It is strongly recommended to rewrite the query using ANSI outer join operators (LEFT OUTER JOIN, RIGHT OUTER JOIN). In the future versions
of SQL Server, non-ANSI join operators will not be supported even in backward-compatibility modes.

or,

Msg 4147, Level 15, State 1, Line 3
The query uses non-ANSI outer join operators (”*=” or “=*”). To run this query without modification, please set the compatibility level for current database to 80 or lower, using stored procedure sp_dbcmptlevel. It is strongly recommended to rewrite the query using ANSI outer join operators (LEFT OUTER JOIN, RIGHT OUTER JOIN). In the future versions of SQL Server, non-ANSI join operators will not be supported even in backward-compatibility modes.

The error happens because Microsoft has dropped support for and eliminated old-style *= and =* outer join operators.

In order to fix the SQL Server error above, DB administrator has to set the database compatibility level to 80, which equivalent with SQL Server 2000 as a temporary workaround. Of course, the best solution is to modify the SQL statements or queries code to remove the old-style join operators with current standard SQL join syntax.

For example, SQL query below is not valid:

SELECT o.name FROM sys.objects o, sys.views v WHERE o.object_id *= v.object_id;

And should be replaced with the following code:

SELECT o.name FROM sys.objects o LEFT JOIN sys.views v ON o.object_id = v.object_id;

Or the following abbreviated inner join statement:

SELECT o.name FROM sys.objects o, sys.views v WHERE o.object_id = v.object_id;

For administrator who can’t change the code, the easiest workaround is to revert the compatibility level of the database in order to provide the backward compatibility to old-style joins. sp_dbcmptlevel stored procedures (deprecated) or Transact-SQL can be used to set certain (partially, not all) database behaviors to be compatible with the earlier version of SQL Server. This MSDN article provides overview on what behaviors are changed on each compatibility level.

Version of SQL Server that can be reverted to can be one of the following:

60 = SQL Server 6.0
65 = SQL Server 6.5
70 = SQL Server 7.0
80 = SQL Server 2000
90 = SQL Server 2005
100 = SQL Server 2008

Note: Compatibility level 60, 65, and 70 no longer available in SQL Server 2008. And future version of SQL Server will support only two (2) prior version of backward compatibility. A database containing an indexed view cannot be changed to a compatibility level lower than 80. Do also take note that when a database is set to backward-compatibility mode, some of the new functionalities may be lost, such as SQL CLR support and SSMS diagrams for the database. Beside, the compatibility mode affects behaviors only for the specified database, not for the entire server.

Steps to Change and Set Compatibility Level of A Database in SQL Server

The following SQL commands can be issued in SQL Server Management Studio Query window. Remember to execute Go after each command.

1. Optional: Set the database to single user access mode:

   ALTER DATABASE database_name SET SINGLE_USER;

2. Set the database compatibility level to one of the earlier version stated above:

   Transact-SQL Method

   ALTER DATABASE database_name SET COMPATIBILITY_LEVEL = { 80 | 90 | 100 }

   For example,

   ALTER DATABASE my_db SET COMPATIBILITY_LEVEL = 80

   sp_dbcmptlevel Stored Procedure Method

   EXEC sp_dbcmptlevel database_name, compatibility_level value;

   For example,

   EXEC sp_dbcmptlevel my_db, 90;

3. Optional: Put back the database in multiuser access mode:

   ALTER DATABASE database_name SET MULTI_USER;

Related Articles

• Uninstall and Remove Multiple Database Instances of Microsoft SQL Server 2005
• Microsoft DreamSpack Offers Free Download and License of Visual Studio 2008, Server 2003, SQL Server 2005 and More
• Download Update for Windows 7 and Server 2008 R2 Beta (KB967062) to Fix Common Application Compatibility Issue
• Optimize SQL Server 2000, 2005 or 2008 in Large RAM System by Locking Pages in Memory and AWE
• Check System Compatibility Via Microsoft New Application Compatibility Toolkit Version 5.5
• How To Perform Scheduled Backups For SQL Server 2005 Express
• Error Has Occurred While Establishing A Connection To SQL Server 2005 Which Does Not Allow Local and Remote Connections
• Microsoft Virtual Server 2005 R2 Download Available for Free
• Change Oracle Database User Password
• Microsoft Virtual Server 2005 R2 SP1 (Enterprise Edition) Direct Download Links

This entry was posted on Sunday, May 17th, 2009 at 2:43 am and is filed under Databases. You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response, or trackback from your own site.