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.

FIX: SQL Server 2000 BIGINT Data Type Is Mapped by MSDASQL to DBTYPE_STR Instead of DBTYPE_I8 in Schema


View products that this article applies to.

This article was previously published under Q245355

↑ Back to the top


Symptoms

When you open a Schema Recordset, such as adSchemaColumns or adSchemaProviderTypes, against Microsoft SQL Server 2000, the BIGINT data type is mapped to adChar (129) instead of adBigInt (20).

↑ Back to the top


Resolution

Use the SQL Server OLE DB Provider.

↑ Back to the top


Status

This bug was corrected in Microsoft Data Access Components 2.5.

↑ Back to the top


More information

Steps to Reproduce Behavior

  1. On a computer with MDAC 2.0 or 2.1, use Microsoft Visual Basic 5.0 or 6.0 to create a new Standard EXE project.
  2. From the Project menu, select References and add a reference to either the Microsoft ActiveX Data Objects 2.0 Library. or the Microsoft ActiveX Data Objects 2.1 Library.
  3. Add a command button (Command1) and the following code to the default form.

    Note You must change User ID <User ID> and password <Strong Password> to the correct values. Make sure that User ID has the appropriate permissions to perform this operation on the database.
    Option Explicit
    
    Private Sub Command1_Click()
    Dim cn As ADODB.Connection, rs As ADODB.Recordset
      Set cn = New ADODB.Connection
    '  cn.Open "Provider=SQLOLEDB;Data Source=myserver;user id=<User ID>;password=<Strong Password>;database=pubs"
      cn.Open "Provider=MSDASQL;Driver={SQL Server};server=myserver;uid=<User ID>;pwd=<Strong Password>;database=pubs"
      cn.Execute "CREATE TABLE bigint_test (ID1 BIGINT, ID2 INT, ID3 SMALLINT, ID4 TINYINT)"
      Set rs = cn.OpenSchema(adSchemaColumns, Array(Empty, Empty, "bigint_test"))
      Do While Not rs.EOF
        Debug.Print rs!COLUMN_NAME, rs!DATA_TYPE
        rs.MoveNext
      Loop
      rs.Close
      cn.Execute "DROP TABLE bigint_test"
      cn.Close
    End Sub
    					
    Change the connect string to point to a Microsoft SQL Server 2000 database.

  4. Run the application and click the command button. The following list of data types will be returned:
    ID1            129      <-- adChar (incorrect)
    ID2            3        <-- adInteger
    ID3            2        <-- adSmallInt
    ID4            17       <-- adUnsignedTinyInt
    						
  5. Comment the cn.Open line and uncomment the alternate code that uses the SQLOLEDB provider. This time, the correct data is output:
    ID1            20      <-- adBigInt
    ID2            3 
    ID3            2 
    ID4            17 
    						
  6. Run the original code on a computer with MDAC 2.5 installed. The correct output will also be observed.

↑ Back to the top


Keywords: kbbug, kbfix, kbmdac250fix, kbmdacnosweep, KB245355

↑ Back to the top

Article Info
Article ID : 245355
Revision : 5
Created on : 12/3/2003
Published on : 12/3/2003
Exists online : False
Views : 503