BUG: UPDATE updates not listed fields

Hi Everybody,

I am in this forum for a first time. Today, I think, I found a bug in SQL Server 2000. I tried it on SQL Server 2005 and it is alive yet. Before I explain exact the problem I have a stupid question. Is there any type of award if I have found a real bug. It is about a situation when I try to update some fields in a table and I get updated some other wrong fields. There aren't any triggers ot something else. Create a new table, insert one row, update some fields but other ones get changed too. Is there something known about this ?

Thanks, Hristo.

No, there's no award, except for bragging rights :)

Post your code/sprocs, DDL etc. so someone can try to duplicate. If there's no triggers, calculated fields etc., it would be a strange bug indeed.

|||

Mike,

This is a simple example what happens:

************************************************************************

CREATE TABLE [dbo].[BUGTABLE] (
BUG1 varchar(1) NULL,
BUG2 varchar(1) NULL,
BUG3 varchar(1) NULL,
BUG4 varchar(1) NULL,
BUG5 varchar(1) NULL,
BUG6 varchar(1) NULL,
BUG7 varchar(1) NULL,
BUG8 varchar(1) NULL,
BUG9 bit NULL,
BUG10 bit NULL
)
GO

INSERT BUGTABLE (BUG10) VALUES('1')

select BUG10 from BUGTABLE

UPDATE BUGTABLE SET
BUG1 = 'T',
BUG2 = 'T',
BUG3 = 'T',
BUG4 = 'T',
BUG5 = 'T',
BUG6 = 'T',
BUG7 = 'T',
BUG8 = 'T',
BUG9 = '0'

select BUG10 from BUGTABLE

***********************************************************

See how BUG10 field is changed by SQL Server :) :) :)

I think this is because of the "optimized" storege of the BIT fields(by 8 fields in a single byte). And in a combination with some other fields this is the result :) I wrote to Microsoft too :)

Thank you for the comments!

|||So, what should we see? It's 1 appeared both times on 8.0.878 server build. What is your's result and build?|||I tried on both SQL Server 2000 & 2005..Nothing seems to be weird...|||No problem here either. What are you seeing?

You assigned BUG10 = 1 and it is still 1 in both SQL 2000 SP4 (8.00.2040) and SQL 2005 SP1 (9.00.2153.00).|||

I am with Microsoft SQL Server 2000 - 8.00.194

On my version and other ones (without SP) BUG10 changes to 0.

It seem that this is fixed in the Service Packs....thanks!!! :)

|||

If sombody have reproduced the problem let me know :)

Finaly, this is a bug, fixed in the recent versions ?

|||Obviously, it was fixed. That version you are using is the original SQL 2000 released over 3 years ago. The solution is update. There are many many many reasons to be running SP4.|||Yup, Thank you. I was excited if it was an unknown bug :)