Import ing Times into SQL Server

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

Import/Export (DTS) and Replication (2000)

Import ing Times into SQL Server

Author

Topic

bspahr75
Starting Member

5 Posts

Posted - 2007-08-01 : 12:44:36

I am trying to import an access database into SQL Server 2000.
I am using the DTS Import wizard to to this.

Most of the tables import fine, but a few are not importing.

Here is the error message.

Table Name Here
Error at destination for Row number 1. Error encountered so far in this task:

1. Insert error, column 7 (‘CallTime’, DBTYPE_DBTIMESTAMP), status 6: Data overflow. 
    Invalid character value for cast specification.

Here is an example of an entry in one of the 'Calltime' rows:
7:45:30 PM . This is a date/time field in Access.

I looked at this SQL table after the import finished and there was no data. Also, it tried to import as a smalldatetime data type.

Is there something I can do in Accesss to fix this problem?

ashley.sql
Constraint Violating Yak Guru

299 Posts

Posted - 2007-08-09 : 11:23:10

try importing as varchar and then convert it to datetime and see if it works.

Ashley Rhodes

bspahr75
Starting Member

5 Posts

Posted - 2007-08-09 : 12:39:22

SQL server was trying to import datetime fields as smalldatetime.
I changed them to datetime in SQL before importing and it worked fine. Can you dig it?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-08-09 : 15:15:31

Yes. You only have to read about DATATYPES in Books Online to figure this out.

E 12°55'05.25"
N 56°04'39.16"

ashley.sql
Constraint Violating Yak Guru

299 Posts

Posted - 2007-08-09 : 15:35:58

Can you dig it?

THATS RUDE. Why did you post before you tried everything.

Ashley Rhodes

bspahr75
Starting Member

5 Posts

Posted - 2007-08-09 : 16:19:26