Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 3 of 3
  1. #1
    Senior Coder
    Join Date
    Dec 2002
    Location
    Arlington, Texas USA
    Posts
    1,065
    Thanks
    4
    Thanked 8 Times in 8 Posts

    Error Importing from Access to SQL Server

    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. The field is filled via an ASP script using the Now() function. 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 a 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.
    Last edited by miranda; 08-19-2005 at 08:57 PM.

  • #2
    Senior Coder
    Join Date
    Apr 2003
    Location
    England
    Posts
    1,192
    Thanks
    5
    Thanked 13 Times in 13 Posts
    ive never used the DTS Import/Export Wizard but since all of these are dates is it possible that you are using smalldatetime instead of datetime so its losin accuracy.. or its just in the wrong format (when inserting/updating mssql i have to use 'MM-dd-yy' although i get an overflow if i dont use '' so that could be antoher reason).. tbh im grabbin at straws

  • #3
    Senior Coder
    Join Date
    Dec 2002
    Location
    Arlington, Texas USA
    Posts
    1,065
    Thanks
    4
    Thanked 8 Times in 8 Posts
    Our Admin is letting the wizard determine the datatypes... I think that is the problem. It looks like the wizard is trying to make them a timestamp data type instead of a datetime data type. That is the only thing i can think of. I copied one of the Access Databases to my jump drive and brought it home and was able to upsize using the Access upsizing wizard. I will discuss this with him tomorrow.


  •  

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •