2012
SELECT 'SQL Server '
+ CAST(SERVERPROPERTY('productversion') AS VARCHAR)
+ '-'
+ CAST(SERVERPROPERTY('productlevel') AS VARCHAR)
+ ' ('
+ CAST(SERVERPROPERTY('edition') AS VARCHAR)
+ ')'
SQL Server 7.0, 2000, and 2005, running the following will extract ONLY the version information.
| SELECT LTRIM(RIGHT(LEFT(@@VERSION,38),9)) |
And the following query will work on SQL Server 2000 and up:
| SELECT ‘SQL Server ‘ + CAST(SERVERPROPERTY(‘productversion’) AS VARCHAR) + ‘ – ‘ + CAST(SERVERPROPERTY(‘productlevel’) AS VARCHAR) + ‘ (‘ + CAST(SERVERPROPERTY(‘edition’) AS VARCHAR) + ‘)’ |
SQL Server 2000 / 2005
If you are running SQL Server 2000, please see the new and dynamic SQL Server 2000 Build Chart.
If you are running SQL Server 2005, please see the following article: What version of SQL Server 2005 do I hav…
Want to find out who installed a specific HotFix? The following registry key will have a sub-key for each build number (e.g. there will be a key called “0818” for 8.00.818) with values for DateInstalled, InstalledBy, and KBArticle.
| HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer\HotFixes\ |
MSDE
If you are using MSDE, you can use osql (other products might tell you with less effort; see Article #2442).
| osql -E -q”SELECT @@VERSION” |
You may need to determine whether MSDE is running as a default instance or a named instance. Look in the Services applet and find services starting with MSSQLSERVER. For example, if you have a service named MSSQLSERVER$MYSERVER, you have a named instance called MYSERVER. So, you can use the following to return version information at a command prompt:
| osql -S”.\MYSERVER” -q”SELECT @@VERSION” |
To determine the service pack that’s installed on your SQL Server, open ISQLW (Query Analyzer) or ISQL or OSQL. Connect to your server. Execute the following command:
| SELECT @@VERSION GO |
The output of this command will be something like the one pasted below. The first line of the output displays the version number of the server. The last 3 digits (build number) of the version number are used to determine the service pack installed on your SQL Server. In this case 623.
Microsoft SQL Server 7.00 – 7.00.623 (Intel X86)
Nov 27 1998 22:20:07
Copyright (c) 1988-1998 Microsoft Corporation
Standard Edition on Windows NT 4.0 (Build 1381: Service Pack 6)
(1 row(s) affected)
|
SQL Server Version Table: |
No SP (RTM or Golden) |
SP1 | SP2 | SP3 / SP3a | SP4 | SP5 | SP5a |
| SQL Server 6.0 | 6.00.121 | 6.00.124 | 6.00.139 | 6.00.151 | |||
| SQL Server 6.5 (Hydra) |
6.50.201 | 6.50.213 | 6.50.240 | 6.50.258 | 6.50.281 | 6.50.415 | 6.50.416 |
| SQL Server 7.0 (Sphinx) |
7.00.623 | 7.00.699 | 7.00.842 | 7.00.961 | 7.00.1063 | ||
| SQL Server 2000 (Shiloh) |
8.00.194 | 8.00.384 | 8.00.534 | 8.00.760 | 8.00.2039 | ||
| SQL Server 2005 (Yukon) |
9.00.1399.06 |
Apart from SELECT @@VERSION, there are other commands too, that show you the build number. Try, sp_server_info and master..xp_msver. In SQL Server 2000, there is a new system function called SERVERPROPERTY, that returns service pack information. Here is an example:
| SELECT SERVERPROPERTY(‘ProductLevel’) GO |
What version of SQL Server 2005 do I have?
There are several different flavors of SQL Server 2005 floating around. Note that there is no longer going to be a push toward a milestone like “Beta 3,” so don’t be watching for it. Microsoft has adopted a CTP-only approach that will be in place for the remainder of the beta period. As for specific build numbers, I have tried to maintain a mapping table, and while it has all of the public releases, it only shows some of the versions that were distributed to smaller audiences, such as TAP customers and other early adopters.
Run the following query (which uses a CTE, for fun):
| WITH Version(ver) AS ( SELECT SUBSTRING ( @@VERSION, CHARINDEX(‘ – ‘, @@VERSION)+3, 32 ) ) SELECT Build = LEFT(ver, CHARINDEX(‘ ‘, ver)) FROM Version |
(Note that this query will not run on SQL Server 2000 or other previous versions.)
You should see a result from this table:
| Build | Explanation |
|---|---|
| 9.00.608 | Beta 1 |
| 9.00.645 | Internal build (???) |
| 9.00.747 | Internal build (IDW1) |
| 9.00.767 | Internal build (IDW2) |
| 9.00.790 | Internal build (IDW3) |
| 9.00.823 | Internal build (IDW4) |
| 9.00.836 | Express Edition Technical Preview |
| 9.00.844 | Internal build (???) |
| 9.00.849 | Internal build (???) |
| 9.00.852 | Beta 2 |
| 9.00.917 | Internal build (???) |
| 9.00.951 | October CTP Release |
| 9.00.981 | December CTP Release |
LINKS:
http://vyaskn.tripod.com/sqlsps.htm
http://sqlserver2000.databases.aspfaq.com/how-do-i-know-which-version-of-sql-server-i-m-running.html
http://support.microsoft.com/default.aspx?scid=kb;en-us;q321185
http://www.aspfaq.com/sql2005/show.asp?id=20
Home