I am looking at the list of version numbers from this page: Microsoft SQL Server Version List, and here's an excerpt:
9.00.3182 2005.90.3182.0 940128 FIX: You receive error 8623 when you run a complex query in SQL Server 2005 August 3, 2007
9.00.3179 2005.90.3179.0 938243 FIX: Error message when you run a full-text query against a catalog in SQL Server 2005: "The execution of a full-text query failed. The content index is corrupt." July 30, 2007
9.00.3178 2005.90.3178.0 938086 FIX: A SQL Server Agent job fails when you run the SQL Server Agent job in the context of a proxy account in SQL Server 2005 August 22, 2007
(note, the question is not about what I should install for SQL Server 2005, it's about that list, and the hotfix version numbers)
Since all of these 3 are hotfixes, if my SQL Server reports version 9.00.3182 (the topmost, latest hotfix, of the 3 I extracted), does that imply that the previous 2 has been installed as well?
I see that a lot of later hotfixes or patches are named "Cumulative update package". Is that just a naming convention or was the earlier ones that aren't named like that individual hotfixes?
Concrete question example. With the above 3 hotfixes, and SQL Server reporting version number 9.00.3182, which of the 3 scenarios below is the most correct one:
- All three hotfixes have been applied.
- Hotfix 9.00.3182 has been applied, but definitely not the other 2.
- Hotfix 9.00.3182 has definitely been applied, there simply isn't enough information available to determine if the other 2 have been applied or not.
The reason I am interested in this is that I'm creating a class library for .NET for reporting what is installed on the SQL Server, since there isn't a single week where someone doesn't ask how to check for a specific .NET version, Windows version, or SQL Server version on Stack Overflow. I thought I at least, for my own part, that I should do enough research to be able to build a class library that can answer it, and thus have a definite answer to give for those questions.
But from the list on that page, given a hotfix KB940545, which SQL Server will report as 9.00.3186, my question is thus if the version number is above that, can I report that KB940545 has been installed (either specifically, or as part of later hotfixes/patches), or is it simply not possible to know this information?
Best Answer
Unless you have access to the log files for every cumulative update install, the only thing you would know is #3:
Cumulative updates are, well, cumulative. So are the builds within a CU branch. So 9.00.3182 will contain the fixes from 3179, 3178, 3165, 3122, 3068, etc. Microsoft used to release certain critical fixes individually, but now they have almost completely stopped doing that, opting for a ~60 day Cumulative Update delivery model (security fixes excepted).
The challenge comes when you pass a service pack boundary. There are cases where a fix is released in a cumulative update right before a service pack is released. The service pack code path was frozen at least 60 days earlier (since SPs go through more rigorous testing/regression etc). So, the service pack - even though released later - is missing fixes that won't be in that branch until the first cumulative update for that service pack. This is why you will often see a CU released out of band, almost immediately after a service pack is released.
Then, of course, there is the situation where multiple branches are being maintained, and multiple CUs come out for the lower service pack. There will be fixes there which weren't even known issues when the newer service pack was released.
This means that in many cases a newer build (say, 2005 SP4, 9.00.5000) is missing fixes from cumulative updates for SP3 (say, CU15, which was released several months after SP4). All of those fixes are usually also available in subsequent CUs for the later service pack, but just looking at version number alone wouldn't really tell you that unless you knew which fixes you are trying cross-reference. You'd need a database for that, not a numeric/string compare.
You're kind of playing with fire here, anyway. You're running SQL Server 2005, and not even on the most recent service pack. Unless you have gone through extended support, you are completely on your own. In fact 2008 and 2008 R2 both hit end of mainstream support in about 60 days.
My philosophy is to always be on the absolute latest service pack for any version I am running, and unless there is something prohibitive, always be on the latest CU for that service pack as well. So, rather than worry about whether the fixes for 3179 or 3178 were installed independently, I'd focus more on getting onto a more modern build (SP4 + MS12-070, putting you at 9.00.5324), or a more modern version (and one that isn't about to sunset).