There are times when we have to look for service pack installed on SQL Server Database Servers. SQL server provides us with many different ways to check service pack installed. Some of most commonly used methods are given below

1). @@version

Using @@version we can see information about SQL server installed on machine

The syntext are

Print @@version

The result set comes as something like this

Microsoft SQL Server 2005 – 9.00.3068.00 (Intel X86)

Feb 26 2008 18:15:01

Copyright (c) 1988-2005 Microsoft Corporation

Developer Edition on Windows NT 5.1 (Build 2600: Service Pack 2)

The informations shows that the machine is running SQL server 2005 on 32 bit processor (Intel X86) with windows xp as operating system (windows NT 5.1) and service pack 2 installed.

2). serverproperty (‘productlevel’)

This is the best way to find service pack installed because it gives to the point information for what we have enquired. The syntax is

print cast(serverproperty(‘productlevel’) as nvarchar)

Productlevel attribute of serverproperty construct actully is used to find the service pack installed we have just cast the result tinto nvarchar so that we can print it to result window.

3).Through SQL Server Configuration Manager

This is my favorite one , because through this way one should not have o write any line of code to get the service pack installed . I like it because some times I have to tell this to system administrators also and they don’t like working inside the management studio. They need a way through which they can find the service pack without making any connection to sql server ot we can say with out writing any code. The steps to find service pack through configuration manager is as below

• Open sql server configuration manager
• Select sql server 2005 services
• On right side select the sql server service
• Right click on SQL service and select properties
• From dialog box open, select advanced tab
• And look for “service pack level” in right pane of grid control.

Also there is internal version numbers which are associated with each release of SQL server

Some of them are given below