FIX: ODBC error when you use scalar functions to insert timestamp with fractional value

Article translations Article translations
Article ID: 259287 - View products that this article applies to.
This article was previously published under Q259287
This article has been archived. It is offered "as is" and will no longer be updated.
Expand all | Collapse all

SYMPTOMS

When you use ODBC scalar functions with the Microsoft Jet 4.0 OLE DB provider to insert a timestamp that contains a fractional (millisecond) value, the Jet ODBC driver (Odbcjt32.dll) may return the following error message:
Return: SQL_ERROR=-1
[Microsoft][ODBC Microsoft Access Driver] Reserved error (|); there is no message for this error.
Note The Jet 3.51 ODBC driver allows the statement to succeed, and returns SQL_SUCCESS_WITH_INFO and a "Data truncated (timestamp)" message.

CAUSE

The Jet OLE DB driver does not support storage of millisecond values.

RESOLUTION

To resolve this problem, install the latest Microsoft Jet 4.0 service pack. For more information, click the following article number to view the article in the Microsoft Knowledge Base:
239114 How to obtain the latest service pack for the Microsoft Jet 4.0 Database Engine

STATUS

Microsoft has confirmed that this is a bug in the Microsoft products that are listed at the beginning of this article. This problem was fixed in Jet 4.0 Service Pack 6 (SP6).

MORE INFORMATION

Scalar functions are inherent to ODBC, and are used when you need syntax or functionality that cannot be driver-specific. ODBC provides a large number of scalar functions for operations such as string conversion, date and time operations, mathematical functions, and so on.

In this circumstance, the error is generated when you use the ODBC scalar syntax to insert a timestamp that contains a fractional (millisecond) value. For example:
Insert into Table1 values (10, {ts '1999-09-02 12:12:12.121000'})
				
If you use the SQLBindParameter function instead of the scalar syntax, the statement succeeds, and returns SQL_SUCCESS_WITH_INFO and a fractional truncation message.

Properties

Article ID: 259287 - Last Review: February 28, 2014 - Revision: 3.6
APPLIES TO
  • Microsoft Data Access Components 2.1
  • Microsoft Data Access Components 2.1 Service Pack 2
  • Microsoft Data Access Components 2.1 Service Pack 1
  • Microsoft Data Access Components 2.1 Service Pack 2
  • Microsoft Data Access Components 2.5
Keywords: 
kbnosurvey kbarchive kbhotfixserver kbbug kbfix kbjet kbmdac210sp2fix kbmdac250fix kbqfe KB259287

Contact us for more help

Contact us for more help
Connect with Answer Desk for expert help.
Get more support from smallbusiness.support.microsoft.com