Data Transfer Issue 1

[logo]

I am trying to upgrade from ACCESS 2000 to SQL Server 7. I am using the DTS wizard, when I transfer from Access->SQL I receive this error:

Error at destination for Row number 1....invalid character value for cast specification.

My Access table does not have any illegal entries...I'm not sure what this error means? Can anyone help me?

 

[tlbroadbent]

It is likely, that the reference to CAST in the error message means that a conversion from the Access data type to a SQL data type was attempted but could not be carried out because the data could not be converted to the destination data type. Did the error message tell you which column and data types were in error?

What are the data types of the Access database? Do the data types of the corresponding columns in the SQL Server match? One typical problem is the conversion of an Access datetime column to a SQL smalldatetime column where data may be truncated.

http://www.ssw.com.au/ssw/UpsizingPRO/DTSProblem.htm

Horror Stories Getting Access Time Data into SQL Server via DTS

Be aware that DTS doesn't handle data transfer for Access as well as the upsizing wizard. Have you considered using the Wizard instead of DTS?

Check the following links for more information.

ACC2000: How to Use Data Transformation Services (DTS) to Export Data from a Microsoft Access Database to a SQL Server Database

http://support.microsoft.com/support/kb/articles/Q250/6/16.ASP

Using the Microsoft Access 2000 Upsizing Tools with SQL Server 7.0 or MSDE

http://msdn.microsoft.com/library/en-us/dnsqlsm/html/upsize00.asp

Upsizing - Data Type Mapping

http://www.ssw.com.au/ssw/UpsizingPRO/UpsizingDataTypeMapping.htm

More Upsizing Links

http://www.ssw.com.au/ssw/UpsizingPRO/frmLinks.htm

Terry L. Broadbent
FAQ183-874 contains tips for posting questions in these forums.
NOTE: Reference to the FAQ is not directed at any individual.