Determining Highest or Default Compatibility Level for a SQL Server Instance

s8vozzvw 于 2023-04-28 发布在 SQL Server

关注(0)|答案(1)|浏览(136)

Is it possible to programmatically determine the highest compatibility level a particular SQL Server instance supports, or at least set the level of a database to the default for the instance without knowing in advance what that is?

The motivation here is that I was previously setting all databases to an earlier compatibility level. Now I would like to migrate them to a higher level to benefit from newer features, but I don't want to break things for customers who happen to still be running older SQL Server versions.

ALTER DATABASE will let me choose a specific compatibility level such as 150 but I can't see a way to just set it to go to the default level for the instance, or to determine the highest level supported except possibly through some hack involving parsing the version number (although this doesn't seem particularly reliable).

sql-server

来源：https://stackoverflow.com/questions/76125246/determining-highest-or-default-compatibility-level-for-a-sql-server-instance

1条答案

按热度按时间

wlzqhblo1#

Unfortunately, there is no built-in way to do this in SQL Server. SSMS and most other tools indeed just parse the version information.

Here is a horrible hack to get the information out of the error message that results from providing an incorrect comaptibility level.

BEGIN TRY
    EXEC('
    ALTER DATABASE master
    SET COMPATIBILITY_LEVEL = 0;
    ');
END TRY
BEGIN CATCH
    DECLARE @msg nvarchar(4000) = ERROR_MESSAGE();
    SELECT SUBSTRING(@msg, LEN(@msg) - 3, 3);
END CATCH;

For reference, the message is something like this, with the last value being the highest.

Valid values of the database compatibility level are 100, 110, 120, 130, 140, 150 or 160.

db<>fiddle for 2014

db<>fiddle for 2022

赞(0）回复(0）举报 2023-04-28

我来回答

Determining Highest or Default Compatibility Level for a SQL Server Instance

1条答案

相关问题

热门标签

最新问答