Microsoft KB Archive/284440

= FIX: UPDATE with CASE Statement That Uses NEWID Function Causes Errors 8951, 8952, 8955 and 8956 =

Article ID: 284440

Article Last Modified on 3/14/2006

-

APPLIES TO


 * Microsoft SQL Server 7.0 Standard Edition
 * Microsoft SQL Server 2000 Standard Edition

-



This article was previously published under Q284440



BUG #: 58247 (SQLBUG_70)

Bug #: 235592 (SHILOH_bugs)



SYMPTOMS
An UPDATE statement that modifies data by using a CASE statement and the NEWID function may report the following error message when running a DBCC CHECKTABLE function on the underlying table:

Server: Msg 8951, Level 16, State 1, Line 2 Table Corrupt: Table '#t__00000000000C' (ID 165575628). Missing or invalid key in index 'main' (ID 2) for the row:

Server: Msg 8955, Level 16, State 1, Line 2 Data row (1:87:0) identified by (RID = (1:87:0) ) has index values (col1 = 9EF944CD-CBB4-4E7E-A1A4-A2E5B1565FC1,).

Server: Msg 8952, Level 16, State 1, Line 2 Table Corrupt: Database 'tempdb', index '#t__00000000000C.main' (ID 165575628) (index ID 2). Extra or invalid key for the keys:

Server: Msg 8956, Level 16, State 1, Line 2 Index row (1:104:0) with values (col1 = 019973DC-D2E6-4840-AEB8-25DC262A1840,) points to the data row identified by (RID = (1:87:0)).

These error messages may occur only if all of the following conditions are true:
 * The table has a data type of uniqueidentifier on one of the columns.
 * The preceding column is updated with a CASE statement along with the NEWID function.
 * There is a unique nonclustered index on the column with the uniqueidentifier datatype.



WORKAROUND
Avoid the use of a CASE statement in an UPDATE statement.



STATUS
Microsoft has confirmed this to be a problem in SQL Server 7.0. This problem has been corrected in U.S. Service Pack 3 for Microsoft SQL Server 7.0. For more information, click the following article number to view the article in the Microsoft Knowledge Base:

274799 INF: How to Obtain Service Pack 3 for Microsoft SQL Server 7.0 and Microsoft Data Engine (MSDE) 1.0

For more information, contact your primary support provider.

Microsoft has confirmed that this is a problem in SQL Server 2000.



Steps to Reproduce Behavior
To reproduce the behavior, follow these steps:

use tempdb go

drop table #t go

create table #t(col1 uniqueidentifier DEFAULT (NewID), CONSTRAINT main UNIQUE NONCLUSTERED (col1)) go

insert into #t values (NewID) go

UPDATE #t set col1 = CASE WHEN 1=1 THEN NEWID ELSE NULL end -- results in checktable errors -- set col1 = NEWID -- doesn't result in the errors go

dbcc traceon(3604) go

dbcc checktable(#t) go

Keywords: kbbug kbfix kbsqlserv2000sp1fix KB284440

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.