4

While deploying the database on the Azure server, I get an error as:

The database compatibility level '15' is not within the supported range of 80 to 130. (Microsoft.SqlServer.Dac)

I tried updating the COMPATIBILITY_LEVEL by the below command:

ALTER DATABASE  [database_name]
SET COMPATIBILITY_LEVEL = 100;  
GO

But when I check the compatibility mode through the "Properties" of the database, I see a blank tab and I can't select any value from the dropdown.

enter image description here

Below is the information of SQL server management studio I've installed on my machine:

enter image description here

Can anyone please suggest what steps should I follow to update the compatibility level of any of the databases while using this version of SSMS?

As mentioned in one of the comments, below is the output for command

SELECT SERVERPROPERTY('ProductVersion')

enter image description here

Also, below is the output for command

SELECT name, compatibility_level FROM sys.databases;

enter image description here

While compatibility level of databases shows 100 and above in the result, not sure why I'm receiving the error. Would appreciate any suggestions.

Deva
  • 1,039
  • 1
  • 14
  • 40
  • 1
    What about SSMS 18, the latest version? – Charlieface Apr 18 '21 at 19:35
  • Database compatibility is a server-side control, it has nothing to do with the client application you're using (in this case SSMS). – AlwaysLearning Apr 18 '21 at 21:26
  • 3
    SSMS 16 won't know anything about compat levels beyond 130. Please use SSMS 18.x which is the most recent and understands all current compat levels. – Conor Cunningham MSFT Apr 19 '21 at 15:42
  • Thanks for suggestion, @ConorCunninghamMSFT Is there anything we can do in 16 version itself to make it work? Also, what about the previous versions like 15/14, are they having similar issues? – Deva Apr 22 '21 at 07:23
  • Please run the following commands and post the results SELECT SERVERPROPERTY('ProductVersion'); and SELECT name, compatibility_level FROM sys.databases; – Steve Ford Apr 22 '21 at 23:21
  • @SteveFord I've added the results, please check. – Deva Apr 23 '21 at 06:27
  • 3
    We made SSMS ship separately and monthly so that it would not be tied to a major release (and not understand future releases). Using the latest SSMS is the recommended path forward. I would not use old SSMS given that the new one is a free download – Conor Cunningham MSFT Apr 23 '21 at 12:55
  • The canonical answer is update your version of SSMS. That's literally it. – Thom A Apr 24 '21 at 19:43

1 Answers1

1

The error message seems to be incorrect because:

  1. the version numbers in Azure SQL aren't relatable to those in SQL Server, and
  2. no single version of SQL Server covers levels from 80 through 130 (SQL Server 2000 through 2016).

As per ALTER DATABASE (Transact-SQL) Compatibility Level

Important

The database engine version numbers for SQL Server and Azure SQL Database are not comparable with each other, and rather are internal build numbers for these separate products. The database engine for Azure SQL Database is based on the same code base as the SQL Server database engine. Most importantly, the database engine in Azure SQL Database always has the newest SQL database engine bits. Version 12 of Azure SQL Database is newer than version 15 of SQL Server.

AlwaysLearning
  • 7,915
  • 5
  • 27
  • 35