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

Article translations Article translations
Article ID: 284440 - View products that this article applies to.
This article was previously published under Q284440
BUG #: 58247 (SQLBUG_70)
Bug #: 235592 (SHILOH_bugs)
Expand all | Collapse all

On This Page

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.

MORE INFORMATION

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
				

Properties

Article ID: 284440 - Last Review: March 14, 2006 - Revision: 4.2
APPLIES TO
  • Microsoft SQL Server 7.0 Standard Edition
  • Microsoft SQL Server 2000 Standard Edition
Keywords: 
kbbug kbfix kbsqlserv2000sp1fix KB284440

Give Feedback

 

Contact us for more help

Contact us for more help
Connect with Answer Desk for expert help.
Get more support from smallbusiness.support.microsoft.com