December 15, 2008 at 5:57 am
I could be wrong here... but...
Somehow you have an index on a computed column that is guaranteed to give a arithmetic overflow error.
For an index on a computer column to work, you need to have ANSI_WARNINGS to ON, which actually sets ARITHABORT to ON.
I'd say its the index on the computed column that's causing you the grief here.
CREATE INDEX [Observances_AK1] ON [dbo].[Observances]([CheckSumID]) ON [PRIMARY]
GO
Can you drop it? It's probably not working anyway.
December 15, 2008 at 6:01 am
Paul Randal (12/14/2008)
Hey George - I had a thought - can you try changing the setting of NUMERIC_ROUNDABORT and see if CHECKTABLE completes? If it does, you might be able to find the dodgy data using data purity checks - try turning on trace flag 2570 when you run it. (You can read about data purity checks in the DBCC CHECKDB BOL in 2005 - there were some available in 2000 but only under that trace flag).Thanks
SQL Server isn't going to like that.
He has an index on his computed column, and to put an index on a computed column SQL Server 2005 requires NUMERIC_ROUNDABORT to be OFF.
I'm almost certain it's that index that's causing the issue, not any underlying problem with the base tables.
December 15, 2008 at 8:27 am
it definitely is the index that upsets dbcc. I am able to retrieve data to usiing that index (according to query plan)
unfortunately being SLQ 2000 hard to prove if index is being used or not. Dropping it is an option, no complaints from app though with it being there., and I can work around it by omitting just this index from dbcc.
---------------------------------------------------------------------
December 15, 2008 at 1:32 pm
December 15, 2008 at 3:09 pm
ta.bu.shi.da.yu (12/15/2008)
Cool...I'm curious, how does that checksum work?
right now I've no idea what that column is used for, its a bought in app used for security monitoring rather than any business function.
The apps called realsecure.
---------------------------------------------------------------------
December 15, 2008 at 9:07 pm
Might be worthwhile logging a call to the company... how they managed to get an index on that column is a bit beyond me 🙂
December 15, 2008 at 9:09 pm
One more question:
What happens if you run:
select (100000000000000000000000000000000 * convert(numeric(6),(convert(int,((convert(numeric(7,2),[ObservanceTime],126) - 35000.00) * 100.0)) % 1000000)) + 1000000000000000000000000.0 * ([SourceID] % 100000000) + 10000000000000000.0 * ([TargetID] % 100000000) + 1000000000.0 * ([SecChkID] % 10000000) + 10000.0 * (isnull([EventUserID],0) % 100000) + 1.0 * ([ObjectID] % 10000)) from Observances
December 16, 2008 at 7:06 am
cs (12/15/2008)
One more question:What happens if you run:
select (100000000000000000000000000000000 * convert(numeric(6),(convert(int,((convert(numeric(7,2),[ObservanceTime],126) - 35000.00) * 100.0)) % 1000000)) + 1000000000000000000000000.0 * ([SourceID] % 100000000) + 10000000000000000.0 * ([TargetID] % 100000000) + 1000000000.0 * ([SecChkID] % 10000000) + 10000.0 * (isnull([EventUserID],0) % 100000) + 1.0 * ([ObjectID] % 10000)) from Observances
it works and returns value 47738801708197016914060500179000000012
which is indicative of the values in the computed column
if your curioosity is piqued I can suppy a few rows from the table
---------------------------------------------------------------------
December 16, 2008 at 7:16 am
[font="Verdana"]
Just a thought...
Looks like it is making a note of security events observed based on the fields in the table.
I think, there might be functions, in there that might be using the value in the column to figure out
which device caused a particular event to fire and decide the level of threat to that device or to put simply the
vulnerability level of the device.
[/font]
December 16, 2008 at 7:25 am
sounds very possible, in which case it would be a column likely to be searched on, and therefore worthy of an index
---------------------------------------------------------------------
December 16, 2008 at 9:21 am
cs (12/15/2008)
Might be worthwhile logging a call to the company... how they managed to get an index on that column is a bit beyond me 🙂
ok, I did a select into a new table for a few rows, then created the index with no problems, so it all seems to hang together!
I really think I just need to work around this.
---------------------------------------------------------------------
Viewing 11 posts - 16 through 25 (of 25 total)
You must be logged in to reply to this topic. Login to reply