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.
-gIt 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.aspx?scid=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:
>
>>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
>|||> 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

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

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 informatio
n
for, so I beg for someone's insight please.
-gIt 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/defaul...id=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 wis
h
>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 o
f
>the database... and this is a VERY difficult problem to websearch informati
on
>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 NU
LL
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 NU
LL
CONSTRAINT [tc_city2] DEFAULT (''),
[tc_state] [varchar] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NU
LL
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 NUL
L
CONSTRAINT [tc_zip42] DEFAULT (''),
[tc_phone] [varchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NOT N
ULL
CONSTRAINT [tc_phone2] DEFAULT (''),
[tc_fax] [varchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NUL
L
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 N
ULL
CONSTRAINT [tc_status2] DEFAULT (''),
[tc_bankName] [varchar] (75) COLLATE SQL_Latin1_General_CP1_CI_AS NO
T NULL
CONSTRAINT [tc_bankName2] DEFAULT (''),
[tc_bankCity] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NO
T 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 NO
T 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] DEF
AULT (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_isTit
leCo2]
DEFAULT (0),
[tc_isEscrowCo] [bit] NOT NULL CONSTRAINT [DF_t_title_co_tc_isEs
crowCo2]
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_b
ankCity], [tc_ABA],
[tc_accountNum], [tc_creditTo], [tc_furtherCredit], [tc_furt
herCreditNum],
[tc_insCloseLtr], [tc_pcFirstName], [tc_pcLastName], [tc_isT
itleCo],
[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 N
OT 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 NU
LL
>CONSTRAINT [tc_zip2] DEFAULT (''),
> [tc_zip4] [varchar] (4) COLLATE SQL_Latin1_General_CP1_CI_AS NOT N
ULL
>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 N
ULL
>CONSTRAINT [tc_fax2] DEFAULT (''),
> [tc_webURL] [varchar] (75) COLLATE SQL_Latin1_General_CP1_CI_AS NO
T 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 NU
LL
>CONSTRAINT [tc_ABA2] DEFAULT (''),
> [tc_accountNum] [varchar] (15) COLLATE SQL_Latin1_General_CP1_CI_A
S 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_C
I_AS NOT
>NULL CONSTRAINT [tc_furtherCredit2] DEFAULT (''),
> [tc_furtherCreditNum] [varchar] (20) COLLATE SQL_Latin1_General_CP
1_CI_AS
>NOT NULL CONSTRAINT [tc_furtherCreditNum2] DEFAULT (''),
> [tc_insCloseLtr] [int] NOT NULL CONSTRAINT [tc_insCloseLtr2] D
EFAULT (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_A
S NOT
>NULL CONSTRAINT [tc_pcLastName2] DEFAULT (''),
> [tc_isTitleCo] [bit] NOT NULL CONSTRAINT [DF_t_title_co_tc_isT
itleCo2]
>DEFAULT (0),
> [tc_isEscrowCo] [bit] NOT NULL CONSTRAINT [DF_t_title_co_tc_is
EscrowCo2]
>DEFAULT (0),
> [tc_active] [bit] NOT NULL CONSTRAINT [DF_t_title_co_tc_active
2] 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_titleCoI
d], [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_fur
therCreditNum],
>[tc_insCloseLtr], [tc_pcFirstName], [tc_pcLastName], [tc_is
TitleCo],
>[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, eve
n
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 ^^
-gsql

Favorite Monitoring (Performance) Tools?

I'm fairly new to the DBA thing.
I've been doing quite a bit of research on monitoring MSSQL performance.

What tools are you guys using?
PerfMon, Dbartisan, Spotlight, or what?

I'd like to find some free or affordable way to watch our Servers to check health/performance/etc.If you're using SQL 2005 (SP2), you can use a combination of SQL Health and History (aka SQL H2) and the new SQL Dashboard. Both require SQL Reporting services.

I'm using a set of vb scripts that use WMI queries to gather basic information: storage, network config, os config, installed apps, memory and cpu utilization. These are stored in a database. You can do a google on "wmi computer inventory vb script" and I think it will come up with a variety of examples.

Regards,

hmscott|||"whatsup" has a lot of robust automation for wmi if you're looking for a prepackaged solution. We've had reasonable luck with it so far.

Fatal error exception after SP installation

Hello, I installed one week ago the last SQL SP on SQL 2k5 64 bit / Windows
2003 64 bit. The actual version 9.0.2047. Starting from that day the service
restart periodically (one time a day) because a fatal exception. The
application works well until unexpected shutdown. I tried to look at SQL log
and the log files... Most of them are like in chinese for me...
I had nothing of this before I installed the SP. Probably it is not directly
linked to SP, but I don't kow where to start from? Could it the memory (I
intend to upgrade to 9GB anyway...)
What I can see for instance is the SQL log and dump file:
2006-10-25 09:01:19.32 spid9s SQL Trace stopped. Trace ID = '2'. Login
Name = 'sa'.
2006-10-25 10:50:22.32 spid402 ex_raise2: Exception raised, major=86,
minor=24, state=116, severity=16, attempting to create symptom dump
2006-10-25 10:50:22.35 spid402 Using 'dbghelp.dll' version '4.0.5'
2006-10-25 10:50:23.91 spid402 **Dump thread - spid = 402, PSS = 0x00000000DEC8BF00, EC = 0x00000000DEC8BF10
2006-10-25 10:50:23.91 spid402 *
2006-10-25 10:50:23.91 spid402 * User initiated stack dump. This is not
a server exception dump.
2006-10-25 10:50:23.91 spid402 *
2006-10-25 10:50:23.94 spid402 ***Stack Dump being sent to C:\Program
Files\Microsoft SQL Server\MSSQL.1\MSSQL\LOG\SQLDump1347.txt
2006-10-25 10:50:23.94 spid402 *
*******************************************************************************
2006-10-25 10:50:23.94 spid402 *
2006-10-25 10:50:23.94 spid402 * BEGIN STACK DUMP:
2006-10-25 10:50:23.94 spid402 * 10/25/06 10:50:23 spid 402
2006-10-25 10:50:23.94 spid402 *
2006-10-25 10:50:23.94 spid402 * ex_raise2: Exception raised, major=86,
minor=24, state=116, severity=16
2006-10-25 10:50:23.94 spid402 *
2006-10-25 10:50:23.94 spid402 * Input Buffer 510 bytes -
2006-10-25 10:50:23.94 spid402 * select doc.RefDoc,
RefDocDet_Manque = (select top 1 D.RefDoc
.....
2006-10-25 10:50:27.12 spid402 Error: 8624, Severity: 16, State: 116.
2006-10-25 10:50:27.12 spid402 Internal Query Processor Error: The query
processor could not produce a query plan. For more information, contact
Customer Support Services.
2006-10-25 10:50:34.04 spid402 ex_raise2: Exception raised, major=86,
minor=24, state=116, severity=16, attempting to create symptom dump
and in the dump file:
=====================================================================
BugCheck Dump
=====================================================================
This file is generated by Microsoft SQL Server
version 9.00.2047.00
upon detection of fatal unexpected error. Please return this file,
the query or program that produced the bugcheck, the database and
the error log, and any other pertinent information with a Service Request.
Computer type is AT/AT COMPATIBLE.
Current time is 10:50:34 10/25/06.
4 Unknown CPU 9., 2205 Mhz processor (s).
Windows NT 5.2 Build 3790 CSD Service Pack 1.
Memory
MemoryLoad = 96%
Total Physical = 4951 MB
Available Physical = 180 MB
Total Page File = 6629 MB
Available Page File = 1933 MB
Total Virtual = 8388607 MB
Available Virtual = 8383305 MB
**Dump thread - spid = 402, PSS = 0x00000000DEC8BF00, EC = 0x00000000DEC8BF10
*
* User initiated stack dump. This is not a server exception dump.
*
***Stack Dump being sent to C:\Program Files\Microsoft SQL
Server\MSSQL.1\MSSQL\LOG\SQLDump1348.txt
*
*******************************************************************************
*
* BEGIN STACK DUMP:
* 10/25/06 10:50:34 spid 402
*
* ex_raise2: Exception raised, major=86, minor=24, state=116, severity=16
*
* Input Buffer 510 bytes -
* select doc.RefDoc, RefDocDet_Manque = (select top 1 D.RefDo
Thnaks in advance for any helpHi
Error 8624 is an internal SQL Server error so you may want to contact PSS.
Where is the query select doc.RefDoc, RefDocDet_Manque = (select top 1
D.RefDoc...?
John
"Catalin NASTAC" wrote:
> Hello, I installed one week ago the last SQL SP on SQL 2k5 64 bit / Windows
> 2003 64 bit. The actual version 9.0.2047. Starting from that day the service
> restart periodically (one time a day) because a fatal exception. The
> application works well until unexpected shutdown. I tried to look at SQL log
> and the log files... Most of them are like in chinese for me...
> I had nothing of this before I installed the SP. Probably it is not directly
> linked to SP, but I don't kow where to start from? Could it the memory (I
> intend to upgrade to 9GB anyway...)
> What I can see for instance is the SQL log and dump file:
> 2006-10-25 09:01:19.32 spid9s SQL Trace stopped. Trace ID = '2'. Login
> Name = 'sa'.
> 2006-10-25 10:50:22.32 spid402 ex_raise2: Exception raised, major=86,
> minor=24, state=116, severity=16, attempting to create symptom dump
> 2006-10-25 10:50:22.35 spid402 Using 'dbghelp.dll' version '4.0.5'
> 2006-10-25 10:50:23.91 spid402 **Dump thread - spid = 402, PSS => 0x00000000DEC8BF00, EC = 0x00000000DEC8BF10
> 2006-10-25 10:50:23.91 spid402 *
> 2006-10-25 10:50:23.91 spid402 * User initiated stack dump. This is not
> a server exception dump.
> 2006-10-25 10:50:23.91 spid402 *
> 2006-10-25 10:50:23.94 spid402 ***Stack Dump being sent to C:\Program
> Files\Microsoft SQL Server\MSSQL.1\MSSQL\LOG\SQLDump1347.txt
> 2006-10-25 10:50:23.94 spid402 *
> *******************************************************************************
> 2006-10-25 10:50:23.94 spid402 *
> 2006-10-25 10:50:23.94 spid402 * BEGIN STACK DUMP:
> 2006-10-25 10:50:23.94 spid402 * 10/25/06 10:50:23 spid 402
> 2006-10-25 10:50:23.94 spid402 *
> 2006-10-25 10:50:23.94 spid402 * ex_raise2: Exception raised, major=86,
> minor=24, state=116, severity=16
> 2006-10-25 10:50:23.94 spid402 *
> 2006-10-25 10:50:23.94 spid402 * Input Buffer 510 bytes -
> 2006-10-25 10:50:23.94 spid402 * select doc.RefDoc,
> RefDocDet_Manque = (select top 1 D.RefDoc
> .....
> 2006-10-25 10:50:27.12 spid402 Error: 8624, Severity: 16, State: 116.
> 2006-10-25 10:50:27.12 spid402 Internal Query Processor Error: The query
> processor could not produce a query plan. For more information, contact
> Customer Support Services.
> 2006-10-25 10:50:34.04 spid402 ex_raise2: Exception raised, major=86,
> minor=24, state=116, severity=16, attempting to create symptom dump
>
> and in the dump file:
> =====================================================================> BugCheck Dump
> =====================================================================>
> This file is generated by Microsoft SQL Server
> version 9.00.2047.00
> upon detection of fatal unexpected error. Please return this file,
> the query or program that produced the bugcheck, the database and
> the error log, and any other pertinent information with a Service Request.
>
>
> Computer type is AT/AT COMPATIBLE.
> Current time is 10:50:34 10/25/06.
> 4 Unknown CPU 9., 2205 Mhz processor (s).
> Windows NT 5.2 Build 3790 CSD Service Pack 1.
>
> Memory
> MemoryLoad = 96%
> Total Physical = 4951 MB
> Available Physical = 180 MB
> Total Page File = 6629 MB
> Available Page File = 1933 MB
> Total Virtual = 8388607 MB
> Available Virtual = 8383305 MB
> **Dump thread - spid = 402, PSS = 0x00000000DEC8BF00, EC => 0x00000000DEC8BF10
> *
> * User initiated stack dump. This is not a server exception dump.
> *
> ***Stack Dump being sent to C:\Program Files\Microsoft SQL
> Server\MSSQL.1\MSSQL\LOG\SQLDump1348.txt
> *
> *******************************************************************************
> *
> * BEGIN STACK DUMP:
> * 10/25/06 10:50:34 spid 402
> *
> * ex_raise2: Exception raised, major=86, minor=24, state=116, severity=16
> *
> * Input Buffer 510 bytes -
> * select doc.RefDoc, RefDocDet_Manque = (select top 1 D.RefDo
>
>
> Thnaks in advance for any help|||That query is a script file that I execute occasionally. It worked without
problems and it makes no problem except that day.
But all the others fatal errors are generated not by file scripts, but by
stored procs. But it is not the same each time... For example in the next log
is stated dbo.ts3_clsAPI_Membres_Upt.
I will install today the patch kb918222 (buid 2153) to see if it will change
something...
I also verify all my SPs and views, functions to use ANSI_NULL ON
Another log file:
Memory
MemoryLoad = 94%
Total Physical = 4951 MB
Available Physical = 273 MB
Total Page File = 6629 MB
Available Page File = 1585 MB
Total Virtual = 8388607 MB
Available Virtual = 8383233 MB
***Stack Dump being sent to C:\Program Files\Microsoft SQL
Server\MSSQL.1\MSSQL\LOG\SQLDump2164.txt
SqlDumpExceptionHandler: Process 61 generated fatal exception c0000005
EXCEPTION_ACCESS_VIOLATION. SQL Server is
terminating this process.
*
*******************************************************************************
*
* BEGIN STACK DUMP:
* 10/26/06 16:32:32 spid 61
*
*
* Exception Address = 0000000001595ECD Module(sqlservr+0000000000595ECD)
* Exception Code = c0000005 EXCEPTION_ACCESS_VIOLATION
* Access Violation occurred writing address 000000016CBF3010
* Input Buffer 255 bytes -
* d b o . t s 3 _ 1a 00 64 00 62 00 6f 00 2e 00 74 00 73 00 33 00 5f 00
* c l s A P I _ M e 63 00 6c 00 73 00 41 00 50 00 49 00 5f 00 4d 00 65 00
* m b r e s _ U p t 6d 00 62 00 72 00 65 00 73 00 5f 00 55 00 70 00 74 00
* & & 00 00 00 00 26 01 01 01 00 02 1f 00 00 26 04 00 00 02
* & §2 2 1f 00 00 26 01 01 02 00 00 a7 32 00 09 04 00 01 32 08
* AVETSIAN §2 00 41 56 45 54 53 49 41 4e 00 00 a7 32 00 09 04 00 01
* 2 Rousana §� 32 07 00 52 6f 75 73 61 6e 61 00 00 a7 96 00 09 04 00
* 2 527 DUDEMAINE 01 32 14 00 35 32 37 20 44 55 44 45 4d 41 49 4e 45 20
* APP.16 & ? & 41 50 50 2e 31 36 00 00 26 04 04 3f 01 00 00 00 00 26
"John Bell" wrote:
> Hi
> Error 8624 is an internal SQL Server error so you may want to contact PSS.
> Where is the query select doc.RefDoc, RefDocDet_Manque = (select top 1
> D.RefDoc...?
> John
>|||Hi
If installing the hotfix does not cure this I would call PSS.
John
"Catalin NASTAC" wrote:
> That query is a script file that I execute occasionally. It worked without
> problems and it makes no problem except that day.
> But all the others fatal errors are generated not by file scripts, but by
> stored procs. But it is not the same each time... For example in the next log
> is stated dbo.ts3_clsAPI_Membres_Upt.
> I will install today the patch kb918222 (buid 2153) to see if it will change
> something...
> I also verify all my SPs and views, functions to use ANSI_NULL ON
> Another log file:
> Memory
> MemoryLoad = 94%
> Total Physical = 4951 MB
> Available Physical = 273 MB
> Total Page File = 6629 MB
> Available Page File = 1585 MB
> Total Virtual = 8388607 MB
> Available Virtual = 8383233 MB
> ***Stack Dump being sent to C:\Program Files\Microsoft SQL
> Server\MSSQL.1\MSSQL\LOG\SQLDump2164.txt
> SqlDumpExceptionHandler: Process 61 generated fatal exception c0000005
> EXCEPTION_ACCESS_VIOLATION. SQL Server is
> terminating this process.
> *
> *******************************************************************************
> *
> * BEGIN STACK DUMP:
> * 10/26/06 16:32:32 spid 61
> *
> *
> * Exception Address = 0000000001595ECD Module(sqlservr+0000000000595ECD)
> * Exception Code = c0000005 EXCEPTION_ACCESS_VIOLATION
> * Access Violation occurred writing address 000000016CBF3010
> * Input Buffer 255 bytes -
> * d b o . t s 3 _ 1a 00 64 00 62 00 6f 00 2e 00 74 00 73 00 33 00 5f 00
> * c l s A P I _ M e 63 00 6c 00 73 00 41 00 50 00 49 00 5f 00 4d 00 65 00
> * m b r e s _ U p t 6d 00 62 00 72 00 65 00 73 00 5f 00 55 00 70 00 74 00
> * & & 00 00 00 00 26 01 01 01 00 02 1f 00 00 26 04 00 00 02
> * & §2 2 1f 00 00 26 01 01 02 00 00 a7 32 00 09 04 00 01 32 08
> * AVETSIAN §2 00 41 56 45 54 53 49 41 4e 00 00 a7 32 00 09 04 00 01
> * 2 Rousana §� 32 07 00 52 6f 75 73 61 6e 61 00 00 a7 96 00 09 04 00
> * 2 527 DUDEMAINE 01 32 14 00 35 32 37 20 44 55 44 45 4d 41 49 4e 45 20
> * APP.16 & ? & 41 50 50 2e 31 36 00 00 26 04 04 3f 01 00 00 00 00 26
>
>
> "John Bell" wrote:
> > Hi
> >
> > Error 8624 is an internal SQL Server error so you may want to contact PSS.
> >
> > Where is the query select doc.RefDoc, RefDocDet_Manque = (select top 1
> > D.RefDoc...?
> >
> > John
> >
>|||Finally, I think that I find the problem. I have a function like GetParam
(@.ParamName as varchar) as int and a procedure that it was called very often,
and it contains:
select Param1 = dbo.GetParam (@.Param1),
Param2 = dbo.GetParam (@.Param2),
Param3 = dbo.GetParam (@.Param3),
...
and so on, around 100 parammeters is the same select, using the same
function (don't tell me that makes nosense to return 100 columns. It is an
old application made by others :o(
The function GetParam called another function which called another funtion
and so on. It seems the the optimizer crashes because of the complexity of
this. It works with some users connected in the same time, but with arround
100 users is the same time...
I rewrite the procedure to store each value in scalar variables (set
@.ParamValue1 = dbo.GetParam (@.Param1) and so on) and select @.ParamValue1,
@.ParamValue2 etc.
Now it works 5 times faster (the SP) and without crashes. Seems that I have
to consider all old SPs, views and functions to try to eliminate this kind of
complexity...
"John Bell" wrote:
> Hi
> If installing the hotfix does not cure this I would call PSS.
> John
> "Catalin NASTAC" wrote:
> > That query is a script file that I execute occasionally. It worked without
> > problems and it makes no problem except that day.
> >
> > But all the others fatal errors are generated not by file scripts, but by
> > stored procs. But it is not the same each time... For example in the next log
> > is stated dbo.ts3_clsAPI_Membres_Upt.
> >
> > I will install today the patch kb918222 (buid 2153) to see if it will change
> > something...
> > I also verify all my SPs and views, functions to use ANSI_NULL ON
> >
> > Another log file:
> > Memory
> > MemoryLoad = 94%
> > Total Physical = 4951 MB
> > Available Physical = 273 MB
> > Total Page File = 6629 MB
> > Available Page File = 1585 MB
> > Total Virtual = 8388607 MB
> > Available Virtual = 8383233 MB
> > ***Stack Dump being sent to C:\Program Files\Microsoft SQL
> > Server\MSSQL.1\MSSQL\LOG\SQLDump2164.txt
> > SqlDumpExceptionHandler: Process 61 generated fatal exception c0000005
> > EXCEPTION_ACCESS_VIOLATION. SQL Server is
> > terminating this process.
> >
> > *
> > *******************************************************************************
> > *
> >
> > * BEGIN STACK DUMP:
> >
> > * 10/26/06 16:32:32 spid 61
> >
> > *
> >
> > *
> >
> > * Exception Address = 0000000001595ECD Module(sqlservr+0000000000595ECD)
> >
> > * Exception Code = c0000005 EXCEPTION_ACCESS_VIOLATION
> >
> > * Access Violation occurred writing address 000000016CBF3010
> >
> > * Input Buffer 255 bytes -
> >
> > * d b o . t s 3 _ 1a 00 64 00 62 00 6f 00 2e 00 74 00 73 00 33 00 5f 00
> >
> > * c l s A P I _ M e 63 00 6c 00 73 00 41 00 50 00 49 00 5f 00 4d 00 65 00
> >
> > * m b r e s _ U p t 6d 00 62 00 72 00 65 00 73 00 5f 00 55 00 70 00 74 00
> >
> > * & & 00 00 00 00 26 01 01 01 00 02 1f 00 00 26 04 00 00 02
> >
> > * & §2 2 1f 00 00 26 01 01 02 00 00 a7 32 00 09 04 00 01 32 08
> >
> > * AVETSIAN §2 00 41 56 45 54 53 49 41 4e 00 00 a7 32 00 09 04 00 01
> >
> > * 2 Rousana §� 32 07 00 52 6f 75 73 61 6e 61 00 00 a7 96 00 09 04 00
> >
> > * 2 527 DUDEMAINE 01 32 14 00 35 32 37 20 44 55 44 45 4d 41 49 4e 45 20
> >
> > * APP.16 & ? & 41 50 50 2e 31 36 00 00 26 04 04 3f 01 00 00 00 00 26
> >
> >
> >
> >
> > "John Bell" wrote:
> >
> > > Hi
> > >
> > > Error 8624 is an internal SQL Server error so you may want to contact PSS.
> > >
> > > Where is the query select doc.RefDoc, RefDocDet_Manque = (select top 1
> > > D.RefDoc...?
> > >
> > > John
> > >
> >

Fatal error exception after SP installation

Hello, I installed one week ago the last SQL SP on SQL 2k5 64 bit / Windows
2003 64 bit. The actual version 9.0.2047. Starting from that day the service
restart periodically (one time a day) because a fatal exception. The
application works well until unexpected shutdown. I tried to look at SQL log
and the log files... Most of them are like in chinese for me...
I had nothing of this before I installed the SP. Probably it is not directly
linked to SP, but I don't kow where to start from? Could it the memory (I
intend to upgrade to 9GB anyway...)
What I can see for instance is the SQL log and dump file:
2006-10-25 09:01:19.32 spid9s SQL Trace stopped. Trace ID = '2'. Login
Name = 'sa'.
2006-10-25 10:50:22.32 spid402 ex_raise2: Exception raised, major=86,
minor=24, state=116, severity=16, attempting to create symptom dump
2006-10-25 10:50:22.35 spid402 Using 'dbghelp.dll' version '4.0.5'
2006-10-25 10:50:23.91 spid402 **Dump thread - spid = 402, PSS =
0x00000000DEC8BF00, EC = 0x00000000DEC8BF10
2006-10-25 10:50:23.91 spid402 *
2006-10-25 10:50:23.91 spid402 * User initiated stack dump. This is not
a server exception dump.
2006-10-25 10:50:23.91 spid402 *
2006-10-25 10:50:23.94 spid402 ***Stack Dump being sent to C:\Program
Files\Microsoft SQL Server\MSSQL.1\MSSQL\LOG\SQLDump1347.txt
2006-10-25 10:50:23.94 spid402 *
****************************************
************************************
***
2006-10-25 10:50:23.94 spid402 *
2006-10-25 10:50:23.94 spid402 * BEGIN STACK DUMP:
2006-10-25 10:50:23.94 spid402 * 10/25/06 10:50:23 spid 402
2006-10-25 10:50:23.94 spid402 *
2006-10-25 10:50:23.94 spid402 * ex_raise2: Exception raised, major=86,
minor=24, state=116, severity=16
2006-10-25 10:50:23.94 spid402 *
2006-10-25 10:50:23.94 spid402 * Input Buffer 510 bytes -
2006-10-25 10:50:23.94 spid402 * select doc.RefDoc,
RefDocDet_Manque = (select top 1 D.RefDoc
.....
2006-10-25 10:50:27.12 spid402 Error: 8624, Severity: 16, State: 116.
2006-10-25 10:50:27.12 spid402 Internal Query Processor Error: The query
processor could not produce a query plan. For more information, contact
Customer Support Services.
2006-10-25 10:50:34.04 spid402 ex_raise2: Exception raised, major=86,
minor=24, state=116, severity=16, attempting to create symptom dump
and in the dump file:
========================================
=============================
BugCheck Dump
========================================
=============================
This file is generated by Microsoft SQL Server
version 9.00.2047.00
upon detection of fatal unexpected error. Please return this file,
the query or program that produced the bugcheck, the database and
the error log, and any other pertinent information with a Service Request.
Computer type is AT/AT COMPATIBLE.
Current time is 10:50:34 10/25/06.
4 Unknown CPU 9., 2205 Mhz processor (s).
Windows NT 5.2 Build 3790 CSD Service Pack 1.
Memory
MemoryLoad = 96%
Total Physical = 4951 MB
Available Physical = 180 MB
Total Page File = 6629 MB
Available Page File = 1933 MB
Total Virtual = 8388607 MB
Available Virtual = 8383305 MB
**Dump thread - spid = 402, PSS = 0x00000000DEC8BF00, EC =
0x00000000DEC8BF10
*
* User initiated stack dump. This is not a server exception dump.
*
***Stack Dump being sent to C:\Program Files\Microsoft SQL
Server\MSSQL.1\MSSQL\LOG\SQLDump1348.txt
*
****************************************
************************************
***
*
* BEGIN STACK DUMP:
* 10/25/06 10:50:34 spid 402
*
* ex_raise2: Exception raised, major=86, minor=24, state=116, severity=16
*
* Input Buffer 510 bytes -
* select doc.RefDoc, RefDocDet_Manque = (select top 1 D.RefDo
Thnaks in advance for any helpHi
Error 8624 is an internal SQL Server error so you may want to contact PSS.
Where is the query select doc.RefDoc, RefDocDet_Manque = (select top 1
D.RefDoc...?
John
"Catalin NASTAC" wrote:

> Hello, I installed one week ago the last SQL SP on SQL 2k5 64 bit / Window
s
> 2003 64 bit. The actual version 9.0.2047. Starting from that day the servi
ce
> restart periodically (one time a day) because a fatal exception. The
> application works well until unexpected shutdown. I tried to look at SQL l
og
> and the log files... Most of them are like in chinese for me...
> I had nothing of this before I installed the SP. Probably it is not direct
ly
> linked to SP, but I don't kow where to start from? Could it the memory (I
> intend to upgrade to 9GB anyway...)
> What I can see for instance is the SQL log and dump file:
> 2006-10-25 09:01:19.32 spid9s SQL Trace stopped. Trace ID = '2'. Logi
n
> Name = 'sa'.
> 2006-10-25 10:50:22.32 spid402 ex_raise2: Exception raised, major=86,
> minor=24, state=116, severity=16, attempting to create symptom dump
> 2006-10-25 10:50:22.35 spid402 Using 'dbghelp.dll' version '4.0.5'
> 2006-10-25 10:50:23.91 spid402 **Dump thread - spid = 402, PSS =
> 0x00000000DEC8BF00, EC = 0x00000000DEC8BF10
> 2006-10-25 10:50:23.91 spid402 *
> 2006-10-25 10:50:23.91 spid402 * User initiated stack dump. This is n
ot
> a server exception dump.
> 2006-10-25 10:50:23.91 spid402 *
> 2006-10-25 10:50:23.94 spid402 ***Stack Dump being sent to C:\Program
> Files\Microsoft SQL Server\MSSQL.1\MSSQL\LOG\SQLDump1347.txt
> 2006-10-25 10:50:23.94 spid402 *
> ****************************************
**********************************
*****
> 2006-10-25 10:50:23.94 spid402 *
> 2006-10-25 10:50:23.94 spid402 * BEGIN STACK DUMP:
> 2006-10-25 10:50:23.94 spid402 * 10/25/06 10:50:23 spid 402
> 2006-10-25 10:50:23.94 spid402 *
> 2006-10-25 10:50:23.94 spid402 * ex_raise2: Exception raised, major=86
,
> minor=24, state=116, severity=16
> 2006-10-25 10:50:23.94 spid402 *
> 2006-10-25 10:50:23.94 spid402 * Input Buffer 510 bytes -
> 2006-10-25 10:50:23.94 spid402 * select doc.RefDoc,
> RefDocDet_Manque = (select top 1 D.RefDoc
> .....
> 2006-10-25 10:50:27.12 spid402 Error: 8624, Severity: 16, State: 116.
> 2006-10-25 10:50:27.12 spid402 Internal Query Processor Error: The que
ry
> processor could not produce a query plan. For more information, contact
> Customer Support Services.
> 2006-10-25 10:50:34.04 spid402 ex_raise2: Exception raised, major=86,
> minor=24, state=116, severity=16, attempting to create symptom dump
>
> and in the dump file:
> ========================================
=============================
> BugCheck Dump
> ========================================
=============================
>
> This file is generated by Microsoft SQL Server
> version 9.00.2047.00
> upon detection of fatal unexpected error. Please return this file,
> the query or program that produced the bugcheck, the database and
> the error log, and any other pertinent information with a Service Request.
>
>
> Computer type is AT/AT COMPATIBLE.
> Current time is 10:50:34 10/25/06.
> 4 Unknown CPU 9., 2205 Mhz processor (s).
> Windows NT 5.2 Build 3790 CSD Service Pack 1.
>
> Memory
> MemoryLoad = 96%
> Total Physical = 4951 MB
> Available Physical = 180 MB
> Total Page File = 6629 MB
> Available Page File = 1933 MB
> Total Virtual = 8388607 MB
> Available Virtual = 8383305 MB
> **Dump thread - spid = 402, PSS = 0x00000000DEC8BF00, EC =
> 0x00000000DEC8BF10
> *
> * User initiated stack dump. This is not a server exception dump.
> *
> ***Stack Dump being sent to C:\Program Files\Microsoft SQL
> Server\MSSQL.1\MSSQL\LOG\SQLDump1348.txt
> *
> ****************************************
**********************************
*****
> *
> * BEGIN STACK DUMP:
> * 10/25/06 10:50:34 spid 402
> *
> * ex_raise2: Exception raised, major=86, minor=24, state=116, severity=16
> *
> * Input Buffer 510 bytes -
> * select doc.RefDoc, RefDocDet_Manque = (select top 1 D.RefD
o
>
>
> Thnaks in advance for any help|||That query is a script file that I execute occasionally. It worked without
problems and it makes no problem except that day.
But all the others fatal errors are generated not by file scripts, but by
stored procs. But it is not the same each time... For example in the next lo
g
is stated dbo.ts3_clsAPI_Membres_Upt.
I will install today the patch kb918222 (buid 2153) to see if it will change
something...
I also verify all my SPs and views, functions to use ANSI_NULL ON
Another log file:
Memory
MemoryLoad = 94%
Total Physical = 4951 MB
Available Physical = 273 MB
Total Page File = 6629 MB
Available Page File = 1585 MB
Total Virtual = 8388607 MB
Available Virtual = 8383233 MB
***Stack Dump being sent to C:\Program Files\Microsoft SQL
Server\MSSQL.1\MSSQL\LOG\SQLDump2164.txt
SqlDumpExceptionHandler: Process 61 generated fatal exception c0000005
EXCEPTION_ACCESS_VIOLATION. SQL Server is
terminating this process.
*
****************************************
************************************
***
*
* BEGIN STACK DUMP:
* 10/26/06 16:32:32 spid 61
*
*
* Exception Address = 0000000001595ECD Module(sqlservr+0000000000595ECD)
* Exception Code = c0000005 EXCEPTION_ACCESS_VIOLATION
* Access Violation occurred writing address 000000016CBF3010
* Input Buffer 255 bytes -
* d b o . t s 3 _ 1a 00 64 00 62 00 6f 00 2e 00 74 00 73 00 33 00 5f 00
* c l s A P I _ M e 63 00 6c 00 73 00 41 00 50 00 49 00 5f 00 4d 00 65 00
* m b r e s _ U p t 6d 00 62 00 72 00 65 00 73 00 5f 00 55 00 70 00 74 00
* & & 00 00 00 00 26 01 01 01 00 02 1f 00 00 26 04 00 00 02
* & §2 2 1f 00 00 26 01 01 02 00 00 a7 32 00 09 04 00 01 32 08
* AVETSIAN §2 00 41 56 45 54 53 49 41 4e 00 00 a7 32 00 09 04 00 01
* 2 Rousana §? 32 07 00 52 6f 75 73 61 6e 61 00 00 a7 96 00 09 04 0
0
* 2 527 DUDEMAINE 01 32 14 00 35 32 37 20 44 55 44 45 4d 41 49 4e 45 20
* APP.16 & ? & 41 50 50 2e 31 36 00 00 26 04 04 3f 01 00 00 00 00 26
"John Bell" wrote:

> Hi
> Error 8624 is an internal SQL Server error so you may want to contact PSS.
> Where is the query select doc.RefDoc, RefDocDet_Manque = (select top 1
> D.RefDoc...?
> John
>|||Hi
If installing the hotfix does not cure this I would call PSS.
John
"Catalin NASTAC" wrote:

> That query is a script file that I execute occasionally. It worked without
> problems and it makes no problem except that day.
> But all the others fatal errors are generated not by file scripts, but by
> stored procs. But it is not the same each time... For example in the next
log
> is stated dbo.ts3_clsAPI_Membres_Upt.
> I will install today the patch kb918222 (buid 2153) to see if it will chan
ge
> something...
> I also verify all my SPs and views, functions to use ANSI_NULL ON
> Another log file:
> Memory
> MemoryLoad = 94%
> Total Physical = 4951 MB
> Available Physical = 273 MB
> Total Page File = 6629 MB
> Available Page File = 1585 MB
> Total Virtual = 8388607 MB
> Available Virtual = 8383233 MB
> ***Stack Dump being sent to C:\Program Files\Microsoft SQL
> Server\MSSQL.1\MSSQL\LOG\SQLDump2164.txt
> SqlDumpExceptionHandler: Process 61 generated fatal exception c0000005
> EXCEPTION_ACCESS_VIOLATION. SQL Server is
> terminating this process.
> *
> ****************************************
**********************************
*****
> *
> * BEGIN STACK DUMP:
> * 10/26/06 16:32:32 spid 61
> *
> *
> * Exception Address = 0000000001595ECD Module(sqlservr+0000000000595ECD)
> * Exception Code = c0000005 EXCEPTION_ACCESS_VIOLATION
> * Access Violation occurred writing address 000000016CBF3010
> * Input Buffer 255 bytes -
> * d b o . t s 3 _ 1a 00 64 00 62 00 6f 00 2e 00 74 00 73 00 33 00 5f 0
0
> * c l s A P I _ M e 63 00 6c 00 73 00 41 00 50 00 49 00 5f 00 4d 00 65 0
0
> * m b r e s _ U p t 6d 00 62 00 72 00 65 00 73 00 5f 00 55 00 70 00 74 0
0
> * & & 00 00 00 00 26 01 01 01 00 02 1f 00 00 26 04 00 00 0
2
> * & §2 2 1f 00 00 26 01 01 02 00 00 a7 32 00 09 04 00 01 32
08
> * AVETSIAN §2 00 41 56 45 54 53 49 41 4e 00 00 a7 32 00 09 04 00
01
> * 2 Rousana §? 32 07 00 52 6f 75 73 61 6e 61 00 00 a7 96 00 09 04
00
> * 2 527 DUDEMAINE 01 32 14 00 35 32 37 20 44 55 44 45 4d 41 49 4e 45 2
0
> * APP.16 & ? & 41 50 50 2e 31 36 00 00 26 04 04 3f 01 00 00 00 00 2
6
>
>
> "John Bell" wrote:
>
>|||Finally, I think that I find the problem. I have a function like GetParam
(@.ParamName as varchar) as int and a procedure that it was called very often
,
and it contains:
select Param1 = dbo.GetParam (@.Param1),
Param2 = dbo.GetParam (@.Param2),
Param3 = dbo.GetParam (@.Param3),
...
and so on, around 100 parammeters is the same select, using the same
function (don't tell me that makes nosense to return 100 columns. It is an
old application made by others :o(
The function GetParam called another function which called another funtion
and so on. It seems the the optimizer crashes because of the complexity of
this. It works with some users connected in the same time, but with arround
100 users is the same time...
I rewrite the procedure to store each value in scalar variables (set
@.ParamValue1 = dbo.GetParam (@.Param1) and so on) and select @.ParamValue1,
@.ParamValue2 etc.
Now it works 5 times faster (the SP) and without crashes. Seems that I have
to consider all old SPs, views and functions to try to eliminate this kind o
f
complexity...
"John Bell" wrote:
[vbcol=seagreen]
> Hi
> If installing the hotfix does not cure this I would call PSS.
> John
> "Catalin NASTAC" wrote:
>

Fastest way to alter table structure

I'm writing a database table designer (works like the Enterprise Manager one, but with restricted datatypes and some checks), and I have a bit of a question:

To alter the structure of a table, I'm currently using the same method as Enterprise Manager (create a temporary table with the new schema, copy the data across, drop the original table, rename the temporary table).

When altering the structure of a table with a large number of rows, but not changing the order of columns or adding columns in the middle of the table:
Would it be faster to execute multiple ALTER TABLE statements? If so, at what point would it become slower? (Say, changing lots of column data types, etc).Thanks

Probably always faster using ALTER TABLE.

I really doubt that ALTER TABLE statements would be noticably slower.

|||Thanks.

I was worried that if there were a lot of column data type changes there could be a lot of conversions, and a lot of overhead from going through the entire table multiple times.
|||I guess that could happen -depends upon the quantity and type of changes.

Failure audit - the basics

Hi,

I'm a bit new to administering a SQL Server and this seems like a pretty basic question, but I'm not sure how to phrase it for the searches. So I apologize for seeking an indulgence...

I have SQL Server 2005 Standard edition running on a server exposed to the Internet. A handful of clients have to connect to it via TCP/IP using SQL Server Studio and ODBC links. But my windows logs are chok-full of failure audits of what I presume to be your garden variety crackers trying default passwords -- several times a minute.

What's the best solution to this, and how would I go about implementing it? Restrict TCP/IP access to certain IP ranges? Is there a 'max login attempts' somewhere? The server uses SQL authentication (not windows) if that makes a difference.

Thanks for the help!

You would need a firewall (or configure it properly) between the Server and the internet to restrict access to IP ranges. As the server uses SQL Server authentication it also uses Windows Authentication. There are two modes Mixed authentication (which includes SQL Server and Windows authentication) and Windows Authentication only. If you have chosen SQL Server authentication for the login and you installed the SQL Server 2005 on a Windows 2003 Server box, you can use the option of password policies, which also will allow you to lock out a account after several unsuccessfull login attempts. But this won′t help you further, because your legal users will be not happy about the fact, that their accounts will be locked out from time to time, although they didn′t try to log on to the server, because another illegal user tried to guess the password. So the only thing would be to prevent access to invalid users actively with a firewall.

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de