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
Can someone please help me figure out how this column is updating itself?