Conversion Failed Due To Data Overflow (Numeric)

Question

I am trying to move data from a .dbf file to a table in SQL Server 2008 and am getting the following error on multiple numeric columns:

OLE DB provider "MSDASQL" for linked server "(null)" returned message "Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done.". Msg 7341, Level 16, State 2, Line 1 Cannot get the current row value of column "[MSDASQL].apryr" from OLE DB provider "MSDASQL" for linked server "(null)". Conversion failed because the data value overflowed the data type used by the provider.

It only happens on numeric columns and not on every numeric column. Character data is fine and there is no date/time data that could give any issues.

Here is a sample of the code I'm using:

insert into [table] select * from OPENROWSET('MSDASQL', 'DRIVER=Microsoft Visual FoxPro Driver; SourceDB=[filepath]; SourceType=DBF', 'select * from [file].dbf)

Since the data in the dbf file is customer data, I've been told I can't manually fix the garbage data in the file (assuming there is any) and everything has to be done through the SQL code. I have searched around the internet and haven't really found a solution to this problem. I'd appreciate any help.

Thank you.

Christopher M. Brown · Accepted Answer · 2013-12-05 19:19:59Z

0

Without knowing more specifics, the situation sounds simple enough: There is data in the dbf file that does not match the data(s) type in your SQL Server table. If that is the case, then you have two options:

Change your SQL Server table to accommodate the data in your dbf file.
Do not import data from the dbf file that is causing the issue.

In option #1, you could modify restrictive numeric or date-type fields to varchar or nvarchar fields. Then, you would want to modify any programs that might be assuming certain data types in the dbf file to accommodate varchar or nvarchar data. For instance you could use some kind of try-catch language that tests the conversion of data before letting a program have access to it.

If you decide to go with option #2, you can change your select query to be filter out data that does not meet the field requirements of your SQL Server table(s).

Good luck!

answered Dec 5, 2013 at 19:19

Christopher M. Brown

2,2301 gold badge14 silver badges19 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Eriand42 Over a year ago

It looks like I'm going to have to modify the properties of most of the numeric columns in the dbf files. I'm just not sure how to do that without going in and doing it manually for every individual file.

Oleg Over a year ago

You can modify field type in DBF file using ALTER TABLE SQL statement. If your DBFs have the same field names, this way can highly improve field type changing.

Oleg · Accepted Answer · 2013-12-05 05:42:49Z

0

Check your field types on SQL Server table. Maybe some of them unable to take your DBF's BCD value.

answered Dec 5, 2013 at 5:42

Oleg

8136 silver badges5 bronze badges

Collectives™ on Stack Overflow

Conversion Failed Due To Data Overflow (Numeric)

2 Answers 2

2 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related