Service Pack Identification
Description
T-SQL script for MS SQL Server, displays information about the current service pack and server version. Allows
automation of a tedious process - the script is already coded to knows which version strings match up to which
service pack releases for versions 6.0, 6.5, 7.0 and 2000 of MS SQL Server.
How many times have you been about to install a SQL Server patch only to find that you need to have at least SPx
installed on your server? What usually follows is a mad dash to figure out how you can figure that out, normally
going through the SQL Server books online, MSDN and finally the last service pack documentation. Let's be honest it's
a pain since the only way to work it out involves turning the obscure number
@@VERSION produces and
looking it up against a table of known version numbers.
After my last dash for this information I decided to build it into a SQL script so that I could just query any server
I needed to and it would return meaningful information about the version of SQL Server it was running, the level of service
pack which had been installed and possibly any major patches or roll-ups. The advantage to this system is that it
never forgets where it left the documentation and it never gets tired and ends up reading the numbers wrong, it
isn't rocket science but it makes maintaining multiple SQL Servers a lot easier.
Currently the script supports all versions from 6.0 through to 2000 with all the official service packs listed for each
version.
Requirements
An accessible installation of either;
- MS SQL Server 6.0 (Pre-release through to Service Pack 3 / SP3)
- MS SQL Server 6.5 (Pre-release through to Service Pack 5a / SP5a)
- MS SQL Server 7.0 (Pre-release through to Service Pack 4 / SP4)
- MS SQL Server 2000 (Pre-release through to Service Pack 3 / SP3)
Single Compressed Download
Individual Components
Installation & Setup
-
Save the T-SQL script somewhere which will be easily accessible to you via the query analyser, naming
it sql_spversion.sql (as a suggestion).
User Guide
Execute the script through your query analyser.
The results window should display a text message outlining the version of SQL Server running and
the level of service pack installed if known. If your version number falls outside the pre-defined
list of service pack version numbers (due to manual patching etc) then you'll be told where your
version number falls ie. "between SP1 and SP2" if you're between two known versions or "above SP5"
if your version number falls outside our list (due to manual patching or because we don't have the
latest service pack listed yet).
External Links
The complete documentation for SQL Server version identification can be found in
KB article q321185.