| Author |
Topic |
|
MirandaJ
Starting Member
35 Posts |
 Posted - 2005-08-19 : 15:05:14
|
| We are attempting to import data from Microsoft Access databases to SQL Server 2000 using the DTS Import/Export Wizard. I have a few questions. 1) Some of the Access tables have a single field for combined date time with the Access data type set to Date/Time and no formatting set. When the conversion happens these become text data type in the SQL Server. Is there a way to have these become either the smalldatetime SQL Server data type or datetime SQL Server data type? 2) On some other Access Tables with the data type set again to Date/Time again no formatting is set but some columns are dates with the date in the Short Date format, and some columns are times in the Short Time format. Some of these columns may contain null values. This is causing the following error. Error at Destination for Row number 1. Errors encountered so far in this task: 1.Insert error column 152 ('ViewMentalTime', DBTYPE_DBTIMESTAMP), status 6: Data overflow.Insert error column 150 ('VRptTime', DBTYPE_DBTIMESTAMP), status 6: Data overflow.Insert error column 147 ('ViewAppTime', DBTYPE_DBTIMESTAMP), status 6: Data overflow.Insert error column 144 ('VPreTime', DBTYPE_DBTIMESTAMP), status 6: Data overflow.Insert error column 15 ('Time', DBTYPE_DBTIMESTAMP), status 6: Data overflow.Invalid character value for cast specification.Invalid character value for cast specification.Invalid character value for cast specification.Invalid character value for cast specification.Invalid character value for cast specification.We thought maybe it was due to the naming of the one field as "Time" so we changed the name of that field but again the same error occurs but with the new field name. When we click on the transform button to view the data types none of them are listed as timestamp.Is it possible that the DB Admin saved some tables and then later on tried to add tables to the same database that is causing this error? Are we better off trying to import the table structure first and then fill the tables later? Any help is appreciated.Miranda |
|
|
ajthepoolman
Constraint Violating Yak Guru
384 Posts |
Posted - 2005-08-19 : 17:36:18
|
| In my previous experience in importing Access tables, the dates were what gave me absolute fits. Everything I had to pull in would default to the wrong date format. I had to use the Transform button and change them all to datetime. At that point they pulled in. If that won't work, could you use a varchar(30) field and then manipulate it once it is in the SQL table to a date or time format that you want?AjHey, it compiles. |
 |
|
|
MirandaJ
Starting Member
35 Posts |
Posted - 2005-08-19 : 17:48:53
|
| That was my thoughts on the matter. However when the DB Admin clicked on the transform button it did not allow us to edit any of the fields. I am thinking he first needs to mark the radio button next to Create destination table. Then come back later on and select the radio button next to Append rows to destination table. Has that been the experience of others?Miranda |
|
|
|
ajthepoolman
Constraint Violating Yak Guru
384 Posts |
Posted - 2005-08-21 : 01:01:48
|
| Append will add the same records over and over each time you run your migration. I am assuming that you don't want multiple sets of the same data in the table. I don't have it in front of me, but I believe there is an option to drop and recreate the table each time. One nice thing about the wizard is that you won't have to specify the new date formats each time you run it even if it is dropping and recreating the table. If you tell it to copy the source table and change the field types once, it will remember that each time you run the wizard.AjHey, it compiles. |
|
|
|
elwoos
Master Smack Fu Yak Hacker
2052 Posts |
Posted - 2005-08-22 : 02:56:54
|
quote:
Invalid character value for cast specification.
This is telling you that SQL server cannot tranlate one or more characters into the datatype that it is trying to convert to. For example if you are putting some numbers into a field one of them may contain the letter o rather than the digit 0 (I get this one a lot). You may find that you need to 'clean' part of the datasteveAlright Brain, you don't like me, and I don't like you. But lets just do this, and I can get back to killing you with beer. |
|
|
|
MirandaJ
Starting Member
35 Posts |
Posted - 2005-08-22 : 10:11:09
|
quote:
Originally posted by elwoos
quote:
Invalid character value for cast specification.
This is telling you that SQL server cannot tranlate one or more characters into the datatype that it is trying to convert to. For example if you are putting some numbers into a field one of them may contain the letter o rather than the digit 0 (I get this one a lot). You may find that you need to 'clean' part of the data
I was pretty sure that is what that meant. I have come across similar errors when debugging asp.net applications. You will notice that every single one of these is of an access date/time data type. What I am wondering is why It is putting it in as a timestamp. We don't want a timestamp. We will want a datetime or smalldatetime data type on the sql server. Miranda |
|
|
|
elwoos
Master Smack Fu Yak Hacker
2052 Posts |
Posted - 2005-08-23 : 03:23:33
|
| I'd check the range of the dates that are in Access, if someone has put in a date with the month and date swapped or perhaps null dates or two digit years. If you don't want the time part from access it might be wise to strip it out before you try to insert it into SQL server. It may be that the time part is what is causing the data overflow error - you may need to alter your dts package to do this. It's wise to check over the package anyway just to check that its not trying to do something daftsteveAlright Brain, you don't like me, and I don't like you. But lets just do this, and I can get back to killing you with beer. |
|
|
|
silas2
Yak Posting Veteran
65 Posts |
Posted - 2005-08-25 : 14:47:02
|
| I've just been doing this, and I gave up with the upsizing wizard for transfering data because of, not just the dates, but also other MDB-SQL Server data differences (double/single quotes, -1 = true in Access,etc.), and bit the bullet and wrote a tool for transfering data(VB.Net/ADO.net) rec by rec, allowing you to build the insert string for each record to get the right target format - ADO.net is actually quite fast so the performance isn't bad (100k ish record quantities). It took a few hours but its better than running the Upsizing Tool endlessly with your fingers crossed. |
|
|
|
martinamullin
Starting Member
1 Post |
Posted - 2007-09-20 : 06:39:57
|
| hey, I was getting the same error and was being given an error message on line xxx. When I went to line xxx the data was all fine. The problem was corrupt data but the application strangely chose line xxx as the location to crash rather than the line the dodgy data was on. I sorted my dates A-Z and could see the bad data. Fixed it and everything worked fine. Lucky for me cos I was just about to write my own transfer code. Thanks for the help |
|
|
|
|
Error Importing from Access to SQL Server