Microsoft KB Archive Search

Notice: This website is an unofficial Microsoft Knowledge Base (hereinafter KB) archive and is intended to provide a reliable access to deleted content from Microsoft KB. All KB articles are owned by Microsoft Corporation. Read full disclaimer for more details.

  1. Home
  2. PRB: ADO Does Not Support Decimal Numbers with Precision That Is Greater Than 28

PRB: ADO Does Not Support Decimal Numbers with Precision That Is Greater Than 28

View products that this article applies to.

Symptoms

When ActiveX Data Objects (ADO) queries a table that contains a numeric or decimal value with a precision that is greater than 28, you receive the following error message:
Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done.

↑ Back to the top

Cause

ADO returns all values in VARIANTs. The decimal value of a VARIANT is documented to hold a decimal value with a maximum precision of 28:
Decimal variables are stored as 96-bit (12-byte) unsigned integers scaled by a variable power of 10. VT_DECIMAL uses the entire 16 bytes of the Variant.
Data types such as Oracle NUMBER can have a precision of up to 38. However, ADO cannot support these large values because of the restriction in a VARIANT.

↑ Back to the top

Resolution

An application can still work with these large numbers through OLE DB. By writing an OLE DB DLL that Microsoft Visual Basic (or any other client application) can call, an application can fetch these large numbers and can store the results in a double. For additional information about how to retrieve such large numbers and how to store large numbers in a double, click the article number below to view the article in the Microsoft Knowledge Base:
229884 HOWTO: Use OLE DB DBTYPE_VARNUMERIC

↑ Back to the top

More information

Use the following Visual Basic code to reproduce the behavior. Make sure that you modify the connection parameters as necessary for your environment before you run the code. 
Sub Main()
   On Error GoTo AdoError
   Dim Con As New ADODB.Connection
   Dim Cmd As ADODB.Command
   Dim rs As ADODB.Recordset
   Dim er As ADODB.Error

   Con = CreateObject("ADODB.Connection")

   Con.ConnectionString = "Provider=MSDAORA;User ID=myID;password=myPwd;Data Source=myoratns;Persist Security Info=True"
   Con.Open

   On Error Resume Next
   Con.Execute ("drop table y")

   On Error GoTo AdoError
   Con.Execute ("Create table y (x number(38, 4))")

   Con.Execute ("Insert Into y(x) Values(0.1234e33)")

   Set Cmd = CreateObject("ADODB.Command")
   Cmd.ActiveConnection = Con
   Cmd.CommandText = "select x from y"
   Set rs = Cmd.Execute
   MsgBox (rs.Fields(0).Value)
   Exit Sub

AdoError:
   For Each er In Con.Errors
       MsgBox (Err.Description)
   Next

↑ Back to the top

Keywords: KB327557, kbprb

↑ Back to the top

Article Info
Article ID : 327557
Revision : 2
Created on : 5/28/2003
Published on : 5/28/2003
Exists online : False
Views : 358