A friend asked me how to check the version of his SQL Server. He told me that he has checked all the About boxes on applications installed with SQL Server but see nothing relevant.
Checking SQL Server version is slightly different from other Microsoft products. You do not get it by checking the About dialog box. It actually makes sense because there are only About boxes for tools shipped with SQL Server and not the database server itself. These About boxes should show the version of those tools.
Microsoft SQL Server is an instance installed on a machine. You may install multiple instances of SQL Server on one machine. Each of these instances could be of different edition or version (for versions that could co-exist). Naturally SQL Server version and edition is found at the server level.
How to Determine SQL Server Edition and Version
The following are five approaches to achieve the stated objective. The first four methods give you both edition and version whereas the fifth method only provides the version. All the methods except for method number five requires login to the SQL Server.
I personally prefer the use of SERVERPROPERTY function explained in approach number two. It has little dependency on availability of installed components yet it presents data in a readable manner.
1. Enterprise Manager
This tool is shipped with Microsoft SQL Server 2000. It provides the most straightforward way to accessing SQL Server edition and version. The whole set of information is shown on the server property page as illustrated in the following figure.
The figure shows the property of a SQL Server 2000 Developer Edition SP4. This method might be straightforward but it requires that you have Enterprise Manager installed.
2. SERVERPROPERTY Function
SQL Server Edition and Version could be acquired using the SERVERPROPERTY function. This is applicable for SQL Server 2000 and SQL Server 2005. This function takes one parameter that specifies the server property requested. The following is the T-SQL script that returns SQL Server version and edition.
SELECT
SERVERPROPERTY ('ProductVersion'),
SERVERPROPERTY ('ProductLevel'),
SERVERPROPERTY ('Edition')
This query will return one row with three columns. The first column returns the SQL Server version in the form of "major.minor.build". The second column denotes the service pack updates whereas the third column shows the edition. Do find out more about the properties that SERVERPROPERTY function returns.
3. @@VERSION Variable
The global variable @@VERSION stores the SQL Server Edition and Version too. You may find out the value by issuing the following T-SQL query. This approach is applicable to SQL Server 6.5, SQL Server 7.0, SQL Server 2000 and SQL Server 2005. The value returned is illustrated in the following figure.
PRINT @@VERSION
The version is found in the form of "major.minor.build". The edition is stated at the final line.
4. Microsoft SQL Server Management Studio
The version of SQL Server connected is shown in the Object Explorer. It is in the form of "major.minor.build". More detail could be found on the property page of the connected instance. The property page also shows the version in number. However, information such as the edition is shown. The following figure shows the object explorer and the property page.
5. SQLSERVER.EXE File Property
The property page of SQLSERVER.EXE shows the SQL Server version too. Edition however could not be found here. The following figure shows the detail tab on SQLSERVER.EXE property page. This method requires no login to the SQL Server. You just need to find the correct SQLSERVER.EXE file.
SQL Server Version Number
If you are unable to use the first two methods mentioned above, you would have to identify the version from product version. There is not a pattern to decode the version number to their relevant service pack update. Please find the equivalent with the version number using the following table.
| Version Number | Service Pack |
| 90.00.3042.00 | SQL Server 2005 SP2 |
| 90.00.2047.00 | SQL Server 2005 SP1 |
| 90.00.1399.00 | SQL Server 2005 RTM |
| 8.00.2039 | SQL Server 2000 SP4 |
| 8.00.760 | SQL Server 2000 SP3a |
| 8.00.760 | SQL Server 2000 SP3 |
| 8.00.534 | SQL Server 2000 SP2 |
| 8.00.384 | SQL Server 2000 SP1 |
| 8.00.194 | SQL Server 2000 RTM |
| 7.00.1063 | SQL Server 7.0 SP4 |
| 7.00.961 | SQL Server 7.0 SP3 |
| 7.00.842 | SQL Server 7.0 SP2 |
| 7.00.699 | SQL Server 7.0 SP1 |
| 7.00.623 | SQL Server 7.0 RTM |
| 6.50.479 | SQL Server 6.5 Service Pack 5a (SP5a) Update |
| 6.50.416 | SQL Server 6.5 Service Pack 5a (SP5a) |
| 6.50.415 | SQL Server 6.5 Service Pack 5 (SP5) |
| 6.50.281 | SQL Server 6.5 Service Pack 4 (SP4) |
| 6.50.258 | SQL Server 6.5 Service Pack 3 (SP3) |
| 6.50.240 | SQL Server 6.5 Service Pack 2 (SP2) |
| 6.50.213 | SQL Server 6.5 Service Pack 1 (SP1) |
| 6.50.201 | SQL Server 6.5 RTM |
Final Thoughts
The approach to use very much depends on preference as well as what is available. There are more ways to achieve the objective. One of them is the use of extended procedure xp_msver. We are unable to cover all methods hence we provide a set of approaches that should work in various conditions.
0 comment :