asked on

SQL Server table column updating itself

Hi,

I'm using a SQL Server 2000 database, one particular table has 141 columns which is average for this database.

I've created a PHP front-end and with an UPDATE statement, I'm writing data to the table.

The table has a mixture of "nvarchar" and "bit" data types, same as all the other tables.

On one input screen, the UPDATE statement attempts to update 102 columns. When it does this, all data is saved but a "bit" value column that had a value of 1, changes to 0 even though this column is not specified in the code. This does not happen on SQL Server 2012.

Through a process of trial and error, I found out that if I remove any 5 columns from the UPDATE statement (leaving me with 97), this issue would no longer happen.

When I run the code below, it tells me my table's maxrowlength is 2898 which is low for this database.

SELECT OBJECT_NAME (sc.[id]) tablename 
, COUNT (1) nr_columns 
, SUM (sc.length) maxrowlength 
FROM syscolumns sc 
join sysobjects so 
on sc.[id] = so.[id] 
WHERE so.xtype = 'U' 
GROUP BY OBJECT_NAME (sc.[id]) 
ORDER BY SUM (sc.length) desc

Open in new window

Can someone please help me figure out how this column is updating itself?

Many thanks

Jim Horn

In SSMS*, navigate to this table and find out if there are any triggers on it.
If yes, open them up and copy-paste into this question.

* assuming 2000 works the same as 2008/2012

kbit

ASKER

Thanks Jim, there are no triggers, this was verified by running

select *
from sysobjects
where xtype ='TR'

Open in new window

Paul MacDonald

This would be my guess as well. Some column or combination of columns updates the bit based on some condition.

kbit

ASKER

Its a very dumb database in that there are no indexes, no rules, no stored procedures, no views etc.

Just simple tables with fields in them...this is the extent of my knowledge of SQL Server

Some more info: If I split the UPDATE into 2 statements and execute them one after another, the relevant columns are updated properly.

Scott Pletcher

Must be something with the mapping of the columns. [I'd say be sure to refresh the view(s) except that there are no views!]

SQL could easily "confuse" a larger value for a bit because of the way that works -- anything greater than 0 is treated as 1, without causing any warning or error, for example:
create table #t1 ( c1 bit )
insert into #t1 values(23456789)

kbit

ASKER

The syntax I'm using is:

UPDATE mytable SET col1='$col1', col2='$col2' WHERE ID=123456

Each column is named explicitly and the mysterious column is not named in the SQL.

All other code was removed from the file, in order to rule out interference from other lines.

Scott Pletcher

You first must accept the fact that SQL is not and will never arbitrarily update a column without being told to one way or another.

So there must be some type of misalignment or trigger or something.

kbit

ASKER

I accept that logic but I also can't explain my issue.

Here is the script...if I run this directly in SQL Server, it updates the columns specified in the script...and also one called "Designcomplete" to 0.

If I remove any 5 columns from this script and run it directly on SQL Server, "Designcomplete" is not set to 0.

UPDATE homesourcedhow_bom SET CLevelQuantity='',BLevelQuantity='',CLevelQuantity2='',BLevelQuantity2='',CLevelQuantity3='',BLevelQuantity3='',Component1='',Component2='',Component3='137208',Component4='',Component5='',Component6='',Component7='',Component8='',Component9='',Component10='',Component11='',Component12='',Component13='',Component14='',Component15='',Component16='',Component17='',Component18='',Component19='',Component20='',Component21='',Component22='',Component23='',Component24='',Component25='',Component26='',Component27='',Component28='',Quantity1='',Quantity2='',Quantity3='1',Quantity4='',Quantity5='',Quantity6='',Quantity7='',Quantity8='',Quantity9='',Quantity10='',Quantity11='',Quantity12='',Quantity13='',Quantity14='',Quantity15='',Quantity16='',Quantity17='',Quantity18='',Quantity19='',Quantity20='',Quantity21='',Quantity22='',Quantity23='',Quantity24='',Quantity25='',Quantity26='',Quantity27='',Quantity28='',SIB8='',SIB9='',SIB10='',SIB11='',SIB12='',SIB13='',SIB14='',SIB15='',SIB16='',SIB17='',SIB18='',SIB19='',SIB20='',SIB21='',SIB22='',SIB23='',SIB24='',SIB25='',SIB26='',SIB27='',SIB28='',BarcodeLine1='',BarcodeLine2='',BarcodeLine3='',BarcodeLine4='',MSRP='',MSRPAustralia='',MSRPIreland='',MSRPUK='',PriceTicket='',PrintPriceAustralia='',ImageRequired='',ImageExisting='',Imagematerial='',ImageNew='',USProducts='',EMEAProducts='',LastsavedNPD='David Cooke (15/04/2014 15:39:59)',Comments='', LastSavedOn=getdate() WHERE Material='120565'

Open in new window

Scott Pletcher

Just some thoughts/possibilities:

1) Is "DesignComplete" a computed column?

