问题描述
我正在从进度数据库中导入数据.
I'm importing data from a progress database.
我遇到以下错误:
是否可以在select语句中指定选择列数据的特定长度?
Is there a way to specify a specific length of the select column's data in the select statement?
例如:SELECT SUBSTRING(EMAIL,15) FROM SQL92.PROGRESSTABLE
SUBSTRING确实为我提供了有效字段值的子字符串，但是当数据集到达脏"行时仍然失败，并显示上述错误.
SUBSTRING does give me the substring of a valid field value, but still fails with the above error when the dataset hits the "dirty" row.
我无权访问Progress数据库，因此无法运行Progress DBTool来修复数据.
I don't have access to the Progress database, so I can't run the progress DBTool to fix data.
在这里提出了同样的问题，但是解决方案从未发布.我可以使IDataReader忽略列长度定义吗?
The same kind of question was asked here, but the solution never posted.Can I make an IDataReader ignore column length definitions?
推荐答案
答案在这里:
使用这种花括号语法来运行本机RDBMS(进度)功能，并修复数据之前(它到达ODBC):
Use this curly brace syntax to run a native RDBMS (Progress) function and fix the data before it hits ODBC:
SELECT { fn CONVERT(SUBSTRING( EMAIL,1,15) , SQL_VARCHAR) }
FROM SQL92.PROGRESSTABLE
我不敢相信人们会选择使用容易允许损坏数据的数据库.
I can't believe people choose to use a database that so easily allows corrupt data.
在某些背景下，如果将SSIS用于Progress，您可能会遇到两种错误:
As some background, you will likely encounter two kinds of errors if using SSIS against Progress:
(可以是任何数据类型)
(Could be any data type)
我想这意味着Progress会在后台自动更改数据类型.它不同于SSIS中保存的一个，它当然不喜欢.
I guess this means that the data type has been automatically changed behind the scenes by Progress. It differs to the one saved in SSIS which of course it doesn't like.
短期解决方案是通过双击源来打开包并刷新元数据
The short term solution is to open the package and refresh metadata by double clicking the source
另一个错误是:
例如，这意味着数据库中有371个字符长的数据，但数据字典却说它有324个字符长.
Which means for example there is data that is 371 chars long in the database but the data dicitionary says its 324 chars long.
这两种方法的长期解决方案是将所有内容包装在与上述类似的结构中-在将其转换为ODBC驱动程序以获取一致的数据类型之前对其进行转换.它当然会截断，但这可能比失败要好.
The long term solution to both of these is to wrap everything in a similar construct to above - cast it before it gets to the ODBC driver to get a consistent data type. It will of course truncate but that's probably better than it failing.
这篇关于SQL SSIS导入错误:表中的"PROGRESS openge"有线协议列具有超出其最大长度或精度的值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！