SQL Compatability Level

SQL Server can run in a number of different compatibility levels, but how do you change it and how do you set it. These compatibility levels reflect the version of SQL server. * 60 = SQL Server 6.0 * 65 = SQL Server 6.5 * 70 = SQL Server 7.0 * 80 = SQL Server 2000 * 90 = SQL Server 2005 Here is a sample script that will show you the current compatibility level, set it to 90, then show you the current level after it has been set. sp_dbcmptlevel dbname go sp_dbcmptlevel dbname, 90 go sp_dbcmptlevel dbname go Once you change the compatibility level, you will want to be sure that your system still runs correctly.

How do you tell what version of SQL Server you are running.

The question came up as to which version of sql server is being run, and how do you determine the exact version number of the SQL server. Here is what I have come up with. From TSQL if you are connected to the SQL Server, you can run the following command: SELECT 'SQL Server ' + CAST(SERVERPROPERTY('productversion') AS VARCHAR) + ' - ' + CAST(SERVERPROPERTY('productlevel') AS VARCHAR) + ' (' + CAST(SERVERPROPERTY('edition') AS VARCHAR) + ')' which should return something like the following: SQL Server 9.00.4035.00 - SP3 (Standard Edition (64-bit)) Additionally if you are at the command prompt on the SQL server itself, you can run the following command: osql -E -q"SELECT @@VERSION"