Results 1 to 3 of 3
  1. #1
    Join Date
    Mar 2015
    Posts
    1

    Unanswered: ALTER DATABASE SET COMPATIBILITY_LEVEL runs irrespective of IF EXIST condition

    Hi,
    Problem:
    I want to set compatibility_level only when it is greater than 110.

    Solution:
    Select the compatibility level and if it is greater than 110, I alter database set compatibility level=110

    ISSUE
    Irrespective of IF Exist statement the alter database statement is executed all the time.

    Here is the sql statement
    IF EXISTS (
    SELECT * FROM sys.databases where compatibility_level >110 AND name='mydatabase'
    )
    BEGIN
    ALTER DATABASE mydatabase SET COMPATIBILITY_LEVEL = 110
    END

    What is that I am missing here ??
    please support.

  2. #2
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Are you sure?

    Here's a bit of air code for you to play with:
    Code:
    SELECT name
         , compatibility_level
    FROM   sys.databases
    ORDER
        BY compatibility_level DESC
    ;
    
    EXEC sp_msforeachdb '
      IF EXISTS (SELECT compatibility_level FROM sys.databases WHERE name = ''?'' AND compatibility_level > 90)
        BEGIN
          SELECT name, compatibility_level FROM sys.databases WHERE name = ''?'';
          ALTER DATABASE [?] SET COMPATIBILITY_LEVEL = 110;
          SELECT name, compatibility_level FROM sys.databases WHERE name = ''?'';
        END
      ;
    ';
    
    SELECT name
         , compatibility_level
    FROM   sys.databases
    ORDER
        BY compatibility_level DESC
    ;
    George
    Home | Blog

  3. #3
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    Here's more:

    Code:
    if exists (select * from sys.databases d where d.compatibility_level = 110 and name='test') begin
       raiserror ('CompatibilityLevel is 110!!!', 0, 1)
    end else begin
       declare @compatibilityLevel int = (select compatibility_level from sys.databases where name = 'test')
       raiserror ('Compatibility level for this database is %d!', 0, 1, @compatibilitylevel)
    end
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

Tags for this Thread

Posting Permissions

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