2) Is homesourcedhow_bom a view? Updating of views can have odd side effects issues you don't think about.

3) Did you try doing a:
SELECT *
INTO holding_table_before
FROM homesourcedhow_bom
WHERE <exact_conditions_above>

Then do the UPDATE, then do:
SELECT *
INTO holding_table_after
FROM homesourcedhow_bom
WHERE <exact_conditions_above>

And view/compare the two holding table rows?

kbit

ASKER

Thanks for the ideas so far.

1) No its not computed

2) Good idea that...I ran the before and after and compared the results..."Designcomplete" was 1 in the first table and 0 in the second

Anthony Perkins

Just to repeat Scott's question as you appear to have overlooked it:

Is homesourcedhow_bom a view?

If the answer is yes, then please post the schema for that VIEW.

Anthony Perkins

Also, you are running these tests in SSMS, right? If the answer is no, then the problem lies in your PHP code and best addressed in that Topic Area.

kbit

ASKER

Sorry "homesourcedhow_bom" is a table, everything I've tested here so far has been done in an SSMS query (no PHP)

Anthony Perkins

Please post the schema (CREATE TABLE) for homesourcedhow_bom.

Scott Pletcher

Also, see if this returns any results:

EXEC sp_helptrigger 'homesourcedhow_bom'

kbit

ASKER

Thanks for the help so far. Unfortunately the trigger returned no records.

The poorly-designed schema is:

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[homesourcedhow_bom]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[homesourcedhow_bom]
GO

