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

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.net> wrote in message
newsTV9j.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
>

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@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

"Erland Sommarskog" <esquel@sommarskog.se> wrote in message
news:Xns9A0AEDA372B21Yazorman@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

"Erland Sommarskog" <esquel@sommarskog.se> wrote in message
news:Xns9A0AEDA372B21Yazorman@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! :-)