SQL 6.5 SP5 Problem for Insert Trigger Update Function

SQL 6.5 SP5 Problem for Insert Trigger Update Function

Post by John Carstense » Wed, 10 Mar 1999 04:00:00

In recent testing, I have found that the trigger function UPDATE under SQL
Server 6.5 SP5 behaves differently then SP4. For insert, the update function
returns true on items that have not been explicitly inserted.

Is this a bug? Is this a new feature change? Where can I get more
information on a bug list for SP5?


SQL 6.5 SP5 Problem for Insert Trigger Update Function

Post by Neil Pik » Thu, 11 Mar 1999 04:00:00


 It's a change rather than a bug....  For buglists see support.microsoft.com
and search the Kb for SQL and kbbug

> In recent testing, I have found that the trigger function UPDATE under SQL
> Server 6.5 SP5 behaves differently then SP4. For insert, the update function
> returns true on items that have not been explicitly inserted.

> Is this a bug? Is this a new feature change? Where can I get more
> information on a bug list for SP5?

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

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

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

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

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

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

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:

TITLE : FIX: SQL Server 6.5 Service Pack 5 Fixlist
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: ...

read more »


SQL 6.5 SP5 Problem for Insert Trigger Update Function

Post by Bas Kroe » Thu, 11 Mar 1999 04:00:00

No, it is not a bug. It works the same under SQL 7.0 now.


Bas Kroes
Object Systems B.V.

Quote:>In recent testing, I have found that the trigger function UPDATE under SQL
>Server 6.5 SP5 behaves differently then SP4. For insert, the update
>returns true on items that have not been explicitly inserted.

>Is this a bug? Is this a new feature change? Where can I get more
>information on a bug list for SP5?


1. Updating NT4.0 SP3 to SP5 with SQL Server 6.5 SP5

We currently have several NT 4.0 SP3 servers with SQL Server 6.5 SP5.  We
are bringing the servers to Y2K compliance by updating NT to SP5.  It seems
to me that I read that doing this could cause problems with the SQL Server.
Has anyone experienced this?  Has anyone taken this route?  Any insight
deeply appreciated.


2. Anyone has patch for Apollo 3 demo to fully functional version ?

3. Trigger IF Update(fieldname) on text field ERROR in 6.5 sp5

4. exporting data from sqlserver to specific cells in excel

5. IF UPDATE bug in SQL 6.5 SP5(a)

6. MSDTC location after running comclust.exe

7. Problem with informix on Ultrix 4.2

8. SQL 6.5 Network Error after updating to sp5

9. SP5 changes update() behavior on SQL 6.5?

10. Update fails in stored procedure SQL 6.5 sp5

11. IF UPDATE bug in SQL 6.5 SP5(a)

12. sql server 6.5 evaluation, sp5, updating to full