SQL 6.5 SP5a Horror Stories...? 
Author Message
 SQL 6.5 SP5a Horror Stories...?
I haven't seen complaints about SP5\a for SQL 6.5.

Has anyone run into problems with it?

Stable or steer clear?



Wed, 08 Aug 2001 03:00:00 GMT
 SQL 6.5 SP5a Horror Stories...?

I don't do a whole lot with SQL, but my only experience with SP5 isn't
good. I had a relatively complex series of views (of views of views
etc.), and another view which simply counted the number of rows in the
final view (plus summed 1 column). Worked pre-SP5, doesn't work
post-SP5. Haven't spent that much time tracking it down yet (it was
easier to dump the data into a temporary table as a work-around), but my
suspicion is that it may have something to do with views containing
unions.

Quote:

> I haven't seen complaints about SP5\a for SQL 6.5.

> Has anyone run into problems with it?

> Stable or steer clear?

--
Douglas J. Steele
Registrar, North York Cosmos Soccer Club
P.O. Box 705
Don Mills, ON  M3C 2T6
(416) 441-1624
http://www.vex.net/cosmos/


Thu, 09 Aug 2001 03:00:00 GMT
 SQL 6.5 SP5a Horror Stories...?

Quote:
> I haven't seen complaints about SP5\a for SQL 6.5.

Q.  Should I apply SP5?  Are there any known issues?
(v1.11 1999.02.16)

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
ftp://ftp.microsoft.com/bussys/sql/public/fixes/usa/SQL65/Sp5/

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
)
go

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

select *
from MyTable T1 INNER JOIN MyView T2 ON T1.ORDER_NUM = T2.ORDER_NUM
go

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

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

select NUMBER
from VIEW_CRASH_TEST
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
years..."

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
updated software.
For the most up to date information about fixes in this service pack, please see the following article in the Microsoft Knowledge Base:

ARTICLE-ID: Q197174
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 »



Thu, 09 Aug 2001 03:00:00 GMT
 SQL 6.5 SP5a Horror Stories...?
"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"

Does this mean that insert triggers do not fire any
more?

Martin Sykes



Thu, 09 Aug 2001 03:00:00 GMT
 SQL 6.5 SP5a Horror Stories...?
No, insert triggers should work just fine.

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.

HTH
--
Kalen Delaney
MCSE, SQL Server MCT, MVP
(Please do not send email.
  Post all followups to the newsgroups.)

Quote:

>"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"

>Does this mean that insert triggers do not fire any
>more?

>Martin Sykes



Thu, 09 Aug 2001 03:00:00 GMT
 SQL 6.5 SP5a Horror Stories...?
Indy,

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
London, England.

Quote:

>I haven't seen complaints about SP5\a for SQL 6.5.

>Has anyone run into problems with it?

>Stable or steer clear?



Thu, 09 Aug 2001 03:00:00 GMT
 SQL 6.5 SP5a Horror Stories...?

Quote:
>Has anyone run into problems with it?

it wont even install if you have any spaces in the path to the SP5
files...beware!


Fri, 10 Aug 2001 03:00:00 GMT
 SQL 6.5 SP5a Horror Stories...?
Indy,

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).

Regards,
Karl Gram

Quote:

>I haven't seen complaints about SP5\a for SQL 6.5.

>Has anyone run into problems with it?

>Stable or steer clear?



Fri, 10 Aug 2001 03:00:00 GMT
 SQL 6.5 SP5a Horror Stories...?
Nor will the client utilities of the gold release. (Gives some SQL-DMO or
some other OLE-type message when you try to launch Enterprise Manager.)

Who ever came up with this "Program Files" nonsense???

Quote:


>it wont even install if you have any spaces in the path to the SP5
>files...beware!



Mon, 13 Aug 2001 03:00:00 GMT
 SQL 6.5 SP5a Horror Stories...?
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???




Sat, 18 Aug 2001 03:00:00 GMT
 SQL 6.5 SP5a Horror Stories...?
Edgardo,

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???

  You'll have to contact MS PSS.

  Q.  Who are Microsoft Product Support Services and how do I contact them?
(v1.1   1999.01.24)

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.

   http://support.microsoft.com/support/supportnet/default.asp

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 http://www.swynk.com/faq/sql/sqlserverfaq.asp
 and GO MSSQL Lib 1 on Compuserve



Sun, 19 Aug 2001 03:00:00 GMT
 
 [ 11 post ] 

 Relevant Pages 

1. SP1 horror stories...

2. Is VB5 the way to go? horror stories

3. Resync , Identity, Join Horror Story

4. Tech Support Horror Stories (Crescent)

5. Horror stories please

6. DBA Horror Stories

7. Upgrade horror stories/victories

8. Horror-story: Oracle Personal Edition 8.1.6 Installation on Win98

9. Any horror stories migrating to/using Oracle 8i?

10. System 10: Is Horror Story True?

11. Metadata and ETL Horror Stories


 
Powered by phpBB® Forum Software