find version & service packs

[rperez]

is there a way i can find out what version and what service pack my sql server
has? thanks in advance.

[Kristin Piskulic]

"rperez" <rperez@ihaveesp.com> wrote:
>
>is there a way i can find out what version and what service pack my sql
server
>has? thanks in advance.

[Kristin Piskulic]

"Kristin Piskulic" <kpiskulic@microcon.com> wrote:

Sorry about the blank response.

Simply type SELECT @@Version in the query analyzer

@@VERSION SQL Server Version
==============================

7.00.623 Original SQL Server 7.0 or MSDE 1.0 release
7.00.699 Database Components SP1
7.00.842 Database Components SP2

Hope this helps!
Kristin Piskulic

>
>"rperez" <rperez@ihaveesp.com> wrote:
>>
>>is there a way i can find out what version and what service pack my sql
>server
>>has? thanks in advance.
>

[Neil Pike]

Q. What SQL Server service pack am I running? How can I get new ones?

A. First get the version number in one of the following ways :-

- Execute "select @@version"
- Look at the top of the latest SQL errorlog file.
- Look at the file properties of the sqlservr.exe file.

All should return the same value, which can be compared with the table below.

For SQL Server OLAP Services, look on the About screen accessed from the Help
menu in OLAP Manager. (Make sure you select the OLAP help menu, not that of
Microsoft Management Console).

If it isn't listed here, then it is a "hot-fix" build that Microsoft PSS has
given you to fix a specific problem. Hot-fix releases are NOT regression
tested, so they should only be applied to production systems to fix a known
problem and under the advice of Microsoft PSS. Hot-fixes (password protected)
are on ftp://ftp.microsoft.com/bussys/sql/transfer. Microsoft PSS will provide
passwords as long as you prove you have the bug concerned. The reason for
restricting hot-fixes this way is so that PSS can track customers who run them
and let them know when a regression tested fix is available.

Service Packs can be downloaded from the Microsoft Download Center at
http://www.microsoft.com/downloads/search.asp or from the FTP site at
ftp://ftp.microsoft.com/bussys/sql/.

Note that you can't apply 6.5 SP4 to either SBS or EE versions of SQL Server.
SP5 and above are compliant with all these releases - i.e. can be applied to
any model of SQL Server (Workstation, Server, EE, SBS etc.)

8.00.047 SQL Server 8.0/2000 EAP4
8.00.078 SQL Server 8.0/2000 EAP5
8.00.100 SQL Server 8.0/2000 Beta 2
8.00.153 SQL Server 8.0/2000 EAP6

7.50.198 SQL Server 7.5/2000 Beta 1

7.00.517 SQL Server 7.0 Beta 3
7.00.583 SQL Server 7.0 RC1
7.00.623 SQL Server 7.0 "gold" release
7.00.677 MSDE from Office 2000
7.00.689 SQL Server 7.0 SP1 Beta
7.00.699 SQL Server 7.0 SP1
7.00.835 SQL Server 7.0 SP2 Beta
7.00.842 SQL Server 7.0 SP2

7.0.1073 SQL 7 OLAP "gold" release
7.0.1245 SQL 7 OLAP SP1
7.0.1325 SQL 7 OLAP SP2 Beta
7.0.1458 SQL 7 OLAP SP2

6.50.201 SQL Server 6.5 "gold" release.
6.50.213 SQL Server 6.5 with Service Pack 1
6.50.240 SQL Server 6.5 with Service Pack 2
6.50.252 SQL Server 6.5 with "bad" Service Pack 3 - SP3 was pulled and
re-issued. Do not use this version.
6.50.258 SQL Server 6.5 with Service Pack 3
6.50.259 SQL Server 6.5 on SBS only
6.50.281 SQL Server 6.5 with Service Pack 4
6.50.297 SQL Server 6.5 included in Site Server 3
6.50.339 SQL Server 6.5 "Y2K" Hot-fix
6.50.415 SQL Server 6.5 with Service Pack 5 - SP5 was re-issued as SP5a. Do
not use this version.
6.50.416 SQL Server 6.5 with Service Pack 5a

6.00.121 SQL Server 6.0 "gold" release.
6.00.124 SQL Server 6.0 with Service Pack 1
6.00.139 SQL Server 6.0 with Service Pack 2
6.00.151 SQL Server 6.0 with Service Pack 3

Here's an SP you can run (amended from an original by Jerry Spivey) to show the
version

drop PROC VERSION_INFO
go
CREATE PROC VERSION_INFO
AS
SET NOCOUNT ON
SELECT CONVERT(CHAR(25),@@SERVERNAME) AS 'SQL SERVER',
SUBSTRING(@@VERSION,23,4) AS 'PRODUCT VERSION',
SUBSTRING(@@VERSION,35,3) AS 'BUILD NUMBER',
CASE SUBSTRING(@@VERSION,35, 3)
WHEN '121' THEN 'NO SP'
WHEN '124' THEN 'SP1'
WHEN '139' THEN 'SP2'
WHEN '151' THEN 'SP3'
WHEN '201' THEN 'NO SP'
WHEN '213' THEN 'SP1'
WHEN '240' THEN 'SP2'
WHEN '252' THEN 'SP3 ** BAD **'
WHEN '258' THEN 'SP3'
WHEN '259' THEN 'SP3 + SBS'
WHEN '281' THEN 'SP4'
WHEN '297' THEN 'SP4 + SBS'
WHEN '339' THEN 'SP4 + Y2K'
WHEN '415' THEN 'SP5 ** BAD **'
WHEN '416' THEN 'SP5a'
WHEN '517' THEN 'Beta 3'
WHEN '583' THEN 'RC1'
WHEN '623' THEN 'NO SP'
WHEN '677' THEN 'MSDE (Office 2000)'
WHEN '689' THEN 'SP1 Beta'
WHEN '699' THEN 'SP1'
WHEN '835' THEN 'SP2 Beta'
WHEN '842' THEN 'SP2'
WHEN '198' THEN 'Beta 1'
WHEN '047' THEN 'EAP4'
WHEN '078' THEN 'EAP5'
WHEN '100' THEN 'Beta2'
WHEN '153' THEN 'EAP6'
ELSE 'Unknown - Must be a Hot-Fix version'
END AS 'SERVICE PACK'
go
EXEC VERSION_INFO

===

v1.22 2000.07.11
Applies to SQL Server versions : All
FAQ Categories : Installation and Upgrades
Related FAQ articles : n/a
Related Microsoft Kb articles : n/a
Other related information : n/a
Authors : Neil Pike

Neil Pike MVP/MCSE. Protech Computing Ltd
Reply here - no email
SQL FAQ (484 entries) see
http://forumsb.compuserve.com/gvforu...?SRV=MSDevApps
(faqxxx.zip in lib 7)
or http://www.ntfaq.com/Articles/Index....partmentID=800
or www.sqlserverfaq.com
or www.mssqlserver.com/faq

[Syed]

"Kristin Piskulic" <kpiskulic@microcon.com> wrote:
>
>"rperez" <rperez@ihaveesp.com> wrote:
>>
>>is there a way i can find out what version and what service pack my sql
>server
>>has? thanks in advance.
>


Use the following in your SQL Query Analyzer.

Select @@Version

[Vinay_Prasad]

Try this :

SELECT SERVERPROPERTY('productversion'), SERVERPROPERTY ('productlevel'), SERVERPROPERTY ('edition')