A. SP5 was released on 24th December 1998. It was re-issued as SP5a on 26th January to fix a problem with 603's on loading dumps. Make sure you apply SP5a (build 416) and not SP5 (build 415). If you are already on SP5 (415) then the only files you need to update are sqlservr.exe (and .dbg) and opends60.dll (and .dbg).

SP5a has undergone more testing and beta releases than any other service-pack because it offers Y2K compliance. However, it contains a LOT of fixes and therefore new or regressed bugs are inevitable. Known regressions are listed at the bottom of the article.

Due to the number of fixes it has gone through a lot more build numbers than previous releases have - though in this case about 50 of the builds should be ignored as Microsoft jumped a load due to internal source release issues. The fixlist is pointed to at the bottom of this message.

For any new release/servicepack you are advised to apply and test on test systems first, and if at all possible do a stress-test so that any problems with blocking/locks are found before rolling out to a production system.

All service-packs come with problems, however in most cases they fix a lot more problems than they cause. 99% of bugs you may find in SP5a will be present in the gold release and all previous service packs as well - they simply aren't fixed in SP5a.

Should you apply it? As it gives Y2K compliance then the answer is probably yes. But see the list of known regressions below and also check the Microsoft Kb - http://support.microsoft.com. Search on SQL AND KBBUG AND SP5. If you don't want to apply SP5a because of one of these bugs then either wait for SP6 (date unknown) or contact Microsoft PSS about availability/stability of hot-fixes. (There are FAQ articles on MSPSS and HOTFIXES)

SP5a is available via the downloads option from http://support.microsoft.com. Also ftp direct from 

Service Pack 5a - Known Bugs/Regressions

These are problems that occur in SP5a but do not occur in SP4 or below.

1. Error 4409 when selecting on a view. Hot-fix build 422 available.

create table MyTable
ORDER_NUM numeric(15,4) NOT NULL

create view MyView as
select t1.ORDER_NUM from MyTable t1
select t1.ORDER_NUM from MyTable t1

select *

2. 605 errors in tempdb. Script below to repro. No fix at the moment.

create view VIEW_CRASH_TEST as
select NUMBER = 1
select NUMBER = CONVERT(numeric(19, 4), 0.0)

select NUMBER
where NUMBER <> 0.0

3. SP5(a) treats all columns in an inserted row as updated for the purposes of checking in a trigger. See Q216700
for more info. Fixed in hot-fix build 422.

4. You may get access violations caused by high stress tempdb deadlocking and attention signals. Errors 603 and 803 are symptomatic. It is fixed in build 437. See Q231323 for more info.

5. Under certain conditions dynamic cursors may go into an infinite loop with SP5a. Putting SP5 or below back on the machine without changing the data fixes the problem. Fixed post SP5a - doesn't occur in build 440 for example. Or make the cursor INSENSITIVE which fixes it.

6. User doesn't have permission to call sp_b directly but calls it via sp_a. This used to work fine, but with SP5a if a user calls sp_b directly and rightly fails they then get access denied to sp_a on all subsequent calls. Fixed in build 446.

7. If you're using a 4mm DAT tape device you may experience errors unless you have the 4mmdat.sys from NT 4.0 SP4 or above applied. SP5 makes a call to a function in the driver that isn't there in earlier versions.

8. When getdate() is used in a cursor with WHERE CURRENT OF you will get "Jan 1 1900" as a date. This is fixed in a post-SP5 build - it works ok on build 452.

Service Pack 5a - Possible Issues

These are issues that haven't been confirmed :-

1. User Comment : "I upgraded from SP4 to SP5(a) over the weekend and and am running into fatal blocking problems all over the place in an app/database that's been running for years..."

Reply : Microsoft have applied several fixes in SP5(a) where table locks have been taken/kept where they weren't needed. These have been removed which should improve concurrency/throughput. However, on systems that don't use best practices to reduce locking problems, the extra page level contention could cause extra blocking/deadlocks.

2. Reports of index corruptions.

3. Incorrect results from count(*) as opposed to select * in view with multiple unions.

4. Stored procedures returning incorrect results in low memory situations.

Microsoft SQL Server 6.5 Service Pack 5a Fixlist

See Q197174 for the official fix-list.

One that isn't documented in this release is the fact that SP5 offers Win2000 compliance - previous versions of SQL Server had problems with DUMP and LOAD DATABASE commands under NT5/Win2000 beta's.

Another is that ADO had problems resulting in "invalid token" errors due to it's use of an undocumented browsetable command. This has now been fixed.