CREATE TABLE [dbo].[homesourcedhow_bom] (
	[Material] [nvarchar] (40) COLLATE Latin1_General_CI_AS NULL ,
	[Component1] [nvarchar] (15) COLLATE Latin1_General_CI_AS NULL ,
	[Component2] [nvarchar] (15) COLLATE Latin1_General_CI_AS NULL ,
	[Component3] [nvarchar] (15) COLLATE Latin1_General_CI_AS NULL ,
	[Component4] [nvarchar] (15) COLLATE Latin1_General_CI_AS NULL ,
	[Component5] [nvarchar] (15) COLLATE Latin1_General_CI_AS NULL ,
	[Component6] [nvarchar] (15) COLLATE Latin1_General_CI_AS NULL ,
	[Component7] [nvarchar] (15) COLLATE Latin1_General_CI_AS NULL ,
	[Component8] [nvarchar] (15) COLLATE Latin1_General_CI_AS NULL ,
	[Component9] [nvarchar] (15) COLLATE Latin1_General_CI_AS NULL ,
	[Component10] [nvarchar] (15) COLLATE Latin1_General_CI_AS NULL ,
	[Component11] [nvarchar] (15) COLLATE Latin1_General_CI_AS NULL ,
	[Component12] [nvarchar] (15) COLLATE Latin1_General_CI_AS NULL ,
	[Component13] [nvarchar] (15) COLLATE Latin1_General_CI_AS NULL ,
	[Component14] [nvarchar] (15) COLLATE Latin1_General_CI_AS NULL ,
	[Component15] [nvarchar] (15) COLLATE Latin1_General_CI_AS NULL ,
	[Component16] [nvarchar] (15) COLLATE Latin1_General_CI_AS NULL ,
	[Component17] [nvarchar] (15) COLLATE Latin1_General_CI_AS NULL ,
	[Component18] [nvarchar] (15) COLLATE Latin1_General_CI_AS NULL ,
	[Component19] [nvarchar] (15) COLLATE Latin1_General_CI_AS NULL ,
	[Component20] [nvarchar] (15) COLLATE Latin1_General_CI_AS NULL ,
	[Component21] [nvarchar] (15) COLLATE Latin1_General_CI_AS NULL ,
	[Component22] [nvarchar] (15) COLLATE Latin1_General_CI_AS NULL ,
	[Component23] [nvarchar] (15) COLLATE Latin1_General_CI_AS NULL ,
	[Component24] [nvarchar] (15) COLLATE Latin1_General_CI_AS NULL ,
	[Component25] [nvarchar] (15) COLLATE Latin1_General_CI_AS NULL ,
	[Component26] [nvarchar] (15) COLLATE Latin1_General_CI_AS NULL ,
	[Component27] [nvarchar] (15) COLLATE Latin1_General_CI_AS NULL ,
	[Component28] [nvarchar] (15) COLLATE Latin1_General_CI_AS NULL ,
	[CLevelQuantity] [nvarchar] (5) COLLATE Latin1_General_CI_AS NULL ,
	[CLevelQuantity2] [nvarchar] (5) COLLATE Latin1_General_CI_AS NULL ,
	[CLevelQuantity3] [nvarchar] (5) COLLATE Latin1_General_CI_AS NULL ,
	[BLevelQuantity] [nvarchar] (5) COLLATE Latin1_General_CI_AS NULL ,
	[BLevelQuantity2] [nvarchar] (5) COLLATE Latin1_General_CI_AS NULL ,
	[BLevelQuantity3] [nvarchar] (5) COLLATE Latin1_General_CI_AS NULL ,
	[Quantity1] [nvarchar] (5) COLLATE Latin1_General_CI_AS NULL ,
	[Quantity2] [nvarchar] (5) COLLATE Latin1_General_CI_AS NULL ,
	[Quantity3] [nvarchar] (5) COLLATE Latin1_General_CI_AS NULL ,
	[Quantity4] [nvarchar] (5) COLLATE Latin1_General_CI_AS NULL ,
	[Quantity5] [nvarchar] (5) COLLATE Latin1_General_CI_AS NULL ,
	[Quantity6] [nvarchar] (5) COLLATE Latin1_General_CI_AS NULL ,
	[Quantity7] [nvarchar] (5) COLLATE Latin1_General_CI_AS NULL ,
	[Quantity8] [nvarchar] (5) COLLATE Latin1_General_CI_AS NULL ,
	[Quantity9] [nvarchar] (5) COLLATE Latin1_General_CI_AS NULL ,
	[Quantity10] [nvarchar] (5) COLLATE Latin1_General_CI_AS NULL ,
	[Quantity11] [nvarchar] (5) COLLATE Latin1_General_CI_AS NULL ,
	[Quantity12] [nvarchar] (5) COLLATE Latin1_General_CI_AS NULL ,
	[Quantity13] [nvarchar] (5) COLLATE Latin1_General_CI_AS NULL ,
	[Quantity14] [nvarchar] (5) COLLATE Latin1_General_CI_AS NULL ,
	[Quantity15] [nvarchar] (5) COLLATE Latin1_General_CI_AS NULL ,
	[Quantity16] [nvarchar] (5) COLLATE Latin1_General_CI_AS NULL ,
	[Quantity17] [nvarchar] (5) COLLATE Latin1_General_CI_AS NULL ,
	[Quantity18] [nvarchar] (5) COLLATE Latin1_General_CI_AS NULL ,
	[Quantity19] [nvarchar] (5) COLLATE Latin1_General_CI_AS NULL ,
	[Quantity20] [nvarchar] (5) COLLATE Latin1_General_CI_AS NULL ,
	[Quantity21] [nvarchar] (5) COLLATE Latin1_General_CI_AS NULL ,
	[Quantity22] [nvarchar] (5) COLLATE Latin1_General_CI_AS NULL ,
	[Quantity23] [nvarchar] (5) COLLATE Latin1_General_CI_AS NULL ,
	[Quantity24] [nvarchar] (5) COLLATE Latin1_General_CI_AS NULL ,
	[Quantity25] [nvarchar] (5) COLLATE Latin1_General_CI_AS NULL ,
	[Quantity26] [nvarchar] (5) COLLATE Latin1_General_CI_AS NULL ,
	[Quantity27] [nvarchar] (5) COLLATE Latin1_General_CI_AS NULL ,
	[Quantity28] [nvarchar] (5) COLLATE Latin1_General_CI_AS NULL ,
	[SIB8] [bit] NULL ,
	[SIB9] [bit] NULL ,
	[SIB10] [bit] NULL ,
	[SIB11] [bit] NULL ,
	[SIB12] [bit] NULL ,
	[SIB13] [bit] NULL ,
	[SIB14] [bit] NULL ,
	[SIB15] [bit] NULL ,
	[SIB16] [bit] NULL ,
	[SIB17] [bit] NULL ,
	[SIB18] [bit] NULL ,
	[SIB19] [bit] NULL ,
	[SIB20] [bit] NULL ,
	[SIB21] [bit] NULL ,
	[SIB22] [bit] NULL ,
	[SIB23] [bit] NULL ,
	[SIB24] [bit] NULL ,
	[SIB25] [bit] NULL ,
	[SIB26] [bit] NULL ,
	[SIB27] [bit] NULL ,
	[SIB28] [bit] NULL ,
	[ItemCatCostRelMatProGlass] [nvarchar] (3) COLLATE Latin1_General_CI_AS NULL ,
	[ItemCatCostRelMatProv1] [nvarchar] (3) COLLATE Latin1_General_CI_AS NULL ,
	[ItemCatCostRelMatProv2] [nvarchar] (3) COLLATE Latin1_General_CI_AS NULL ,
	[ItemCatCostRelMatProv3] [nvarchar] (3) COLLATE Latin1_General_CI_AS NULL ,
	[ItemCatCostRelMatProv4] [nvarchar] (3) COLLATE Latin1_General_CI_AS NULL ,
	[ItemCatCostRelMatProv5] [nvarchar] (3) COLLATE Latin1_General_CI_AS NULL ,
	[ItemCatCostRelMatProv6] [nvarchar] (3) COLLATE Latin1_General_CI_AS NULL ,
	[ItemCatCostRelMatProv7] [nvarchar] (3) COLLATE Latin1_General_CI_AS NULL ,
	[ItemCatCostRelMatProv8] [nvarchar] (3) COLLATE Latin1_General_CI_AS NULL ,
	[ItemCatCostRelMatProv9] [nvarchar] (3) COLLATE Latin1_General_CI_AS NULL ,
	[ItemCatCostRelMatProv10] [nvarchar] (3) COLLATE Latin1_General_CI_AS NULL ,
	[ItemCatCostRelMatProv11] [nvarchar] (3) COLLATE Latin1_General_CI_AS NULL ,
	[ItemCatCostRelMatProv12] [nvarchar] (3) COLLATE Latin1_General_CI_AS NULL ,
	[ItemCatCostRelMatProv13] [nvarchar] (3) COLLATE Latin1_General_CI_AS NULL ,
	[ItemCatCostRelMatProv14] [nvarchar] (3) COLLATE Latin1_General_CI_AS NULL ,
	[ItemCatCostRelMatProv15] [nvarchar] (3) COLLATE Latin1_General_CI_AS NULL ,
	[ItemCatCostRelMatProv16] [nvarchar] (3) COLLATE Latin1_General_CI_AS NULL ,
	[ItemCatCostRelMatProv17] [nvarchar] (3) COLLATE Latin1_General_CI_AS NULL ,
	[ItemCatCostRelMatProv18] [nvarchar] (3) COLLATE Latin1_General_CI_AS NULL ,
	[ItemCatCostRelMatProv19] [nvarchar] (3) COLLATE Latin1_General_CI_AS NULL ,
	[ItemCatCostRelMatProv20] [nvarchar] (3) COLLATE Latin1_General_CI_AS NULL ,
	[ItemCatCostRelMatProv21] [nvarchar] (3) COLLATE Latin1_General_CI_AS NULL ,
	[ItemCatCostRelMatProv22] [nvarchar] (3) COLLATE Latin1_General_CI_AS NULL ,
	[ItemCatCostRelMatProv23] [nvarchar] (3) COLLATE Latin1_General_CI_AS NULL ,
	[ItemCatCostRelMatProv24] [nvarchar] (3) COLLATE Latin1_General_CI_AS NULL ,
	[ItemCatCostRelMatProv25] [nvarchar] (3) COLLATE Latin1_General_CI_AS NULL ,
	[ItemCatCostRelMatProv26] [nvarchar] (3) COLLATE Latin1_General_CI_AS NULL ,
	[ItemCatCostRelMatProv27] [nvarchar] (3) COLLATE Latin1_General_CI_AS NULL ,
	[ItemCatCostRelMatProv28] [nvarchar] (3) COLLATE Latin1_General_CI_AS NULL ,
	[BarcodeLine1] [nvarchar] (20) COLLATE Latin1_General_CI_AS NULL ,
	[BarcodeLine2] [nvarchar] (20) COLLATE Latin1_General_CI_AS NULL ,
	[BarcodeLine3] [nvarchar] (20) COLLATE Latin1_General_CI_AS NULL ,
	[BarcodeLine4] [nvarchar] (20) COLLATE Latin1_General_CI_AS NULL ,
	[PriceTicket] [bit] NULL ,
	[PrintPriceAustralia] [bit] NULL ,
	[MSRP] [nvarchar] (20) COLLATE Latin1_General_CI_AS NULL ,
	[MSRPAustralia] [nvarchar] (20) COLLATE Latin1_General_CI_AS NULL ,
	[MSRPIreland] [nvarchar] (20) COLLATE Latin1_General_CI_AS NULL ,
	[MSRPUK] [nvarchar] (20) COLLATE Latin1_General_CI_AS NULL ,
	[ImageRequired] [bit] NULL ,
	[ImageExisting] [bit] NULL ,
	[Imagematerial] [nvarchar] (20) COLLATE Latin1_General_CI_AS NULL ,
	[ImageNew] [bit] NULL ,
	[ImageCreated] [bit] NULL ,
	[USProducts] [nvarchar] (5) COLLATE Latin1_General_CI_AS NULL ,
	[EMEAProducts] [nvarchar] (5) COLLATE Latin1_General_CI_AS NULL ,
	[UOMC] [nvarchar] (20) COLLATE Latin1_General_CI_AS NULL ,
	[UOMB] [nvarchar] (50) COLLATE Latin1_General_CI_AS NULL ,
	[Comments] [nvarchar] (255) COLLATE Latin1_General_CI_AS NULL ,
	[Purchasingcomplete] [bit] NULL ,
	[Planningcomplete] [bit] NULL ,
	[Designcomplete] [bit] NULL ,
	[LastsavedNPD] [nvarchar] (50) COLLATE Latin1_General_CI_AS NULL ,
	[LastsavedPur] [nvarchar] (50) COLLATE Latin1_General_CI_AS NULL ,
	[LastsavedPlanning] [nvarchar] (50) COLLATE Latin1_General_CI_AS NULL ,
	[LastsavedDesign] [nvarchar] (50) COLLATE Latin1_General_CI_AS NULL ,
	[LastSavedOn] [smalldatetime] NULL 
) ON [PRIMARY]
GO

