DBCC Checktable Problem

Question

Post reply

DBCC Checktable Problem

John Carlson-431129

SSChasing Mays

Points: 613
More actions
July 28, 2008 at 5:47 am

#72770

This one has me stumped. I have a table with the last column being a computed column. After the computation, it is always a 38,0 numeric.
When running a dbcc checktable I get the error
[font="Courier New"]Server: Msg 8115, Level 16, State 1, Line 2
Arithmetic overflow error converting numeric to data type numeric.[/font]
If the index on CheckSumID is dropped, the error does not occur.
I've added the code below to recreate the problem. Any insights would be appreciated.
By the way, this error occurs in SQL 2000 SP3 & SP4, but does NOT occur in SQL 2005.
Thanks,
John
[font="Courier New"]use tempdb
GO
CREATE TABLE [dbo].[ObservancesTest] (
[ObservanceTime] [datetime] NOT NULL ,
[SecChkID] [int] NULL ,
[SourceID] [int] NOT NULL ,
[TargetID] [int] NOT NULL ,
[ObjectID] [int] NULL ,
[CheckSumID] AS (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) + 1.0 * ([ObjectID] % 10000))
) ON [PRIMARY]
GO
set ANSI_PADDING,ANSI_WARNINGS,CONCAT_NULL_YIELDS_NULL,ARITHABORT,QUOTED_IDENTIFIER,ANSI_NULLS on
GO
CREATE INDEX [Observances_AK1] ON [dbo].[ObservancesTest]([CheckSumID]) ON [PRIMARY]
GO
insert ObservancesTest (ObservanceTime, SecChkID, SourceID, TargetID, ObjectID)
values('4/26/2008 1:00:00 AM',500443,1806,2647,10022)
go
dbcc checktable (ObservancesTest)
GO[/font]

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Jo Pattyn SSC-Dedicated Points: 32348 More actions · Answer 1

Indeed weird, creating indexes on that column and selects don't cause problems.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 2

Is this the same problem that was posted on the sqlserver.programing newsgroup?

http://www.microsoft.com/communities/newsgroups/en-us/default.aspx?dg=microsoft.public.sqlserver.programming&mid=df887dcc-8552-4e6c-a215-cde618bd3baa

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

John Carlson-431129 SSChasing Mays Points: 613 More actions · Answer 3

Hi Gail,

You are correct. This looks like my exact problem.

I notice his table is named "Observances" (mine is ObservancesTest, but it is really Observances in the DB), and his deals with a numeric(38,0) also.

I am sure we are using a database from the same vendor product (RealSecure).

Thanks for pointing this out, as this helps to confirm that this seems to be a weird bug that manifests itself in this particular situation with a computed field.

I also notice that he did not specify that his problem field is a computed field. I think this is key, because when I created a manual numeric(38,0) and populated with the exact same number as would be computed, the problem did not exist.

Thanks,

John

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 4

I was just wondering. It's not usual to see exactly the same problem from two different people.

There's an ongoing discussion over this elsewhere, I'll let you know if anything useful pops out.

I know it's a major work around, but until you get a solution, maybe drop the NC index before doing the CheckDB and recreate it after. I don't know if that's practical with the number of rows though.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

John Carlson-431129 SSChasing Mays Points: 613 More actions · Answer 5

Thanks Gail, this actually sounds like a good workaround.

It takes about 30 seconds to recreate this index, so I will probably go with this workaround if it is accepted by the application owners.

Thanks again,

John