field changing unexpectedly

This is very frightening to me, a bit field completely unmentioned in an
update query keeps changing. There are no triggers, or other queries being
run.
I have a table with a bit field called "tc_active" nonull, default to (1).
The problem started on just one machine when I started editing the table,
inserted two new bit fields above tc_active, and enterprise manager crashed
while saving the table. I've since re-imported it from another server and
repeated the changes with no crash.
I run a query that updates many other fields in the table, but makes no
mention of tc_active. After it runs, tc_active has been changed to 0. I wish
I could pin down the causality, because I've tried splitting the query in
two, and each half, run independantly, does not cause the behavior.
I've tried renaming tc_active to tc_activeOLD, making a new tc_active field,
saving the table, editing it again, delete the new field and name the old
field back to tc_active, run the same problematic query again, and the
problem goes away.
however, if i re-import the table again from another server (which, by the
way doesn't share the problem) the problem will come back again.
I'm very shaken right now by this loss of faith in the correct operations of
the database... and this is a VERY difficult problem to websearch information
for, so I beg for someone's insight please.
-g
It is highly unlikely that the db itself is causing this but if you want to
be sure and the data is worth that much you should give MS PSS a call.
http://support.microsoft.com/default...d=fh;EN-US;sql SQL Support
http://www.mssqlserver.com/faq/general-pss.asp MS PSS
Andrew J. Kelly SQL MVP
"evilme" <evilme@.discussions.microsoft.com> wrote in message
news:12DFE953-EEC1-40D4-A372-3E112B3221BA@.microsoft.com...
> This is very frightening to me, a bit field completely unmentioned in an
> update query keeps changing. There are no triggers, or other queries being
> run.
> I have a table with a bit field called "tc_active" nonull, default to (1).
> The problem started on just one machine when I started editing the table,
> inserted two new bit fields above tc_active, and enterprise manager
> crashed
> while saving the table. I've since re-imported it from another server and
> repeated the changes with no crash.
> I run a query that updates many other fields in the table, but makes no
> mention of tc_active. After it runs, tc_active has been changed to 0. I
> wish
> I could pin down the causality, because I've tried splitting the query in
> two, and each half, run independantly, does not cause the behavior.
> I've tried renaming tc_active to tc_activeOLD, making a new tc_active
> field,
> saving the table, editing it again, delete the new field and name the old
> field back to tc_active, run the same problematic query again, and the
> problem goes away.
> however, if i re-import the table again from another server (which, by the
> way doesn't share the problem) the problem will come back again.
> I'm very shaken right now by this loss of faith in the correct operations
> of
> the database... and this is a VERY difficult problem to websearch
> information
> for, so I beg for someone's insight please.
> -g
>
|||Is your server up-to-date on service packs? What does
SELECT @.@.VERSION return?
At least one vaguely similar bug was fixed some time ago:
http://support.microsoft.com/kb/294872; though yours is
not quite the same, it could be related.
If that's not it, can you post the CREATE TABLE and index
statements and the query that is giving you trouble?
Steve Kass
Drew University
evilme wrote:

>This is very frightening to me, a bit field completely unmentioned in an
>update query keeps changing. There are no triggers, or other queries being
>run.
>I have a table with a bit field called "tc_active" nonull, default to (1).
>The problem started on just one machine when I started editing the table,
>inserted two new bit fields above tc_active, and enterprise manager crashed
>while saving the table. I've since re-imported it from another server and
>repeated the changes with no crash.
>I run a query that updates many other fields in the table, but makes no
>mention of tc_active. After it runs, tc_active has been changed to 0. I wish
>I could pin down the causality, because I've tried splitting the query in
>two, and each half, run independantly, does not cause the behavior.
>I've tried renaming tc_active to tc_activeOLD, making a new tc_active field,
>saving the table, editing it again, delete the new field and name the old
>field back to tc_active, run the same problematic query again, and the
>problem goes away.
>however, if i re-import the table again from another server (which, by the
>way doesn't share the problem) the problem will come back again.
>I'm very shaken right now by this loss of faith in the correct operations of
>the database... and this is a VERY difficult problem to websearch information
>for, so I beg for someone's insight please.
> -g
>
>
|||"Steve Kass" wrote:
> Is your server up-to-date on service packs? What does
> SELECT @.@.VERSION return?
well, my server's kept up to date, but it's only my devbox that's having
this problem. I get the following:
"Microsoft SQL Server 2000 - 8.00.194 (Intel X86) Aug 6 2000 00:57:48
Copyright (c) 1988-2000 Microsoft Corporation Developer Edition on Windows
NT 5.1 (Build 2600: Service Pack 1)"

> At least one vaguely similar bug was fixed some time ago:
> http://support.microsoft.com/kb/294872; though yours is
> not quite the same, it could be related.
it does seem very similar, thanks for this tip.

> If that's not it, can you post the CREATE TABLE and index
> statements and the query that is giving you trouble?
CREATE TABLE [t_title_co2] (
[tc_titleCoId] [int] IDENTITY (1, 1) NOT NULL ,
[tc_name] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
CONSTRAINT [tc_name2] DEFAULT (''),
[tc_address] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
CONSTRAINT [tc_address2] DEFAULT (''),
[tc_city] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
CONSTRAINT [tc_city2] DEFAULT (''),
[tc_state] [varchar] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
CONSTRAINT [tc_state2] DEFAULT (''),
[tc_zip] [varchar] (5) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
CONSTRAINT [tc_zip2] DEFAULT (''),
[tc_zip4] [varchar] (4) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
CONSTRAINT [tc_zip42] DEFAULT (''),
[tc_phone] [varchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
CONSTRAINT [tc_phone2] DEFAULT (''),
[tc_fax] [varchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
CONSTRAINT [tc_fax2] DEFAULT (''),
[tc_webURL] [varchar] (75) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
CONSTRAINT [tc_webURL2] DEFAULT (''),
[tc_status] [varchar] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
CONSTRAINT [tc_status2] DEFAULT (''),
[tc_bankName] [varchar] (75) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
CONSTRAINT [tc_bankName2] DEFAULT (''),
[tc_bankCity] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
CONSTRAINT [tc_bankCity2] DEFAULT (''),
[tc_ABA] [varchar] (9) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
CONSTRAINT [tc_ABA2] DEFAULT (''),
[tc_accountNum] [varchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
NULL CONSTRAINT [tc_accountNum2] DEFAULT (''),
[tc_creditTo] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
CONSTRAINT [tc_creditTo2] DEFAULT (''),
[tc_furtherCredit] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
NULL CONSTRAINT [tc_furtherCredit2] DEFAULT (''),
[tc_furtherCreditNum] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS
NOT NULL CONSTRAINT [tc_furtherCreditNum2] DEFAULT (''),
[tc_insCloseLtr] [int] NOT NULL CONSTRAINT [tc_insCloseLtr2] DEFAULT (0),
[tc_pcFirstName] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
NULL CONSTRAINT [tc_pcFirstName2] DEFAULT (''),
[tc_pcLastName] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
NULL CONSTRAINT [tc_pcLastName2] DEFAULT (''),
[tc_isTitleCo] [bit] NOT NULL CONSTRAINT [DF_t_title_co_tc_isTitleCo2]
DEFAULT (0),
[tc_isEscrowCo] [bit] NOT NULL CONSTRAINT [DF_t_title_co_tc_isEscrowCo2]
DEFAULT (0),
[tc_active] [bit] NOT NULL CONSTRAINT [DF_t_title_co_tc_active2] DEFAULT (1)
) ON [PRIMARY]
GO
and the offending query:
UPDATE t_title_co2
SET
tc_state = 'IL',
tc_zip = '11111',
tc_phone = '(111) 111-1111',
tc_fax = '(111) 111-1111',
tc_bankName = 'demonstration Community Bank',
tc_bankCity = 'chicago',
tc_ABA = '111111111',
tc_accountNum = '11111111',
tc_creditTo = 'A Company, Inc.',
tc_furtherCredit = '',
tc_furtherCreditNum = '',
tc_insCloseLtr = '1',
tc_isTitleCo =1,
tc_isEscrowCo =1,
tc_pcFirstName = 'joe',
tc_pcLastName = 'smith'
WHERE (tc_titleCoId = 360)
aaand an insert line just to get a row of data in there
SET IDENTITY_INSERT t_title_co2 ON
INSERT INTO [DATABASENAME].[dbo].[t_title_co2]([tc_titleCoId], [tc_name],
[tc_address], [tc_city], [tc_state], [tc_zip], [tc_zip4], [tc_phone],
[tc_fax], [tc_webURL], [tc_status], [tc_bankName], [tc_bankCity], [tc_ABA],
[tc_accountNum], [tc_creditTo], [tc_furtherCredit], [tc_furtherCreditNum],
[tc_insCloseLtr], [tc_pcFirstName], [tc_pcLastName], [tc_isTitleCo],
[tc_isEscrowCo], [tc_active])
VALUES(360, 'moo', '222', 'here', 'mn', '44444', '', '', '', '', '', '', '',
'', '', '', '', '', 0, '', '', 0, 0, 1)
SET IDENTITY_INSERT t_title_co2 OFF
the query doesn't have the problem if i remove the tc_state line, or both
the _isEscrowCo and _isTitleCo lines, or several other things that have no
rhyme or reason related to tc_active.
thanks again
-g
|||You should install Service Pack 3a on your development machine.
Not only will this fix a number of bugs, it will protect you from
security vulnerabilities, including the SQL Slammer work, which
you are not protected against.
http://www.microsoft.com/sql/downloads/2000/sp3.asp
If you still have the problem after upgrading, let us know.
When I run this on an up-to-date server, the value of tc_active
remains equal to 1 after the update.
SK
evilme wrote:

>"Steve Kass" wrote:
>
>well, my server's kept up to date, but it's only my devbox that's having
>this problem. I get the following:
>"Microsoft SQL Server 2000 - 8.00.194 (Intel X86) Aug 6 2000 00:57:48
>Copyright (c) 1988-2000 Microsoft Corporation Developer Edition on Windows
>NT 5.1 (Build 2600: Service Pack 1)"
>
>
>it does seem very similar, thanks for this tip.
>
>
>CREATE TABLE [t_title_co2] (
>[tc_titleCoId] [int] IDENTITY (1, 1) NOT NULL ,
>[tc_name] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
>CONSTRAINT [tc_name2] DEFAULT (''),
>[tc_address] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
>CONSTRAINT [tc_address2] DEFAULT (''),
>[tc_city] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
>CONSTRAINT [tc_city2] DEFAULT (''),
>[tc_state] [varchar] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
>CONSTRAINT [tc_state2] DEFAULT (''),
>[tc_zip] [varchar] (5) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
>CONSTRAINT [tc_zip2] DEFAULT (''),
>[tc_zip4] [varchar] (4) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
>CONSTRAINT [tc_zip42] DEFAULT (''),
>[tc_phone] [varchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
>CONSTRAINT [tc_phone2] DEFAULT (''),
>[tc_fax] [varchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
>CONSTRAINT [tc_fax2] DEFAULT (''),
>[tc_webURL] [varchar] (75) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
>CONSTRAINT [tc_webURL2] DEFAULT (''),
>[tc_status] [varchar] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
>CONSTRAINT [tc_status2] DEFAULT (''),
>[tc_bankName] [varchar] (75) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
>CONSTRAINT [tc_bankName2] DEFAULT (''),
>[tc_bankCity] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
>CONSTRAINT [tc_bankCity2] DEFAULT (''),
>[tc_ABA] [varchar] (9) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
>CONSTRAINT [tc_ABA2] DEFAULT (''),
>[tc_accountNum] [varchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
>NULL CONSTRAINT [tc_accountNum2] DEFAULT (''),
>[tc_creditTo] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
>CONSTRAINT [tc_creditTo2] DEFAULT (''),
>[tc_furtherCredit] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
>NULL CONSTRAINT [tc_furtherCredit2] DEFAULT (''),
>[tc_furtherCreditNum] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS
>NOT NULL CONSTRAINT [tc_furtherCreditNum2] DEFAULT (''),
>[tc_insCloseLtr] [int] NOT NULL CONSTRAINT [tc_insCloseLtr2] DEFAULT (0),
>[tc_pcFirstName] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
>NULL CONSTRAINT [tc_pcFirstName2] DEFAULT (''),
>[tc_pcLastName] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
>NULL CONSTRAINT [tc_pcLastName2] DEFAULT (''),
>[tc_isTitleCo] [bit] NOT NULL CONSTRAINT [DF_t_title_co_tc_isTitleCo2]
>DEFAULT (0),
>[tc_isEscrowCo] [bit] NOT NULL CONSTRAINT [DF_t_title_co_tc_isEscrowCo2]
>DEFAULT (0),
>[tc_active] [bit] NOT NULL CONSTRAINT [DF_t_title_co_tc_active2] DEFAULT (1)
>) ON [PRIMARY]
>GO
>and the offending query:
>UPDATE t_title_co2
>SET
>tc_state = 'IL',
>tc_zip = '11111',
>tc_phone = '(111) 111-1111',
>tc_fax = '(111) 111-1111',
>tc_bankName = 'demonstration Community Bank',
>tc_bankCity = 'chicago',
>tc_ABA = '111111111',
>tc_accountNum = '11111111',
>tc_creditTo = 'A Company, Inc.',
>tc_furtherCredit = '',
>tc_furtherCreditNum = '',
>tc_insCloseLtr = '1',
>tc_isTitleCo =1,
>tc_isEscrowCo =1,
>tc_pcFirstName = 'joe',
>tc_pcLastName = 'smith'
>WHERE (tc_titleCoId = 360)
>aaand an insert line just to get a row of data in there
>SET IDENTITY_INSERT t_title_co2 ON
>INSERT INTO [DATABASENAME].[dbo].[t_title_co2]([tc_titleCoId], [tc_name],
>[tc_address], [tc_city], [tc_state], [tc_zip], [tc_zip4], [tc_phone],
>[tc_fax], [tc_webURL], [tc_status], [tc_bankName], [tc_bankCity], [tc_ABA],
>[tc_accountNum], [tc_creditTo], [tc_furtherCredit], [tc_furtherCreditNum],
>[tc_insCloseLtr], [tc_pcFirstName], [tc_pcLastName], [tc_isTitleCo],
>[tc_isEscrowCo], [tc_active])
>VALUES(360, 'moo', '222', 'here', 'mn', '44444', '', '', '', '', '', '', '',
>'', '', '', '', '', 0, '', '', 0, 0, 1)
>SET IDENTITY_INSERT t_title_co2 OFF
>
>the query doesn't have the problem if i remove the tc_state line, or both
>the _isEscrowCo and _isTitleCo lines, or several other things that have no
>rhyme or reason related to tc_active.
>thanks again
> -g
>
|||
> If you still have the problem after upgrading, let us know.
> When I run this on an up-to-date server, the value of tc_active
> remains equal to 1 after the update.
well, the service pack fixed the problem.
i suspected it would since my kept-updated server was fine, and my
devmachine had such odd behavior. I really should have done that sooner, even
if it is an offline devmachine.
ahh, it's nice to have faith in the database again.
thanks for the help--you're a peach ^^
-g