Page 1 of 2 12 LastLast
Results 1 to 15 of 19
  1. #1
    Join Date
    Jan 2003
    Posts
    11

    Unanswered: problem with transferring Access date field to SQL

    Hello
    I have asked a similiar question before but nothing seems to work...

    Here is the problem...

    Access2000 database...when I try and transport the tables to SQL I get an error:

    The transfer will start...and there are many date fields that are obviously being transferred already but then it craps out on about the 13,000 record...when I go to check that field that it indicates caused the error
    ...there is either a normal mm/dd/yyyy date there...or it may not have any date recorded.
    (others before this record may have nothing recorded and still copy!!)

    ...I made all the date fields in the SQL table datetime as opposed to the smalldatetime.

    Error says...
    error at Destination for row number 13182.
    Insert error...column 113 ('hmd2', DBTYPE_DBTIMESTAMP), status 6: Data overflow.
    Invalid character value for cast specification.

    Any help greatly appreciated.

  2. #2
    Join Date
    May 2002
    Posts
    299

    Re: problem with transferring Access date field to SQL

    It's best to format your source date data to "YYYYMMDD" which is always understood correctly by sqlserver. The error is quite common when your date is out of range due to mistranslation. Please take a look at "set dateformat" in sql book online for some info.
    --
    -oj
    http://www.rac4sql.net

  3. #3
    Join Date
    Feb 2003
    Posts
    6

    Re: problem with transferring Access date field to SQL

    Originally posted by Jacqueline
    Hello
    I have asked a similiar question before but nothing seems to work...

    Here is the problem...

    Access2000 database...when I try and transport the tables to SQL I get an error:

    The transfer will start...and there are many date fields that are obviously being transferred already but then it craps out on about the 13,000 record...when I go to check that field that it indicates caused the error
    ...there is either a normal mm/dd/yyyy date there...or it may not have any date recorded.
    (others before this record may have nothing recorded and still copy!!)

    ...I made all the date fields in the SQL table datetime as opposed to the smalldatetime.

    Error says...
    error at Destination for row number 13182.
    Insert error...column 113 ('hmd2', DBTYPE_DBTIMESTAMP), status 6: Data overflow.
    Invalid character value for cast specification.

    Any help greatly appreciated.

    Can you check the actual value you tried to insert?
    Since SQL Server datetime only takes year from 1753(?), you might have something that earlier than 1/1/1753...

  4. #4
    Join Date
    Jan 2003
    Posts
    11

    I did check the value trying to insert...

    The field that is causing the problem doesn't even have a value in it.
    If I enter a date value for the record it still stops at that record and date field.

  5. #5
    Join Date
    Jan 2003
    Posts
    11

    I have already transported other tables without problem...

    Hi
    Thanks for your response...I have already transported other tables with that date format...mm/dd/yyyy without problem...that is why I'm unclear as to what is causing the problem with this table.

  6. #6
    Join Date
    Feb 2003
    Posts
    109

    uh is it really a date format?

    uh in Access, is this really a date format, or is it text?

    i would just assume that it is an empty string, or a space that is trying to be converted to a date in sql server, and thats why it is erroring.
    Access 2002 ADP Rocks my World

    Long live SQL Server and 64bit Windows!!!

  7. #7
    Join Date
    Jan 2003
    Posts
    11

    It is...

    In the Access table it is of datatype datetime...with a format of mm/dd/yyyy.

  8. #8
    Join Date
    Feb 2003
    Posts
    109

    what is the error. how do you know what record errored.

    The field that is causing the problem doesn't even have a value in it.


    how do you know this?

    i dont want to sounds dumb-- but this column isnt a required field is it?
    Access 2002 ADP Rocks my World

    Long live SQL Server and 64bit Windows!!!

  9. #9
    Join Date
    Jan 2003
    Posts
    11

    Hi Aaron...

    The field isn't a required field.

    ...it is a table which is part of a family practice doctor's office...there are about 20 datetime fields in this table...and as the table copies to SQL...it gets up to this record 13,182 and then gives me the above mentioned error message...which shows the field in that record that caused the problem. (which in this case is the datetime field 'hmd2')

  10. #10
    Join Date
    Feb 2003
    Posts
    109

    then id start with things like

    a) dropping all the indexes in Access and possibly rebuilding
    b) import all of the objects from the (broken) Access database into a new container. (make a new database/MDB and reimport all of the objects)
    c) rebuilding the table in Access (running a make-table and rebuilding)
    d) examining if there are any constraints or triggers on the SQL Server side of the equation.
    e) if you have a timestamp column in sql, id consider dropping it until you can get the data into the db; or just drop it entirely (ive never found anything useful for that column)

    i would assume that it is just some general jet corruption error-- i havent seen any in years-- is it Access 97?

    sorry i cant diagnose this better-- i really dont know enough background info to diagnose this.

    ive working on 2,000 different mdbs during my career, and ive never found anything that i cant fix in jet.

    id love to look at the mdb if you want me to do that tonight..
    aaron_kempf@hotmail.com
    Access 2002 ADP Rocks my World

    Long live SQL Server and 64bit Windows!!!

  11. #11
    Join Date
    Mar 2003
    Location
    Bucharest, Romania
    Posts
    80

    Re: problem with transferring Access date field to SQL

    It's a common problem when transferin data from Access or from Visual FoxPro.
    The actual problem is the date interpretation in Access, VFP and SQL Server when two digit year date's are involved.


    Access accepts dates from January 1st, 100

    VFP accepts dates from January 1st, 1

    MS SQL Server accepts dates from January 1st, 1753 (see MSDN documentation: INVESTIGATING DATE ERRORS in Tehnical Resources)


    I'm 99.999% sure that one of your datetime fields in Access contains a date value that is prior to 01/01/1753. When you insert dates in four digit year format is always possible to enter year 0200 instead of 2003 or something like this.

  12. #12
    Join Date
    Jan 2003
    Posts
    11

    Thanks...

    Thanks for responding...there are about 20 date fields in this table...all formatted the same...mm/dd/yyyy.

    The problem is only with 1 of the date fields...experimenting, I deleted that field...and the table transported fine without errors.

    However, when the table (with the field in) transported it would give the error for a record that didn't even have a date in that field.

    So it's not that an erroneous date is causing the error because it gives the error on a field with no date value in it.

  13. #13
    Join Date
    Feb 2003
    Posts
    109

    clarification

    do you allow nulls?

    do you use triggers on this table?
    Access 2002 ADP Rocks my World

    Long live SQL Server and 64bit Windows!!!

  14. #14
    Join Date
    Jan 2003
    Posts
    11
    Yes it allows nulls and no triggers used on the table.

    It has copied 13000 records before it gives the error...and a lot of those records have null date fields but seem to copy fine!

    Soooo frustrating!

  15. #15
    Join Date
    Mar 2003
    Posts
    3

    Red face

    Did you ever find a resolve for this issue. I'm experiencing the same issues (i.e. one table giving the error message you had on import, although other tables with similar date/time formats are importing fine) and its driving me crazy!!!

    Appreciate any insights you may have had since posting this problem...

Posting Permissions

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