fractional truncation message

Collapse
This topic is closed.
X
X
Collapse
 
  • Page of 1
  • Time
  • Show
Clear All
new posts
Previous template Next
  • Neil
    #1

    fractional truncation message

    Using SQL 7 with an Access 2000 MDB front end with ODBC linked tables.

    Have a DateModified field which previously was smalldatetime. Changed over
    the weekend to datetime. Field is updated with a trigger that sets value to
    GetDate().

    One record in the table now cannot be updated. In Access, get message:

    [microsoft][odbc sql server driver]fractional truncation (#0)
    [microsoft][odbc sql server driver]timeout expired (#0)

    In QA (using Update), no message; just hangs.

    Note that many records in the table have been modified since the change, and
    there doesn't seem to be a problem. It just seems to be this one record
    (possible others that we haven't come across yet) that will not allow
    updates.

    Any ideas how to resolve this?

    Thanks!

    Neil


    Tags: None
    • Neil
      #2
      Re: fractional truncation message

      Forgot to note in the below:

      * The Update query I'm running in QA just sets a field value to it's current
      value -- i.e., Update MyTable Set Field1=100 Where PKID=12345. In other
      words, not touching that date modified field at all.

      * I tried copying the record to a new record. The new record, with all the
      same data, works fine, and allows the update.

      Thus, I'm concluding that this one record got corrupted somehow when I was
      modifying the field type. How can I resolve without recreating the record
      (or is that the only way)?

      Thanks!



      "Neil" <nospam@nospam. netwrote in message
      news:DTV9j.228$ se5.71@nlpi069. nbdc.sbc.com...
      Using SQL 7 with an Access 2000 MDB front end with ODBC linked tables.
      >
      Have a DateModified field which previously was smalldatetime. Changed over
      the weekend to datetime. Field is updated with a trigger that sets value
      to GetDate().
      >
      One record in the table now cannot be updated. In Access, get message:
      >
      [microsoft][odbc sql server driver]fractional truncation (#0)
      [microsoft][odbc sql server driver]timeout expired (#0)
      >
      In QA (using Update), no message; just hangs.
      >
      Note that many records in the table have been modified since the change,
      and there doesn't seem to be a problem. It just seems to be this one
      record (possible others that we haven't come across yet) that will not
      allow updates.
      >
      Any ideas how to resolve this?
      >
      Thanks!
      >
      Neil
      >

        Comment

        • Erland Sommarskog
          #3
          Re: fractional truncation message

          Neil (nospam@nospam. net) writes:
          Using SQL 7 with an Access 2000 MDB front end with ODBC linked tables.
          >
          Have a DateModified field which previously was smalldatetime. Changed
          over the weekend to datetime. Field is updated with a trigger that sets
          value to GetDate().
          >
          One record in the table now cannot be updated. In Access, get message:
          >
          [microsoft][odbc sql server driver]fractional truncation (#0)
          [microsoft][odbc sql server driver]timeout expired (#0)
          Note that these errors are from ODBC SQL Server driver, not from
          SQL Server.
          In QA (using Update), no message; just hangs.
          Did you close down the Access application? While hanging in QA is
          consistent with he "timeout expired" in Access (QA does not have
          any timeout by default), my suspicion is that the first error
          left the row lock and blocked.

          You can use sp_who2 to determine if you have any blocking. If there a
          non-zero value in the Blk column, that is the spid of the blocker.
          Examine further to see which application it's running.



          --
          Erland Sommarskog, SQL Server MVP, esquel@sommarsk og.se

          Books Online for SQL Server 2005 at
          http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx

          Books Online for SQL Server 2000 at
          http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

            Comment

            • Neil
              #4
              Re: fractional truncation message


              "Erland Sommarskog" <esquel@sommars kog.sewrote in message
              news:Xns9A0AEDA 372B21Yazorman@ 127.0.0.1...
              Neil (nospam@nospam. net) writes:
              >Using SQL 7 with an Access 2000 MDB front end with ODBC linked tables.
              >>
              >Have a DateModified field which previously was smalldatetime. Changed
              >over the weekend to datetime. Field is updated with a trigger that sets
              >value to GetDate().
              >>
              >One record in the table now cannot be updated. In Access, get message:
              >>
              >[microsoft][odbc sql server driver]fractional truncation (#0)
              >[microsoft][odbc sql server driver]timeout expired (#0)
              >
              Note that these errors are from ODBC SQL Server driver, not from
              SQL Server.
              >
              >In QA (using Update), no message; just hangs.
              >
              Did you close down the Access application? While hanging in QA is
              consistent with he "timeout expired" in Access (QA does not have
              any timeout by default), my suspicion is that the first error
              left the row lock and blocked.
              >
              You can use sp_who2 to determine if you have any blocking. If there a
              non-zero value in the Blk column, that is the spid of the blocker.
              Examine further to see which application it's running.
              >
              >

              Yeah, you were right. I just tried it, now while everyone's out of the
              database, and it updated fine. I also downloaded a backup to my development
              machine, and it was fine there too.

              The reason I thought it was corrupted was because, from what I read, the
              "fractional truncation" message has to do with date/time fields. And since I
              had just changed the datemodified field from smalldatetime to datetime over
              the weekend, it seemed like too much of a coincidence that this error had
              nothing to do with that, especially since I've never seen this error before
              in all the years that we've had this configuration (about 7 years). So there
              definitely seemed to be a correlation.

              Neil


                Comment

                • Neil
                  #5
                  Re: fractional truncation message


                  "Erland Sommarskog" <esquel@sommars kog.sewrote in message
                  news:Xns9A0AEDA 372B21Yazorman@ 127.0.0.1...
                  Neil (nospam@nospam. net) writes:
                  >Using SQL 7 with an Access 2000 MDB front end with ODBC linked tables.
                  >>
                  >Have a DateModified field which previously was smalldatetime. Changed
                  >over the weekend to datetime. Field is updated with a trigger that sets
                  >value to GetDate().
                  >>
                  >One record in the table now cannot be updated. In Access, get message:
                  >>
                  >[microsoft][odbc sql server driver]fractional truncation (#0)
                  >[microsoft][odbc sql server driver]timeout expired (#0)
                  >
                  Note that these errors are from ODBC SQL Server driver, not from
                  SQL Server.
                  >
                  >In QA (using Update), no message; just hangs.
                  >
                  Did you close down the Access application? While hanging in QA is
                  consistent with he "timeout expired" in Access (QA does not have
                  any timeout by default), my suspicion is that the first error
                  left the row lock and blocked.
                  >
                  You can use sp_who2 to determine if you have any blocking. If there a
                  non-zero value in the Blk column, that is the spid of the blocker.
                  Examine further to see which application it's running.
                  >
                  >
                  P.S. Thanks for your assistance! :-)


                    Comment

                    Previous template Next
                    Working...