I am trying to import data from Access 2000 to SQL2K. I am receiving the following error

Error at destination Row number 387. Errors encountered so far in this task: 1
Insert error, column 12 ('Birthdate', DBTYPE_DBTIMESTAMP), status 6: Data overflow
Invalid character value for cast specification

This column is going from Access Date/Time to SQL2K smalldatetime.

I have checked the data in the field and it was fine, not out of range. I have tried deleting the row 386 and 387 and I still get the same error, even though the data now in row 387 has changed. I also tried changing the data format on my regional settings and got the same error

I get the same error on other tables that include a date field

Any suggestions? Thank yo

Randy

Try taking the date from Access and enter it directly into SQL Server. Does
it work? The error suggests you have either too small or too large a date
coming in or something like dd/mm is mm/dd or the other way around.

--
--

Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP)
www.SQLDTS.com - The site for all your DTS needs.
I support PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org


"Randy" <anonymous (AT) discussions (DOT) microsoft.com> wrote

though the data now in row 387 has changed. I also tried changing the data
format on my regional settings and got the same error.

Another thing to look for is that Access supports dates back
to 1/1/100 and SQL Server supports dates back to 1/1/1900
for smalldatetime. I've seen people get burned by typos in
their Access date fields which caused these problems, i.e.
years of 203 instead of 2003.

-Sue

On Fri, 9 Apr 2004 10:01:04 -0700, "Randy"
<anonymous (AT) discussions (DOT) microsoft.com> wrote:

Thank you. I sorted the birthdate column and found dates as old as the year 195. They were not close to the row in the error message but they were in there none the less

Thanks again

Randy Jones

Thank you. I sorted the birthdate column and found dates as old as the year 195. They were not close to the row in the error message but they were in there none the less

Thanks again

Randy Jone

----- Sue Hoegemeier wrote: ----

Another thing to look for is that Access supports dates bac
to 1/1/100 and SQL Server supports dates back to 1/1/190
for smalldatetime. I've seen people get burned by typos i
their Access date fields which caused these problems, i.e
years of 203 instead of 2003

-Su

On Fri, 9 Apr 2004 10:01:04 -0700, "Randy
<anonymous (AT) discussions (DOT) microsoft.com> wrote