Using DTS, I am trying to import a numeric field (5,3) in Visual Foxpro into
a field in a SQL table, but keep getting this not-very-helpful error message:

"Error at source for row # 12. Multiple-step OLE DB operation generated
errors. Check each OLD DB status value, if available. No work was done."
(Row # 12 is the first row with a value >= 1.0.) As long as the source file
contains only values <1.0, the import works. (All other columns import
without a problem.) I have tried importing into a decimal data type field
using various combinations of precision and scale values in the SQL table
column definition. Likewise, I have tried importing into a floating data
type field. All to no avail. Anybody have any ideas?

Hi Frank,

An N(5, 3) field has width 5 including the decimal point. Since Numeric
fields are stored as characters in Fox tables there are 5 characters
allocated but you can actually enter invalid values such as 123.5 in the
field. This confuses your middleware (Jet, ODBC, OLE DB) and the result is
that it won't import the data.

Is this a one-time operation with a (relatively) small table that you could
zip up and send to me? I could change the field to a different data type
that would import successfully.

--
Cindy Winegarden MCSD, Microsoft Most Valuable Professional
cindy (AT) cindywinegarden (DOT) com


"Frank Ford" <FrankFord (AT) discussions (DOT) microsoft.com> wrote

Thanks for the information, Cindy.

After posting this question yesterday, I spent the entire day struggling
with the issue and eventually learned quite a bit -- some of it rather
discouraging. Basically, it appears that for importing and exporting numeric
fields, the only Foxpro precision that doesn't generate an error is (15,9).
(I dug up a mention of this with regard to exports in the MSFT KB, then by
experimenting found it applies to imports, as well.) The result is that
you cannot import or export data with a value > 9999.999999999 or <
-999.999999999. (You would think you could go to 99999, but it doesn't work
-- I tried. Even though the (15,9) Foxpro field looks fine with a number
larger than 9999, DTS chokes as soon as it encounters it).

For my purposes, since on an ongoing basis I'm importing huge files
containing large numbers, this "feature" makes DTS utterly unusable.
Untimately, I found that the only approach that works is to first export from
Foxpro to a text file, then import the text file into SQL (inefficient and a
pain to set up, but it gets the job done.)

Thanks again for your help and your offer.

Frank

"Cindy Winegarden" wrote: