Hi there,
I've got an error while trying to import a table from MS Access to SQL Server 2000.
"Error at Destination for Row number 231. Error encountered so far in this task: 1. Insert error, column 6('DATE', DBTYPE_DBTIMESTAMP), status 6: Data overflow. Invalid character value for case specification."
I've changed the data type from smalldatetime to datetime but it still doesn't work.
What should I do, please anyone help.
Thanks

make sure the Dates in your access table are true valid date

for example: These are not valid dates
08-32-2004
02-31-2004

Same problem here.

For example, the Access table affected has 40 rows, of which 5 have dates in them, which are in the format of dd/mm/yyyyy and the remainder are just empty. Interestingly, the same rows have another date column in the same format, which works just fine? The only noticeable difference seems to be that the problem column (in this table) contains dates that are in the future - as far as 31/03/2093.

I am using DTS to import the Access DB into SQL and in all honesty am not any kind of expert with poking around in the DTS configuration; though I am familiar with SQL.

On a table with 5 rows from 40 records, I can simply recreate the entries, but some of the tables affected by this problem have many thousands of records and I just wouldn't have the time to enter that amount of data.

Any advice welcomed.

Regards,

Sean Anderson

Further on this matter:

It seems that the problem occurs on a 'Date/Time' column if (a) the column contains only a time or (b) if the column contains a date that is "far" into the future.

I have valid columns containing dates in 2093, which are failing and I have other columns that relate to opening hours - 8:30AM which have been saved in Date/Time as a time.

Whilst I appreciate that SQL will struggle as it would expect 8:30 on a particular date, but doesn't know what date... The data, in the application is valid and needs to be handled as expected.

Regards,

Sean Anderson