July 28, 2008 at 2:26 pm
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]
July 28, 2008 at 2:53 pm
Please don't cross post. It just fragments replies and wastes people's time. Many of us read all the forums.
No replies to this thread please. Direct replies to:
http://www.sqlservercentral.com/Forums/Topic541789-5-1.aspx
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
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply