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

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).

Use the SQL Server OLE DB Provider.

This bug was corrected in Microsoft Data Access Components 2.5.

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.