Results 1 to 5 of 5

Thread: Set Compatibility Level for SQL Server 2008 or 2005 Database

  1. #1
    UltimateX is offline Member
    Last Online
    26th June 2010 @ 12:36 PM
    Join Date
    26 Jul 2007
    Location
    O . o
    Posts
    17,684
    Threads
    517
    Thanked
    19

    Cool 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;

  2. #2
    Join Date
    20 Jan 2009
    Location
    Multan
    Age
    38
    Posts
    807
    Threads
    25
    Thanked
    11

    Default

    Thanks

  3. #3
    UltimateX is offline Member
    Last Online
    26th June 2010 @ 12:36 PM
    Join Date
    26 Jul 2007
    Location
    O . o
    Posts
    17,684
    Threads
    517
    Credits
    0
    Thanked
    19

    Default

    Quote sadeeqbao said: View Post
    Thanks
    pasand karne ka shukria

  4. #4
    farrukhnawaz is offline Senior Member+
    Last Online
    20th August 2022 @ 09:35 PM
    Join Date
    11 Jan 2010
    Location
    Lahore
    Age
    38
    Posts
    68
    Threads
    3
    Credits
    1,393
    Thanked: 1

    Default

    Nice work. Is the name of the database to be modified.
    COMPATIBILITY_LEVEL { 150 | 140 | 130 | 120 | 110 | 100 | 90 | 80 }
    Is the version of SQL Server with which the database is to be made compatible.
    paraphrase spin

  5. #5
    Keenreaves's Avatar
    Keenreaves is offline Senior Member+
    Last Online
    4th April 2023 @ 09:59 AM
    Join Date
    13 Apr 2020
    Gender
    Male
    Posts
    180
    Threads
    68
    Credits
    -133
    Thanked
    8

    Default

    اچھی پوسٹ

Similar Threads

  1. Database attaching error Sql server 2008
    By salmanpatni in forum Ask an Expert
    Replies: 1
    Last Post: 14th April 2013, 07:13 AM
  2. form data store in database(sql server 2008)
    By salmanpatni in forum Ask an Expert
    Replies: 2
    Last Post: 28th March 2013, 12:59 PM
  3. SQL Server 2005
    By mihmay in forum Ask an Expert
    Replies: 1
    Last Post: 2nd December 2011, 10:30 AM
  4. urgent require SQL database Server 2005 full
    By jamat ali in forum Ask an Expert
    Replies: 1
    Last Post: 22nd May 2011, 03:21 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •