Kbase P142363: Cannot read date field from Progress database into SQL Server database
| Autor |
Progress Software Corporation - Progress |
| Acesso |
Público |
| Publicação |
03/04/2009 |
|
Status: Unverified
SYMPTOM(s):
Cannot read date field from Progress database into SQL Server database
The following error message is displayed in SQL Server:
Error at destination for row number 3757, error encountered so far in this task:1 insert error, column 9 {movindate dbtype dbtimestamp status 6 data overflow} invalid character value for cast specified.
Data transfer stops after error message is received.
A query on the same data also fails when queried through a SQL Server 2005 linked server.
FACT(s) (Environment):
Data is pulled into SQL Server 2000 using Data Transformation Services (DTS).
Data is transferred via ODBC.
Data can be transferred successfully to Excel through ODBC.
Data can be read successfully in SQL Explorer (JDBC).
All Supported Operating Systems
Progress 9.x
OpenEdge 10.x
CAUSE:
The records that failed to transfer contained dates with three-digit years (for example, "01/01/001"). Progress interprets such years as valid (for example, "01/01/001" is read as "01/01/0001"), and the ODBC driver is able to transmit them, but SQL Server rejects the date as invalid.
FIX:
Find and correct the records containing dates with three-digit years in the Progress database before loading the records into the SQL Server database. The following ABL / 4GL code will find such records:
FOR EACH myTable WHERE YEAR(myDate) < 1000:
/* correct the date format or otherwise process the record */
END.