I haven't seen complaints about SP5\a for SQL 6.5.
Has anyone run into problems with it?
Stable or steer clear?
> I haven't seen complaints about SP5\a for SQL 6.5.
> Has anyone run into problems with it?
> Stable or steer clear?
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).
If you aren't having any problems with your system then don't apply SP5a for 3 months after it's release - this article will be kept updated with any reported
problems/regressions with the service pack. After that then yes, apply it, as it is required for Y2K compliance.
If you ARE having problems with corruptions, AV's, hangs etc. then yes, SP5a is worth a try straight away as it has a LOT of fixes in for these sorts of problems.
SP5a has undergone more testing and beta releases than any other service-pack because it offers Y2K compliance. It should therefore be very stable.
It has also gone through a lot more builds than previous releases have - though in this case about 50 of the builds should be ignored as MS jumped a load due to internal source
release issues. The fixlist is copied 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 - they are un-fixed/unknown bugs that are present in every release.
SP5a is available via the downloads option from http://support.microsoft.com. Also ftp direct from
Service Pack 5a - Known Bugs/Regressions
1. Error 4409 when selecting on a view. Hot-fix available.
create table MyTable
X int NOT NULL,
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
from MyTable T1 INNER JOIN MyView T2 ON T1.ORDER_NUM = T2.ORDER_NUM
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)
where NUMBER <> 0.0
Service Pack 5a - Possible Issues
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
Reply : MS 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. SP5(a) now treats all columns in an inserted row as updated for the purposes of checking in a trigger. This is the same as SQL 7 but different from 6.5 SP4 and earlier
behaviour. More of a fix/feature rather than a bug, but be aware if you do these checks.
Microsoft SQL Server 6.5 Service Pack 5 Fixlist
The fixes noted are all the public ones that MS acknowledges - there are always more fixes in every service pack than MS have publicly documented (for whatever reason).
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.
** MS Supplied text follows
The following is a list of fixes and various other improvements that have been made in the Microsoft SQL Server version 6.5 Service Pack 5. For more information, contact your
primary support provider.
Please note that workarounds described in these articles have been provided for your information only. It is not necessary to implement these workarounds if you have the
For the most up to date information about fixes in this service pack, please see the following article in the Microsoft Knowledge Base:
TITLE : FIX: SQL Server 6.5 Service Pack 5 Fixlist
LIST OF PROBLEMS CORRECTED IN SERVICE PACK 5
FIX: Security Mgr w/ Long Group Names Can Cause SQL Shutdown (Q147830)
FIX: Mirror Enabled After Losing Primary Device (Q150458)
FIX: Query Times Out Too Early Under Multiprotocol Net-Library (Q152062)
FIX: sproc Created but No Code in syscomments (Q154001)
FIX: No Information Returned by xp_logininfo (Q155556)
FIX: Perfmon Terminates While Monitoring SQL Procedure Cache (Q155766)
FIX: Problem Using Xp_readerrorlog When Reading Large Errorlog (Q157804)
FIX: DYNAMIC Cursor Fails to Release Sh_Page Lock (Q159042)
FIX: Xp_cmdshell Run by Non-SA Causes Error 1326 (Q159221)
FIX: Multiplying Numeric Datatypes May Yield Lower Precision (Q163069)
FIX: Views Containing a UNION Resolve with Materializing Model (Q163082)
FIX: Dump/Load May Fail to Work with Older Tape Drives and SCSI (Q163152)
FIX: Insert/Update May Cause Client to Stop Responding (Q164690)
FIX: Unhandled Exception in SQL Server Using Keyset Cursors (Q166233)
FIX: Striped Dumps May Cause Error 18001 (Q166387)
FIX: Dump File Handle Not Released After Error on Load Table (Q166758)
FIX: LE Threshold Config. Values Do Not Take Effect Immediately (Q167603)
FIX: Cannot Load a Table If the Owner Is Not SA or DBO (Q169492)
FIX: Wrong Join Plan Selected That Causes Excessive Reads (Q170295)
FIX: Deadlock on Table with Text/Image Column May Cause Errors (Q170296)
FIX: Handled Exception Error Caused by Server Side Cursors (Q170616)
FIX: Higher Cost Executing Query on View with Subquery (Q170803)
FIX: Update View with Inner Join from Cursor Causes AV (Q170994)
FIX: CmdExec May Cause Exception Violation on Scheduled Tasks (Q171323)
FIX: Shared Intent Lock Acquired by READTEXT Are Not Released (Q171867)
FIX: Infinite Loop When UPDATE of a Cursor with WHERE CURRENT OF (Q172309)
FIX: AV Querying a View w/ CASE WHEN EXISTS and FORCEPLAN Is ON (Q172571)
FIX: Managing Permissions Fails w/Err 21770 w/ User-Owned Tables (Q173568)
FIX: Dumping to a DLT Tape Drive May Fail to Span Tapes (Q174483)
FIX: Concurrent Xp_makewebtask May Cause an Access Violation (Q174853)
FIX: Problems with Server Cursors on a Stored Procedure (Q175126)
FIX: If ANSI_DEFAULTS On, Join Sequences in St. Procs Cause AV (Q175142)
FIX: Force Index Option Fails with Temporary Tables in St. Procs (Q175359)
FIX: Rebuilding Indexes w/ SQLMaint May Cause Incorrect Database (Q175789)
FIX: Primary Key Violation Causes AV, Text Field Involved (Q176092)
FIX: Double-Byte Character Set Insertion May Fail w/ Error 2627 (Q176479)
FIX: Err 1203 w/ SELECT ORDER BY DESC After an UPDATE in a Tran (Q176491)
FIX: Msdb..Syshistory Rows May Be Incorrectly Deleted (Q176684)
FIX: DELETE Statement May Cause Server to Become Unresponsive (Q176999)
FIX: Delete Using EXISTS & Subquery Doesn't Escalate Page Lock (Q177114)
FIX: SELECT Into Variable with NO_BROWSETABLE Fails (Q178366)
FIX: Regression Old Syntax Outer Join May Produce Error 613 (Q178443)
FIX: Concurrent Distribution Tasks May Cause an Unhandled Error (Q178473)
FIX: UPDLOCK Locking Option Sets Only Shared Lock with sp_cursor (Q179222)
FIX: Long Comment in .SQC File May Cause AV in Nsqlprep.exe (Q179283)
FIX: 1203 If Attentions During Maintenance to Nonclustered Index (Q179924)
FIX: INSERTs into Temp Tables w/Identity May Cause Deadlock (Q180101)
FIX: SELECT INTO Temp Tables w/Identity Columns May Cause Errors (Q180102)
FIX: Database Fallback May Cause Dbid to Change (Q180603)
FIX: Xp_makewebtask Procedure May Cause Access Violation (Q180778)
FIX: Queries Using Derived Tables or GROUP BY May Cause an AV (Q181048)
FIX: INSERT..SELECT with Ignore_Dup_Row Clustered Index Stops (Q181244)
FIX: Writelog Timeout When Replicating Cross-DB Transactions (Q181557)
FIX: SQL Server Cluster Setup May Fail on Third-Party Disk Drive (Q184008)
FIX: Repeated DBCC DBREINDEX Causes Stack Overflow Error (Q184478)
FIX: INSERT..EXEC with RPC May Cause Access Violation, Msg 806 (Q184494)
FIX: Intstdist.sql Updates MSjob_commands Incorrectly (Q184882)
FIX: SQL Server Service Stopped When IsAlive Fails to Connect (Q185806)
FIX: Delete Where Current of Cursor Causes Server Instability (Q185964)
FIX: Query Cancellation May Fail If No Delay After Query (Q185965)
FIX: Duplicate Jobs Added to the MSjobs Tables (Q186001)
FIX: Problems with Network Error During dbwritetext/dbmoretext (Q186916)
FIX: Large Query Text from Socket Client May Cause ODS AV (Q187278)
FIX: Large Number of Client Disconnects May Cause AV in ODS (Q187370)
FIX: RPC Returns Error 7222 When Re-executed After Canceled RPC (Q187857)
FIX: INSERT SELECT Statement Does Not Release Shared Intent Lock (Q188034)
FIX: AV in Cursoropen() When Using 'WHERE CURRENT OF' and Cancel (Q189029)
FIX: Shared Intent Locks Held After Validating Constraint (Q189098)
FIX: ORDER BY Queries May Remove Spaces from ANSI_PADDING Tables (Q189343)
FIX: Msg 267 and 202 on Stored Proc If Table Dropped/Re-created (Q192829)
FIX: Database Dumps May Be Overwritable Despite Options (Q192952)
FIX: Expired Dump Media Cannot Be Overwritten After 12/31/99 (Q192953)
FIX: Add New Task in Task Manager Does Not Allow Date 2/29/2000 (Q192955) ...
read more »
Does this mean that insert triggers do not fire any
This is referring to the IF UPDATE(column_name) condition.
In an update trigger, IF UPDATE is true only for column explicitly mentioned
in the SET clause.
In an insert trigger, you can also use IF UPDATE. Prior to SP5, IF UPDATE
was true only for those column that were explicitly given a value in the
values clause, or that had a default defined. If a column defaulted to NULL
in the insert statement, IF UPDATE would not be true for that column. In
SQL6.5sp5 and SQL 7, IF UPDATE is true for all columns in an update trigger,
so it's basically not useful.
If you've never used IF UPDATE in an insert trigger, you don't have anything
to worry about.
MCSE, SQL Server MCT, MVP
(Please do not send email.
Post all followups to the newsgroups.)
>Does this mean that insert triggers do not fire any
I'm currently doing a full system stress/compatibility test on one of our
big systems (15GB, 700 user connections), this currently resides on 3.51 and
6.5 SP4, I'm moving it to a bigger machine running NT4 SP4 and 6.5 SP5A. So
far so good, just one problem with ALTER table thats come to light so far,
this involves multiple connections running non-stored procedure SQL, with
the ALTER table blocking the users, the connections fail after the alter has
finished with msg 225.
I'll post back here at the end of this coming week with my recommendation.
Hope the above helps.
Tony Rogerson MCP, MS SQL Server MVP
Torver Computer Consultants Ltd
>Has anyone run into problems with it?
>Stable or steer clear?
I ran into a lot of problems that mainly have to do with the use of views
which contain unions. The biggest problem I encounter is MSG 4409 (See all
my posts in this newsgroup).
My suggestion therefore: don't install unless you run into problems with SP4
(see also Neil's reply).
>Has anyone run into problems with it?
>Stable or steer clear?
Who ever came up with this "Program Files" nonsense???
>it wont even install if you have any spaces in the path to the SP5
You'll have to contact MS PSS.Quote:> I just applied sp5a, error 603 is still alive and well... I can not get any
> backups made after the sp5 installation work. Any ideas???
Q. Who are Microsoft Product Support Services and how do I contact them?
A. These are people who support all MS's products, write fixes, take the calls, sort out workarounds etc. They work 24x7x365. The only way to contact them to place an
initial call is by phone (unless you have a premier support contract in which case there are web based methods). For details of your nearest PSS support centre go to.
All calls are chargeable either to your credit card or an existing account. Your call fee WILL be re-imbursed if the problem turns out to be an MS bug, or a feature that
hasn't been publicly documented. The person answering the phone cannot know it's a bug, so they have to take details of your credit card before passing you on to a technician.
When the call is closed the technician decides whether to mark the call as "free" in which case you get a refund automatically.
Neil Pike MVP/MCSE. Protech Computing Ltd
(Please post ALL replies to the newsgroup only unless indicated otherwise)
For SQL FAQ entries see www.ntfaq.com/sql.html
and GO MSSQL Lib 1 on Compuserve
Has anyone experienced weird results mixing SP1 machines? I installed SP1
on my machine that I use to remotly administer my OLAP server, then
installed SP1 on my OLAP server. I had intermittant problems connecting to
the OLAP server and building/scheduling cubes, even after SP1 was installed
on both machines.
I eventually backed off and uninstalled MS OLAP Services on both machines
and then re-installed OLAP without SP1. Since then I've had problems with
corrupt cubes and botched builds. I'm afraid to re-install SP1, but I don't
know what else to do. It's a production SQL 6.5 server, so if I hose the
machine I'm toast.
Any ideas or similar horror stories?