由于数据溢出转换失败(数字) [英] Conversion Failed Due To Data Overflow (Numeric)

问题描述

我正在尝试将数据从.dbf文件移动到SQL Server 2008中的表，并且在多个数字列上遇到以下错误:

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:

链接服务器(null)"的OLE DB提供程序"MSDASQL"返回消息多步OLE DB操作生成了错误.检查每个OLE DB状态值(如果可用).未完成工作.". Msg 7341，第16级，状态2，第1行 无法从链接服务器(null)"的OLE DB提供程序"MSDASQL"获取列"[MSDASQL] .apryr"的当前行值.转换失败，因为数据值溢出了提供程序使用的数据类型.

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:

插入[表]，然后从OPENROWSET('MSDASQL'中选择* 'DRIVER = Microsoft Visual FoxPro驱动程序； SourceDB = [文件路径]; SourceType = DBF'， '选择 * 来自[file] .dbf)

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

由于dbf文件中的数据是客户数据，所以我被告知无法手动修复文件中的垃圾数据(假设有任何数据)，并且所有操作都必须通过SQL代码完成.我已经在互联网上搜索了，还没有真正找到解决该问题的方法.我将不胜感激.

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.

谢谢.

推荐答案

在不了解更多细节的情况下，情况听起来很简单:dbf文件中的数据与SQL Server表中的数据类型不匹配. .如果是这种情况，那么您有两个选择:

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. 更改SQL Server表以容纳dbf文件中的数据.

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

请勿从导致问题的dbf文件中导入数据.

Do not import data from the dbf file that is causing the issue.

在选项#1中，您可以将限制性数字或日期类型的字段修改为varchar或nvarchar字段.然后，您可能想要修改任何可能在dbf文件中假设某些数据类型的程序，以容纳varchar或nvarchar数据.例如，您可以使用某种try-catch语言来在允许程序访问数据之前测试数据的转换.

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.

如果决定使用选项#2，则可以将选择查询更改为过滤掉不满足SQL Server表的字段要求的数据.

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).

祝你好运！

这篇关于由于数据溢出转换失败(数字)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！