Open in new window

Anthony Perkins

Unfortunately I was unable to duplicate your problem on SQL Server 2000, so I have no idea what is going on.

kbit

ASKER

Yeah I cant figure it either. It's not just one record, I've tried it on a few other ones and the same thing happens.

I can also see that it wasn't doing this mysterious update until recently.

The server that contains the database has been rebooted since the issue was first noticed and was still doing it when it came back online.

Anthony Perkins

Have you tried checking the database integrity?
What about copying the table to a new database and attempting to duplicate the problem?

kbit

ASKER

Some good ideas there, thanks.

I copied the table and ran the same query as above (with the new table name of course), same problem.

I ran checkdb, it came back with 0 allocation errors and 0 consistency errors. The number of records on the table is correct versus a physical count.

To rule out the possibility that blank strings in the UPDATE were causing the issue, I put a 1 in instead of them all and it produced the same issue.

Anthony Perkins

I copied the table and ran the same query as above (with the new table name of course), same problem.
But that is not what I suggested: What about copying the table to a new database and attempting to duplicate the problem?

kbit

ASKER

Sorry, just done that now, same result as on the other table.

If I remove any 5 columns from the UPDATE, it holds the value of 1...exact same behaviour as on the other table

Anthony Perkins

Then quite frankly I have no idea. I have never seen anything like that.

