Unresolvable DTS Error 1

[dillbeck]

I get the following error when running DTS for a specific table in an Access 97 database: "Error at Destination for Row 26. Errors encountered so far in this task: 1.
Invalid character value for cast specification". When I look at row 26 in the Access table I see nothing wrong with the data. It seems to match by datatype. The table is not predefined in SQL Server. I am not doing any transformations. I have no idea what "cast specification" means. This is the very last table I need to convert in the very last Access database from which I am moving all tables to SQL Server 7.0. Then I'll been done with this long project. Any ideas of where to look for a solution?

 

[MaffewW]

I'm pretty certain this sounds like some kind of conversion error, possibly on a datetime field. Have you got any datetime fields in this table? If so, have a look at line26. could it be interpreted differently? eg are the previous 25 lines all less than 12 on the day part and line 26 could be UK 14/01/2003. If SQL Server has interpreted all the previous lines as mm/dd/yyyy then something like this could cause the problem.

Check over line 26 thouroughly, there is some kind of datatype difference somewhere than the previous 25 lines I think. Let us know if you find anything with this line and need a workaround

Matt

Brighton, UK

 

[dillbeck]

The table in question has four date-type fields: Start Date, Start Time, End Date, End Time. The data was created in Access, which has it's own formatting controls. Where data is found in these fields, it appears to be valid. Only the Start Date is a required field. The 26th row date data looks fine. The date fields are formatted mm/dd/yyyy. The time fields are defined as Short Time. All other fields in the table are either Text(255 bytes or less) or Number(1 double, 1 currency and 4 integer).
I did discover what the CAST command was about, but I am not using it and the table is not predefined in SQL. I'm just running a plain vanilla DTS procedure through the Wizard.
I'm beginning to suspect data corruption in the Access table because I did change the sequence of some fields prior to the DTS attempt. My very first attempt at DTS failed because SQL did not like it that I had a "Yes/No" field as part of the primary key. I restructured the table so that a Text field and the Start Date field made up the key. Then I got the error I brought to Tek-Tips.

 

[MaffewW]

ok, I'm a a bit stuck what could be wrong with row 26 then, I'm 99% certain SQL and Access are interpreting the data in one (or more) of the fields differently though. It might import from the csv file straight to SQL Server

Next thing I would try, is to export the file to a txt/.csv file and have a look at the data there. It may be that you can see something wrong with the 26th line then when viewed in notepad. If the data isnt of a sensitive nature or too big perhaps you could post lines 24 to 26?

Matt

Brighton, UK

 

[dillbeck]

Thanks for your suggestions. I will create a CSV export out of Access and take a look at the data and also try to import it.

 

[dillbeck]

I exported the table as a text file with the CSV format. I examined the data in Excel. I saw nothing unusual. I then imported the CSV file into SQL Server. The result wasn't pretty (fld1, fld2,etc- all text and all at 255 bytes), but I ultimately got it right.

Thanks again for your suggestions!

 

[charuguliani]

CAST takes an expression and converts to the target datatype. You could also use the CONVERT function. CAST is an ANSI SQL-92 standard so I try to use it everywhere except date conversions. CONVERT does a much better job on dates.