IMPORT ERROR PLEASE HELP - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

IMPORT ERROR PLEASE HELP

Author

Topic

dragonsbb16
Starting Member

3 Posts

Posted - 2006-05-18 : 15:15:42

Hello...
i am getting this error when importing tables via the DTS...i have gotten this s few times before and found it was bad data...but this record the data is good..and i cant seem to figure out what SQL is telling me.

ERROR MSG*******
Error at Destination for number 5568. Errors encountered so far in this task:1. Insert Error, column 3('Field3', DB_TYPE_DBTIMESTAMP), status 6: Data overflow. Invalid character value for cast specification.

Here is Record 5568 in my Access DB
Key 10232 (Long Integer)
ShipNumber 205789 (String)
Field3 11/17/04 (Date/Time)-Short Date
Ship_Time 13:17 (String)

Here is SQL Server Tbl Layout

Key (int)-4
ShipNumber (nvarchar)-22
Field3(smalldatetime)-4
Ship_Time (nvarchar)-5

What in the world is wrong b/c i am not seeing it....Please help thanks.

JIM

nr
SQLTeam MVY

12543 Posts

Posted - 2006-05-19 : 06:17:21

I would import dates into character columns then change them afterwards.
It's sometomes due to the date dormat - thinking the day is a month but that's unlikely in your situation and that it was ok up to row 5568.
It also looks like that should have no problem fitting in to a smalldatetime.
Dump them all into character then you can look to see what wouldn't convert.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

cmdr_skywalker
Posting Yak Master

159 Posts

Posted - 2006-05-19 : 08:32:27

the smalldatetime range is January 1, 1900 to June 6, 2079. The Record 5568 is not the record that you see in the Access database window but what SQL considers record 5568 during IMPORT. Run a query in the Access database:

SELECT *
FROM
WHERE Field3 < #1/1/1900#

and see if it returns the erring record.

May the Almighty God bless us all!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources