Previous Topic

Next Topic

Book Contents

Book Index

Load problems

Problems when Source and Target are in the same database

see Data Loader error with MS SQL Server and Sybase

Unable to start transaction

see MS Access: load unable to start

Excel Dates

MS Excel appears to have a bug with dates prior to 1 Mar 1900. As Excel incorrectly thinks that 29 Jan 1900 is a leap day, all dates prior to this are one day out of sync between how they appear in Excel and how they appear in AQT. If you are loading date values from an Excel spreadsheet into a table, you may notice this problem.

DB2 Long Varchar for Bit Data

You may run into problems loading data into these columns. When running the load, the ODBC driver misreports the datatypes of these columns. We have no resolution to this.

Use of Parameter Markers

The Data Loader makes extensive use of parameter markers. However, some ODBC Drivers cannot process parameter markers successfully. For these databases, you can specify the Load using Insert statements option. This is not as fast as the parameter-marker method, however for some databases it provides a more reliable method for loading data.

Oracle and DB2 LOBs

Both Oracle and DB2 cannot load zero-length LOBs. The ODBC Driver hangs or crashes when this is attempted. To circumvent this, for these databases AQT will load a single blank into the LOB.

Loading Dates from Excel

Excel dates often come across to AQT in dd/mm/yyyy format, which won't load into most databases. The solution to this is to use the date function to convert the date to yyyy-mm-dd format. See Map to target from Source for information on the date function.

Loading Dates into MS Access

When loading a date into MS Access, you may get the message:

22005;Invalid character value for cast specification (null)

This error is quite misleading; the cause of the problem is the date is not in the correct format.

Loading Numeric Values into Oracle when the Decimal Separator is a Comma

Many countries use a decimal separator of comma. A numeric value will be displayed as 1234,56. However the period must still be used when loading the numeric value. For instance, you must specify:

insert into numeric_table values(1234.56)

even when your decimal separator is a comma. If you use values(1234,56) you will get a syntax error.

As a result, the Data Loader will always specify numeric values with a period rather than a comma.

The only place this doesn't work is with Oracle when your NLS_NUMERIC_CHARACTERS is set to ',.'. This specifies to Oracle that the decimal separator is comma, and in this case Oracle will give an error when AQT passes a numeric value with a period.

There are two ways to get around this: