[Microsoft][ODBC SQL Server Driver]Numeric value out of range
-
Hello Everyone,
Can someone help me with this error:
[Microsoft][ODBC SQL Server Driver]Numeric value out of range
Issuing this command:
exec sp_executesql N'SELECT sysrecno,access,opermode_id,oprm_usage,transp_mode,one_way,forbidden_turn,u_turns,u_turn_width,u_turn_class,u_turn_dur,deadend_dur,left_turn_dur,inter_cross_dur,side_change,speed,arc_spd_fct,zigz_speed,zigz_arc_spd_fct,arc_speed,both_arc_speed,minimize,max_detour_dis,max_detour_dur,right_turn_dur,u_turn_pen,deadend_pen,left_turn_pen,inter_cross_pen,right_turn_pen,side_change_pen FROM roudev.dbo.oprmchar WHERE opermode_id = @opermode_id AND oprm_usage = @oprm_usage', N'@opermode_id varchar(7),@oprm_usage smallint', @opermode_id = 'NoRestr', @oprm_usage = 0
On this table:
CREATE TABLE oprmchar
( sysrecno binary(8) not null,
access smallint not null,
opermode_id varchar( 8 ) null,
oprm_usage smallint null,
transp_mode smallint null,
one_way tinyint null,
forbidden_turn tinyint null,
u_turns tinyint null,
u_turn_width int null,
u_turn_class smallint null,
u_turn_dur int null,
deadend_dur int null,
left_turn_dur int null,
inter_cross_dur int null,
side_change int null,
speed float null,
arc_spd_fct float null,
zigz_speed float null,
zigz_arc_spd_fct float null,
arc_speed smallint null,
both_arc_speed smallint null,
minimize smallint null,
max_detour_dis int null,
max_detour_dur int null,
right_turn_dur int null,
u_turn_pen int null,
deadend_pen int null,
left_turn_pen int null,
inter_cross_pen int null,
right_turn_pen int null,
side_change_pen int null )
GO
CREATE UNIQUE CLUSTERED INDEX idx1 ON oprmchar
( opermode_id, oprm_usage )
CREATE UNIQUE INDEX ksysoprmchar ON oprmchar (sysrecno)
GO
N.B. The result of this query return no rows.
Maybe its a corruption problem but I see nothing in the log files and nothing in the event viewer.
Best regards,
Carl
-
We finnally found data corruption even if dbbcc checkDB return no errors.
Carl
-
What did you find was the problem? I'm getting the same error and my DBCCs aren't finding any issues.
My table design is:
CREATE TABLE [Cont_Load].[f213] (
[fid] [int] NOT NULL ,
[numofpts] [int] NOT NULL ,
[entity] [smallint] NOT NULL ,
[eminx] [float] NOT NULL ,
[eminy] [float] NOT NULL ,
[emaxx] [float] NOT NULL ,
[emaxy] [float] NOT NULL ,
[eminz] [float] NULL ,
[emaxz] [float] NULL ,
[min_measure] [float] NULL ,
[max_measure] [float] NULL ,
[float] NOT NULL ,
[len] [float] NOT NULL ,
[points] [image] NULL
) ON [Cont_Data] TEXTIMAGE_ON [Cont_Data]
GO
ALTER TABLE [Cont_Load].[f213] WITH NOCHECK ADD
CONSTRAINT [f213_pk] PRIMARY KEY CLUSTERED
(
[fid]
) WITH FILLFACTOR = 90 ON [Cont_Data]
GO
CREATE INDEX [f213_area_ix2] ON [Cont_Load].[f213](
) WITH FILLFACTOR = 90 ON [Cont_Data]
GO
CREATE INDEX [f213_len_ix3] ON [Cont_Load].[f213]([len]) WITH FILLFACTOR = 90 ON [Cont_Data]
GO
It's a table internal to our ESRI GIS system. I didn't notice that there is an image field on it. I was generating checksums to compare backups when I noticed the problem, I was using the following script:
sp_MSforeachtable @command1='select cast(getdate() as smalldatetime) as DateBackedUp,
''?'' as TableName, count(*) as RecCount, CHECKSUM_AGG(BINARY_CHECKSUM(*)) as Checksum from ?'
-----
[font="Arial"]Knowledge is of two kinds. We know a subject ourselves or we know where we can find information upon it. --Samuel Johnson[/font] -
Hello Wayne,
It was in 2005... One year in IT and you see a lot of things...
I don't remember this particular case.
Maybe it was corruption in the database that we noticed wihtout DBCC.
Sorry.
Carl
-
I was hoping. But I was also anticipating that this might be your answer, what's 2 1/2 years between proverbial friends?
-----
[font="Arial"]Knowledge is of two kinds. We know a subject ourselves or we know where we can find information upon it. --Samuel Johnson[/font] -
I think that I've found the error in my case. It relates to having an index on a Float data type and not having a hot fix for SP3A installed (we're on SP3 on this box).
As this database is static and generated by ESRI software, I'm leaving it alone for now and contacting their tech support.
I wanted to post a reply and a link to this Technet KB article: http://support.microsoft.com/kb/900335/en-us in case anyone else runs into this.
The database, if queries in Query Analyzer, did not return any rows if I did a Select Top 100 *. If I did a Select *, it produced the ODBC error. If the table is opened in Enterprise Manager, you can see one of the float fields containing the value "1.#QNAN". Apparently NAN stands for Not a Number.
-----
[font="Arial"]Knowledge is of two kinds. We know a subject ourselves or we know where we can find information upon it. --Samuel Johnson[/font]
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply