{"id":191,"date":"2006-11-15T17:50:04","date_gmt":"2006-11-15T16:50:04","guid":{"rendered":"http:\/\/www.galhano.com\/blog\/?p=191"},"modified":"2016-01-22T13:02:15","modified_gmt":"2016-01-22T13:02:15","slug":"como-saber-que-versao-do-sql-server-estou-a-correr","status":"publish","type":"post","link":"http:\/\/galhano.com\/blog\/?p=191","title":{"rendered":"Como saber que vers\u00c3\u00a3o do SQL Server estou a correr?"},"content":{"rendered":"

2012<\/p>\n

[sql]<\/p>\n

SELECT ‘SQL Server ‘
\n+ CAST(SERVERPROPERTY(‘productversion’) AS VARCHAR)
\n+ ‘-‘
\n+ CAST(SERVERPROPERTY(‘productlevel’) AS VARCHAR)
\n+ ‘ (‘
\n+ CAST(SERVERPROPERTY(‘edition’) AS VARCHAR)
\n+ ‘)’<\/p>\n

[\/sql]<\/p>\n

SQL Server 7.0, 2000, and 2005,<\/span><\/span> running the following will extract ONLY the version information.
\n<\/span><\/p>\n\n\n\n
SELECT LTRIM(RIGHT(LEFT(@@VERSION,38),9))<\/span><\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n


\nAnd the following query will work on SQL Server 2000 and up:
\n<\/span><\/p>\n\n\n\n
SELECT ‘SQL Server ‘
\n+ CAST(SERVERPROPERTY(‘productversion’) AS VARCHAR) + ‘ – ‘
\n+ CAST(SERVERPROPERTY(‘productlevel’) AS VARCHAR) + ‘ (‘
\n+ CAST(SERVERPROPERTY(‘edition’) AS VARCHAR) + ‘)’<\/span><\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n


\n<\/span> SQL Server 2000 \/ 2005<\/span><\/strong> <\/span><\/p>\n

If you are running SQL Server 2000, please see the new and dynamic SQL Server 2000 Build Chart<\/a>.<\/span><\/p>\n

If you are running SQL Server 2005, please see the following article: What version of SQL Server 2005 do I hav…<\/a><\/span><\/p>\n

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.<\/span><\/p>\n\n\n\n
HKEY_LOCAL_MACHINE\\SOFTWARE\\Microsoft\\MSSQLServer\\HotFixes\\<\/span><\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n


\nMSDE<\/strong> <\/span><\/span><\/span><\/p>\n

If you are using MSDE, you can use osql (other products might tell you with less effort; see Article #2442<\/a>).
\n<\/span><\/p>\n\n\n\n
osql -E -q”SELECT @@VERSION”<\/span><\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n


\nYou 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:
\n<\/span><\/p>\n\n\n\n
osql -S”.\\MYSERVER” -q”SELECT @@VERSION”<\/span><\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n

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:<\/span><\/p>\n\n\n\n
SELECT @@VERSION
\nGO<\/span><\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n

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.<\/span><\/p>\n

Microsoft SQL Server 7.00 – 7.00.623 (Intel X86)
\nNov 27 1998 22:20:07
\nCopyright (c) 1988-1998 Microsoft Corporation
\nStandard Edition on Windows NT 4.0 (Build 1381: Service Pack 6)<\/span><\/p>\n

(1 row(s) affected)
\n<\/span><\/p>\n\n\n\n\n\n\n\n\n
\n

SQL Server Version Table:<\/span><\/span><\/strong><\/p>\n<\/td>\n

No SP
\n(RTM or Golden)<\/span><\/td>\n		SP1<\/span><\/td>\nSP2<\/span><\/td>\nSP3 \/ SP3a<\/span><\/td>\nSP4<\/span><\/td>\nSP5<\/span><\/td>\nSP5a<\/span><\/td>\n<\/tr>\n
SQL Server 6.0<\/span><\/td>\n 6.00.121<\/span><\/td>\n 6.00.124<\/span><\/td>\n 6.00.139<\/span><\/td>\n 6.00.151 <\/span><\/td>\n<\/td>\n<\/td>\n<\/td>\n<\/tr>\n
SQL Server 6.5
\n(Hydra)<\/span><\/td>\n		 6.50.201<\/span><\/td>\n 6.50.213<\/span><\/td>\n 6.50.240<\/span><\/td>\n 6.50.258<\/span><\/td>\n 6.50.281<\/span><\/td>\n 6.50.415<\/span><\/td>\n 6.50.416 <\/span><\/td>\n<\/tr>\n
SQL Server 7.0
\n(Sphinx)<\/span><\/td>\n		 7.00.623<\/span><\/td>\n 7.00.699<\/span><\/td>\n 7.00.842 <\/span><\/td>\n 7.00.961<\/span><\/td>\n 7.00.1063<\/span><\/td>\n<\/td>\n<\/td>\n<\/tr>\n
SQL Server 2000
\n(Shiloh)<\/span><\/td>\n		 8.00.194<\/span><\/td>\n 8.00.384<\/span><\/td>\n 8.00.534<\/span><\/td>\n 8.00.760<\/span><\/td>\n 8.00.2039<\/span><\/td>\n<\/td>\n<\/td>\n<\/tr>\n
SQL Server 2005
\n(Yukon)<\/span><\/td>\n		 9.00.1399.06<\/span><\/td>\n<\/td>\n<\/td>\n<\/td>\n<\/td>\n<\/td>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n

Apart from <\/span> SELECT @@VERSION<\/span>, there are other commands too, that show you the build number. Try, <\/span> sp_server_info<\/span> and <\/span>master..xp_msver<\/span>. In SQL Server 2000, there is a new system function called <\/span> SERVERPROPERTY<\/span>, that returns service pack information. Here is an example:
\n<\/span><\/p>\n\n\n\n
SELECT SERVERPROPERTY(‘ProductLevel’)
\nGO<\/span><\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n

What version of SQL Server 2005 do I have?<\/span><\/span><\/strong><\/span>
\n<\/span><\/span>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. <\/span>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. <\/span><\/p>\n

Run the following query (which uses a CTE, for fun): <\/span><\/p>\n\n\n\n
WITH Version(ver)
\nAS
\n(
\nSELECT SUBSTRING
\n(
\n@@VERSION,
\nCHARINDEX(‘ – ‘, @@VERSION)+3,
\n32
\n)
\n)
\nSELECT Build = LEFT(ver, CHARINDEX(‘ ‘, ver))
\nFROM Version<\/span><\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n

(Note that this query will not run on SQL Server 2000 or other previous versions.) <\/span><\/p>\n

You should see a result from this table: <\/span><\/p>\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n
Build<\/span><\/th>\nExplanation<\/span><\/th>\n<\/tr>\n
9.00.608<\/span><\/td>\nBeta 1<\/span><\/td>\n<\/tr>\n
9.00.645<\/span><\/td>\nInternal build (???)<\/span><\/td>\n<\/tr>\n
9.00.747<\/span><\/td>\nInternal build (IDW1)<\/span><\/td>\n<\/tr>\n
9.00.767<\/span><\/td>\nInternal build (IDW2)<\/span><\/td>\n<\/tr>\n
9.00.790<\/span><\/td>\nInternal build (IDW3)<\/span><\/td>\n<\/tr>\n
9.00.823<\/span><\/td>\nInternal build (IDW4)<\/span><\/td>\n<\/tr>\n
9.00.836<\/span><\/td>\nExpress Edition Technical Preview<\/span><\/td>\n<\/tr>\n
9.00.844<\/span><\/td>\nInternal build (???)<\/span><\/td>\n<\/tr>\n
9.00.849<\/span><\/td>\nInternal build (???)<\/span><\/td>\n<\/tr>\n
9.00.852<\/span><\/td>\nBeta 2<\/span><\/td>\n<\/tr>\n
9.00.917<\/span><\/td>\nInternal build (???)<\/span><\/td>\n<\/tr>\n
9.00.951<\/span><\/td>\nOctober CTP Release<\/span><\/td>\n<\/tr>\n
9.00.981<\/span><\/td>\nDecember CTP Release<\/span><\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n

LINKS<\/strong>:<\/p>\n

http:\/\/vyaskn.tripod.com\/sqlsps.htm<\/a><\/p>\n

http:\/\/sqlserver2000.databases.aspfaq.com\/how-do-i-know-which-version-of-sql-server-i-m-running.html<\/a><\/p>\n

http:\/\/support.microsoft.com\/default.aspx?scid=kb;en-us;q321185<\/a><\/p>\n

http:\/\/www.aspfaq.com\/sql2005\/show.asp?id=20<\/a><\/p>\n


\n<\/span><\/p>\n","protected":false},"excerpt":{"rendered":"

2012 [sql] SELECT ‘SQL Server ‘ + CAST(SERVERPROPERTY(‘productversion’) AS VARCHAR) + ‘-‘ + CAST(SERVERPROPERTY(‘productlevel’) AS VARCHAR) + ‘ (‘ + CAST(SERVERPROPERTY(‘edition’) AS VARCHAR) + ‘)’ [\/sql] 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: […]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"close","ping_status":"close","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[9],"tags":[],"class_list":["post-191","post","type-post","status-publish","format-standard","hentry","category-sql-server","author-admin"],"_links":{"self":[{"href":"http:\/\/galhano.com\/blog\/index.php?rest_route=\/wp\/v2\/posts\/191","targetHints":{"allow":["GET"]}}],"collection":[{"href":"http:\/\/galhano.com\/blog\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"http:\/\/galhano.com\/blog\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"http:\/\/galhano.com\/blog\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"http:\/\/galhano.com\/blog\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=191"}],"version-history":[{"count":1,"href":"http:\/\/galhano.com\/blog\/index.php?rest_route=\/wp\/v2\/posts\/191\/revisions"}],"predecessor-version":[{"id":2002,"href":"http:\/\/galhano.com\/blog\/index.php?rest_route=\/wp\/v2\/posts\/191\/revisions\/2002"}],"wp:attachment":[{"href":"http:\/\/galhano.com\/blog\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=191"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/galhano.com\/blog\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=191"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/galhano.com\/blog\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=191"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}