Conversion Failed Due To Data Overflow (Numeric)

问题:

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.

回答1:

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:

1. Change your SQL Server table to accommodate the data in your dbf file.

2. 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!

回答2:

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