ASKER CERTIFIED SOLUTION

kbit

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

SOLUTION

kbit

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Anthony Perkins

even though I'm already running SP2
I tested it on SQL Server 2000 with SP4.

Anthony Perkins

A known bug has been found which most likely explains the issue I was having.
I very much doubt it as I stated I tested with SQL Server 2000 Service Pack 4.

But don't take my word for it, try applying Service Pack 4 yourself and then re-test. Otherwise your hypothesis is pure speculation and not very useful in the EE PAQ.

kbit

ASKER

Unfortunately I can't apply SP4 as I'm not the server's admin.

Microsoft acknowledge that there was a bug in some version of 2000 earlier than your version, hence you're not seeing it.

Either way, changing bit to tinyint did fix the issue, proving the fact that were no procedures or views causing the issue and that the SQL was ok.

I'm open to suggestions on how to close the question

Anthony Perkins

Microsoft acknowledge that there was a bug in some version of 2000 earlier than your version
We need to be clear about this as it is important to future readers: The specific quote from the KB that you refer to indirectly, states:

A table that was created with int columns, which appear somewhere after a bit column, may have the int columns zeroed out when other columns in the table are updated.
...
Microsoft has confirmed that this is a problem in SQL Server 2000. This problem was first corrected in SQL Server 2000 Service Pack 1.

Since you are asking about bit (not integer) columns getting updated and you are using Service Pack 2, I cannot see how this KB can possibly be related.

So the correct solution to the problem if you are using SQL Server 2000 is upgrade to Service Pack 3 or 4, if you can live with the inherent security problems prior to Service Pack 3 or you are unable to upgrade, then you are going to have to find some other workaround.

kbit

ASKER

A known bug has been found which most likely explains the issue I was having.

Changing to tinyint is acceptable as a workaround