Import table from Access to SQL

[yuchieh]

I tried to import this table from Access to SQL using DTS Import Wizard. I did this procedure with other tables and it worked. However, with this one table, it gave me the error message "Error at destination for row number 263, invalid character value for cast specification."

I checked the data in the original table, my boss checked, but there is nothing wrong. If I deleted that row, it would say "error.. row number 262,..."

Any suggestion??

Thank you. Tina

 

[yuben]

Instead of trying to figure out why the DTS wizard is not working, which could take days, export your access table from within access to a textfile, excel file or some other file that the DTS also recognises. Then use the DTS to import the file.

If you try long enough you may figure out what's going wrong with importing directly from access but I don't recommend it. (I think you should drop any primary keys in the access table and recreate the key or you should compress the database.)

 

[tgriffin]

I had this happen quite a bit to me too. It usually happened on a date field in my situation. There was an extra space in the beginning or an extra 0 on the end or something similar. I changed the datatype to nvarchar or something like that and it worked fine then. That just requires some cleanup after you get it there. Using the text file would also work too. Check for valid dates in either Excel or Access.

Best of luck!

 

[yuchieh]

Yes, I checked the data and found some wrong in the date field. It works fine now after I cleaned up the data.

Thank you both very much.