conversion error

Hi, can anyone please shed some light on this error:

[OLE DB Destination [466]] Error: There was an error with input column "Price" (518) on input "OLE DB Destination Input" (479). The column status returned was: "Conversion failed because the data value overflowed the specified type.".

The column "price" is a numeric (9)

In the flat file connection manager, the datatype for the price column is a float [dt r4]. I've also tried numeric, etc.

How do I resolve this error?

Thanks much

Don't you have a scale on that Price column? Can't a price have cents?|||

Yes, the price has cents.

|||

sadie wrote:

Yes, the price has cents.

But you said the field is NUMERIC(9). There's no scale, so the cents (decimal) can't be stored.|||

Hmm,

here's what the database says, and the data looks like: xxx.xxxxxxxxxxxx, so that's correct.

type computed length prec scale Price numeric no 9 18 12

|||Okay, so it's a NUMERIC(18,12)

Seems weird for a price field as it can only hold $999. Anyway, back to the problem at hand.... Do you have data that exceeds $999?

EDIT: I apparently can't do math, everyone. I still had "9" stuck in my head. 12 - 9 = 3. |||

I think I am missing something here.

According to this definition of the numeric datatype:

The numeric data type store numbers with a decimal place. When you use this data type you specify the precision (how many numbers total) and scale (how many numbers to the right of the decimal).

So wouldn't a numeric(18,12) be able to hold an 18 digit number with a MINIMUM of 6 digits on the left of the decimal point, and 12 digits on the right?

|||

Nevermind. There is a bad row in the data file. That is what is causing the overflow error.

Thanks

|||

sadie wrote:

I think I am missing something here.

According to this definition of the numeric datatype:

The numeric data type store numbers with a decimal place. When you use this data type you specify the precision (how many numbers total) and scale (how many numbers to the right of the decimal).

So wouldn't a numeric(18,12) be able to hold an 18 digit number with a MINIMUM of 6 digits on the left of the decimal point, and 12 digits on the right?

That is correct.

|||

sadie wrote:

I think I am missing something here.

According to this definition of the numeric datatype:

The numeric data type store numbers with a decimal place. When you use this data type you specify the precision (how many numbers total) and scale (how many numbers to the right of the decimal).

So wouldn't a numeric(18,12) be able to hold an 18 digit number with a MINIMUM of 6 digits on the left of the decimal point, and 12 digits on the right?

Yes, 18 specifies how many significant digits there are, while 12 of those 18 are to the right of the decimal point. Sorry, I'm losing my math mind, apparently.|||

No problem.

Your response gave me the idea to check my data file, and that's how I found the bad rows.

containstable ignored words

Dear sir,

when i am using containtable i am getting sql error saying

Microsoft OLE DB Provider for SQL Server error '80040e14'

A clause of the query contained only ignored words.

what are ignored words and how to avoid this error.

Any suggestion??Ignored words(noise words) are words such as:a, as, etc
you could check them( add/remove) in the default installation path:
C:\Program Files\Common Files\System\MSSearch\Data\Config

You also may encounter Error 7619, "The query contained only ignored words" when using any of the full-text predicates in a full-text query, such as CONTAINS(pr_info, 'between AND king'). The word "between" is an ignored or noise word and the full-text query parser considers this an error, even with an OR clause. Consider rewriting this query to a phrase-based query, removing the noise word, or options offered in Knowledge Base article Q246800, "INF: Correctly Parsing Quotation Marks in FTS Queries". Also, consider using Windows 2000 Server: there have been some enhancements to the word-breaker files for Indexing Services.